功能说明：
1、自定义表头、多级表头的表格导出
效果展示：
maven引入依赖：
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency>
工具类：
com.alibaba.excel.EasyExcel; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; import com.google.common.collect.Lists; import com.heit.common.core.exception.ZtCommonException; import lombok.SneakyThrows; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.ObjectUtils; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import javax.servlet.http.HttpServletResponse; import javax.validation.constraints.NotNull; import java.net.URLEncoder; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 导出数据 * author: 唐泽齐 */ public class ExclUtil { /** * 导出excel * @param response * @param outFileName 导出文件名 * @param headList 表头列表 分页名->合并列名->...->...->列名 * @param dataList 数据列表 */ @SneakyThrows public static void write(@NotNull HttpServletResponse response, String outFileName, List<List<String>> headList, List<List<Object>> dataList) { if (ObjectUtils.isEmpty(response)) throw new ZtCommonException("导出环境异常!"); if (ObjectUtils.isEmpty(outFileName)) throw new ZtCommonException("未设置导出文件名!"); if (ObjectUtils.isEmpty(headList)) throw new ZtCommonException("未设置导出表格式!"); if (ObjectUtils.isEmpty(dataList)) throw new ZtCommonException("暂无可导出的数据!"); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(outFileName, "UTF-8").replaceAll("\\+", "%20") + ".xls"); response.setHeader("Access-Control-Expose-Headers", "Content-disposition"); // 设置单元格样式 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(getHeadStyle(), getContentStyle()); // 列宽策略设置 ExcelCellWidthStyleStrategy widthStyleStrategy = new ExcelCellWidthStyleStrategy(); //导出数据 EasyExcel.write(response.getOutputStream()). registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(widthStyleStrategy) .head(headList) .sheet().doWrite(dataList); } private static final String[] _formats = new String[]{ "General", "0", "0.00", "#,##0", "#,##0.00", "\"$\"#,##0_);(\"$\"#,##0)", "\"$\"#,##0_);[Red](\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)", "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)", "0%", "0.00%", "0.00E+00", "# ?/?", "# ??/??", "m/d/yy", "d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM", "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm", "reserved-0x17", "reserved-0x18", "reserved-0x19", "reserved-0x1A", "reserved-0x1B", "reserved-0x1C", "reserved-0x1D", "reserved-0x1E", "reserved-0x1F", "reserved-0x20", "reserved-0x21", "reserved-0x22", "reserved-0x23", "reserved-0x24", "#,##0_);(#,##0)", "#,##0_);[Red](#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);[Red](#,##0.00)", "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)", "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)", "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", "mm:ss", "[h]:mm:ss", "mm:ss.0", "##0.0E+0", "@" // 文本格式 }; /** * 标题样式 * * @return */ private static WriteCellStyle getHeadStyle() { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景颜色 // headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex()); // headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND); // 字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontName("宋体");//设置字体名字 headWriteFont.setFontHeightInPoints((short) 10);//设置字体大小 headWriteFont.setBold(true);//字体加粗 headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体; // 样式 headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框; headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色; headWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框; headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色; headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框; headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色; headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框; headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色; headWriteCellStyle.setWrapped(true); //设置自动换行; headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐; headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适 return headWriteCellStyle; } /** * 内容样式 * * @return */ private static WriteCellStyle getContentStyle() { // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 背景绿色 // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定 // contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND); // 设置字体 WriteFont contentWriteFont = new WriteFont(); contentWriteFont.setFontHeightInPoints((short) 9);//设置字体大小 contentWriteFont.setFontName("宋体"); //设置字体名字 contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体; //设置样式; contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框; contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色; contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框; contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色; contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框; contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色; contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框; contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色; contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 contentWriteCellStyle.setWrapped(true); //设置自动换行; contentWriteCellStyle.setDataFormat((short) 49);//设置单元格格式是：文本格式，方式长数字文本科学计数法 contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适 return contentWriteCellStyle; } static class ExcelCellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { // 可以根据这里的最大宽度，按自己需要进行调整,搭配单元格样式实现类中的，自动换行，效果更好 private static final int MAX_COLUMN_WIDTH = 20; private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap(16); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > MAX_COLUMN_WIDTH) { columnWidth = MAX_COLUMN_WIDTH; } Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData 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; } } } } } public static void main(String[] args) { //导出环境 HttpServletResponse response = null; //文件名、分页名 String outFileName = "测试文件"; //表头格式 List<List<String>> headList = new ArrayList<List<String>>(); headList.add(Lists.newArrayList(outFileName, "合并列1", "列1")); headList.add(Lists.newArrayList(outFileName, "合并列1", "列2")); headList.add(Lists.newArrayList(outFileName, "合并列2", "列3")); headList.add(Lists.newArrayList(outFileName, "合并列2", "列4")); //数据 List<List<Object>> dataList = new ArrayList<>(); for (int i= 0;i<100;i++) { List<Object> list = new ArrayList<>(); list.add("1"+i); list.add("2"+i); list.add("3"+i); list.add("4"+i); dataList.add(list); } //导出表格 write(response,outFileName,headList,dataList); } }
来源链接：https://www.cnblogs.com/tangzeqi/p/18880124
© 版权声明
本站所有资源来自于网络，仅供学习与参考，请勿用于商业用途，否则产生的一切后果将由您(转载者)自己承担！
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
THE END
暂无评论内容