杭州 高端网站建设,网站制作和设计需要多少钱,百斯特网站建设,做网站去哪个平台java easyPOI导出一对多数据#xff0c;设置边框#xff0c;字体#xff0c;字体大小 需求总是千奇百怪#xff0c;解决的方式也可以是多种多样。 今天碰到导出excel是一对多结构的#xff0c;以往导出的数据都是一条一条的#xff0c;所以采用的是比较方便简单的方法eas…java easyPOI导出一对多数据设置边框字体字体大小 需求总是千奇百怪解决的方式也可以是多种多样。 今天碰到导出excel是一对多结构的以往导出的数据都是一条一条的所以采用的是比较方便简单的方法easyExcel今天猛然碰到一对多导出虽然用easyExcel也可以但是相对比较麻烦没有easyPOI快捷之前有自己写过导出excel一个表格一个表格画但是太麻烦。今天正好需求不急就慢慢研究用easypoi导出写出通用方法以后遇到类似的就好办多了直接调方法。废话不多说先上效果图
首先是实体中设置
public class YjwzStockInVO extends TYjwzStockIn implements Serializable {//注意needMerge true 必加不然合并的单元格边框不会合并需要导出的实体加Excel注解不需要导出的字段加ExcelIgnore注解排除Excel(needMerge true,name 状态名称)ApiModelProperty(value 状态名称)private String ztmc;Excel(needMerge true,name 一级品类)ApiModelProperty(value 一级物资名称)private String wzOnemc;Excel(needMerge true,name 二级品类)ApiModelProperty(value 二级物资名称)private String wzTwomc;Excel(needMerge true,name 三级品类)ApiModelProperty(value 三级物资名称)private String wzThreemc;Excel(needMerge true,name 四级品类)ApiModelProperty(value 四级物资名称)private String wzRourmc;Excel(needMerge true,name 数据来源)ApiModelProperty(value 数据来源1市应急平台2人工新增)DataBindDict(sourceField #sjly, sourceFieldCombination sjly)private String sjlymc;ExcelCollection(name 应急物资明细)ApiModelProperty(value 应急物资详情)private ListYjwzStockInDtlVO dtlList;
}子类
public class YjwzStockInDtlVO extends TYjwzStockInDtl implements Serializable {Excel(name 单据类型)ApiModelProperty(value 单据类型1采购入库2调拨入库)DataBindDict(sourceField #djlx, sourceFieldCombination lx)private String djlxmc;Excel(name 入库质检)ApiModelProperty(value 入库质检1已检验入库2未检验入库)DataBindDict(sourceField #rkzj, sourceFieldCombination zjlx)private String rkzjmc;Excel(name 质检结果)ApiModelProperty(value 质检结果1合格2不合格)DataBindDict(sourceField #zjjg, sourceFieldCombination zjjg)private String zjjgmc;Excel(name 存储期单位)ApiModelProperty(value 存储期单位1年2月)DataBindDict(sourceField #ccqdw, sourceFieldCombination ccqdw)private String ccqdwmc;
}然后是工具类准备 1首先是设置字体样式的工具类
package com.sydata.zt.common.excel;import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;/*** Author xx* Date 2023/12/5 17:37* Description: poi导出excel样式设置工具* Version 1.0*/
public class ExcelStyleUtil implements IExcelExportStyler {private static final short STRING_FORMAT (short) BuiltinFormats.getBuiltinFormat(TEXT);private static final short FONT_SIZE_TEN 9;private static final short FONT_SIZE_ELEVEN 10;private static final short FONT_SIZE_TWELVE 10;/*** 大标题样式*/private CellStyle headerStyle;/*** 每列标题样式*/private CellStyle titleStyle;/*** 数据行样式*/private CellStyle styles;public ExcelStyleUtil(Workbook workbook){this.init(workbook);}/*** 初始化样式* param workbook*/private void init(Workbook workbook) {this.headerStyle initHeaderStyle(workbook);this.titleStyle initTitleStyle(workbook);this.styles initStyles(workbook);}/*** 初始化大标题样式** param workbook* return*/private CellStyle initHeaderStyle(Workbook workbook) {CellStyle style getBaseCellStyle(workbook);style.setFont(getFont(workbook,FONT_SIZE_ELEVEN,Boolean.TRUE));return style;}/*** 初始化小标题样式* param workbook* return*/private CellStyle initTitleStyle(Workbook workbook) {CellStyle style getBaseCellStyle(workbook);style.setFont(getFont(workbook,FONT_SIZE_ELEVEN,Boolean.TRUE));return style;}/*** 数据行样式* param workbook* return*/private CellStyle initStyles(Workbook workbook) {CellStyle style getBaseCellStyle(workbook);style.setDataFormat(STRING_FORMAT);return style;}/*** 大标题样式* param color* return*/Overridepublic CellStyle getHeaderStyle(short color) {return headerStyle;}/*** 每列标题样式* param color* return*/Overridepublic CellStyle getTitleStyle(short color) {return titleStyle;}/*** 数据行样式* param b* param excelExportEntity* return*/Overridepublic CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {return styles;}/*** 获取行样式方法* param cell* param i* param entity* param o* param o1* return*/Overridepublic CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) {return getStyles(true,entity);}Overridepublic CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {return null;}/*** 基础样式* param workbook* return*/private CellStyle getBaseCellStyle(Workbook workbook) {CellStyle style workbook.createCellStyle();//下边框style.setBorderBottom(BorderStyle.THIN);//左边框style.setBorderLeft(BorderStyle.THIN);//右边框style.setBorderRight(BorderStyle.THIN);//上边框style.setBorderTop(BorderStyle.THIN);//水平居中style.setAlignment(HorizontalAlignment.CENTER);//上下居中style.setVerticalAlignment(VerticalAlignment.CENTER);//设置自动换行style.setWrapText(Boolean.TRUE);return style;}/*** 字体样式* param workbook* param size* param isBold* return*/private Font getFont(Workbook workbook,short size,boolean isBold){Font font workbook.createFont();//字体大小font.setFontHeightInPoints(size);//字体是否加粗font.setBold(isBold);//设置字体
// font.setFontName();return font;}
}
2然后导出excel工具类
package com.sydata.zt.common.excel;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.CollectionUtils;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;/*** author hm* date 2023/3/14 17:33*/
public class EasyExcelGeneralUtil {/*** 设置response编码*/public static void setResponseContentType(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);response.setHeader(Content-Disposition, attachment;filename URLEncoder.encode(fileName, utf-8) .xlsx);response.setHeader(Access-Control-Expose-Headers, Content-Disposition);}public static void exportExcelByEasyPoi(HttpServletResponse response, String fileName, ListObject vos, Class? classType) throws IOException {ExportParams exportParams new ExportParams();// 设置sheet得名称exportParams.setSheetName(fileName);//设置边框字体字体大小exportParams.setStyle(ExcelStyleUtil.class);MapString, Object map new HashMap();// title的参数为ExportParams类型目前仅仅在ExportParams中设置了sheetNamemap.put(title, exportParams);// 模版导出对应得实体类型的class文件map.put(entity, classType);// sheet中要填充得数据map.put(data, vos);ListMapString, Object sheetsList new ArrayList();sheetsList.add(map);//创建excel文件的方法Workbook workbook ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);//通过response输出流直接输入给客户端ServletOutputStream outputStream response.getOutputStream();workbook.write(outputStream);outputStream.flush();outputStream.close();}}
准备工作已完成接下来就可以愉快的导出了
SneakyThrowsPostMapping(/export)ApiOperation(value 导出)public void export(HttpServletRequest request, HttpServletResponse response,RequestBody YjwzStockInDTO yjwzStockInDTO){request.getSession();String fileName 文件名称;EasyExcelGeneralUtil.setResponseContentType(response,fileName);//设置的分页最多能导出10000条数据yjwzStockInDTO.setPageNum(1);yjwzStockInDTO.setPageSize(10000);//查库得到需要导出的数据ListObject vos (ListObject) stockInService.page(yjwzStockInDTO).getRows();EasyExcelGeneralUtil.exportExcelByEasyPoi(response,fileName,vos,YjwzStockInVO.class);}好了完整的过程就是这样了直接掉接口导出就可以了。如有不足之处还请多多指导
