EasyExcel write (export) Fanrou novel network https://www.quei.info

The essence of the Internet is sharing, which can share technology, experience, emotion and happiness~

I had this idea many years ago, and I have been engaged in the IT industry for a long time. I should record and share what I have learned in my work and spare time, and study and communicate with people.

If you are the one, please go to the island for a chat! Java is always welcome!

Today, let's take a look at using EasyExcel to export excel (write data into Excel)......

EasyExcel export Excel has many basic examples on the official website (EasyExcel official website - export data). I will extract them again, which is meaningless. In this part, you can go to the official website to find the example code according to your actual scene;

What I mainly share in this article is: how to encapsulate some tool classes in complex dynamic scenarios through reflection mechanism, and use tool classes to help us reduce repetitive work and make it easy to generate reports.

Function list:

1. The simplest Excel export
2. Control cell style through registerWriteHandler
3. Customize header through head
4. Consolidated header (static)
5. Dynamic header (dynamic consolidation header & horizontal infinite expansion column)
6. Multiple sheet pages

code snippet

@Data
public class Student {
    @ExcelIgnore
    private String stuId;

    @ExcelProperty("full name", index=0)
    private String name;
    @ExcelProperty("subject", index=1)
    private String subject;
    @ExcelProperty("fraction", index=2)
    private Double score;
}

public Object exportExcel(Class <? > excelBeanClass, List < List <? >> dataList, String title) {
    File file = new File(CommonConstants.CDN_FILE_LOCAL_REPORT);
    boolean mkdir = true;
    if(!file.exists()) {
        mkdir = file.mkdirs();
    }
    if(!mkdir) {
        return new ASOError(CommonConstants.ErrorEnum.OPRATION_FAIL.getCode(), "Failed to create file");
    }
    String fileName = title + "_" + System.currentTimeMillis() + ".xlsx";
    String filePath = CommonConstants.CDN_FILE_LOCAL_REPORT + File.separator + fileName;

    // core
    witeExcel(filePath, title, excelBeanClass, dataList);
    
    ObjectResponse < String > resp = new ObjectResponse < > ();
    String downloadPath = CommonConstants.CDN_FILE_REMOTE + "report" + File.separator + fileName;
    resp.setData(downloadPath);
    logger.info("generateReport: downloadPath={}", downloadPath);
    return resp;
}

1. The simplest Excel export

public void witeExcel(String file, String title, Class <? > excelBeanClass, List < List <? >> dataList) {
    ExcelWriterBuilder write = EasyExcel.write(filePath, excelBeanClass);
    write.autoCloseStream(true).sheet(title).doWrite(dataList);
}

public static void main(String []args) {
   List < List <? >> data = new ArrayList();
   ......
   System.out.println("download url is :" + exportExcel(Student.class, data, "Excel name"));
}

1. Control cell style through registerWriteHandler

public void witeExcel(String file, String title, Class <?> excelBeanClass, List <List<?>> dataList, List<WriteHandler> writeHandlerList) {
    ExcelWriterBuilder write = EasyExcel.write(filePath, excelBeanClass);
    if(CollectionUtils.isNotEmpty(writeHandlerList)) {
        **writeHandlerList.forEach(write::registerWriteHandler);**
    }
    write.autoCloseStream(true).sheet(title).doWrite(dataList);
}

public static void main(String []args) {
   //Get policy for headers and content
   WriteCellStyle headWriteCellStyle = new WriteCellStyle();
   WriteFont headWriteFont = new WriteFont();
   headWriteCellStyle.setWriteFont(headWriteFont);
   // ... Content cells can be styled in the same way
   WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

   HorizontalCellStyleStrategy horizontalCellStyleStrategy =
        new HorizontalCellStyleStrategy(headWriteCellStyle， contentWriteCellStyle);

   //The strategy of column width, which is a small unit
   Integer columnWidthArr[] = {3000, 3000, 2000, 6000};
   List<Integer> columnWidths = Arrays.asList(columnWidthArr);
   CustomSheetWriteHandler customSheetWriteHandler = new CustomSheetWriteHandler(columnWidths);

   //Custom cell policy
   CustomCellWriteHandler  = new CustomCellWriteHandler(yellowRowsSet);

   List<WriteHandler> writeHandlerList = new ArrayList();
   writeHandlerList.add(horizontalCellStyleStrategy);
   writeHandlerList.add(customSheetWriteHandler);
   writeHandlerList.add(customCellWriteHandler);

   List<List<Student>> data = new ArrayList();
   ......
   System.out.println("download url is :" + exportExcel(Student.class, data, "Excel name"));
}

1. Customize header through head

public void witeExcel(String file, String title, List<List<String>> head, List < List <? >> dataList) {
    ExcelWriterBuilder = EasyExcel.write(filePath);
    // head can be customized as needed
    **write.head(head);**
    write.autoCloseStream(true).sheet(title).doWrite(dataList);
}

public static void main(String []args) {
   List < List <? >> data = new ArrayList();
   ......
   System.out.println("download url is :" + exportExcel(Student.class, data, "Excel name"));
}

1. Consolidated header (static)

@Data
public class Student {
    @ExcelIgnore
    private String stuId;

    @ExcelProperty(value="full name", index=0)
    private String name;
    @ExcelProperty(value="achievement, subject", index=1)
    private String subject;
    @ExcelProperty(value="achievement, fraction", index=2)
    private Double score;
}

full name	achievement
	subject	fraction
AAA	language	100

1. Dynamic header (dynamic consolidation header & horizontal infinite expansion column)

//Complete header consolidation based on the configuration of Student object (static)
public void witeExcel(String mainTitle, Class <?> excelBeanClass) {
    Field[] fields = excelBeanClass.getDeclaredFields();
    for(Field field: fields) {
        ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class);
        if(excelProperty != null) {//Field processing marked with ExcelProperty annotation
            try {
                InvocationHandler excelH = Proxy.getInvocationHandler(excelProperty);
                Field excelF = excelH.getClass().getDeclaredField("memberValues");
                excelF.setAccessible(true);
                Map < String, Object > excelPropertyValues = (Map<String, Object>) excelF.get(excelH);
                excelPropertyValues.put("value", new String[] {mainTitle, excelProperty.value()[0]});
            } catch(Exception e) {
                //TODO: exception handling
            }
        }
    }
}

//Dynamically merge header based on Student object (dynamic)
public void dynamicHeaderByExcelProperty(String mainTitle, String secondTitle, Class <? > excelBeanClass) {
    Field[] fields = excelBeanClass.getDeclaredFields();
    for(Field field: fields) {
        ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class);
        if(excelProperty != null) {
            try {
                InvocationHandler excelH = Proxy.getInvocationHandler(excelProperty);
                Field excelF = excelH.getClass().getDeclaredField("memberValues");
                excelF.setAccessible(true);
                Map <String, Object> excelPropertyValues = (Map <String, Object> ) excelF.get(excelH);
                excelPropertyValues.put("value", new String[] { mainTitle, secondTitle, excelProperty.value()[0]});
            } catch(Exception e) {
                //TODO: exception handling
            }
        }
    }
}

//Single level dynamic merging and horizontal infinite expansion column of user-defined header
public List<List<String>> dynamicHeaderByCustom(String mainTitle, Class <?> excelBeanClass) {
    List<List<String>> headList = new ArrayList<>();
    Field[] fields = excelBeanClass.getDeclaredFields();
    for(Field field: fields) {
        ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class);
        if(excelProperty != null) {
            List <String> fieldHeadList = new ArrayList<> ();
            // TODO: extension to be optimized, specify the merging of different columns
            if(StringUtils.isNotBlank(mainTitle)) {
                fieldHeadList.add(mainTitle);
            }
            fieldHeadList.addAll(Arrays.asList(excelProperty.value()));
            headList.add(fieldHeadList);
        }
    }
    return headList;
}
//Multi level dynamic merging and horizontal infinite expansion column of user-defined header
public List<List<String>> dynamicHeaderByCustom(String mainTitle, List <Class<?>> excelBeanClassList) {
    List<List<String>> headList = new ArrayList<>();
    System.out.println("excelBeanClassList.size()=" + excelBeanClassList.size());
    excelBeanClassList.forEach(v - > {
        headList.addAll(this.dynamicHeaderByCustom(mainTitle, v));
    });
    return headList;
}

public static void main(String[] args) {
    List < List <? >> data = new ArrayList();
    ......
    System.out.println("download url is :" + exportExcel("achievement", "subject", data, "Excel name"));
}

full name	achievement		Volume A (dynamic)				Volume B (dynamic)
	subject	fraction	Completion	Judgment question	Question and answer	Additional questions	Completion	Judgment question	Question and answer	Additional questions
AAA	mathematics	110	20 points	15 points	45 points	10 points	20 points	20 points	50 points	10 points

1. Multiple sheet pages

public void witeExcel(Class<?> excelBeanClass, String title, List<ReportSheetInfo> sheets) {
    ExcelWriterBuilder write;
    if(excelBeanClass != null) {
        write = EasyExcel.write(filePath, targetClass);
    } else {
        write = EasyExcel.write(filePath);
    }
    ExcelWriter excelWriter = write.build();
    sheets.forEach(v - > {
        ExcelWriterSheetBuilder writeSheet = EasyExcel.writerSheet(sheets.indexOf(v), v.getSheetTitle());
        writeSheet.head(v.getHead());
        if(CollectionUtils.isNotEmpty(v.getWriteHandlerList())) {
            v.getWriteHandlerList().forEach(writeSheet::registerWriteHandler);
        }
        excelWriter.write(v.getDataList(), writeSheet.build());
    });
    excelWriter.finish();
    write.autoCloseStream(true);
}

// The writeHandlerList, dataList, and head collections are defined in the ReportSheetInfo class
public static void main(String[] args) {
    List<ReportSheetInfo> sheets = new ArrayList();
    ......
    System.out.println("download url is :" + exportExcel(Student.class, "Excel name", sheets));
}

The above is about the export of EasyExcel. Some source codes have been posted. If you need a complete source code, you can comment below. That's all for today's sharing......