Great question! The core SheetJS (js-xlsx) library doesn't natively support cell styling like background colors or font sizes—you'll need to use a community-maintained extension called xlsx-style (since the official Pro version is paid) to add these features. Here's a step-by-step solution tailored to your code:

1. Switch to xlsx-style #

The base xlsx package doesn't handle styling, so first install the extended version:

npm install xlsx-style --save

(Note: If you're using CDNs, you can find a hosted version of xlsx-style instead of npm.)

2. Modify Your Code to Add Styles & Auto-Fit Columns #

Here's how to update your existing code to include background colors, font sizes, and auto-fit column widths:

// Import xlsx-style (replace your existing XLSX import)
const XLSX = require('xlsx-style');

// Sample data (replace with your actual data)
const data = [
  { Name: "Alice", Age: 30, City: "New York" },
  { Name: "Bob", Age: 25, City: "London" }
];

// 1. Create worksheet from JSON
let ws = XLSX.utils.json_to_sheet(data);

// 2. Add cell styles (background color + font size)
// First, get the range of cells in the worksheet
const range = XLSX.utils.decode_range(ws['!ref']);

// Loop through all cells to apply styles (or target specific cells)
for (let R = range.s.r; R <= range.e.r; ++R) {
  for (let C = range.s.c; C <= range.e.c; ++C) {
    const cellAddress = XLSX.utils.encode_cell({r: R, c: C});
    
    // If cell doesn't exist, create it
    if (!ws[cellAddress]) ws[cellAddress] = { v: "" };
    
    // Apply style: light gray background + 12pt font
    ws[cellAddress].s = {
      fill: {
        fgColor: { rgb: "E0E0E0" } // Light gray background (hex without #)
      },
      font: {
        sz: 12 // Font size in points
      },
      alignment: {
        wrapText: true, // Optional: wrap text if needed
        vertical: "center"
      }
    };
  }
}

// 3. Auto-fit column widths
ws['!cols'] = [];
// Calculate max width for each column
for (let C = range.s.c; C <= range.e.c; ++C) {
  let maxWidth = 0;
  for (let R = range.s.r; R <= range.e.r; ++R) {
    const cellAddress = XLSX.utils.encode_cell({r: R, c: C});
    const cellValue = ws[cellAddress]?.v || "";
    // Approximate width based on character count (adjust multiplier as needed)
    const cellWidth = cellValue.toString().length * 1.2;
    if (cellWidth > maxWidth) maxWidth = cellWidth;
  }
  // Set column width (minimum 10 to avoid too narrow columns)
  ws['!cols'].push({ wch: Math.max(maxWidth, 10) });
}

// 4. Add worksheet to workbook and export
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

// Export as Excel file
XLSX.writeFile(wb, "styled_data.xlsx");

Key Explanations: #

• Cell Styles: The s property on each cell defines styles. For background color, use fill.fgColor.rgb (note: hex values are without the #). For font size, set font.sz to the point size.
• Auto-Fit Columns: We calculate the maximum width for each column by iterating through all cells, then set ws['!cols'] with the calculated widths (wch is the width in character units).
• Targeting Specific Cells: If you don't want to style all cells, you can target specific ranges (e.g., only headers) by adjusting the loop conditions or directly setting styles for specific cell addresses like ws['A1'].s = { ... }.

Notes: #

• If you encounter issues with xlsx-style and modern Node.js versions, you might need to use a fork like xlsx-styles (note the plural) or adjust your build settings.
• For more advanced styles (borders, text color), you can extend the s object with additional properties (e.g., font.color.rgb for text color, border for cell borders).

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