EasyExcel: Efficient Java Excel Processing Framework

Introduction to EasyExcel

EasyExcel is a Java-based framework designed for efficient Excel file processing. Unlike traditional libraries such as Apache POI and JXL, EasyExcel addresses the critical issue of high memory consumption during Excel operations. While Apache POI offers a SAX mode API to partially mitigate memory overflow problems, it still has significant drawbacks, particularly with Excel 2007+ files where decompression and storage occur entirely in memory.

EasyExcel reimagines the parsing approach for Excel 2007+ files. For instance, parsing a 3MB Excel file with POI's SAX mode typically requires around 100MB of memory, whereas EasyExcel can reduce this to just a few megabytes. Moreover, EasyExcel eliminates memory overflow issues even when processing extremely large Excel files. For Excel 2003 files, EasyExcel builds upon POI's SAX mode with additional model conversion encapsulation, providing a more user-friendly interface.

Performance Metrics

EasyExcel demonstrates impressive performance capabilities. It can process a 75MB Excel file containing 460,000 rows and 25 columns using only 16MB of memory in approximately 23 seconds (tested with version 3.2.1+). For even faster processing, an express mode is available, though it requires slightly more memory (around 100MB).

Reading Excel Files

Basic Reading

The simplest approach to reading Excel files involves three key steps:

  1. Create a Java entity class that corresponds to the Excel structure
  2. Implement a listener to handle row-by-row reading callbacks
  3. Execute the reading operation
// Entity class
@Getter
@Setter
@EqualsAndHashCode
public class SampleData {
    private String text;
    private Date date;
    private Double numericValue;
}

// Listener implementation
@Slf4j
public class SampleDataListener implements ReadListener<SampleData> {
    private static final int BATCH_SIZE = 100;
    private List<SampleData> cachedData = ListUtils.newArrayListWithExpectedSize(BATCH_SIZE);
    private SampleDataRepository repository;
    
    public SampleDataListener() {
        this.repository = new SampleDataRepository();
    }
    
    public SampleDataListener(SampleDataRepository repository) {
        this.repository = repository;
    }
    
    @Override
    public void invoke(SampleData data, AnalysisContext context) {
        log.info("Parsed data row: {}", JSON.toJSONString(data));
        cachedData.add(data);
        if (cachedData.size() >= BATCH_SIZE) {
            saveData();
            cachedData = ListUtils.newArrayListWithExpectedSize(BATCH_SIZE);
        }
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("All data parsing completed!");
    }
    
    private void saveData() {
        log.info("Saving {} records to database", cachedData.size());
        repository.save(cachedData);
        log.info("Data saved successfully!");
    }
}

// Reading implementation
@Test
public void basicRead() {
    String filePath = getFilePath() + "demo" + File.separator + "demo.xlsx";
    EasyExcel.read(filePath, SampleData.class, new SampleDataListener())
        .sheet()
        .doRead();
}

Reading with Column Specifications

You can specify columns by either index or name using the @ExcelProperty annotation:

@Getter
@Setter
@EqualsAndHashCode
public class ColumnSpecifiedData {
    @ExcelProperty(index = 2)
    private Double numericValue;
    
    @ExcelProperty("Text Column")
    private String text;
    
    @ExcelProperty("Date Column")
    private Date date;
}

Reading Multiple Sheets

EasyExcel supports reading multiple sheets from a single Excel file:

@Test
public void readMultipleSheets() {
    String filePath = getFilePath() + "demo" + File.separator + "demo.xlsx";
    
    // Read all sheets
    EasyExcel.read(filePath, SampleData.class, new SampleDataListener())
        .doReadAll();
    
    // Read specific sheets
    try (ExcelReader excelReader = EasyExcel.read(filePath).build()) {
        ReadSheet sheet1 = EasyExcel.readSheet(0)
            .head(SampleData.class)
            .registerReadListener(new SampleDataListener())
            .build();
        ReadSheet sheet2 = EasyExcel.readSheet(1)
            .head(SampleData.class)
            .registerReadListener(new SampleDataListener())
            .build();
        
        excelReader.read(sheet1, sheet2);
    }
}

Data Type Conversion

EasyExcel provides built-in and custom conversion capabilities for dates, numbers, and other formats:

@Getter
@Setter
@EqualsAndHashCode
public class ConversionData {
    @ExcelProperty(converter = CustomStringConverter.class)
    private String text;
    
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private Date date;
    
    @NumberFormat("#.##%")
    private String percentage;
}

public class CustomStringConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }
    
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        return "Processed: " + context.getReadCellData().getStringValue();
    }
    
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>(context.getValue());
    }
}

Writing Excel Files

Basic Writing

Writing Excel files follows a straightforward approach:

@Getter
@Setter
@EqualsAndHashCode
public class ExportData {
    @ExcelProperty("Text Column")
    private String text;
    
    @ExcelProperty("Date Column")
    private Date date;
    
    @ExcelProperty("Numeric Column")
    private Double numericValue;
    
    @ExcelIgnore
    private String ignoredField;
}

@Test
public void basicWrite() {
    String fileName = getFilePath() + "export_" + System.currentTimeMillis() + ".xlsx";
    
    EasyExcel.write(fileName, ExportData.class)
        .sheet("Data Sheet")
        .doWrite(generateTestData());
}

private List<ExportData> generateTestData() {
    List<ExportData> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        ExportData item = new ExportData();
        item.setText("Sample Text " + i);
        item.setDate(new Date());
        item.setNumericValue(0.56);
        data.add(item);
    }
    return data;
}

Column Selection and Positioning

You can control which columns to export and thier positions:

@Test
public void selectiveColumnWrite() {
    String fileName = getFilePath() + "selective_export_" + System.currentTimeMillis() + ".xlsx";
    
    // Exclude specific columns
    Set<String> excludedColumns = new HashSet<>();
    excludedColumns.add("date");
    EasyExcel.write(fileName, ExportData.class)
        .excludeColumnFiledNames(excludedColumns)
        .sheet("Data Sheet")
        .doWrite(generateTestData());
    
    // Include only specific columns
    fileName = getFilePath() + "specific_export_" + System.currentTimeMillis() + ".xlsx";
    Set<String> includedColumns = new HashSet<>();
    includedColumns.add("date");
    EasyExcel.write(fileName, ExportData.class)
        .includeColumnFiledNames(includedColumns)
        .sheet("Data Sheet")
        .doWrite(generateTestData());
}

Complex Headers

Creating multi-level headers is straightforward with EasyExcel:

@Getter
@Setter
@EqualsAndHashCode
public class ComplexHeaderData {
    @ExcelProperty({"Main Category", "Text Column"})
    private String text;
    
    @ExcelProperty({"Main Category", "Date Column"})
    private Date date;
    
    @ExcelProperty({"Main Category", "Numeric Column"})
    private Double numericValue;
}

@Test
public void complexHeaderWrite() {
    String fileName = getFilePath() + "complex_header_" + System.currentTimeMillis() + ".xlsx";
    
    EasyExcel.write(fileName, ComplexHeaderData.class)
        .sheet("Data Sheet")
        .doWrite(generateTestData());
}

Multiple Writing Operations

For large datasets, you can perform multiple write operations efficiently:

@Test
public void multipleWriteOperations() {
    // Write to same sheet
    String fileName = getFilePath() + "multi_write_same_sheet_" + System.currentTimeMillis() + ".xlsx";
    
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, ExportData.class).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet("Data Sheet").build();
        
        for (int i = 0; i < 5; i++) {
            List<ExportData> batchData = generateTestData();
            excelWriter.write(batchData, writeSheet);
        }
    }
    
    // Write to different sheets
    fileName = getFilePath() + "multi_write_diff_sheets_" + System.currentTimeMillis() + ".xlsx";
    
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, ExportData.class).build()) {
        for (int i = 0; i < 5; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet " + i).build();
            List<ExportData> batchData = generateTestData();
            excelWriter.write(batchData, writeSheet);
        }
    }
}

Excel Template Filling

Basic Template Filling

EasyExcel supports filling data into predefined Excel templates:

@Getter
@Setter
@EqualsAndHashCode
public class FillData {
    private String name;
    private double amount;
    private Date date;
}

@Test
public void basicTemplateFill() {
    String templatePath = getFilePath() + "templates" + File.separator + "basic_template.xlsx";
    String outputPath = getFilePath() + "filled_basic_" + System.currentTimeMillis() + ".xlsx";
    
    // Fill with object
    FillData fillData = new FillData();
    fillData.setName("John Doe");
    fillData.setAmount(1250.75);
    fillData.setDate(new Date());
    
    EasyExcel.write(outputPath)
        .withTemplate(templatePath)
        .sheet()
        .doFill(fillData);
    
    // Fill with Map
    outputPath = getFilePath() + "filled_basic_map_" + System.currentTimeMillis() + ".xlsx";
    Map<String, Object> dataMap = new HashMap<>();
    dataMap.put("name", "Jane Smith");
    dataMap.put("amount", 875.50);
    dataMap.put("date", new Date());
    
    EasyExcel.write(outputPath)
        .withTemplate(templatePath)
        .sheet()
        .doFill(dataMap);
}

List Data Filling

Filling lists of data into templates is also supported:

@Test
public void listTemplateFill() {
    String templatePath = getFilePath() + "templates" + File.separator + "list_template.xlsx";
    String outputPath = getFilePath() + "filled_list_" + System.currentTimeMillis() + ".xlsx";
    
    // One-time fill (all data in memory)
    EasyExcel.write(outputPath)
        .withTemplate(templatePath)
        .sheet()
        .doFill(generateTestData());
    
    // Batch fill (memory efficient)
    outputPath = getFilePath() + "filled_list_batch_" + System.currentTimeMillis() + ".xlsx";
    
    try (ExcelWriter excelWriter = EasyExcel.write(outputPath).withTemplate(templatePath).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(generateTestData(), writeSheet);
        excelWriter.fill(generateTestData(), writeSheet);
    }
}

Complex Template Filling

For more complex scenarios with multiple data types:

@Test
public void complexTemplateFill() {
    String templatePath = getFilePath() + "templates" + File.separator + "complex_template.xlsx";
    String outputPath = getFilePath() + "filled_complex_" + System.currentTimeMillis() + ".xlsx";
    
    try (ExcelWriter excelWriter = EasyExcel.write(outputPath).withTemplate(templatePath).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder()
            .forceNewRow(Boolean.TRUE)
            .build();
        
        excelWriter.fill(generateTestData(), fillConfig, writeSheet);
        excelWriter.fill(generateTestData(), fillConfig, writeSheet);
        
        Map<String, Object> summaryData = new HashMap<>();
        summaryData.put("reportDate", "2023-06-15");
        summaryData.put("totalRecords", 1000);
        
        excelWriter.fill(summaryData, writeSheet);
    }
}

Web Integration

File Upload and Processing

EasyExcel integrates seamlessly with web applications for file upload and processing:

@PostMapping("/upload")
@ResponseBody
public String handleFileUpload(@RequestParam MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(dataRepository))
        .sheet()
        .doRead();
    return "File processed successfully";
}

File Download

Generating and downloading Excel files in web applications:

@GetMapping("/download")
public void handleFileDownload(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("Data Export", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    
    EasyExcel.write(response.getOutputStream(), DownloadData.class)
        .sheet("Data")
        .doWrite(generateExportData());
}

@GetMapping("/downloadWithFallback")
public void downloadWithJsonFallback(HttpServletResponse response) throws IOException {
    try {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("Data Export", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
        EasyExcel.write(response.getOutputStream(), DownloadData.class)
            .autoCloseStream(Boolean.FALSE)
            .sheet("Data")
            .doWrite(generateExportData());
    } catch (Exception e) {
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        
        Map<String, String> errorResponse = new HashMap<>();
        errorResponse.put("status", "error");
        errorResponse.put("message", "Download failed: " + e.getMessage());
        
        response.getWriter().println(JSON.toJSONString(errorResponse));
    }
}

Advanced Features

Image Export

EasyExcel supports exporting images in Excel files:

@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(100)
@ColumnWidth(100 / 8)
public class ImageExportData {
    private File imageFile;
    private InputStream imageStream;
    
    @ExcelProperty(converter = StringImageConverter.class)
    private String imagePath;
    
    private byte[] imageBytes;
    private URL imageUrl;
    
    private WriteCellData<Void> customImageData;
}

@Test
public void exportWithImages() throws Exception {
    String fileName = getFilePath() + "image_export_" + System.currentTimeMillis() + ".xlsx";
    String imagePath = getFilePath() + "images" + File.separator + "sample.jpg";
    
    try (InputStream inputStream = FileUtils.openInputStream(new File(imagePath))) {
        List<ImageExportData> exportData = new ArrayList<>();
        ImageExportData dataItem = new ImageExportData();
        
        // Set image in various formats
        dataItem.setImageBytes(FileUtils.readFileToByteArray(new File(imagePath)));
        dataItem.setImageFile(new File(imagePath));
        dataItem.setImagePath(imagePath);
        dataItem.setImageStream(inputStream);
        dataItem.setImageUrl(new URL("https://example.com/sample.jpg"));
        
        // Configure custom image placement
        WriteCellData<Void> customImageData = new WriteCellData<>();
        dataItem.setCustomImageData(customImageData);
        customImageData.setType(CellDataTypeEnum.STRING);
        customImageData.setStringValue("Image with caption");
        
        List<ImageData> imageList = new ArrayList<>();
        ImageData imageData = new ImageData();
        imageList.add(imageData);
        customImageData.setImageDataList(imageList);
        
        imageData.setImage(FileUtils.readFileToByteArray(new File(imagePath)));
        imageData.setImageType(ImageType.PICTURE_TYPE_PNG);
        imageData.setTop(5);
        imageData.setRight(40);
        imageData.setBottom(5);
        imageData.setLeft(5);
        
        exportData.add(dataItem);
        
        EasyExcel.write(fileName, ImageExportData.class)
            .sheet()
            .doWrite(exportData);
    }
}

Custom Cell Styles

You can apply custom styling to Excel cells using annotations or programmatically:

// Using annotations
@Getter
@Setter
@EqualsAndHashCode
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
@HeadFontStyle(fontHeightInPoints = 20)
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)
@ContentFontStyle(fontHeightInPoints = 20)
public class StyledData {
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)
    @HeadFontStyle(fontHeightInPoints = 30)
    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("Text Column")
    private String text;
    
    @ExcelProperty("Date Column")
    private Date date;
    
    @ExcelProperty("Numeric Column")
    private Double numericValue;
}

// Using programmatic styling
@Test
public void programmaticStyleWrite() {
    String fileName = getFilePath() + "styled_export_" + System.currentTimeMillis() + ".xlsx";
    
    // Define header style
    WriteCellStyle headerStyle = new WriteCellStyle();
    headerStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headerFont = new WriteFont();
    headerFont.setFontHeightInPoints((short) 20);
    headerStyle.setWriteFont(headerFont);
    
    // Define content style
    WriteCellStyle contentStyle = new WriteCellStyle();
    contentStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    contentStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentFont = new WriteFont();
    contentFont.setFontHeightInPoints((short) 20);
    contentStyle.setWriteFont(contentFont);
    
    // Create style strategy
    HorizontalCellStyleStrategy styleStrategy = 
        new HorizontalCellStyleStrategy(headerStyle, contentStyle);
    
    EasyExcel.write(fileName, ExportData.class)
        .registerWriteHandler(styleStrategy)
        .sheet("Styled Data")
        .doWrite(generateTestData());
}

Merging Cells

EasyExcel provides options for merging cells in Excel files:

// Using annotations
@Getter
@Setter
@EqualsAndHashCode
public class MergeData {
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty("Text Column")
    private String text;
    
    @ExcelProperty("Date Column")
    private Date date;
    
    @ExcelProperty("Numeric Column")
    private Double numericValue;
}

// Using programmatic merging
@Test
public void mergeCellsWrite() {
    String fileName = getFilePath() + "merged_export_" + System.currentTimeMillis() + ".xlsx";
    
    // Using annotation approach
    EasyExcel.write(fileName, MergeData.class)
        .sheet("Merged Data")
        .doWrite(generateTestData());
    
    // Using programmatic approach
    fileName = getFilePath() + "programmatic_merge_" + System.currentTimeMillis() + ".xlsx";
    LoopMergeStrategy mergeStrategy = new LoopMergeStrategy(2, 0);
    
    EasyExcel.write(fileName, ExportData.class)
        .registerWriteHandler(mergeStrategy)
        .sheet("Merged Data")
        .doWrite(generateTestData());
}

Conclusion

EasyExcel provides a comprehensive solution for Excel processing in Java applications, with significant advantages in memory efficiency and performance compared to traditional libraries. Its intuitive API for reading, writing, and template filling operations makes it suitable for a wide range of applications, from simple data exports to complex report generation.

Posted on Fri, 05 Jun 2026 18:21:23 +0000 by suttercain

Hot Tags

©2026 Freaks City