import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.read.metadata.ReadSheet; import com.alibaba.excel.write.handler.WriteHandler; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.apache.ibatis.session.ResultHandler; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.nio.charset.StandardCharsets; import java.util.*; import java.util.function.BiConsumer; @Slf4j public class ExcelUtils { private static final String FILE_SUFFIX = ".xlsx"; /** * 导出excel * @param response * @param fileName 文件名 * @param sheetName sheet名 * @param list 数据 list为空返回 空Excel */ public static void export(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class<?> zlass) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileNameCode = URLEncoder.encode(fileName, "utf-8").replaceAll("\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameCode + FILE_SUFFIX); EasyExcel.write(response.getOutputStream(), zlass) .sheet(sheetName).doWrite(list); } /** * excel导出 批注 * @param response * @param fileName 文件名 * @param list 导出数据 * @param writeHandler 批注拦截器 */ public static void export(HttpServletResponse response, String fileName, List list, WriteHandler writeHandler, Class zlass) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileNameCode = URLEncoder.encode(fileName, StandardCharsets.UTF_8); response.setHeader("Content-disposition", "attachment;filename=" + fileNameCode + FILE_SUFFIX); EasyExcel.write(response.getOutputStream(), zlass) .inMemory(Boolean.TRUE).registerWriteHandler(writeHandler) .sheet(fileName).doWrite(list); } public static void fill(ExcelWriter excelWriter, FillConfig fillConfig, Object data, String sheetName){ WriteSheet writeSheet= EasyExcel.writerSheet().build(); if (sheetName != null) { writeSheet.setSheetName(sheetName); } excelWriter.fill(data, fillConfig, writeSheet); } /** * 目前仅支持mybatis的流处理 * @param query 查询条件的bean * @param consumer DAO对应的方法 * @param excelWriter 由于分层的原因这个需要手动构造来传 * 参考构造 * ExcelWriter excelWriter = EasyExcel.write(os, OrderDownloadDTO.class).build(); * @param <T> * @param <S> */ public static <T,S> void bigDataExport(T query, BiConsumer<T, ResultHandler<S>> consumer, ExcelWriter excelWriter) { try { WriteSheet[] writeSheet = new WriteSheet[] { EasyExcel.writerSheet(0, "sheet").build() }; List<S> list = new ArrayList<>(1000); // 0 代表当前的条数，等于100万时会被置为0 // 1 代表已经有多少个一百万 int [] c = new int[2]; consumer.accept(query, data -> { c[0]++; list.add(data.getResultObject()); if ((c[0] % 1000) == 0) { excelWriter.write(list, writeSheet[0]); list.clear(); if (c[0] == 1000000) { c[1]++; writeSheet[0] = EasyExcel.writerSheet(c[1], "sheet" + c[1]).build(); c[0] = 0; } } }); //可能有剩余的数据 excelWriter.write(list, writeSheet[0]); } finally { excelWriter.finish(); } } /** * 导出Excel * @param response * @param fileName 文件名 * @param list 导出数据 * @return void */ public static void export(HttpServletResponse response, String fileName, List<?> list, Class<?> zlass) throws Exception { export(response, fileName, fileName, list, zlass); } /** * 读取excel文件 * @param file 文件 * @param clazz 模板类 * @return java.util.List */ public static <T> List<T> read(MultipartFile file, Class<T> clazz) { try { return EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(clazz).sheet() .doReadSync(); } catch (IOException e) { e.printStackTrace(); } return null; } /** * Excel多sheet导出 * @param data must not null * @param sheetNames enable is null * @param outputStream */ public static void writeExcel(List<List> data, List<String> sheetNames, OutputStream outputStream) { if(data == null) { throw new RuntimeException("数据不存在"); } if(sheetNames != null && data.size() != sheetNames.size()) { throw new RuntimeException("sheet数据数量和sheet名称数量不相等"); } ExcelWriter excelWriter = EasyExcel.write(outputStream).build(); for(int i = 0;i < data.size();i++) { List d = data.get(i); if(d != null && d.size() > 0) { WriteSheet ws = EasyExcel.writerSheet().sheetNo(i) .sheetName(sheetNames == null ? "sheet" + i : sheetNames.get(i)) .head(d.get(0).getClass()).build(); excelWriter.write(d, ws); } } excelWriter.finish(); } public static List<LinkedHashMap<Integer, Object>> readExcel(InputStream inputStream) { return EasyExcel.read(inputStream).headRowNumber(0).autoCloseStream(true).doReadAllSync(); } public static List<LinkedHashMap<Integer, Object>> readExcel(InputStream inputStream, String sheetName) { return EasyExcel.read(inputStream).headRowNumber(0).autoCloseStream(true).sheet(sheetName).doReadSync(); } public static List<LinkedHashMap<Integer, Object>> readExcel(InputStream inputStream, Integer sheetIndex) { return EasyExcel.read(inputStream).headRowNumber(0).autoCloseStream(true).sheet(sheetIndex).doReadSync(); } public static Map<String, List<LinkedHashMap<Integer, Object>>> readNameExcel(InputStream inputStream, List<ReadSheet> readSheets) { SyncManySheetNameReadListener syncReadListener = new SyncManySheetNameReadListener(); EasyExcel.read(inputStream).headRowNumber(0).registerReadListener(syncReadListener).autoCloseStream(true) .build().read(readSheets).finish(); return syncReadListener.getMap(); } public static Map<String, List<LinkedHashMap<Integer, Object>>> readNameExcel(InputStream inputStream) { SyncManySheetNameReadListener syncReadListener = new SyncManySheetNameReadListener(); EasyExcel.read(inputStream).headRowNumber(0).registerReadListener(syncReadListener).autoCloseStream(true) .doReadAll(); return syncReadListener.getMap(); } public static Map<Integer, List<LinkedHashMap<Integer, Object>>> readIndexExcel(InputStream inputStream, List<ReadSheet> readSheets) { SyncManySheetIndexReadListener syncReadListener = new SyncManySheetIndexReadListener(); EasyExcel.read(inputStream).headRowNumber(0).registerReadListener(syncReadListener).autoCloseStream(true) .build().read(readSheets).finish(); return syncReadListener.getMap(); } public static Map<String, List<LinkedHashMap<Integer, Object>>> readExcel(List<String> sheetNames, InputStream is) { Map<String, List<LinkedHashMap<Integer, Object>>> data; if(DataUtils.isEmpty(sheetNames)) { data = new HashMap<>(); data.put("", readExcel(is)); } else { List<ReadSheet> list = new ArrayList<>(); for(String name : sheetNames) { list.add(new ReadSheet(null, name)); } data = readNameExcel(is, list); } return data; } public static String getAsString(LinkedHashMap<Integer, Object> cell, int i) { Object o = cell.get(i); if(o == null) { return null; } return o.toString(); } public static String getAsStringEmptyToNull(LinkedHashMap<Integer, Object> cell, int i) { String t = getAsString(cell, i); if(StringUtils.isBlank(t)) { return null; } return t; } private static class SyncManySheetNameReadListener extends AnalysisEventListener<Object> { private Map<String, List<LinkedHashMap<Integer, Object>>> map = new HashMap<>(); public SyncManySheetNameReadListener() { } @Override public void invoke(Object object, AnalysisContext context) { map.computeIfAbsent(context.readSheetHolder().getSheetName(), a -> new ArrayList<>()).add((LinkedHashMap<Integer, Object>) object); } @Override public void doAfterAllAnalysed(AnalysisContext context) { } public Map<String, List<LinkedHashMap<Integer, Object>>> getMap() { return this.map; } } private static class SyncManySheetIndexReadListener extends AnalysisEventListener<Object> { private Map<Integer, List<LinkedHashMap<Integer, Object>>> map = new HashMap<>(); public SyncManySheetIndexReadListener() { } @Override public void invoke(Object object, AnalysisContext context) { map.computeIfAbsent(context.readSheetHolder().getSheetNo(), a -> new ArrayList<>()).add((LinkedHashMap<Integer, Object>) object); } @Override public void doAfterAllAnalysed(AnalysisContext context) { } public Map<Integer, List<LinkedHashMap<Integer, Object>>> getMap() { return this.map; } } public static class MergeCell extends AbstractMergeStrategy { @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { if(relativeRowIndex == null || relativeRowIndex == 0) { return; } int rowIndex = cell.getRowIndex(); int colIndex = cell.getColumnIndex(); sheet = cell.getSheet(); Row preRow = sheet.getRow(rowIndex - 1); Cell preCell = preRow.getCell(colIndex); List<CellRangeAddress> list = sheet.getMergedRegions(); for(int i = 0;i < list.size();i++) { CellRangeAddress cellRangeAddress = list.get(i); if(cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) { int lastColIndex = cellRangeAddress.getLastColumn(); int firstColIndex = cellRangeAddress.getFirstColumn(); CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex); sheet.addMergedRegion(cra); RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); } } } } }
模版导出 excel 使用例子
@Test public void test7() throws Exception { InputStream is = FileUtil.getInJarFileStream("invoice.xls"); ExcelWriter writer = EasyExcel.write("/Users/abc/Desktop/test.xls") .registerWriteHandler(new ExcelUtils.MergeCell()) .withTemplate(is).build(); InvoiceInfoDTO dto = FillBeanUtil.randomFillBean(InvoiceInfoDTO.class); ExcelUtils.fill(writer, null, dto, "invoice"); dto.getDetails().add(FillBeanUtil.randomFillBean(InvoiceDetailEntity.class)); FillConfig config = FillConfig.builder().forceNewRow(true).build(); ExcelUtils.fill(writer, config, dto.getDetails(), "invoice"); writer.finish(); }