如何为SheetJS生成的.xlsx文件添加单元格边框及其他样式？

Hey Roberto, great question! The core SheetJS xlsx library doesn't natively support cell styling (like borders or background colors) out of the box, but we can fix this with a style-enabled fork of the library and some targeted code changes. Let's walk through how to get your desired cell borders and add other styles like background colors.

Step 1: Switch to a Style-Enabled SheetJS Library #

First, you'll need to use sheetjs-style (a maintained fork that supports cell styling) instead of the standard xlsx package. If you're using npm, install it with:

npm install sheetjs-style

If you're using a browser script, replace your existing SheetJS script tag with the one for sheetjs-style.

Step 2: Modify Your makeSheet Function to Add Styles #

Here's how to update your function to add borders to all cells and optionally set background colors (like for header rows):

function makeSheet(wb, day){
  // Create sheet from table as before
  var ws = XLSX.utils.table_to_sheet(document.getElementById("table"+day));
  
  // Get the range of cells in the sheet
  const range = XLSX.utils.decode_range(ws['!ref']);

  // Add borders to every cell
  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});
      
      // Create empty cell object if it doesn't exist
      if(!ws[cellAddress]) ws[cellAddress] = { v: "" };
      
      // Define border style (thin black borders on all sides)
      ws[cellAddress].s = {
        border: {
          top: { style: 'thin', color: { rgb: '000000' } },
          bottom: { style: 'thin', color: { rgb: '000000' } },
          left: { style: 'thin', color: { rgb: '000000' } },
          right: { style: 'thin', color: { rgb: '000000' } }
        }
      };
    }
  }

  // Optional: Add background color to the header row
  const headerRowIndex = range.s.r; // First row is the header
  for(let C = range.s.c; C <= range.e.c; ++C) {
    const cellAddress = XLSX.utils.encode_cell({ r: headerRowIndex, c: C });
    if(ws[cellAddress]) {
      // Set light gray background
      ws[cellAddress].s.fill = {
        fgColor: { rgb: 'E0E0E0' }
      };
      // Keep the border style we added earlier
      ws[cellAddress].s.border = {
        top: { style: 'thin', color: { rgb: '000000' } },
        bottom: { style: 'thin', color: { rgb: '000000' } },
        left: { style: 'thin', color: { rgb: '000000' } },
        right: { style: 'thin', color: { rgb: '000000' } }
      };
    }
  }

  // Add sheet to workbook as before
  wb.SheetNames.push(day);
  wb.Sheets[day] = ws;
  // Your column width logic remains unchanged...
}

Key Style Options Explained #

• Borders: The border object lets you define styles for each side (top/bottom/left/right). Supported styles include thin, medium, thick, dashed, dotted, etc. Colors use RGB values without the # prefix (e.g., 000000 for black).
• Background Colors: Use the fill.fgColor.rgb property to set a cell's background. Pick any RGB value (e.g., FF0000 for red, FFFF00 for yellow).
• Other Styles: You can also add font styles (bold, italic), text alignment, and more by expanding the s object. For example:

  ws[cellAddress].s = {
    font: { bold: true, italic: true },
    alignment: { horizontal: 'center', vertical: 'middle' }
  };
  

Why This Works #

The standard xlsx library ignores style properties when exporting, but sheetjs-style includes support for the s (style) property on cells and exports these styles to the final .xlsx file. By iterating over all cells and adding the s property, we ensure every cell gets your desired border (and any other styles you want to apply).

内容的提问来源于stack exchange，提问作者Roberto Sepúlveda Bravo