网站后台没有编辑器,湖州网站做等保费用,专做英文类网站,企业站用wordpress做好吗概述
主要记录在开发中遇到的问题#xff1a;使用easyexcel导出excel#xff0c;一般数据都是保存在数据库中#xff0c;如果查询返回的是一个实体类#xff0c;且里面有嵌套的实体类对象#xff0c;这时导出的时候要先对查询出的数据进行业务逻辑处理#xff0c;让它符…概述
主要记录在开发中遇到的问题使用easyexcel导出excel一般数据都是保存在数据库中如果查询返回的是一个实体类且里面有嵌套的实体类对象这时导出的时候要先对查询出的数据进行业务逻辑处理让它符合导出规范但是当数据量很大时容易jvm内存easyexcel(CellDataTypeEnum)只支持基本数据类型的导入如图1-1所示可以通过自定义数据类型(Converter)进行转换但是这样只能返回一种基本的数据类型像这种实体类里面通常会有很多信息如果都要导出这种方式也不合适。目前我能想到的方式是通过它提供的各种拦截器处理大概流程如下
通过自定义数据类型将实体类转换为String类型然后通过拦截器将字符串拆分添加对应列的数据。 图1-1 excel内部支持的数据类型
代码展示 excel日期转换类
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;import java.text.SimpleDateFormat;
import java.util.Date;/*** Description excel日期转换类 即将Mongo中类型为long的时间戳转化为yyyy-MM-dd HH:mm:ss时间* author HuangAnting* date 2022/4/8 10:16
*/
public class DateConverter implements ConverterLong {Overridepublic Class? supportJavaTypeKey() {//对象属性类型return Long.class;}Overridepublic CellDataTypeEnum supportExcelTypeKey() {//CellData属性类型return CellDataTypeEnum.STRING;}Overridepublic WriteCellData? convertToExcelData(WriteConverterContextLong context) throws Exception {//对象属性转CellDataLong cellValue context.getValue();new SimpleDateFormat(yyyy-MM-dd HH:mm:ss).format(new Date(cellValue));if (cellValue null) {return new WriteCellData();} else {return new WriteCellData( new SimpleDateFormat(yyyy-MM-dd HH:mm:ss).format(new Date(cellValue)));}}
}自增序号拦截器
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;/*** Description 自增序号拦截器* author HuangAnting* date 2022/4/12 17:27
*/
public class IndexRowWriteHandler implements RowWriteHandler {private static final String FIRST_CELL_NAME 序号;/*** 序号的样式与其他列保持一样的样式*/private CellStyle firstCellStyle;private String description;/*** 列号*/private int count 0;public IndexRowWriteHandler(String description){this.description description;}Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {// 每一行首列单元格Cell indexCell row.createCell(0);if(!isHead){indexCell.setCellValue(count);try {row.getCell(3).setCellValue(description);}catch (Exception e){}}else {Workbook workbook writeSheetHolder.getSheet().getWorkbook();firstCellStyle firstCellStyle(workbook);indexCell.setCellValue(FIRST_CELL_NAME);indexCell.setCellStyle(firstCellStyle);writeSheetHolder.getSheet().setColumnWidth(0, 6 * 256);}}/*** excel首列序号列样式* param workbook* return*/public CellStyle firstCellStyle(Workbook workbook) {CellStyle cellStyle workbook.createCellStyle();//居中cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 灰色cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//设置边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//文字Font font workbook.createFont();font.setBold(Boolean.TRUE);cellStyle.setFont(font);return cellStyle;}
}实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.hat.easyexcel_export.excel.DateConverter;
import com.hat.easyexcel_export.excel.MonPointConverter;
import lombok.Data;/*** Description 导出实体类* author HuangAnting* date 2022/4/12 17:26
*/
Data
public class Car {/** 名称*/ExcelProperty(value 名称,index 1)private String name;/** 价格*/ExcelProperty(value 价格,index 2)private Double price;/** 描述*/ColumnWidth(15)ExcelProperty(value 描述,index 3)private String description;/** 坐标*/ExcelProperty(value 坐标,converter MonPointConverter.class,index 4)private MonPoint monPoint;/** 日期*/ExcelProperty(value 日期,converter DateConverter.class)private Long createDate;
}自定义对象拦截器import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.fastjson.JSONObject;
import com.hat.easyexcel_export.entity.MonPoint;
import org.apache.poi.ss.usermodel.*;/*** Description 行拦截器 将字符串的经纬度转换成两列数据* author HuangAnting* date 2022/4/12 17:26
*/
public class CustomRowWriteHandler implements RowWriteHandler {/*** 序号的样式与其他列保持一样的样式*/private CellStyle firstCellStyle;private static final String LON_CELL_NAME 经度;private static final String LAT_CELL_NAME 纬度;/*** 列号*/Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean isHead) {Cell cell row.getCell(4);row.removeCell(cell);Cell lon row.createCell(4);Cell lat row.createCell(5);if (!isHead) {String stringCellValue cell.getStringCellValue();try {MonPoint monPoint JSONObject.parseObject(stringCellValue, MonPoint.class);lon.setCellValue(monPoint.getLon());lat.setCellValue(monPoint.getLat());}catch (Exception e){}}else {Workbook workbook writeSheetHolder.getSheet().getWorkbook();firstCellStyle firstCellStyle(workbook);lon.setCellStyle(firstCellStyle);lat.setCellStyle(firstCellStyle);lon.setCellValue(LON_CELL_NAME);lat.setCellValue(LAT_CELL_NAME);}}/*** excel首列序号列样式** param workbook* return*/public CellStyle firstCellStyle(Workbook workbook) {CellStyle cellStyle workbook.createCellStyle();//居中cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 灰色cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//设置边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//文字Font font workbook.createFont();font.setBold(Boolean.TRUE);cellStyle.setFont(font);return cellStyle;}
}gitee地址easyexcel_export
以上仅代表本人对easyexcel的认识和了解欢迎大家批评指正。
