如何使用POI为Excel工作表设置行/列宽及行颜色样式？

Got it, let's figure out how to add row/column widths and row colors to your Excel file using Apache POI—building right off the code you shared! I'll break this down into simple, actionable steps with code examples that fit your existing setup.

1. First: Create Custom Cell Styles #

Since POI applies styling to individual cells (not just rows directly), we'll define reusable styles for your header row, data rows, and keep your text wrapping setting intact.

// Your existing initialization code
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Test Result");

// Style for header row (with background color)
CellStyle headerStyle = workbook.createCellStyle();
// Set a light blue background fill
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// Keep your text wrapping setting
headerStyle.setWrapText(true);
// Optional: Add bold font for better header visibility
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerStyle.setFont(headerFont);

// Style for even-numbered data rows (alternating color)
CellStyle evenRowStyle = workbook.createCellStyle();
evenRowStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
evenRowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
evenRowStyle.setWrapText(true);

// Default style for odd-numbered data rows (no fill)
CellStyle defaultRowStyle = workbook.createCellStyle();
defaultRowStyle.setWrapText(true);

2. Set Column Widths #

Use sheet.setColumnWidth() to define how wide each column should be. The width value is measured in 1/256 of a character width, so multiply your desired character count by 256 for accurate sizing.

// Set column 0 to fit ~20 characters
sheet.setColumnWidth(0, 20 * 256);
// Set column 1 to fit ~30 characters
sheet.setColumnWidth(1, 30 * 256);
// Add more columns here as needed for your data

3. Write Data with Applied Styles #

Now let's integrate these styles into your existing code for writing headers and data rows. I'll assume your testresultdata LinkedHashMap holds your header and row data:

LinkedHashMap<String, Object[]> testresultdata = new LinkedHashMap<>();
// Example data (replace with your actual data)
testresultdata.put("Header", new Object[]{"Test Name", "Status", "Details"});
testresultdata.put("Row1", new Object[]{"Login Test", "Pass", "Successfully authenticated user"});
testresultdata.put("Row2", new Object[]{"Checkout Test", "Fail", "Timed out during payment processing"});

// Write the header row first
int rowNum = 0;
Row headerRow = sheet.createRow(rowNum++);
String[] headers = (String[]) testresultdata.get("Header");
for (int col = 0; col < headers.length; col++) {
    Cell cell = headerRow.createCell(col);
    cell.setCellValue(headers[col]);
    cell.setCellStyle(headerStyle); // Apply header style
}

// Write data rows with alternating colors
for (Map.Entry<String, Object[]> entry : testresultdata.entrySet()) {
    // Skip the header entry since we already wrote it
    if (entry.getKey().equals("Header")) continue;

    Row row = sheet.createRow(rowNum++);
    Object[] rowData = entry.getValue();

    // Pick style based on row number (alternating colors)
    CellStyle currentStyle = (rowNum % 2 == 0) ? evenRowStyle : defaultRowStyle;

    for (int col = 0; col < rowData.length; col++) {
        Cell cell = row.createCell(col);
        // Handle different data types (adjust based on your actual data)
        if (rowData[col] instanceof String) {
            cell.setCellValue((String) rowData[col]);
        } else if (rowData[col] instanceof Integer) {
            cell.setCellValue((Integer) rowData[col]);
        }
        // Apply the style to each cell in the row
        cell.setCellStyle(currentStyle);
    }
}

// Save the workbook to a file
try (FileOutputStream outputStream = new FileOutputStream("TestResult.xls")) {
    workbook.write(outputStream);
} catch (IOException e) {
    e.printStackTrace();
}

Quick Notes to Remember #

• Column Width Logic: The 256 multiplier is important—POI uses this unit to account for varying character widths.
• Row Colors: Since POI doesn't style entire rows directly, we loop through each cell in the row to apply the style. Alternating colors make your sheet much easier to read.
• Color Options: Use IndexedColors for predefined colors, or if you need custom shades, you can create them with XSSFColor (for .xlsx files instead of .xls).

内容的提问来源于stack exchange，提问作者Zakaria Shahed