-
2021-12-15 10:28:37
工具类
/** * excel 流导出(需返回前端指定信息时调用该方法) * * @param response 输出流 * @param fileName 文件名前缀 * @param exportData 导出数据 * @param clazz Class对象 * @throws IOException 异常 */ public static void exportExcel(HttpServletResponse response, String fileName, List exportData, Class clazz) throws IOException { exportExcel(response, fileName, exportData, clazz, "导出成功", ResultCode.SUCCESS.getCode()); } /** * excel 流导出(需返回前端指定信息时调用该方法) * * @param response 输出流 * @param fileName 文件名前缀 * @param exportData 导出数据 * @param clazz Class对象 * @param resultMsg 返回前端数据(放header里面返回)为空默认导出成功 * @param code 返回前端状态码:与controller返回状态码一致 全部成功:600 失败:800 为空默认600(放header里面与resultMsg一起返回) * @throws IOException 异常 */ public static void exportExcel(HttpServletResponse response, String fileName, List exportData, Class clazz, String resultMsg, Long code) throws IOException { try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 设置返回前端信息 Map<String, Object> result = new HashMap<>(); result.put("code", code); result.put("msg", resultMsg); response.setHeader("result", URLEncoder.encode(JSONUtil.toJsonStr(result), "UTF-8")); //设置该属性前端可见 response.setHeader("Access-Control-Expose-Headers", "result,Content-disposition"); // 使用URLEncoder.encode 防止中文乱码 String fileNameEncode = URLEncoder.encode(fileName, "UTF-8") + DateUtil.formatDateTime(new Date()); response.setHeader("Content-disposition", "attachment;filename=" + fileNameEncode + ".xlsx"); // 设置不关闭流 EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new CellWriteWidthHandler()).autoCloseStream(Boolean.FALSE).sheet(fileName).doWrite(exportData); } catch (Exception e) { log.error("下载文件失败:", e); // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); response.getWriter().println(JSON.toJSONString(CommonResult.failed("下载失败"))); } }
使用:
@ApiOperation("导出车辆") @PostMapping(value = "/excel/export") public void export(@ApiParam(name = "搜索条件") @RequestBody @Valid CarPageReq carPageReq, HttpServletResponse response) throws IOException { List<CarInfo> carInfos = iCarInfoService.getCarInfo(carPageReq); List<CarExportExcelDTO> carExportExcelDTOS = new ArrayList<>(); CarExportExcelDTO carExportExcelDTO = null; for (CarInfo carInfo : carInfos) { carExportExcelDTO = new CarExportExcelDTO(); BeanUtils.copyProperties(carInfo, carExportExcelDTO); carExportExcelDTO.setFirstRecordDate(DateUtil.format(carInfo.getFirstRecordDate(), "yyyy-MM-dd")); carExportExcelDTO.setWhetherUse(UseStatusEnum.translate(carInfo.getWhetherUse())); carExportExcelDTO.setInsuranceStatus(InsuranceStatusEnum.translate(carInfo.getInsuranceStatus())); carExportExcelDTOS.add(carExportExcelDTO); } ExcelUtils.exportExcel(response, "车辆信息", carExportExcelDTOS, CarExportExcelDTO.class); }
自动调整列宽:
public class CellWriteWidthHandler extends AbstractColumnWidthStyleStrategy { private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(CellWriteHandlerContext context) { List<WriteCellData<?>> cellDataList = context.getCellDataList(); Cell cell = context.getCell(); boolean needSetWidth = context.getHead() || (null != cellDataList && cellDataList.size() > 0); WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder(); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, context.getHead()); if (columnWidth >= 0) { if (columnWidth > 50) { columnWidth = 50; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } }
更多相关内容 -
EasyExcel导出.txt
2021-11-04 11:53:48EasyExcel 导出,含动态表头导出 -
easyExcel导出合并单元格策略
2021-12-21 17:12:32easyExcel导出合并单元格策略 WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)) .registerWriteHandler(new CustomCellWriteHandler()) //设置合并单元格策略 .registerWriteHandler(new ... -
利用easyExcel导出上万条数据,自定义策略合并单元格
2020-12-04 12:45:30easyExcel 2.1.7 poi 3.17 springboot 2.2.5 lombok 1.18.12 -
EasyExcel 导出
2021-08-12 15:11:43文章目录1.EasyExcel 写入1.1. 创建excel映射对象、一次写入response1.2. 不创建excel映射对象、多次写入File1.3. 单元格合并策略1.4. 列宽样式策略1.5. 转换器1.5.1. 转换器注入方式1.5.2....excel导出实体: @文章目录
1.EasyExcel 导出
1.1. Response流导出单sheet页
1.1.1 response流处理(防止中文乱码):
response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode(getTitle(), "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
1.1.2 根据excel映射对象导出(适合结构化数据:列表台账)
excel导出实体:
@Data @NoArgsConstructor //@ContentRowHeight(50)//内容单元格高度 //@HeadRowHeight(50)//表头单元格高度 //@ColumnWidth(50)//单元格宽度 public class DemoExcelBO { @ExcelProperty(value = {"导出台账","序号"}) private Integer no; @ExcelProperty(value = {"导出台账","编号"}) private String code; }
执行实际的导出动作:
//要导出的excel 数据 List<DemoExcelBO> dataList = new ArrayList<>(); //指定outputStream和 excel映射实体 EasyExcel.write(response.getOutputStream(), DemoExcelBO.class) //注册 WriteHandler ,getDefaultWriteHandler() 见下 .registerWriteHandler(getDefaultWriteHandler()) //注入原生 或 自定义转换器 .registerConverter(new DateStringConverter()) .registerConverter(new BigDecimalStringConverter()) .sheet("这是指定要写入的sheet页") .doWrite(dataList );
1.1.3. 根据单元格集合导出(适合非结构化数据:详情页)
//执行excel写入对象 ExcelWriter excelWriter = EasyExcel.write(url).build(); //当前sheet页 写入对象创建者 ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet("测试sheet"); //todo sheetBuilder 可以注入 writeHandler WriteSheet writeSheet = sheetBuilder.build(); //实际要写入的数据 ,List<Object> 为每一行的合并前的数据,放入List中为整个要写入的数据 List<List<Object>> dataList = new ArrayList<>(); //可以多次调用 excelWriter.write() 方法 。 写入同一sheet 的话 ,WriteSheet 对象不变 excelWriter.write(list, writeSheet); //关流 excelWriter.finish();
1.2. 单元格样式(背景色、加粗、字体、边框)
HorizontalCellStyleStrategy 分别设置 表头 和 表体 的样式:
//默认设置的样式 (表头样式 和 表体样式 不同) default WriteHandler getDefaultWriteHandler() { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont headWriteFont = new WriteFont(); // headWriteFont.setBold(true);// 加粗 headWriteFont.setFontHeightInPoints((short) 11);//字体大小 headWriteCellStyle.setWriteFont(headWriteFont); headWriteCellStyle.setWrapped(false);//自动换行 // headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);//CENTER WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);//THIN contentWriteCellStyle.setBorderLeft(BorderStyle.NONE); contentWriteCellStyle.setBorderRight(BorderStyle.NONE); contentWriteCellStyle.setBorderTop(BorderStyle.NONE); contentWriteCellStyle.setWrapped(false);//自动换行 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); return horizontalCellStyleStrategy; }
1.3. 单元格合并策略
easyExcel 抽象父类类 :AbstractMergeStrategy
public abstract class AbstractMergeStrategy implements CellWriteHandler { @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { //在创建单元格之前调用 } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //创建单元格后调用 } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //在单元格上的所有操作完成后调用 if (isHead) { return; } merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex); } //合并策略 protected abstract void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex); }
1.3.1 只合并一次
easyExcel 预制子类:OnceAbsoluteMergeStrategy
//创建单元格时只合并一次 public class OnceAbsoluteMergeStrategy extends AbstractMergeStrategy { private int firstRowIndex; private int lastRowIndex; private int firstColumnIndex; private int lastColumnIndex; }
1.3.2. 循环合并
easyExcel 预制子类:LoopMergeStrategy
//循环区域合并 public class LoopMergeStrategy extends AbstractMergeStrategy { private int eachRow; private int columnCount; private int columnIndex; }
继承 AbstractMergeStrategy 重写 merge()方法 ,可自定义合并策略
1.4. 列宽样式策略
easyExcel 抽象父类:
public abstract class AbstractColumnWidthStyleStrategy implements CellWriteHandler, NotRepeatExecutor { @Override public String uniqueValue() { return "ColumnWidthStyleStrategy"; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { //在创建单元格之前调用 } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //在创建单元格之后调用 } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //在单元格上的所有操作完成后调用 setColumnWidth(writeSheetHolder, cellDataList, cell, head, relativeRowIndex, isHead); } //列宽样式策略 protected abstract void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead); }
1.4.1. 根据每个列标题返回列宽
easyExcel 预制子类:AbstractHeadColumnWidthStyleStrategy
//根据每个列标题返回列宽 public abstract class AbstractHeadColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { }
1.4.2. 以最长列的宽度作为所有列的宽度
easyExcel 预制子类:LongestMatchColumnWidthStyleStrategy
//以最长列的宽度作为宽度 public class LongestMatchColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { }
1.4.3. 定长列宽
easyExcel 预制子类:SimpleColumnWidthStyleStrategy
//所有列的宽度相同 public class SimpleColumnWidthStyleStrategy extends AbstractHeadColumnWidthStyleStrategy { }
继承 AbstractColumnWidthStyleStrategy 重写 setColumnWidth()方法 ,可自定义列宽策略
1.4.4. 自动调整列宽(推荐)
在这里插入代码片
1.5. 转换器
easyExcel 转换器 父接口:
public interface Converter<T> { // 写入excel时 ,指定一个class ,该class 会在写入时 被拦截执行转换 Class supportJavaTypeKey(); // 读取excel时 ,指定一个excel字段类型,该类型 会在读取时 被拦截执行转换 CellDataTypeEnum supportExcelTypeKey(); //写入时 , java 类型 转换 excel数据类型 动作 T convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception; //读取时 , excel数据类型 转换 java 类型动作 CellData convertToExcelData(T value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception; }
有预制转换器,均实现改接口,如需自定义转换器,可实现 Converter 接口。
1.5.1. 转换器注入方式
方式一: 在excel实体上指定 某些特定字段生效
public class ContractAccountExcelBO { @ExcelProperty(value = {"合同导出台账","","序号"} ,converter = IntegerStringConverter.class) private Integer no; }
方式二: .registerConverter() 方法注入 ,本次读取或写入时,均生效
//要导出的excel 数据 List<DemoExcelBO> dataList = new ArrayList<>(); EasyExcel.write(response.getOutputStream(),DemoExcelBO.class ) .registerConverter(new BigDecimalStringConverter()) .doWrite(dataList);//
1.5.2. 转换器 注解
指定日期格式注解:@DateTimeFormat(“yyyy-MM-dd”)
指定数字格式注解:@NumberFormat("#.00")
NumberFormat 中 format格式 遵循 Bigdecimal.format() 策略(#不补位,没有数字则空。0自动补位,没有数字自动补零)。1.6. RowWriteHandler 行处理器
// 行创建 动作 拦截器 public interface RowWriteHandler extends WriteHandler { //行 创建前执行 void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead); //行 创建后执行 void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead); //在该行的所有操作完成后调用。填充数据时不调用此方法。 void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead); }
1.7. CellWriteHandler 单元格处理器
// 单元格创建 动作 拦截器 public interface CellWriteHandler extends WriteHandler { //单元格创建 前动作 void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead); //单元格创建 后动作 void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead); //在单元格上的所有操作完成后调用 void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead); }
1.8. 图片导出
EasyExcel支持5种图片类型导出,根据1.1.3.把图片形式放入单元格中,写会即可
@Data @ContentRowHeight(200) @ColumnWidth(200 / 8) public class ImageData { // 图片导出方式有5种 private File file; private InputStream inputStream; /** * 如果string类型 必须指定转换器,string默认转换成string,该转换器是官方支持的 */ @ExcelProperty(converter = StringImageConverter.class) private String string; private byte[] byteArray; /** * 根据url导出 版本2.1.1才支持该种模式 */ private URL url; }
-
使用EasyExcel导出数据
2021-07-29 17:09:49使用EasyExcel导出 下载模板 导入excel功能,都需要对应的模板导入,也有动态的表头数据导入,可以看excel行数据不规则解析匹配。这里一般都是固定excel文件,存放在项目路径下面,下载模板的时候都是找对应的文件。...使用EasyExcel导出
下载模板
导入excel功能,都需要对应的模板导入,也有动态的表头数据导入,可以看excel行数据不规则解析匹配。这里一般都是固定excel文件,存放在项目路径下面,下载模板的时候都是找对应的文件。
@GetMapping("/downloadTemplate") public void downloadTemplate(HttpServletResponse response) { String fileName = "导入模板.xlsx"; ClassPathResource classPathResource = new ClassPathResource("template/file.xlsx"); try (InputStream inputStream = classPathResource.getInputStream(); OutputStream out = response.getOutputStream();) { setResponseAttribute(fileName, response); Map<String, String> map = new HashMap<>(4); int year = DateUtil.thisYear(); map.put("lastYear", String.valueOf(year - 1)); map.put("currentYear", String.valueOf(year)); ExcelWriterBuilder writerBuilder = EasyExcel.write(out).withTemplate(inputStream); //这边第二个sheet页面里面有个参数需要动态生成,指定了往第二个sheet页里面填充值 writerBuilder.sheet(1).doFill(map); } catch (IOException e) { log.error(e.getMessage(), e); } }
数据下载
表头数据修改
一般导出数据固定的话都可以使用对象导入,建立对应的对象,使用EasyExcel中相应的注解,对Excel中单元格样式,字体样式,背景颜色等等都可以实现。
/** * * @author 洛必达法则不会 * @since 2021-07-22 */ @Data @HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER) @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER) @HeadFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑") @ContentFontStyle(fontHeightInPoints = 10) @HeadRowHeight(18) @ColumnWidth(14) public class TalentSituationHeadData { /** * 年份 */ @ColumnWidth(8) @ExcelProperty(value = "年份", index = 0) private String year; /** * 事业部 */ @ExcelProperty(value = "事业分部", index = 1) private String deptName; /** * 待招岗位 */ @ExcelProperty(value = "待招岗位", index = 2) private String recruitedPost; /** * 姓名 */ @HeadStyle(fillForegroundColor = 44) @ExcelProperty(value = "姓名", index = 3) private String name; }
本次功能导入需要导出一个Excel包含多个sheet页,其中一个sheet页面的表头需要根据时间动态生成,直接使用@ExcelProperty注解,value值都是固定的。
// 首先尝试的通过 WriteSheet中head属性获取表头的字段,再修改里面对应的值,实际运行中属性值是null,此方法不可以 WriteSheet potentialCustomerSheet = EasyExcel.writerSheet(index,sheetNameEnum.getName()).head(PotentialCustomerHeadData.class).build(); List<List<String>> headList = potentialCustomerSheet.getHead(); //另一种就是重新构造List<List<String>> headList,通过EasyExcel.write(fileName).head(headList).sheet("模板");写入表头,这种方式在对象的字体颜色注解就没有,需要重新构造对应的格式,也比较繁琐 List<List<String>> headList = buildHeadDataList(); EasyExcel.write(fileName).head(headList).sheet("模板"); //之前的逻辑写好,不想用上面的方法,后面就采用反射的方式,拿到对应属性上面的注解,修改里面的值再通过EasyExcel.writerSheet写入 String year = StrUtil.isNotBlank(param.getStartDate()) ? param.getStartDate().substring(0, 4) : Integer.toString(DateUtil.thisYear()); Field yearEstimateField = PotentialCustomerHeadData.class.getDeclaredField("yearEstimate"); yearEstimateField.setAccessible(true); ExcelProperty excel = yearEstimateField.getAnnotation(ExcelProperty.class); InvocationHandler excelH = Proxy.getInvocationHandler(excel); // 获取 AnnotationInvocationHandler 的 memberValues 字段 Field excelF = excelH.getClass().getDeclaredField("memberValues"); excelF.setAccessible(true); Map excelValues = (Map) excelF.get(excelH); excelValues.put("value", new String[]{"项目利润", String.format("%s预估\n(万元/年)", year)}); WriteSheet potentialCustomerSheet = EasyExcel.writerSheet(index, sheetNameEnum.getName()).head(PotentialCustomerHeadData.class).build();
合并单元格
刚好一个表单导出的时候,需要实现单元格合并,实现AbstractMergeStrategy类的方法,具体合并策略根据业务需求实现。
//策略类 public class CellMergeStrategy extends AbstractMergeStrategy { private Map<String, List<RowRange>> strategyMap; private Sheet sheet; public CellMergeStrategy() { } public CellMergeStrategy(Map<String, List<RowRange>> strategyMap) { this.strategyMap = strategyMap; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) { this.sheet = sheet; if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) { /** * 保证每个cell被合并一次,如果不加上面的判断,因为是一个cell一个cell操作的, * 例如合并A2:A3,当cell为A2时,合并A2,A3,但是当cell为A3时,又是合并A2,A3, * 但此时A2,A3已经是合并的单元格了 */ for (Map.Entry<String, List<RowRange>> entry : strategyMap.entrySet()) { int columnIndex = Integer.parseInt(entry.getKey()); entry.getValue().forEach(rowRange -> { //添加一个合并请求 sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(), rowRange.getEnd(), columnIndex, columnIndex)); }); } } } }
/** * Excel工具类 */ public class ExcelUtil { //获取需要合并的单元格 public static Map<String, List<RowRange>> getProfitReachedMergeStrategy(List<ProfitReachedHeadData> profitReachedHeadDataList) { Map<String, List<RowRange>> strategyMap = new HashMap<>(); ProfitReachedHeadData previousData = null; int size = profitReachedHeadDataList.size(); for (int i = 0; i < size; i++) { ProfitReachedHeadData currentData = profitReachedHeadDataList.get(i); if (previousData != null) { if (currentData.getDeptName().equals(previousData.getDeptName())) { fillStrategyMap(strategyMap, "0", i); } if (currentData.getOrgName().equals(previousData.getOrgName())) { fillStrategyMap(strategyMap, "1", i); } } previousData = currentData; } return strategyMap; } /** * 增加合并策略 * @param strategyMap Map<String, List<RowRange>> * @param key String * @param index int */ private static void fillStrategyMap(Map<String, List<RowRange>> strategyMap, String key, int index) { List<RowRange> rowRangeList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key); boolean flag = false; for (RowRange rowRange : rowRangeList) { //分段list中是否有end索引是上一行索引的,如果有,则索引+1 if (rowRange.getEnd() == index) { rowRange.setEnd(index + 1); flag = true; } } //如果没有,则新增分段 if (!flag) { rowRangeList.add(new RowRange(index, index + 1)); } strategyMap.put(key, rowRangeList); } public class RowRange { private int start; private int end; } }
//业务实现 List<ProfitReachedHeadData> profitSummaryHeadDataList = profitSummaryService.buildProfitReachedHeadDataList(entry.getValue()); Map<String, List<RowRange>> summaryStrategyMap = ExcelUtil.getProfitReachedMergeStrategy(profitSummaryHeadDataList); WriteSheet profitSummarySheet = EasyExcel.writerSheet(index, String.format("%s-%s合计-%s月", sheetNameEnum.getName(),permissionOperate.getDeptName(),entry.getKey().substring(5))).registerWriteHandler(new CellMergeStrategy(summaryStrategyMap)).head(ProfitReachedHeadData.class).build(); excelWriter.write(profitSummaryHeadDataList, profitSummarySheet);
-
使用easyExcel导出数据
2021-10-31 09:45:56如题,使用easyExcel简单的导出一下数据,代码如下: 首先在pom.xml里面引入如下依赖: <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <...如题,使用easyExcel简单的导出一下数据,代码如下:
首先在pom.xml里面引入如下依赖:<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.20</version> </dependency>
controller层代码:
@RestController @RequestMapping("/export") public class ExportTestController { private static Logger log= LoggerFactory.getLogger(ExportTestController.class); @RequestMapping("/hello") public String hello(){ return "hello world!"; } @Autowired private ExcelTestService excelTestService; /** * 史上最简单的数据数据导出demo * 开箱即会 */ @RequestMapping("/exporttest") public void exporttest(HttpServletResponse response){ excelTestService.export(response); } }
service层代码:
import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.support.ExcelTypeEnum; import com.hdd.exportTest.model.Users; import com.hdd.exportTest.service.ExcelTestService; import org.springframework.stereotype.Service; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.util.ArrayList; import java.util.List; /** * @ClassName ExcelTestServiceImpl * @DesCription TODO * @Author 584654 * @Date 2021/10/31 9:08 * @Version 1.0 * @PackageName com.hdd.exportTest.service.impl **/ @Service public class ExcelTestServiceImpl implements ExcelTestService { public void export(HttpServletResponse response){ //需要导出的数据 List<Users> list=getUsersList(); try { ServletOutputStream out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true); String fileName = new String("用户信息表格" .getBytes("utf-8"), "iso8859-1"); Sheet sheet1 = new Sheet(1, 0, Users.class); sheet1.setSheetName("sheet1"); response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.write(list, sheet1); writer.finish(); out.flush(); } catch (Exception e) { e.printStackTrace(); } } private List<Users> getUsersList() { List<Users> usersList=new ArrayList<Users>(); usersList.add(new Users("高俊也",28,"男")); usersList.add(new Users("李雪琴",25,"女")); return usersList; } }
model层代码:
import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.metadata.BaseRowModel; import lombok.Data; import java.io.Serializable; /** * @ClassName Users * @DesCription TODO * @Author 584654 * @Date 2021/10/31 9:13 * @Version 1.0 * @PackageName com.hdd.exportTest.model **/ @Data public class Users extends BaseRowModel implements Serializable { private static final long serialVersionUID = 15353L; @ExcelProperty(value = "姓名",index = 0) private String name; @ExcelProperty(value = "年龄",index = 1) private Integer age; @ExcelProperty(value = "性别",index = 2) private String sex; public Users(String name, Integer age, String sex) { this.name = name; this.age = age; this.sex = sex; } }
启动项目后导出数据结果如下:
-
easyExcel导出
2020-03-30 18:32:09easyExcel导出 错误信息: java.lang.IllegalStateException: Cannot add merged region C3:E3 to sheet because it overlaps with an existing merged region (B3:D3). 报表如上图所示,三级子表头有重复项,在... -
easyExcel导出通用方法
2021-10-11 19:07:08easyExcel导出通用方法 开箱即用 导出效果 导出通用方法 ```java @Slf4j public class EasyExcelExport { /** * EasyExcel输出通用 * * @param response * @param title * @param companyName * @param ... -
Java EasyExcel 导出
2022-04-20 09:56:32maven依赖 <dependency>...easyexcel</artifactId> <version>2.2.7</version> </dependency> Model import java.math.BigDecimal; import java.util.Date; import com -
EasyExcel导出大量数据
2021-08-25 11:35:111:EasyExcel导出数据 2:查询时分sheet页查询写入 3:自定义导出格式 1:pom文件: <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> &... -
EasyExcel 动态表头 导出
2020-11-20 10:48:12EasyExcel 动态表头 导出 ,非注解,后台导出, 可分页,可限制每页显示行数,依赖 com.alibaba easyexcel -
EasyExcel导出自定义合并单元格的策略
2022-02-11 10:19:29目前网上找到的EasyExcel自定义合并单元格都是ExcelFillCellMergeStrategy,这个工具类只要下一行的cell和上一行的cell内容相同就会合并,不符合目前的需求。本例也是在此基础上进行逻辑修改。 参考链接。 测试代码 ... -
easyexcel导出动态模板(动态导出下拉框)
2022-04-22 11:39:25使用easyexcel下载模板 -
EasyExcel导出Excel 自定义导出时间格式
2022-01-20 15:21:20导出 Excel想要设置自定义的 导出时间样式方法 超详细 -
EasyExcel导出excel表格
2022-04-02 17:41:10传统Excel操作或者解析都是利用Apach POI进行操作,但是使用过这个框架的人都知道,这个框架并不完美,有较...EasyExcel实现excel导出: try { //设置头居中 WriteCellStyle headWriteCellStyle = new WriteCellSty -
EasyExcel导出工具类
2022-02-22 16:42:21EasyExcel导出工具类 -
EasyExcel导出自适应列宽
2022-01-18 14:20:09EasyExcel导出时,默认有个列宽,数据太长会隐藏起来,加个设置就可以了,但不能保证完全OK,但是可以用,总比没有强。 // 设置下载类型,这个类型支持在线预览 response.setContentType("application/octet-... -
EasyExcel导出详解
2022-04-17 14:18:421.EasyExcel简单介绍 EasyExcel工具是阿里的一个操作excel的开源项目,对现有的POI框架进行性能优化,解决了大数据量时内存溢出的问题。同时封装的更加简单灵活,适合初学者上手。 EasyExcel仓库 EasyExcel官方文档 ... -
EasyExcel导出图片
2021-12-22 18:17:28在excel里导出图片: 1、数据实体 import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.*; import lombok.Data; import org.apache.poi.ss.usermodel.... -
easyExcel导出文件为空解决
2021-10-24 23:27:05在操作过程中发现了一个问题,直接拷用官方文档的文件导出的文件是空的,在一番研究之下写出了后面的代码,实现了用easyExcel的导出。直接上干货 包引入 这里说个贼有意思的事情,官方文档也是写的简介,简介到pom... -
使用easyExcel导出excel文件
2022-02-13 17:45:171.导入jar包 <dependency>...easyexcel</artifactId> <version>3.0.5</version> </dependency> 2.点击导出按钮,浏览器开始下载对应的数据,也可以实现批量导出 3.实... -
easyexcel导出excel文件之图片导出
2021-03-09 22:39:161.easyexcel导出图片简单操作参考官网 2.项目中遇到的问题:项目导出报表需要导出图片 easyexcel导出图片,数据库中的存储的图片是字符串类型(实际上还是图片的URL链接),需要先转为URL,再进行导出操作. 图片字段... -
SpringBoot使用easyexcel导出Excel
2022-02-26 14:42:55--easyexcel,推荐使用2.0 以上版本,功能更加完善--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version&... -
easyexcel 导出文档(笔记)
2022-03-21 16:29:37阅读官方文档(很有必要)文档链接 ...导出文档功能主要代码 java 部分 try{ response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder. -
easyexcel 动态导出复杂表头.zip
2021-05-11 16:17:44easyexcel 动态导出复杂表头 例子 带cellRange -
EasyExcel导出报表(多table包含合并策略)
2022-02-09 16:47:21EasyExcel导出报表(多table包含合并策略) 导入Pom <!-- easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>... -
easyexcel 导出excel文件
2021-11-22 16:08:22easyexcel 引入: <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.4</version> </dependency&... -
Java通过EasyExcel导出excel
2022-03-29 10:53:00Java通过EasyExcel导出excel