清远网站设计公司,企业网站建设常见问题,呼和浩特网站优化,为什么做网站比app便宜一、导入maven依赖
#xff08;很多旧项目自定义了一套Excel导出工具#xff0c;poi版本可能不兼容#xff0c;一般poi新旧版本不兼容分界线在3.17#xff0c;选择3.17版本不会发生代码不兼容情况#xff09; dependencygroupIdcom.alibaba/groupId很多旧项目自定义了一套Excel导出工具poi版本可能不兼容一般poi新旧版本不兼容分界线在3.17选择3.17版本不会发生代码不兼容情况 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion2.2.6/versionexclusionsexclusiongroupIdorg.apache.poi/groupIdartifactIdpoi/artifactId/exclusionexclusiongroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactId/exclusionexclusiongroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml-schemas/artifactId/exclusion/exclusions/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion3.17/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion3.17/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion3.17/versionclassifiersources/classifier/dependencydependencygroupIdorg.apache.commons/groupIdartifactIdcommons-collections4/artifactIdversion4.1/version/dependency二、重写easyExcel-自定义写入处理器 import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;
import java.util.List;/*** 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略需要重写merge()方法* author reshui* date 2023/9/4**/
public class CustomMergeStrategy extends AbstractMergeStrategy {/*** 分组每几行合并一次*/private ListInteger exportFieldGroupCountList;/*** 目标合并列index*/private Integer targetColumnIndex;/*** 需要开始合并单元格的首行index*/private Integer rowIndex;/*** param exportDataList exportDataList为待合并目标列的值* param targetColumnIndex 需要合并的列* return {code }* author reshui* date 2023/09/05*/public CustomMergeStrategy(ListString exportDataList, Integer targetColumnIndex) {this.exportFieldGroupCountList getGroupCountList(exportDataList);this.targetColumnIndex targetColumnIndex;}Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (null rowIndex) {rowIndex cell.getRowIndex();}// 仅从首行以及目标列的单元格开始合并忽略其他if (cell.getRowIndex() rowIndex cell.getColumnIndex() targetColumnIndex) {mergeGroupColumn(sheet);}}private void mergeGroupColumn(Sheet sheet) {int rowCount rowIndex;for (Integer count : exportFieldGroupCountList) {if (count 1) {rowCount count;continue;}// 合并单元格CellRangeAddress cellRangeAddress new CellRangeAddress(rowCount, rowCount count - 1, targetColumnIndex, targetColumnIndex);sheet.addMergedRegionUnsafe(cellRangeAddress);rowCount count;}}/*** 该方法将目标列根据值是否相同连续可合并存储可合并的行数* param exportDataList* return {code ListInteger }* author reshui* date 2023/09/05*/private ListInteger getGroupCountList(ListString exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList();}ListInteger groupCountList new ArrayList();int count 1;for (int i 1; i exportDataList.size(); i) {if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {count;} else {groupCountList.add(count);count 1;}}// 处理完最后一条后groupCountList.add(count);return groupCountList;}}
三、导出工具类封装
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;/*** 下载excel文件工具** author reshui* date 2023/09/05*/
public class DownloadExcelUtil {private static final Logger log LoggerFactory.getLogger(DownloadExcelUtil.class);private final static String separatorChar -;public static T void downloadFile(HttpServletResponse response, ClassT clazz, String data) throws IOException {String timeStamp DateUtil.format(DateUtil.date(), yyyyMMddHHmmss);String fileName timeStamp -log;downloadFile(response, clazz, data, fileName, 数据, null);}/*** param response 响应请求* param clazz 导出数据类型* param data 数据源* param customFileName 文件名* param sheetName 页名* param writeHandlerList 自定义写入处理器* return {code T }* author reshui* date 2023/09/05*/public static T T downloadFile(HttpServletResponse response, ClassT clazz, String data, String customFileName, String sheetName, ListWriteHandler writeHandlerList) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题请直接用浏览器或者用postmantry {response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码 当然和easy-excel没有关系String fileName URLEncoder.encode(customFileName, UTF-8).replaceAll(\\, %20);response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx);// 这里需要设置不关闭流ExcelWriterSheetBuilder writerSheetBuilder EasyExcel.write(response.getOutputStream(), clazz).autoCloseStream(Boolean.FALSE).sheet(sheetName);if (CollUtil.isNotEmpty(writeHandlerList)) {for (WriteHandler writeHandler : writeHandlerList) {writerSheetBuilder.registerWriteHandler(writeHandler);}}writerSheetBuilder.doWrite(JSONObject.parseArray(data, clazz));} catch (Exception e) {// 重置responseresponse.reset();response.setContentType(application/json);response.setCharacterEncoding(utf-8);MapString, String map new HashMap(2);map.put(status, failure);map.put(message, 下载文件失败 e.getMessage());response.getWriter().println(JSON.toJSONString(map));}return null;}/*** 出把excel** param timeStamp 时间戳* param excelFileName excel文件名字* param headClassType 头类类型* param resultExcelList 结果excel表* param filePath 文件路径* author reshui* date 2023/02/15*/public static void outputExcelToLocal(String timeStamp, String excelFileName, Class headClassType, List resultExcelList, String filePath) {//文件时间戳timeStamp Objects.nonNull(timeStamp) ? timeStamp : StrUtil.EMPTY;String partFileName filePath File.separator excelFileName separatorChar timeStamp -log.xlsx;FileUtil.touch(partFileName);EasyExcel.write(partFileName, headClassType).sheet(源数据).doWrite(resultExcelList);}/*** 简单把excel** param excelFileName excel文件名字* param headClassType 头类类型* param resultExcelList 结果excel表* param filePath 文件路径* author reshui* date 2023/02/15*/public static void easyOutputExcelToLocal(String excelFileName, Class headClassType, List resultExcelList, String filePath) {String timeStamp DateUtil.format(DateUtil.date(), yyyyMMddHHmmss);outputExcelToLocal(timeStamp, excelFileName, headClassType, resultExcelList, filePath);}public static void main(String[] args) {String timeStamp DateUtil.format(DateUtil.date(), yyyyMMddHHmmss);String titleTmpDirPath FileUtil.getTmpDirPath() File.separator test File.separator timeStamp File.separator;try {System.out.println(日志存储地址-[ titleTmpDirPath ]);log.info(日志存储[ titleTmpDirPath ]);String partFileName titleTmpDirPath timeStamp -log.xlsx;FileUtil.touch(partFileName);EasyExcel.write(partFileName, String.class).sheet(源数据).doWrite(null);} catch (Exception e) {log.error(日志存储[ titleTmpDirPath ] -error:, e);}}
}四、运行
RestController
RequestMapping(/check)
public class TestController {RequestMapping(value /run1,method {RequestMethod.GET})public void run1(HttpServletResponse response) throws IOException {ListDemoData demoDataList data1();ListWriteHandler customMergeStrategies Arrays.asList(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getName).collect(Collectors.toList()), 1));DownloadExcelUtil.downloadFile(response,DemoData.class, JSONObject.toJSONString(demoDataList),测试,页1, customMergeStrategies);}public static ListDemoData data1(){ListDemoData demoDataList new ArrayList();DemoData demoData0 new DemoData();demoData0.setId(0);demoData0.setName(hello0);demoDataList.add(demoData0);DemoData demoData1 new DemoData();demoData1.setId(1);demoData1.setName(hello1);demoDataList.add(demoData1);DemoData demoData11 new DemoData();demoData11.setId(1);demoData11.setName(hello1);demoDataList.add(demoData11);DemoData demoData2 new DemoData();demoData2.setId(2);demoData2.setName(hello2);demoDataList.add(demoData2);return demoDataList;}
}
