Application scenarios of Excel import and export

1. Data import: reduce the workload of input

2. Data export: Archive statistics

3. Data transmission: Data transmission between heterogeneous systems

Official website address:EasyExcel · Language Finch (yuque.com)

Two, EasyExcel introduction

1. EasyExcel characteristics

Java domain parsing, Excel generation frameworks are more famous Apache POI, JXL, etc., but they all have a serious problem is very memory consumption, if your system concurrency is not large, it may be ok, but once the concurrency will be OOM or JVM frequent full GC.

EasyExcel is an Excel processing framework of Alibaba open source, known for its simple use and memory saving. The main reason for EasyExcel to greatly reduce memory usage is that it does not load all file data into memory at a time when parsing Excel, but reads data from disk row by row and analyzes it one by one.

EasyExcel parses a row by row and notifys the result of the row in observer mode (AnalysisEventListener).


try (OutputStream os = response.getOutputStream()) {
            String sheetName = "xxxxx";
            String fileName = URLEncoder.encode("xxxxx" + System.currentTimeMillis(), "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition"."attachment; filename=" + fileName + ".xlsx");
            / / aaaaaaa title
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), aaaaaaa.class).build();
            // Write sheetName, not set for the title
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();
            // Set the label to red
            WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).registerWriteHandler(new ExportWriterHandlerExcel()).build();
            // Writes create headers
            excelWriter.write(excelVOS, writeSheet, writeTable0);
            excelWriter.finish();
            os.flush();
        } catch (IOException e) {
            log.error(e," ExportWriterHandlerExcel is error",e.getMessage());
        }
		
Copy the code

Implement CellWriteHandler

Implement CellWriteHandler custom cell style processor that supports font style, background color, border style, alignment, and line wrap.

public class ExportWriterHandlerExcel extends ExcelWriteHandler implements CellWriteHandler { @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {/ / skip the title if (isHead | | cell. GetRowIndex () < 3) {return; } // If (!" name".equals(head.getFieldName())) { return; } Row row = cell.getRow(); if (null == row) { return; } // Get columns from 0 Cell endTime = row.getcell (5); if (null ! = endTime && ! StringUtils. IsEmpty (endTime. GetStringCellValue ())) {/ / split time String [] the split = endTime. GetStringCellValue (). The split (" - "); long parseLong = DateUtil.parse(split[1]).getTime(); If (dateutil.date ().getTime() > parseLong) {setColor(endTime, writeSheetHolder); } } Cell targetMoney = row.getCell(6); Cell donationMoney = row.getCell(7); if (null ! = targetMoney && ! StringUtils.isEmpty(targetMoney.getStringCellValue()) && null ! = donationMoney && ! StringUtils.isEmpty(donationMoney.getStringCellValue())) { BigDecimal target = new BigDecimal(targetMoney.getStringCellValue()); BigDecimal donation = new BigDecimal(donationMoney.getStringCellValue()); if (donation.compareTo(target) > 0) { setColor(donationMoney, writeSheetHolder); }}}}Copy the code

ExcelWriteHandler Custom class marking methods in red

public class ExcelWriteHandler { protected void setColor(Cell cell, WriteSheetHolder writeSheetHolder) { Sheet sheet = writeSheetHolder.getSheet(); Workbook workbook = sheet.getWorkbook(); Font font = workbook.createFont(); font.setFontHeightInPoints((short)12); font.setColor(IndexedColors.RED.getIndex()); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(font); cell.setCellStyle(cellStyle); }}Copy the code

Example entity class annotations

// Excel width @columnWidth (20) // color font size @headfontStyle (fontHeightInPoints = 15)Copy the code

Double data

try (OutputStream os = response.getOutputStream();) { String sheetName = "xxxxx"; String fileName = URLEncoder.encode("xxxxxx" + System.currentTimeMillis(), "UTF-8"); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx"); // Large list header FirstHeadVO ExcelWriter ExcelWriter = EasyExcel. Write (response.getOutputStream(), firsTheadvo.class).build(); WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build(); WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build(); WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).registerWriteHandler(new FirstHeadVOWriterHandlerExcel()).build(); // Small table header writetable0.setclazz (secondexcelvo.class); // The first write creates a header excelwriter. write(totalList, writeSheet, writeTable0); Excelwriter.write (getFirstHeadVO(datapagelist.getDatalist ()), writeSheet, writeTable1); excelWriter.finish(); os.flush(); } catch (IOException e) { log.error("call method ,{},{}", e, e.getMessage()); }Copy the code

Other style

Blog.csdn.net/qq_38974638…