5 Ways to Create Excel Sheets in Java Spring

Excel spreadsheets are an integral part of data handling and analysis in many businesses, and when building Spring applications, knowing how to generate Excel files can be quite beneficial. There are multiple libraries and methods to achieve this task, each with its own set of advantages. In this blog post, we'll explore five different ways to create Excel sheets in a Java Spring environment, discussing their implementation, strengths, and potential pitfalls.

1. Using Apache POI

Apache POI is one of the most widely used libraries for working with Microsoft Office documents, including Excel. Here's how you can implement it in a Spring application:

• Dependency: Add Apache POI to your project's dependencies in the pom.xml or build.gradle file.
• Create Workbook: Use the HSSF (for .xls) or XSSF (for .xlsx) classes to create a workbook.
• Work with Sheets and Cells: Create sheets, rows, and cells to populate data.
• Save: Write the workbook to an output stream to save or send as a response.

public void createExcelFile() {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("My Sheet");

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("Hello, World!");

    try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
        workbook.write(fileOut);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

📌 Note: Apache POI is powerful but can be resource-intensive for very large datasets. Consider memory usage when working with thousands of rows.

2. Apache POI with SXSSF for Large Datasets

For dealing with large datasets, Apache POI's SXSSF (Streaming API) is a better option:

• Dependency: Use the same dependency as for Apache POI.
• Create Workbook: Use new SXSSFWorkbook(windowSize), where windowSize controls how many rows are kept in memory at one time.
• Streaming Data: Write data to the Excel file in a streaming manner to conserve memory.
• Save: Similar to POI's HSSF/XSSF, you'll write to an output stream.

public void createExcelFileStreaming() throws IOException {
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(100); FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
        Sheet sheet = workbook.createSheet("Large Dataset");
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum + "_" + cellNum);
            }
            if (rowNum % 100 == 0) {
                sheet.flushRows(100);
            }
        }
        workbook.write(fileOut);
    }
}

📌 Note: The streaming API of POI does not keep the entire workbook in memory, which makes it ideal for very large datasets, but you'll lose the ability to modify rows or cells after creation.

3. Using JExcelAPI

Though less popular nowadays, JExcelAPI is still a straightforward option for creating Excel files:

• Dependency: Include JExcelAPI in your project dependencies.
• Create Workbook: Use WritableWorkbook to create the workbook.
• Add Sheets: Work with WritableSheet to add data.
• Save: Write to an output stream or file.

public void createExcelFileJExcel() throws IOException, WriteException {
    WritableWorkbook workbook = Workbook.createWorkbook(new File("workbook.xls"));
    WritableSheet sheet = workbook.createSheet("Sheet1", 0);
    Label label = new Label(0, 0, "Hello, World!");
    sheet.addCell(label);
    workbook.write();
    workbook.close();
}

4. Integration with JasperReports

JasperReports, known for report generation, can also produce Excel sheets through its exporter:

• Dependency: Include JasperReports and iText (for PDF) dependencies.
• Create Report: Design your report using JasperSoft Studio or JRXML.
• Export to Excel: Compile the report and use the Excel exporter.
• Save: Generate the Excel file from the report.

public void createExcelFileWithJasper() throws JRException, IOException {
    JasperReport jasperReport = JasperCompileManager.compileReport("report.jrxml");
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, new HashMap<>(), new JREmptyDataSource());
    JExcelApiExporter exporter = new JExcelApiExporter();
    exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
    OutputStream outputStream = new FileOutputStream("report.xls");
    exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outputStream));
    exporter.exportReport();
}

📌 Note: JasperReports is robust for creating complex reports, but keep in mind that setting it up can be time-consuming and might be overkill for simple Excel generation tasks.

5. Using Spring Batch with ExcelJob

Spring Batch can be used for batch processing, including writing data to Excel:

• Spring Batch Integration: Ensure Spring Batch is part of your project.
• Create a Job: Configure a job using an ItemReader, ItemProcessor (if needed), and ExcelItemWriter.
• Configure the Excel Writer: Set up the Excel writer with necessary properties.
• Execute Job: Run the job to generate the Excel file.

public class ExcelJobConfiguration {

    @Bean
    public FlatFileItemReader<MyData> reader() {
        FlatFileItemReader<MyData> reader = new FlatFileItemReader<>();
        // Reader configuration
        return reader;
    }

    @Bean
    public Step excelExportStep(JobRepository jobRepository, PlatformTransactionManager transactionManager) {
        return new StepBuilder("excelExportStep", jobRepository)
                .<MyData, MyData>chunk(10, transactionManager)
                .reader(reader())
                .writer(writer())
                .build();
    }

    @Bean
    public Job excelExportJob(JobRepository jobRepository) {
        return new JobBuilder("excelExportJob", jobRepository)
                .start(excelExportStep(jobRepository, transactionManager))
                .build();
    }

    @Bean
    public ExcelItemWriter<MyData> writer() {
        ExcelItemWriter<MyData> writer = new ExcelItemWriter<>();
        // Writer configuration for Excel
        return writer;
    }
}

Each of these methods offers unique advantages:

• Apache POI and its streaming variant provide flexibility for both small and large datasets.
• JExcelAPI offers a lighter alternative but with some limitations in formatting.
• JasperReports integrates with Spring easily for those already using it for reporting.
• Spring Batch with ExcelJob configuration is excellent for batch processing, allowing complex workflows.

When choosing a method, consider your project's scale, complexity, and performance requirements. For small datasets, simpler methods like Apache POI or JExcelAPI might suffice. For batch processing or complex reports, JasperReports or Spring Batch could be more suitable.

Which method is best for handling large datasets in Excel?

+

For large datasets, Apache POI with SXSSF or Spring Batch with an Excel job is recommended as they handle memory more efficiently.

Can Apache POI work with the newer .xlsx format?

+

Yes, Apache POI supports the .xlsx format through the XSSF API.

Is there any method better for real-time Excel generation?

+

Apache POI is generally the quickest for real-time generation due to its versatility and wide support.