The Hidden Pitfalls of Excel Column Widths

TLDR

• Excel column width calculation is not an absolute value; it depends on the "default font" and "font size" of the file.
• Setting fonts for individual cells does not affect column width; you must adjust the default font settings at the Workbook level.
• When exporting Excel files using NPOI or EPPlus, failing to unify the default font will result in column widths that do not match expectations.
• After changing the Excel default font, you must restart the Excel software and create a new file for the changes to take effect.
• EPPlus is more intuitive when handling default font application, while NPOI requires attention to its limitations regarding default font property support.

---

The Impact of Excel Default Fonts on Column Width

When you might encounter this issue: When developers use NPOI or EPPlus to export Excel files and find that even with the same column width values set, the resulting Excel column widths do not match the template provided by the client.

Excel's column width calculation logic is based on the character width of the "default font." Even if a developer sets a font for specific cells, that setting does not change the calculation basis for the entire column. If the default font of the exported file differs from the client's template (e.g., the default is Calibri 11pt, while the template uses a different setting), the actual displayed width will vary even if the column width values are identical.

WARNING

After changing the default font size, you must close all Excel documents and create a new Excel document for the new settings to be applied.

Setting the Default Font Using NPOI

When you might encounter this issue: When developing Excel reports using NPOI and requiring precise control over column display widths.

In NPOI, although you can access the default font via GetFontAt(0) and modify its properties, testing shows that the modified font name and size may not be correctly reflected in the column width calculation basis, leading to discrepancies between the produced Excel column widths and expectations.

using IWorkbook workbook = new XSSFWorkbook();
IFont defaultFont = workbook.GetFontAt(0);
// Modify default font properties
defaultFont.FontName = "微軟正黑體";
defaultFont.FontHeightInPoints = 20;

workbook.CreateSheet()
    .CreateRow(0)
    .CreateCell(0)
    .SetCellValue("Test");

using FileStream fileStream = new("Test.xlsx", FileMode.Create, FileAccess.Write);
workbook.Write(fileStream);

Setting the Default Font Using EPPlus

When you might encounter this issue: When a project uses EPPlus as the Excel processing tool and aims to ensure via code that the default style of the exported file matches client requirements.

EPPlus allows direct access to Workbook.Styles.Fonts to modify default styles. Compared to NPOI, EPPlus is more effective at applying modified default font settings to the column width calculation basis, ensuring consistency in column width display.

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using ExcelPackage package = new();
// Access and modify the default font
ExcelFontXml defaultFont = package.Workbook.Styles.Fonts[0];
defaultFont.Name = "微軟正黑體";
defaultFont.Size = 20;

ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Sheet1");
sheet.Cells[1,1,1,1].Value = "Test";

using FileStream fileStream = new("Test.xlsx", FileMode.Create, FileAccess.Write);
package.SaveAs(fileStream);

Conclusion

• The primary cause of inconsistent column widths is different "default font" settings, not simply the column width values themselves.
• If alignment with a specific template is required, you should prioritize checking and unifying the default font settings of the programmatically generated files.
• It is recommended to define a unified Excel style configuration at the beginning of the project to avoid repeatedly adjusting styles for individual cells while neglecting the global default baseline.

Changelog

1. 2025-08-31

   • Initial version created.