easyexcel-wraper

easyexcel-wraper是什么?

一个方便读取excel内容，且可以使用注解进行内容验证的包装工具

用到alibaba 2.1.4版本

定义导出excel复杂表头

@ExcelProperty

这是最常用的一个注解，注解中有三个参数value,index,converter分别代表列明，列序号，数据转换方式，注意value={“A”,“B”}，如图“A”代表同一单元格上层，“B”下层

@ColumnWidth

设置单元格的宽度

package com.hhh.sup.project.model;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.metadata.BaseRowModel;import lombok.Data;/*** @Description: 工程状态统计导出* @Author 3hcw* @Date /2/3 15:01*/@Datapublic class ProjectStatusExportBean extends BaseRowModel {//序号@ColumnWidth(10)@ExcelProperty(index = 0,value = {"序号"})private Integer xh;@ColumnWidth(20)@ExcelProperty(index = 1,value = {"施工许可号"})private String builderLicense;@ColumnWidth(20)@ExcelProperty(index = 2,value = {"报监编号"})private String zlProjectNum;@ColumnWidth(40)@ExcelProperty(index = 3,value = {"工程名称"})private String projectName;@ColumnWidth(20)@ExcelProperty(index = 4,value = {"工程情况","建筑类别"})private String buildType;@ColumnWidth(20)@ExcelProperty(index = 5,value = {"工程情况","具体类型"})private String buildChildType;@ColumnWidth(20)@ExcelProperty(index = 6,value = {"工程情况","监督面积(㎡)"})private String allArea;@ColumnWidth(20)@ExcelProperty(index = 7,value = {"工程情况","工程造价(万元)"})private String projectCost;@ColumnWidth(20)@ExcelProperty(index =8,value = {"工程情况","地上层次"})private String floorUp;@ColumnWidth(20)@ExcelProperty(index = 9,value = {"工程情况","地下层次"})private String floorDown;@ColumnWidth(20)@ExcelProperty(index = 10,value = {"工程情况","高度"})private String buildHeightStr;@ColumnWidth(20)@ExcelProperty(index = 11,value = {"工程情况","工程报监日期"})private String reportDate;@ColumnWidth(20)@ExcelProperty(index = 12,value = {"工程情况","实际开工日期"})private String beginDate;@ColumnWidth(20)@ExcelProperty(index = 13,value = {"工程情况","建设单位"})private String jsUnit;@ColumnWidth(20)@ExcelProperty(index = 14,value = {"工程情况","施工单位"})private String sgUnit;@ColumnWidth(20)@ExcelProperty(index = 15,value = {"工程情况","监理单位"})private String jlUnit;@ColumnWidth(20)@ExcelProperty(index = 16,value = {"工程情况","设计单位"})private String sjUnit;@ColumnWidth(20)@ExcelProperty(index = 17,value = {"工程情况","勘察单位"})private String kcUnit;@ColumnWidth(20)@ExcelProperty(index = 18,value = {"工程情况","图审机构"})private String tsUnit;@ColumnWidth(20)@ExcelProperty(index = 19,value = {"工程情况","检测机构"})private String jcUnit;@ColumnWidth(20)@ExcelProperty(index = 20,value = {"工程情况","工程地址"})private String address;@ColumnWidth(20)@ExcelProperty(index = 21,value = {"形象进度","桩基"})private String zj;@ColumnWidth(20)@ExcelProperty(index = 22,value = {"形象进度","深基坑"})private String sjk;@ColumnWidth(20)@ExcelProperty(index = 23,value = {"形象进度","基础"})private String jc;@ColumnWidth(20)@ExcelProperty(index = 24,value = {"形象进度","主体"})private String zt;@ColumnWidth(20)@ExcelProperty(index = 25,value = {"形象进度","装饰"})private String zs;@ColumnWidth(20)@ExcelProperty(index = 26,value = {"形象进度","节能"})private String jn;@ColumnWidth(20)@ExcelProperty(index = 27,value = {"形象进度","幕墙"})private String mq;@ColumnWidth(20)@ExcelProperty(index = 28,value = {"竣工验收日期"})private String endDate;@ColumnWidth(20)@ExcelProperty(index = 29,value = {"监督小组成员"})private String monitorZl;@ColumnWidth(20)@ExcelProperty(index = 30,value = {"备注"})private String remark;}

Controller层

@PostMapping("/projectStatusExportExcel")public void projectStatusExportExcel(ManagerProjectBean bean, HttpServletResponse response, HttpSession session) {// 判断是否可以获取到用户UserInfoBean userInfoBean = (UserInfoBean) session.getAttribute("userInfoBean");if (FundStringUtil.isEmpty(userInfoBean) && FundStringUtil.isNotEmpty(bean.getUserId())) {userInfoBean = personService.findUserInfoBean(bean.getUserId());}if (FundStringUtil.isNotEmpty(userInfoBean.getCompanyType())) {if (VersionMonitorTypeEnum.SUP_VERSION_MONITOR_TYPE_02.getCode().equalsIgnoreCase(userInfoBean.getVersionType())) {bean.setAqGovunitId(userInfoBean.getCompanyId());} else {bean.setZlGovunitId(userInfoBean.getCompanyId());}}if (null != userInfoBean) {try {List<ProjectStatusExportBean> list = managerProjectService.findManagerProjectStatusList(bean, userInfoBean);String sheetName = "工程状态统计";//ExportUtils.writeSingleExcel(sheetName,sheetName,list, ProjectStatusExportBean.class);ExportUtils.writeSingleExcel(new MergeStrategy(list.size(),1),sheetName,sheetName,list, ProjectStatusExportBean.class);} catch (IOException e) {e.printStackTrace();}}}

编写一个ExportUtils工具类

public static <T> void writeSingleExcel(MergeStrategy mergeStrategy,String fileName,String sheetName, List<T> tList, Class tClass) throws IOException{HttpServletResponse response = RequestHolder.getResponse();try (ServletOutputStream outputStream = response.getOutputStream()){setResponse(fileName, response);EasyExcel.write(outputStream, tClass).autoCloseStream(Boolean.FALSE).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomCellWriteHandler()).sheet(sheetName).registerWriteHandler(mergeStrategy).doWrite(tList);} catch (Exception e) {errorWrite(response, e);}}/*** 设置导出信息* @param fileName* @param response* @throws UnsupportedEncodingException*/private static void setResponse(String fileName, HttpServletResponse response) throws UnsupportedEncodingException {// 重置responseresponse.reset();response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码fileName = URLEncoder.encode(fileName + DateTimeFormatter.ofPattern("yyyy-MM-dd_HH_mm_ss").format(LocalDateTime.now()) + ExcelTypeEnum.XLSX.getValue(), "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName);}

自定义拦截器。对第一行第一列的头超链接到:/HXNLYW

package com.hhh.framework.handler;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.mon.usermodel.HyperlinkType;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.Hyperlink;import org.apache.poi.ss.usermodel.Row;import java.util.List;/*** 自定义拦截器。对第一行第一列的头超链接到:/HXNLYW** @author subway*/public class CustomCellWriteHandler implements CellWriteHandler {@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {if (isHead && cell.getColumnIndex() == 0) {CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);hyperlink.setAddress("/HXNLYW");cell.setHyperlink(hyperlink);}}}

自定义的合并策略,参考官方文档的LoopMergeStrategy

package com.hhh.framework.util;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.merge.AbstractMergeStrategy;import mons.collections.map.HashedMap;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.Arrays;import java.util.HashSet;import java.util.Map;import java.util.Set;/*** @Description: 自定义的合并策略,参考官方文档的LoopMergeStrategy* @Author 3hcw* @Date -03-08 18:37:37*/public class MergeStrategy extends AbstractMergeStrategy {// 合并的列编号，从0开始，指定的index或自己按字段顺序数private Set<Integer> mergeCellIndex = new HashSet<>();// 数据集大小，用于区别结束行位置private Integer maxRow = 0;// 禁止无参声明private MergeStrategy() {}//mergeCellIndex哪列数据需要合并public MergeStrategy(Integer maxRow, int... mergeCellIndex) {Arrays.stream(mergeCellIndex).forEach(item -> {this.mergeCellIndex.add(item);});this.maxRow = maxRow;}// 记录上一次合并的信息private Map<Integer, MergeRange> lastRow = new HashedMap();// 每行每列都会进入，绝对不要在这写循环@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {int currentCellIndex = cell.getColumnIndex();// 判断该行是否需要合并if (mergeCellIndex.contains(currentCellIndex)) {String currentCellValue = cell.getStringCellValue();int currentRowIndex = cell.getRowIndex();if (!lastRow.containsKey(currentCellIndex)) {// 记录首行起始位置lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));return;}//有上行这列的值了，拿来对比.MergeRange mergeRange = lastRow.get(currentCellIndex);if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {// 结束的位置触发下合并.// 同行同列不能合并，会抛异常if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));}// 更新当前列起始位置lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));}// 合并行 + 1mergeRange.endRow += 1;// 结束的位置触发下最后一次没完成的合并if (relativeRowIndex.equals(maxRow - 1)) {MergeRange lastMergeRange = lastRow.get(currentCellIndex);// 同行同列不能合并，会抛异常if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));}}}}}class MergeRange {public int startRow;public int endRow;public int startCell;public int endCell;public String lastValue;public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {this.startRow = startRow;this.endRow = endRow;this.startCell = startCell;this.endCell = endCell;this.lastValue = lastValue;}}

页面html

//新增按钮$("#export").click(function () {$("#exportProjectForm").attr("action", getRootPath() + "sup/projectRegisterRest/projectStatusExportExcel");$("#exportProjectForm").submit();});

最后导出效果

寄语

技术来之不易，望各位码友多多分享

如果觉得《java 复杂表头excel导出合并单元格》对你有帮助，请点赞、收藏，并留下你的观点哦！