This guide shows how to export large datasets to XLSX using Apache POI’s SXSSF (streaming) API while automatically sizing columns based on the actual cell content. It also includes a compact styling cookbook to borders, fonts, alignment, wrapping, and merged regions.

Export utility (SXSSF) with adaptive column widths

The example accepts a LinkedHashMap to keep column order and a list of maps as the data source. Date values are formatted with a configurable pattern. Column widths are first auto-calcluated and then scaled to avoid truncated text.

package example.export;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelExporter {

    /**
     * Stream an XLSX file to the HTTP response using SXSSF.
     * - headers: LinkedHashMap of column header -> row key (to read from each data map)
     * - rows: list of data rows (String key -> Object value)
     */
    public static void writeXlsxToHttpResponse(
            String fileNamePrefix,
            LinkedHashMap<String, String> headers,
            List<Map<String, Object>> rows,
            String datePattern,
            HttpServletResponse response) throws IOException {

        if (datePattern == null || datePattern.isEmpty()) {
            datePattern = "yyyy-MM-dd";
        }
        SimpleDateFormat df = new SimpleDateFormat(datePattern);

        // Keep at most N rows in memory; others are flushed to temp files
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);
        wb.setCompressTempFiles(true);

        CellStyle headStyle = createHeaderStyle(wb);
        CellStyle bodyStyle = createBodyStyle(wb);

        // One sheet may hold up to 1,048,576 rows (including header)
        final int excelRowLimit = 1_048_576;
        final int dataStartRow = 1;

        Sheet sheet = null;
        int written = 0;
        int currentRowIdx = 0;

        // column order (header titles and keys)
        List<String> columnTitles = new ArrayList<>(headers.keySet());
        List<String> columnKeys = new ArrayList<>(headers.values());

        for (int rowNum = 0; rowNum < rows.size(); rowNum++) {
            // Create new sheet on first row or when hitting row limit
            if (sheet == null || currentRowIdx >= excelRowLimit) {
                sheet = wb.createSheet();
                // For SXSSF, track columns to enable auto-size after streaming
                sheet.trackAllColumnsForAutoSizing();

                // header row
                Row header = sheet.createRow(0);
                for (int c = 0; c < columnTitles.size(); c++) {
                    Cell cell = header.createCell(c);
                    cell.setCellValue(columnTitles.get(c));
                    cell.setCellStyle(headStyle);
                }
                currentRowIdx = dataStartRow; // next row: first data row
            }

            Map<String, Object> rowMap = rows.get(rowNum);
            Row excelRow = sheet.createRow(currentRowIdx++);

            for (int c = 0; c < columnKeys.size(); c++) {
                Object value = rowMap.get(columnKeys.get(c));
                Cell cell = excelRow.createCell(c);
                cell.setCellValue(stringify(value, df));
                cell.setCellStyle(bodyStyle);
            }
            written++;
        }

        // Adjust column widths for all sheets
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
            Sheet sh = wb.getSheetAt(s);
            // First auto-size to content
            for (int c = 0; c < columnTitles.size(); c++) {
                sh.autoSizeColumn(c, true);
            }
            // Then scale slightly so content has breathing room
            for (int c = 0; c < columnTitles.size(); c++) {
                int autoWidth = sh.getColumnWidth(c);
                // Scale by ~1.7x, clamp to Excel max (255 characters * 256)
                int scaled = (int) Math.min(255 * 256, Math.round(autoWidth * 1.7));
                sh.setColumnWidth(c, Math.max(autoWidth, scaled));
            }
        }

        // Stream to client
        String stamped = fileNamePrefix + "_" + new java.text.SimpleDateFormat("yyyyMMdd_HHmmssSSS")
                .format(new Date()) + ".xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String encoded = URLEncoder.encode(stamped, StandardCharsets.UTF_8.name());
        response.setHeader("Content-Disposition", "attachment; filename=" + encoded);

        try (OutputStream os = response.getOutputStream()) {
            wb.write(os);
        } finally {
            // Dispose of temporary files backing SXSSF
            wb.dispose();
        }
    }

    private static String stringify(Object v, SimpleDateFormat df) {
        if (v == null) return "";
        if (v instanceof Date) return df.format((Date) v);
        return String.valueOf(v);
    }

    private static CellStyle createHeaderStyle(Workbook wb) {
        CellStyle st = wb.createCellStyle();
        st.setAlignment(HorizontalAlignment.CENTER);
        st.setVerticalAlignment(VerticalAlignment.CENTER);
        st.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        st.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        st.setBorderTop(BorderStyle.THIN);
        st.setBorderRight(BorderStyle.THIN);
        st.setBorderBottom(BorderStyle.THIN);
        st.setBorderLeft(BorderStyle.THIN);
        Font f = wb.createFont();
        f.setFontHeightInPoints((short) 12);
        f.setBold(true);
        st.setFont(f);
        return st;
    }

    private static CellStyle createBodyStyle(Workbook wb) {
        CellStyle st = wb.createCellStyle();
        st.setAlignment(HorizontalAlignment.CENTER);
        st.setVerticalAlignment(VerticalAlignment.CENTER);
        st.setBorderTop(BorderStyle.THIN);
        st.setBorderRight(BorderStyle.THIN);
        st.setBorderBottom(BorderStyle.THIN);
        st.setBorderLeft(BorderStyle.THIN);
        st.setWrapText(true);
        Font f = wb.createFont();
        f.setBold(false);
        st.setFont(f);
        return st;
    }
}

Key points for width calculation:

• Call sheet.trackAllColumnsForAutoSizing() when using SXSSF.
• Call autoSizeColumn first to measure content.
• Scale the resulting width (e.g., ×1.6–1.8). The snippet above uses ×1.7 and clamps to Excel’s maximum column width.

POI styling quick reference (borders, fonts, fills, alignment)

Bellow are concise exampels using modern org.apache.poi.ss.usermodel APIs.

Workbook and sheet

Workbook wb = new org.apache.poi.hssf.usermodel.HSSFWorkbook(); // or new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");

Backgroudn fill

CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

Borders

CellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);

Horizontal/vertical alignment

CellStyle centered = wb.createCellStyle();
centered.setAlignment(HorizontalAlignment.CENTER);
centered.setVerticalAlignment(VerticalAlignment.CENTER);

Fonts (family, size, bold)

Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short)16);
titleFont.setBold(true);

CellStyle title = wb.createCellStyle();
title.setFont(titleFont);

Column width

// Width units are 1/256th of a character
sheet.setColumnWidth(0, 20 * 256); // roughly 20 characters wide

Wrap text

CellStyle wrap = wb.createCellStyle();
wrap.setWrapText(true);

Row height

Row r0 = sheet.createRow(0);
r0.setHeightInPoints(20f); // exact height in points

Row r5 = sheet.createRow(5);
r5.setHeight((short) (25 * 20)); // height units (twips): 1 pt = 20 twips

Default column width and row height

Sheet s2 = wb.createSheet("Sheet2");
s2.setDefaultColumnWidth(20);
s2.setDefaultRowHeightInPoints(20f);

Merge cells

// Merge from row r to r, col 0 to col 6 (inclusive)
int r = 0;
CellRangeAddress region = new CellRangeAddress(r, r, 0, 6);
sheet.addMergedRegion(region);

Border thickness with colors

CellStyle strongBorder = wb.createCellStyle();
strongBorder.setAlignment(HorizontalAlignment.CENTER);

strongBorder.setBorderBottom(BorderStyle.MEDIUM);
strongBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex());

strongBorder.setBorderLeft(BorderStyle.MEDIUM);
strongBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex());

strongBorder.setBorderRight(BorderStyle.MEDIUM);
strongBorder.setRightBorderColor(IndexedColors.BLACK.getIndex());

strongBorder.setBorderTop(BorderStyle.MEDIUM);
strongBorder.setTopBorderColor(IndexedColors.BLACK.getIndex());

Font color

Font red = wb.createFont();
red.setFontHeightInPoints((short)12);
red.setFontName("SimSun");
red.setColor(IndexedColors.RED.getIndex());

CellStyle redText = wb.createCellStyle();
redText.setFont(red);

Auto-size then scale column width (standalone snippet)

// After writing all rows
sheet.trackAllColumnsForAutoSizing();
for (int c = 0; c < numCols; c++) {
    sheet.autoSizeColumn(c, true);
}
for (int c = 0; c < numCols; c++) {
    int w = sheet.getColumnWidth(c);
    int scaled = (int) Math.min(255 * 256, Math.round(w * 1.7));
    sheet.setColumnWidth(c, Math.max(w, scaled));
}