Exporting data to Excel is a common business requirement. In most cases, simple formatting is sufficient, so there are several viable solutions available. Some implementations are quite straightforward.

I. Available Solutions

Currently, we can consider several categories of solutions:

1. Solutions provided by word processing enterprises -- This option is not currently available, possibly because these companies either don't consider it worthwhile or have specific reasons for not directly participating. For example, Microsoft and Kingsoft don't provide such solutions. If they did, it would likely eliminate many third-party vendors. How ever, it's likely that original manufacturers don't see this as valuable, or are intentionally allowing third-party vendors to operate.

2. Solutions provided by third-party vendors that conform to standards

   1. APACHE poi, the most well-known solution, has existed for about 20 years. The earliest version was Version 0.1 (2001-08-28).
   2. DOCX4J, also usable but less commonly adopted. https://www.docx4java.org/ . Offers paid, enterprise-level solutions.
   3. easyExcel, from Alibaba, essentially a wrapper around POI. This greatly simplifies Excel programming for annotation enthusiasts.
   4. Others, not yet collected.

3. Simple solutions developed by individual companies

   1. Outputting CSV files, a clever workaround.
   2. Outputting XML files, another clever workaround.
   3. Others, not yet collected.

poi+annotations

If you want a simpler approach, easyExcel is worth considering.

If you're in a rush and the client has no specific requirements for Excel formatting, this is also a viable option as it saves time. In certain projects, this is preferred by project managers as it can save considerable time.

However, this annotation-based approach has significant limitations. It can only be used with POJO/bean objects, formatting cannot be customized, and each query requires its own POJO.

Additionally, when exporting tens of thousands of rows, performance may become an issue. This slowness is difficult to optimize with current JVM versions. Using this approach means performing several unnecessary steps at least tens of thousands of times:

a. Data mapping to POJO, requiring repeated reflection and implicit conversions for each cell in every row.

In some business scenarios, this might be a moderate problem. Furthermore, slower execution naturally consumes more energy.

The biggest issue is the difficulty in customizing Excel styles. If you must use this implementation, the workflow becomes: some people write POJOs with annotations, while a core developer implements the annotations.

Currently, annotation-based approaches with reflection are always slower (though this is less of a concern nowadays).

poi (without annotations)

The non-annotation approach offers sufficiant flexibility and avoids the need for POJOs and annotations. By developing a custom utility, you can easily implement a simple comprehensive query reporting system with export functionality.

Combined with templates, this approach can be quite effective.

Personally, I prefer using POI directly and developing common utilities within my projects and teams. Other solutions currently hold little appeal for me.

II. Direct POI Export to HTTP Response Stream

This discussion doesn't cover extreme programming scenarios, focusing instead on exporting moderately sized Excel files, such as those with 100,000 rows. For larger files, alternative approaches are typically used.

Due to project requirements, the generated Excel files don't need to be cached on the server but can be directly output to the HTTP response stream.

The following example demonstrates how to output a very simple Excel export.

Environment: Windows 11, JDK 1.8, Spring Boot 2.6.7, poi-5.2.2, jQuery 3.6.0, Edge

2.1 Backend

pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Core code (source partially unknown):

import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import tools.model.ExportExcelParam;

/**
 * Excel export utility
 * 
 * @author lzfto
 * @since
 */

public class ExcelExporter {

    /**
     * Export excel to HTTP response stream
     * 
     * @param param
     */
    public static void exportToHttpResponse(ExportExcelParam param) {
        writeToStream(param.getFileName(), param.getColumnList(), param.getHeaderTitle(), param.getDataList(),
                param.getResponse(), param.getUserAgent());
    }

    /**
     * Export file to HTTP response stream
     * @param fileName      Must have .xls or .xlsx extension
     * @param columnList    Key list, non-empty - list of map keys, multiple keys separated by commas.
     * @param headerTitle   Header - non-empty. Multiple headers separated by commas
     * @param dataList      List of maps - non-empty. Map keys must correspond to columnList
     * @param response      HTTP response
     * @param userAgent     Client information (mobile not currently supported)
     * @apiNote If dataList has more than 5000 rows, XSSFWorkbook will be used to avoid memory overflow
     * For extremely large datasets, consider using caching with progressive transmission instead
     */
    private static void writeToStream(String fileName, String columnList, String headerTitle,
            List<Map<String, Object>> dataList, HttpServletResponse response, String userAgent) {

        // Step 1: Create a workbook, corresponding to an Excel file
        Workbook workbook = null;
        if(dataList.size() < 5000) {
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook();
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook();
            }    
        }
        else {
            workbook = new SXSSFWorkbook();
        }
        

        // Step 2: Add a sheet to the workbook, corresponding to a sheet in the Excel file
        Sheet sheet = workbook.createSheet("Data");
        // Step 3: Add header row (row 0) to the sheet, note that older POI versions have limitations on Excel row/column counts
        Row headerRow = sheet.createRow(0);
        // Step 4: Create cells and set header values, center alignment
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // Create font for header
        Font headerFont = workbook.createFont();
        headerFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        // Apply font to current style
        headerStyle.setFont(headerFont);


        Cell cell = null;
        String[] headerTitles = headerTitle.split(",");
        for (int i = 0; i < headerTitles.length; i++) {
            cell = headerRow.createCell(i);
            cell.setCellValue(headerTitles[i]);
            cell.setCellStyle(headerStyle);
        }

        // Step 5: Write entity data (in practice, this data comes from a database)
        // Optional: Set a font for content (omitted here for brevity)
        String[] columns = StringUtils.split(columnList, ',');
        for (int i = 0; i < dataList.size(); i++) {
            Row dataRow = sheet.createRow(i + 1);
            Map<String, Object> dataMap = dataList.get(i);
            for (int j = 0; j < columns.length; j++) {
                cell = dataRow.createCell(j);
                String value = "";
                if (dataMap.get(columns[j]) != null) {
                    value = dataMap.get(columns[j]).toString();
                }
                cell.setCellValue(value);
            }
        }

        // Auto-size columns based on content
        for (int i = 0; i < headerTitles.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // Step 6: Write Excel data to HTTP response stream
        try {
            String outputFileName = fileName;
            if (userAgent != null && userAgent.toUpperCase().indexOf("MSIE") > 0) {
                outputFileName = URLEncoder.encode(fileName, "UTF-8");
            } else if (userAgent != null && userAgent.toUpperCase().indexOf("IPHONE") > 0) {
                outputFileName = new String(fileName.getBytes(), "ISO-8859-1");
            } else {
                outputFileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
            }
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment; filename=\"" + outputFileName + "\"");
            workbook.write(response.getOutputStream());            
            response.getOutputStream().flush();

        } catch (Exception e) {
            System.err.println("Export error: " + e.getMessage());
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception ex) {
                System.err.println("Error closing workbook: " + ex.getMessage());
            }
        }
    }
}

Important note: Memory overflow issues are handled using SXSSFWorkbook, but this component has several limitations.

Regarding SXSSFWorkbook, refer to: https://poi.apache.org/components/spreadsheet/

Here's some relevant information:

Since 3.8-beta3, POI provides a low-memory footprint SXSSF API built on top of XSSF.

SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

In auto-flush mode the size of the access window can be specified, to hold a certain number of rows in memory. When that value is reached, the creation of an additional row causes the row with the lowest index to be removed from the access window and written to disk. Or, the window size can be set to grow dynamically; it can be trimmed periodically by an explicit call to flushRows(int keepRows) as needed.

Due to the streaming nature of the implementation, there are the following limitations when compared to XSSF:
    <strong><em>.Only a limited number of rows are accessible at a point in time.
    .Sheet.clone() is not supported.
    .Formula evaluation is not supported</em></strong>

See more details at SXSSF How-To

For proper usage, thoroughly read https://poi.apache.org/components/spreadsheet/how-to.html#sxssf and the official API documentation. More details are omitted for brevity.

2.2 Front end

Native JavaScript solution

/**
 * Export all filtered content as Excel format
 */
function exportData() {
  const params = getFilterParams();
  const url = '/api/export/excel';
  const xhr = new XMLHttpRequest();
  xhr.open('POST', url, true);    // Can also use POST method depending on API design
  xhr.setRequestHeader('Content-Type', 'application/json');
  xhr.responseType = "blob";  // Response type: blob
  
  // Define handler for completed request
  xhr.onload = function () {
    if (this.status === 200) {
      const blob = this.response;
      const fileReader = new FileReader();
      fileReader.readAsDataURL(blob); 
      fileReader.onload = function (event) {
        // Conversion complete, create anchor tag for download
        const downloadLink = document.createElement('a');
        downloadLink.download = 'exported_data.xlsx';
        downloadLink.href = event.target.result;
        document.body.appendChild(downloadLink); 
        downloadLink.click();
        document.body.removeChild(downloadLink);
      };
    }
  };
  
  // Send AJAX request
  xhr.send(JSON.stringify(params));
}

This is basic code without special optimizations or comprehensive error handling.

jQuery solution

Alternatively, jQuery can be used:

function downloadAsExcel() {
    const params = getFilterParams();
    $.ajax({
        url: '/api/export/excel',
        type: 'POST',
        dataType: 'blob',
        contentType: "application/json",
        async: true,
        data: JSON.stringify(params),
        success: function (response, status, xhr) {
            const blob = response;
            const reader = new FileReader();
            reader.readAsDataURL(blob);
            reader.onload = function (event) {
                // Conversion complete, create anchor tag for download
                const link = document.createElement('a');
                link.download = 'exported_data.xlsx';
                link.href = event.target.result;
                $('body').append(link); 
                link.click();
                $(link).remove();
            };
        },
        error: function (error) {
            displayErrorMessage('Error', 'Network error occurred during export', 1);
        }
    });
}

III. Summary

If you prefer to avoid annotations and want more flexibility, it's recommended to use POI directly for Excel export operations.

POI is quite powerful, though it doesn't handle extreme cases and complex scenarios as well as original manufacturers' solutions. However, it's sufficient for most use cases.

If not for export purposes, consider using APIs provided by manufacturers like Microsoft and Kingsoft, which offer perfect implementations.