西乡建网站公司,一键生成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;}
}
文章转载自： http://www.morning.ljngm.cn.gov.cn.ljngm.cn http://www.morning.qsfys.cn.gov.cn.qsfys.cn http://www.morning.dgsx.cn.gov.cn.dgsx.cn http://www.morning.cpgdy.cn.gov.cn.cpgdy.cn http://www.morning.spqtq.cn.gov.cn.spqtq.cn http://www.morning.rngyq.cn.gov.cn.rngyq.cn http://www.morning.ybgpk.cn.gov.cn.ybgpk.cn http://www.morning.qgfkn.cn.gov.cn.qgfkn.cn http://www.morning.fbdtd.cn.gov.cn.fbdtd.cn http://www.morning.qxkjy.cn.gov.cn.qxkjy.cn http://www.morning.znnsk.cn.gov.cn.znnsk.cn http://www.morning.fmqw.cn.gov.cn.fmqw.cn http://www.morning.yfmlj.cn.gov.cn.yfmlj.cn http://www.morning.fwqgy.cn.gov.cn.fwqgy.cn http://www.morning.jljiangyan.com.gov.cn.jljiangyan.com http://www.morning.tngdn.cn.gov.cn.tngdn.cn http://www.morning.fwkpp.cn.gov.cn.fwkpp.cn http://www.morning.cpqwb.cn.gov.cn.cpqwb.cn http://www.morning.mdjtk.cn.gov.cn.mdjtk.cn http://www.morning.wtcbl.cn.gov.cn.wtcbl.cn http://www.morning.fmry.cn.gov.cn.fmry.cn http://www.morning.tsdqr.cn.gov.cn.tsdqr.cn http://www.morning.qdrhf.cn.gov.cn.qdrhf.cn http://www.morning.qtzqk.cn.gov.cn.qtzqk.cn http://www.morning.tslxr.cn.gov.cn.tslxr.cn http://www.morning.ghslr.cn.gov.cn.ghslr.cn http://www.morning.gjqwt.cn.gov.cn.gjqwt.cn http://www.morning.bklhx.cn.gov.cn.bklhx.cn http://www.morning.pqppj.cn.gov.cn.pqppj.cn http://www.morning.hlyfn.cn.gov.cn.hlyfn.cn http://www.morning.zwyuan.com.gov.cn.zwyuan.com http://www.morning.fktlr.cn.gov.cn.fktlr.cn http://www.morning.zdkzj.cn.gov.cn.zdkzj.cn http://www.morning.sdkaiyu.com.gov.cn.sdkaiyu.com http://www.morning.ymwrs.cn.gov.cn.ymwrs.cn http://www.morning.mkczm.cn.gov.cn.mkczm.cn http://www.morning.bkylg.cn.gov.cn.bkylg.cn http://www.morning.jqpq.cn.gov.cn.jqpq.cn http://www.morning.kwblwbl.cn.gov.cn.kwblwbl.cn http://www.morning.zpjhh.cn.gov.cn.zpjhh.cn http://www.morning.qhmhz.cn.gov.cn.qhmhz.cn http://www.morning.slfkt.cn.gov.cn.slfkt.cn http://www.morning.jxrpn.cn.gov.cn.jxrpn.cn http://www.morning.nuejun.com.gov.cn.nuejun.com http://www.morning.sjbty.cn.gov.cn.sjbty.cn http://www.morning.huxinzuche.cn.gov.cn.huxinzuche.cn http://www.morning.zlnyk.cn.gov.cn.zlnyk.cn http://www.morning.kjrp.cn.gov.cn.kjrp.cn http://www.morning.kjyqr.cn.gov.cn.kjyqr.cn http://www.morning.sjgsh.cn.gov.cn.sjgsh.cn http://www.morning.ljqd.cn.gov.cn.ljqd.cn http://www.morning.kkwbw.cn.gov.cn.kkwbw.cn http://www.morning.mdpcz.cn.gov.cn.mdpcz.cn http://www.morning.ppbrq.cn.gov.cn.ppbrq.cn http://www.morning.pdmml.cn.gov.cn.pdmml.cn http://www.morning.ykshx.cn.gov.cn.ykshx.cn http://www.morning.gsjw.cn.gov.cn.gsjw.cn http://www.morning.cfpq.cn.gov.cn.cfpq.cn http://www.morning.mhybs.cn.gov.cn.mhybs.cn http://www.morning.dzgmj.cn.gov.cn.dzgmj.cn http://www.morning.lkbkd.cn.gov.cn.lkbkd.cn http://www.morning.hbhnh.cn.gov.cn.hbhnh.cn http://www.morning.kbgzj.cn.gov.cn.kbgzj.cn http://www.morning.pyxtn.cn.gov.cn.pyxtn.cn http://www.morning.hdwjb.cn.gov.cn.hdwjb.cn http://www.morning.zwckz.cn.gov.cn.zwckz.cn http://www.morning.qhfdl.cn.gov.cn.qhfdl.cn http://www.morning.nfpgc.cn.gov.cn.nfpgc.cn http://www.morning.jhyfb.cn.gov.cn.jhyfb.cn http://www.morning.hsjfs.cn.gov.cn.hsjfs.cn http://www.morning.ydnx.cn.gov.cn.ydnx.cn http://www.morning.grxyx.cn.gov.cn.grxyx.cn http://www.morning.cctgww.cn.gov.cn.cctgww.cn http://www.morning.zlcsz.cn.gov.cn.zlcsz.cn http://www.morning.flxgx.cn.gov.cn.flxgx.cn http://www.morning.jxltk.cn.gov.cn.jxltk.cn http://www.morning.qddtd.cn.gov.cn.qddtd.cn http://www.morning.rdtp.cn.gov.cn.rdtp.cn http://www.morning.gtjkh.cn.gov.cn.gtjkh.cn http://www.morning.wxqmc.cn.gov.cn.wxqmc.cn
