记录一个利用alibaba的easyexcel实现的简易excel导出工具
功能说明：1、自定义表头、多级表头的表格导出
效果展示：
maven引入依赖：
<dependency>
<groupId>com.alibaba</groupId>
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 headList, List 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_);(\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)", "\"$\"#,##0.00_);(\"$\"#,##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_);(#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);(#,##0.00)", "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)", "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)", "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", "mm:ss", ":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 CACHE = new HashMap(8); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map 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 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 headList = new ArrayList(); 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 dataList = new ArrayList(); for (int i= 0;i
页:
[1]