1. Excel parsing tool EasyExcel comprehensive exploration

1.1. Introduction

Earlier we thought that Excel parsing generally uses POI, but POI has a serious problem in that it consumes a lot of memory. Therefore, ali staff rewrote it to create EasyExcel, which solved the problem of excessive memory consumption and encapsulated it to make it more convenient for users to use

Next, I will first introduce all its functional details, how to use and part of the source code analysis

1.2. Excel to read

Example 1.2.1.

{@link DemoData} * <p>2. Because the default is asynchronous reading of Excel, you need to create an Excel line-by-line callback listener, see {@link DemoDataListener} * <p>3. */ @test public void simpleRead() {String fileName = testFileutil.getPath () + "demo" + file.separator + "demo.xlsx"; // We need to specify which class to use to read, EasyExcel. Read(fileName, demodata.class, new DemoDataListener()).sheet().doread (); }Copy the code

• The official instructions are also clear and easy to usefileNameisPath + File name.DemoDataIs the entity class for Excel data,DemoDataListenerThe name sounds like a listener, which is used to listen to and process every piece of data that is read

1.2.2. Source code analysis

1.2.2.1. Core source codeXlsxSaxAnalyser

• The core Excel parsing I think is this classXlsxSaxAnalyser, does a lot of things in its constructor

public XlsxSaxAnalyser(AnalysisContext analysisContext, InputStream decryptedStream) throws Exception { ... SheetMap XSSFReader XSSFReader = new XSSFReader(PKG); analysisUse1904WindowDate(xssfReader, readWorkbookHolder); stylesTable = xssfReader.getStylesTable(); sheetList = new ArrayList<ReadSheet>(); sheetMap = new HashMap<Integer, InputStream>(); XSSFReader.SheetIterator ite = (XSSFReader.SheetIterator)xssfReader.getSheetsData(); int index = 0; if (! ite.hasNext()) { throw new ExcelAnalysisException("Can not find any sheet!" ); } while (ite.hasNext()) { InputStream inputStream = ite.next(); sheetList.add(new ReadSheet(index, ite.getSheetName())); sheetMap.put(index, inputStream); index++; }}Copy the code

1.2.2.2. doRead

• The one that really starts doing the parsing in the example isdoReadMethod, keep going into this method, and you’ll see that the final method that actually executes isXlsxSaxAnalyserOf the classexecuteMethods; You can see in the following methodparseXmlSourceAnd what that means issheetMapReal data cached

@Override public void execute(List<ReadSheet> readSheetList, Boolean readAll) { for (ReadSheet readSheet : sheetList) { readSheet = SheetUtils.match(readSheet, readSheetList, readAll, analysisContext.readWorkbookHolder().getGlobalConfiguration()); if (readSheet ! = null) { analysisContext.currentSheet(readSheet); parseXmlSource(sheetMap.get(readSheet.getSheetNo()), new XlsxRowHandler(analysisContext, stylesTable)); // The last sheet is read analysisContext.readSheetHolder().notifyAfterAllAnalysed(analysisContext); }}}Copy the code

Overview 1.2.2.3.DemoDataListenerimplementation

• Corresponding to our users need handwritten code, our listenerDemoDataListenerThere are two implementation methods in,invokeThat corresponds to the code aboveparseXmlSourcewhiledoAfterAllAnalysedCorresponds to the above methodnotifyAfterAllAnalysed, respectively, parses each piece of data first and notifies all listeners when the last page has been read

@override public void invoke(DemoData data, AnalysisContext context) {logger. info(" parsed to a data :{}", JSON.toJSONString(data)); list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); Logger. info(" All data parsed!" ); }Copy the code

1.2.2.4. parseXmlSourceThe specific implementation

• Look at the key points of the identification, which is the core of the analysis

private void parseXmlSource(InputStream inputStream, ContentHandler handler) { InputSource inputSource = new InputSource(inputStream); try { SAXParserFactory saxFactory = SAXParserFactory.newInstance(); saxFactory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true); saxFactory.setFeature("http://xml.org/sax/features/external-general-entities", false); saxFactory.setFeature("http://xml.org/sax/features/external-parameter-entities", false); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader xmlReader = saxParser.getXMLReader(); xmlReader.setContentHandler(handler); / / key xmlReader. Parse (inputSource); inputStream.close(); } catch (ExcelAnalysisException e) { throw e; } catch (Exception e) { throw new ExcelAnalysisException(e); } finally { if (inputStream ! = null) { try { inputStream.close(); } catch (IOException e) { throw new ExcelAnalysisException("Can not close 'inputStream'!" ); }}}}Copy the code

• Since the layers are so deep, I use a screenshot to show how it is called

1.2.2.5. notifyAfterAllAnalysedThe specific implementation

• Specific seenotifyAfterAllAnalysedThe code that we implementedDemoDataListenerListener inheritanceAnalysisEventListenerAnd theAnalysisEventListenerimplementationReadListenerinterface

@Override public void notifyAfterAllAnalysed(AnalysisContext analysisContext) { for (ReadListener readListener : readListenerList) { readListener.doAfterAllAnalysed(analysisContext); }}Copy the code

1.3. Write Excel

Example 1.3.1.

• The following example, or simple to use, and read more similar

The simplest write * / * * * < p > 1. Create excel corresponding entity object Reference to {@ link com. Alibaba. Easyexcel. Test. The demo. Write. DemoData} * < p > 2. */ @test public void simpleWrite() {String fileName = testFileutil.getPath () + "write" + System.currentTimeMillis() + ".xlsx"; // We need to specify which class to read, and then write the first sheet, Easyexcel.write (fileName, demodata.class).sheet(" template ").dowrite (data()); } private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); Data. setString(" string "+ I); data.setDate(new Date()); Data. SetDoubleData (0.56); list.add(data); } return list; }Copy the code

1.3.2. Source code analysis

1.3.2.1. doWrite

• Like to readdoWriteIt’s the real thing. This time we’ll follow through this portal

    public void doWrite(List data) {
        if (excelWriter == null) {
            throw new ExcelGenerateException("Must use 'EasyExcelFactory.write().sheet()' to call this method");
        }
        excelWriter.write(data, build());
        excelWriter.finish();
    }Copy the code

1.3.2.2. write

• Obviously,writeCore. Continue inExcelWriterClass, look at the nameaddContentIs to add data, byexcelBuilderExcel builder to add, this isExcelBuilderImplclass

    public ExcelWriter write(List data, WriteSheet writeSheet, WriteTable writeTable) {
        excelBuilder.addContent(data, writeSheet, writeTable);
        return this;
    }Copy the code

1.3.2.3. addContent

• As you can see below, the display encapsulates and instantiates some of the data createdExcelWriteAddExecutorWrite data executor, the core isaddMethods the

@Override public void addContent(List data, WriteSheet writeSheet, WriteTable writeTable) { try { if (data == null) { return; } context.currentSheet(writeSheet, WriteTypeEnum.ADD); context.currentTable(writeTable); if (excelWriteAddExecutor == null) { excelWriteAddExecutor = new ExcelWriteAddExecutor(context); } / / core excelWriteAddExecutor. Add (data); } catch (RuntimeException e) { finish(); throw e; } catch (Throwable e) { finish(); throw new ExcelGenerateException(e); }}Copy the code

1.3.2.4. add

• You can see that you’re obviously walking through the dataaddOneRowOfDataToExcelInsert into Excel

    public void add(List data) {
        if (CollectionUtils.isEmpty(data)) {
            return;
        }
        WriteSheetHolder writeSheetHolder = writeContext.writeSheetHolder();
        int newRowIndex = writeSheetHolder.getNewRowIndexAndStartDoWrite();
        if (writeSheetHolder.isNew() && !writeSheetHolder.getExcelWriteHeadProperty().hasHead()) {
            newRowIndex += writeContext.currentWriteHolder().relativeHeadRowIndex();
        }
        // BeanMap is out of order,so use fieldList
        List<Field> fieldList = new ArrayList<Field>();
        for (int relativeRowIndex = 0; relativeRowIndex < data.size(); relativeRowIndex++) {
            int n = relativeRowIndex + newRowIndex;
            addOneRowOfDataToExcel(data.get(relativeRowIndex), n, relativeRowIndex, fieldList);
        }
    }Copy the code

1.3.2.5. addOneRowOfDataToExcel

• So we’re going to do the preparation of creating Excel rows, including the attributes of the rows that the processor needs to handle, and then we’re going to do the example of inserting a Java object and enteringaddJavaObjectToExcelmethods

    private void addOneRowOfDataToExcel(Object oneRowData, int n, int relativeRowIndex, List<Field> fieldList) {
        if (oneRowData == null) {
            return;
        }
        WriteHandlerUtils.beforeRowCreate(writeContext, n, relativeRowIndex, Boolean.FALSE);
        Row row = WorkBookUtil.createRow(writeContext.writeSheetHolder().getSheet(), n);
        WriteHandlerUtils.afterRowCreate(writeContext, row, relativeRowIndex, Boolean.FALSE);
        if (oneRowData instanceof List) {
            addBasicTypeToExcel((List)oneRowData, row, relativeRowIndex);
        } else {
            addJavaObjectToExcel(oneRowData, row, relativeRowIndex, fieldList);
        }
        WriteHandlerUtils.afterRowDispose(writeContext, row, relativeRowIndex, Boolean.FALSE);
    }Copy the code

1.3.2.6. addJavaObjectToExcel

• inExcelWriteAddExecutorExecute in the executor classaddJavaObjectToExcel, the data is parsed into title and content and encapsulated into a format suitable for ExcelCellDataAfter this step we haven’t seen the file stream generated, so the next step

private void addJavaObjectToExcel(Object oneRowData, Row row, int relativeRowIndex, List<Field> fieldList) { WriteHolder currentWriteHolder = writeContext.currentWriteHolder(); BeanMap beanMap = BeanMap.create(oneRowData); Set<String> beanMapHandledSet = new HashSet<String>(); int cellIndex = 0; // If it's a class it needs to be cast by type if (HeadKindEnum.CLASS.equals(writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())) { Map<Integer, Head> headMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap(); Map<Integer, ExcelContentProperty> contentPropertyMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getContentPropertyMap(); for (Map.Entry<Integer, ExcelContentProperty> entry : contentPropertyMap.entrySet()) { cellIndex = entry.getKey(); ExcelContentProperty excelContentProperty = entry.getValue(); String name = excelContentProperty.getField().getName(); if (writeContext.currentWriteHolder().ignore(name, cellIndex)) { continue; } if (! beanMap.containsKey(name)) { continue; } Head head = headMap.get(cellIndex); WriteHandlerUtils.beforeCellCreate(writeContext, row, head, cellIndex, relativeRowIndex, Boolean.FALSE); Cell cell = WorkBookUtil.createCell(row, cellIndex); WriteHandlerUtils.afterCellCreate(writeContext, cell, head, relativeRowIndex, Boolean.FALSE); Object value = beanMap.get(name); CellData cellData = converterAndSet(currentWriteHolder, excelContentProperty.getField().getType(), cell, value, excelContentProperty); WriteHandlerUtils.afterCellDispose(writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE); beanMapHandledSet.add(name); } } // Finish if (beanMapHandledSet.size() == beanMap.size()) { return; } if (cellIndex ! = 0) { cellIndex++; } Map<String, Field> ignoreMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getIgnoreMap(); initFieldList(oneRowData.getClass(), fieldList); for (Field field : fieldList) { String filedName = field.getName(); boolean uselessData = ! beanMap.containsKey(filedName) || beanMapHandledSet.contains(filedName) || ignoreMap.containsKey(filedName) || writeContext.currentWriteHolder().ignore(filedName, cellIndex); if (uselessData) { continue; } Object value = beanMap.get(filedName); if (value == null) { continue; } WriteHandlerUtils.beforeCellCreate(writeContext, row, null, cellIndex, relativeRowIndex, Boolean.FALSE); Cell cell = WorkBookUtil.createCell(row, cellIndex++); WriteHandlerUtils.afterCellCreate(writeContext, cell, null, relativeRowIndex, Boolean.FALSE); CellData cellData = converterAndSet(currentWriteHolder, value.getClass(), cell, value, null); WriteHandlerUtils.afterCellDispose(writeContext, cellData, cell, null, relativeRowIndex, Boolean.FALSE); }}Copy the code

1.3.2.7. finish

• doWriteThere is another step after middlefinish

    public void finish() {
        excelBuilder.finish();
    }Copy the code

• in-depthExcelBuilderImplclass

    @Override
    public void finish() {
        if (context != null) {
            context.finish();
        }
    }Copy the code

• toWriteContextImplWrite content implementation classfinishIn the method, we can see thatwriteWorkbookHolder.getWorkbook().write(writeWorkbookHolder.getOutputStream());This sentence is key, will write Excel hold container content stream output; After that is the process of closing the stream and deleting the temporary files

@Override public void finish() { WriteHandlerUtils.afterWorkbookDispose(this); if (writeWorkbookHolder == null) { return; } Throwable throwable = null; boolean isOutputStreamEncrypt = false; try { isOutputStreamEncrypt = doOutputStreamEncrypt07(); } catch (Throwable t) { throwable = t; } if (! IsOutputStreamEncrypt) {try {/ / key writeWorkbookHolder getWorkbook (), write (writeWorkbookHolder. GetOutputStream ()); writeWorkbookHolder.getWorkbook().close(); } catch (Throwable t) { throwable = t; } } try { Workbook workbook = writeWorkbookHolder.getWorkbook(); if (workbook instanceof SXSSFWorkbook) { ((SXSSFWorkbook)workbook).dispose(); } } catch (Throwable t) { throwable = t; } try { if (writeWorkbookHolder.getAutoCloseStream() && writeWorkbookHolder.getOutputStream() ! = null) { writeWorkbookHolder.getOutputStream().close(); } } catch (Throwable t) { throwable = t; } if (! isOutputStreamEncrypt) { try { doFileEncrypt07(); } catch (Throwable t) { throwable = t; } } try { if (writeWorkbookHolder.getTempTemplateInputStream() ! = null) { writeWorkbookHolder.getTempTemplateInputStream().close(); } } catch (Throwable t) { throwable = t; } clearEncrypt03(); if (throwable ! = null) { throw new ExcelGenerateException("Can not close IO", throwable); } if (LOGGER.isDebugEnabled()) { LOGGER.debug("Finished write."); }}Copy the code

1.4. File upload

• It provides a receiveInputStreamParameter, and then Excel read is not much different

{@link UploadData} * <p> * 2. Because the default is asynchronous reading of Excel, you need to create an Excel line-by-line callback listener, see {@link UploadDataListener} * <p> * 3. @responseBody public String upload(MultipartFile file) throws IOException {/ @postmapping ("upload") @responseBody public String upload(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener()).sheet().doRead(); return "success"; }Copy the code

1.5. File download

• Write to provide parametersOutputStream, other and file write similar

/** ** file download * <p> * 1. Create the entity object corresponding to Excel refer to {@link DownloadData} * <p> * 2. * <p> * 3. */ @getMapping ("download") public void download(HttpServletResponse Response) Throws IOException {// SetContentType ("application/vnd.ms-excel"); postman Response.setContentType ("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder. Encode (" test ", "utF-8 "); response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx"); EasyExcel. Write(response.getOutputStream(), downloaddata.class).sheet(" template ").dowrite (data()); }Copy the code

1.6. Reading skills

1.6.1. Excel reads multiple pages

• These are the most basic single page reads and writes that we callsheet()Method is actually the default page 1, so how to read multiple pages?

/** * read multiple or all of the sheets. Note that one sheet cannot be read more than once. {@link DemoData} * <p> * 2. Because the default is asynchronous reading of Excel, you need to create an Excel line-by-line callback listener, see {@link DemoDataListener} * <p> * 3. */ @test public void repeatedRead() {String fileName = testFileutil.getPath () + "demo" + file.separator + "demo.xlsx"; // DemoDataListener's doAfterAllAnalysed is called once after each sheet is read. Then all sheets will write EasyExcel. Read (fileName, demodata.class, new DemoDataListener()).doreadAll (); // Read part sheet fileName = testFileutil.getPath () + "demo" + file.separator + "demo.xlsx"; ExcelReader excelReader = EasyExcel.read(fileName).build(); // Register the same head and Listener to use a different Listener ReadSheet readSheet1 = EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build(); ReadSheet readSheet2 = EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();  ExcelReader. Read (readSheet1, readSheet2); Excelreader.finish (); }Copy the code

• You can seedoReadAllThe Sheet method reads all sheet pages
• To read individual pages, use the second methodreadSheet(index)Index is the page position, counting from 0

1.6.2. Custom field conversion

• When we read and write, we may need to convert dates, add fixed prefixes to strings, and so on

@data public class ConverterData {/** * I customize the converter, no matter what the database passes. I gave him a "custom:" * / @ ExcelProperty (converter = CustomStringStringConverter. Class) private String String; /** ** string (); */ @dateTimeFormat (" YYYY MM dd HH MM ss SEC ") private String date; /** * I want to receive the percentage of the number */ @numberFormat ("#.##%") private String doubleData; }Copy the code

• asCustomStringStringConverterClass is a custom converter that can modify strings to some extent, and date number formatting, it has provided annotationsDateTimeFormatandNumberFormat
• The converter is implemented as followsConverterInterface can be used aftersupportExcelTypeKeyThis is to determine the cell type,convertToJavaDataThis is the read transformation,convertToExcelDataThis is a write conversion

import com.alibaba.excel.converters.Converter; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.property.ExcelContentProperty; public class CustomStringStringConverter implements Converter<String> { @Override public Class supportJavaTypeKey() { return String.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; Override public String convertToJavaData(CellData CellData, ExcelContentProperty contentProperty, GlobalConfiguration GlobalConfiguration) {return "Custom:" + cellData.getStringValue(); } @override public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return new CellData(value); }}Copy the code

• Here, the interception of the analytical result is as follows, and the original data isThe value is 0 2020/1/1 1:01

{"date":"2020 01 01 01 01 minute 01 second ","doubleData":"100%","string":" custom: string 0"}Copy the code

1.6.3. Specify the number of header rows

EasyExcel. Read (fileName, demodata.class, new DemoDataListener()).sheet() If you have multiple rows, you can set other values. It is ok not to pass in, because by default DemoData will be parsed. It does not specify a header, which defaults to 1 line.Copy the code

1.6.4. Read header data

• As long as the realizationAnalysisEventListenerOverrides the listener of the interfaceinvokeHeadMapMethods can be

Public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {logger. info(" Parse to a header :{}", json.tojsonString (headMap)); }Copy the code

1.6.5. Conversion exception processing

• As long as the realizationAnalysisEventListenerOverrides the listener of the interfaceonExceptionMethods can be

@override public void onException(Exception Exception, AnalysisContext context) {logger. error(" Failed to parse, But continue parsing the next line :{}", exception.getMessage()); if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception; LOGGER. The error (" {}, the first {} column analysis abnormal ", excelDataConvertException. GetRowIndex (), excelDataConvertException. GetColumnIndex ()); }}Copy the code

1.6.6. Read cell parameters and types

• Use class attributesCellDataencapsulated

@Data public class CellDataReadDemoData { private CellData<String> string; Private CellData<Date> Date; private CellData<Double> doubleData; Private CellData<String> formulaValue; }Copy the code

• This reads the following data, including the cell data type

{"date":{"data":1577811661000,"dataFormat":22,"dataFormatString":" M/D /yy H: mm, "" formula" : false, "numberValue" : 43831.0423726852, "type" : "NUMBER"}, "doubleData" : {" data ": 1.0," formula ": false," numberVa Lue ": 1," type ":" NUMBER "}, "formulaValue" : {" data ":" string "01," formula ": true," formulaValue ":" _xlfn. CONCAT (A2, C2) ", "stringValue" : 01 "STRING", "type" : "STRING"}, "STRING" : {" data ":" STRING 0 ", "dataFormat" : 0, "dataFormatString" : "General", "formula" : false, "stringVal Ue ":"STRING 0","type":"STRING"}Copy the code

1.6.7. Synchronization return

• It is not recommended, but if you must use it in certain situations, it can be used as followsdoReadSyncMethod, which returns directlyList

/** * synchronous return, not recommended, */ @test public void synchronousRead() {String fileName = testFileutil.getPath () + "demo" + File.separator + "demo.xlsx"; // We need to specify which class to use to read, Finish List<Object> List = EasyExcel.read(fileName).head(demodata.class).sheet().doreadsync (); for (Object obj : list) { DemoData data = (DemoData)obj; Logger. info(" read data :{}", json.tojsonString (data)); Finish list = EasyExcel. Read (fileName).sheet().doreadsync (); Map<Integer, String> data = (Map<Integer, String>)obj; for (Object obj: list) {// Return the key-value pair of each column representing the column and its value Map<Integer, String> data = (Map<Integer, String>)obj; Logger. info(" read data :{}", json.tojsonString (data)); }}Copy the code

1.6.8. Read without objects

• As the name implies, instead of creating an entity object to read Excel data, we’ll use a Map to receive it, but this is not date-friendly and can be used for reading simple fields
• All else being the same, the listener’s inheritance of generic parameters changes to Map

public class NoModleDataListener extends AnalysisEventListener<Map<Integer, String>> {
    ...
}Copy the code

• The results are captured below

{0:" string 0",1:"2020-01-01 01:01:01",2:"1"}Copy the code

1.7. Writing skills

1.7.1. Exclude and write only specific fields

• useexcludeColumnFiledNamesTo exclude specific field writes, useincludeColumnFiledNamesIndicates that only specific fields are written

/** * export only specified columns * <p> * 1. Create excel corresponding entity object see {@link DemoData} * <p> * 2. */ @test public void excludeOrIncludeWrite() {String fileName = testFileutil.getPath () + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx"; Date Set<String> excludeColumnFiledNames = new HashSet<String>(); excludeColumnFiledNames.add("date"); // We need to specify which class to read, and then write the first sheet, EasyExcel. Write (fileName, Class).excludecolumNFiledNames (excludeColumnFiledNames).sheet(" template ").dowrite (data()); fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx"; Date Set<String> includeColumnFiledNames = new HashSet<String>(); includeColumnFiledNames.add("date"); // We need to specify which class to read, and then write the first sheet, EasyExcel. Write (fileName, Demodata.class).includecolumnFiledNames (includeColumnFiledNames).sheet(" template ").dowrite (data()); }Copy the code

1.7.2. Specify write columns

• The order in which columns are written can be specified, specifying index on the entity-class annotation, from smallest to largest and left to right

@data public class IndexData {@excelProperty (value = "String title ", index = 0) private String String; @excelProperty (value = "Date ", index = 1) private Date Date; /** * @excelProperty (value = "number title ", index = 3) private Double doubleData; }Copy the code

1.7.3. Complex header writes

• The complex head shown below

• We can do this by modifying the entity class annotations

@data Public class ComplexHeadData {@ExcelProperty({" master title ", "String title "}) private String String; @excelProperty ({" private title ", "Date title "}) private Date Date; @excelProperty ({" header ", "header "}) private Double doubleData; }Copy the code

1.7.4. Repeat multiple writes

• There are three types: 1. Write the same sheet repeatedly; 2. Write different sheets to the same object. 3. Write different sheets to different objects

{@link ComplexHeadData} * <p> * 2. Use the {@link ExcelProperty} annotation to specify the complex header * <p> * 3. */ @test public void repeatedWrite() {// method 1 is written to the same sheet String fileName = testFileutil.getPath () + "repeatedWrite" + System.currentTimeMillis() + ".xlsx"; ExcelWriter = EasyExcel. Write (fileName, demodata.class).build(); ExcelWriter = EasyExcel. WriteSheet = EasyExcel. WriterSheet (" template ").build(); For (int I = 0; int I = 0; i < 5; List<DemoData> data = data(); WriteSheet. SetSheetName (" template "); excelWriter.write(data, writeSheet); } /// Do not forget that Finish will help close the stream excelwriter.finish (); // If the same object is written to a different sheet fileName = testFileutil.getPath () + "repeatedWrite" + System.currentTimemillis () + ".xlsx"; ExcelWriter = EasyExcel. Write (fileName, demodata.class).build(); // call write, which I called five times, based on the total number of database pages. This is going to end up in 5 sheets for (int I = 0; i < 5; I ++) {sheetNo writeSheet = EasyExcel. WriterSheet (I, "template "+ I).build(); List<DemoData> data = data(); excelWriter.write(data, writeSheet); } /// Do not forget that Finish will help close the stream excelwriter.finish (); FileName = testFileutil.getPath () + "repeatedWrite" + System.currentTimemillis () + ".xlsx"; ExcelWriter = EasyExcel. Write (fileName).build(); // call write, which I called five times, based on the total number of database pages. This is going to end up in 5 sheets for (int I = 0; i < 5; I++) {// each time you create writeSheet, note that sheetNo must be specified. Note here that demodata. class can change every time, The same class I'm using here for convenience can actually always change writeSheet = easyExcel.writersheet (I, "template "+ I).head(demodata.class).build(); List<DemoData> data = data(); excelWriter.write(data, writeSheet); } /// Do not forget that Finish will help close the stream excelwriter.finish (); }Copy the code

1.7.5. Image export

• The export of pictures, there may be such a demand, it provides four types of data export, or very rich

@Test public void imageWrite() throws Exception { String fileName = TestFileUtil.getPath() + "imageWrite" + System.currentTimeMillis() + ".xlsx"; InputStream = null; InputStream = null; try { List<ImageData> list = new ArrayList<ImageData>(); ImageData imageData = new ImageData(); list.add(imageData); String imagePath = TestFileUtil.getPath() + "converter" + File.separator + "img.jpg"; / / into the four types of images Actual use just need to choose a imageData. SetByteArray (FileUtils. ReadFileToByteArray (new File (imagePath))); imageData.setFile(new File(imagePath)); imageData.setString(imagePath); inputStream = FileUtils.openInputStream(new File(imagePath)); imageData.setInputStream(inputStream); EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list); } finally { if (inputStream ! = null) { inputStream.close(); }}}Copy the code

• The picture is

@Data @ContentRowHeight(100) @ColumnWidth(100 / 8) public class ImageData { private File file; private InputStream inputStream; /** * If the string type must specify the converter, Default converted to string string * / @ ExcelProperty (converter = StringImageConverter. Class) private string string; private byte[] byteArray; }Copy the code

Export result: two rows and four columns, each column corresponding to a picture, four export types can be

• Among themStringImageConverterCustom converter is

public class StringImageConverter implements Converter<String> { @Override public Class supportJavaTypeKey() { return String.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.IMAGE; } @Override public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { throw new UnsupportedOperationException("Cannot convert images to string"); } @Override public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws IOException { return new CellData(FileUtils.readFileToByteArray(new File(value))); }}Copy the code

1.7.6. Field width and height Settings

• Set the entity class annotation properties

@Data @ContentRowHeight(10) @HeadRowHeight(20) @ColumnWidth(25) public class WidthAndHeightData { @excelProperty (" String title ") private String String; @excelProperty (" Date title ") private Date Date; @columnWidth (50) @excelProperty (" number ") private Double doubleData; }Copy the code

1.7.7. Custom styles

• Implementation can be complex, requiring headers, content strategies, font sizes, etc

@Test public void styleWrite() { String fileName = TestFileUtil.getPath() + "styleWrite" + System.currentTimeMillis() + ".xlsx"; WriteCellStyle headWriteCellStyle = new WriteCellStyle(); / / set to RED headWriteCellStyle. SetFillForegroundColor (IndexedColors. RED. GetIndex ()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)20); headWriteCellStyle.setWriteFont(headWriteFont); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // Specify FillPatternType as FillPatternType.SOLID_FOREGROUND otherwise cannot display the background color. Head the default FillPatternType so I can not specify contentWriteCellStyle. SetFillPatternType (FillPatternType. SOLID_FOREGROUND); / / background GREEN contentWriteCellStyle. SetFillForegroundColor (IndexedColors. GREEN. GetIndex ()); WriteFont contentWriteFont = new WriteFont(); / / font size contentWriteFont setFontHeightInPoints (20) (short); contentWriteCellStyle.setWriteFont(contentWriteFont); / / this strategy is head is the style of the head The style of the content is content Other strategies can achieve HorizontalCellStyleStrategy HorizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); // We need to specify which class to read, and then write the first sheet, EasyExcel. Write (fileName, DemoData. Class). RegisterWriteHandler (horizontalCellStyleStrategy). Sheet (" template "). DoWrite (data ()); }Copy the code

• Results the following
  
  

1.7.8. Cell merging

@Test public void mergeWrite() { String fileName = TestFileUtil.getPath() + "mergeWrite" + System.currentTimeMillis() + ".xlsx"; // merge every 2 rows. LoopMergeStrategy = new LoopMergeStrategy(2, 0); // We need to specify which class to read, and then write the first sheet, EasyExcel. Write (fileName, DemoData. Class). RegisterWriteHandler (loopMergeStrategy). Sheet (" template "). DoWrite (data ()); }Copy the code

• The effect is as follows, the first column of cell data, 2,3 rows merged

1.7.9. Automatic column width

• According to the author’s description, POI is not friendly to Chinese automatic column width adaptation, and EasyExcel is not able to accurately adapt column width for numbers. The adaptation strategy provided by POI can be used, but it cannot be accurately adapted, and can be rewritten by itself
• Register the processor if you want to use itLongestMatchColumnWidthStyleStrategy

@Test public void longestMatchColumnWidthWrite() { String fileName = TestFileUtil.getPath() + "longestMatchColumnWidthWrite" + System.currentTimeMillis() + ".xlsx"; // We need to specify which class to read, and then write the first sheet, EasyExcel. Write (fileName, LongestMatchColumnWidthData.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy ()). Sheet (" template "). DoWrite (dataLong ()); }Copy the code

1.7.10. Drop down, hyperlinks

• Dropdown, hyperlinks and other functions need to be customized implementation

@Test public void customHandlerWrite() { String fileName = TestFileUtil.getPath() + "customHandlerWrite" + System.currentTimeMillis() + ".xlsx"; // We need to specify which class to read, and then write the first sheet, EasyExcel. Write (fileName, DemoData.class).registerWriteHandler(new CustomSheetWriteHandler()) .registerWriteHandler(new CustomCellWriteHandler ()). Sheet (" template "). DoWrite (data ()); }Copy the code

• The main one is the processorCustomCellWriteHandlerClass, its implementationCellWriteHandlerInterface, we are in the post-processing methodafterCellDisposeDo the processing

public class CustomCellWriteHandler implements CellWriteHandler { private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class); @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// Anything can be done to the cell here logger. info(" {} line, {} column written." , cell.getRowIndex(), cell.getColumnIndex()); if (isHead && cell.getColumnIndex() == 0) { CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper(); Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL); hyperlink.setAddress("https://github.com/alibaba/easyexcel"); cell.setHyperlink(hyperlink); }}}Copy the code

1.7.11. Do not create object writes

• Do not set object class when setting write, add in headList>The object of the head

@test public void noModleWrite() {// 1 String fileName = testFileutil.getPath () + "noModleWrite" + System.currentTimeMillis() + ".xlsx"; // We need to specify which class to read, and then write the first sheet, EasyExcel.write(fileName).head(head()).sheet(" template ").dowrite (dataList()); } private List<List<String>> head() { List<List<String>> list = new ArrayList<List<String>>(); List<String> head0 = new ArrayList<String>(); Head0.add (" string "+ System.currentTimemillis ()); List<String> head1 = new ArrayList<String>(); Head1.add (" number "+ System.currentTimemillis ()); List<String> head2 = new ArrayList<String>(); Head2.add (" date "+ System.currentTimemillis ()); list.add(head0); list.add(head1); list.add(head2); return list; }Copy the code

1.8. Summarize

• Unconsciously listed so many easyExcel using skills and ways, here should include most of our work often used in excel reading and writing skills, welcome to collect

Github address of easyExcel

Welcome to collect the author knowledge collation, did not register please click here