CODEWITHSUNDEEP
c#excelopenxmlopenxml-sdk
NixonUposseen
NixonUposseen

Reputation: 63

Custom column width in OpenXML vs MS Excel

I'm creating an XLSX file with OpenXML SDK and I'm specifying a custom column width to 15:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create("Book1.xlsx", SpreadsheetDocumentType.Workbook))
{
    WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet();

    spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(
        new Sheets(
            new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }));

    worksheetPart.Worksheet.Append(
        new Columns(
            new Column() { Min = 1, Max = 1, Width = 15, CustomWidth = true }),
        new SheetData());

    workbookPart.Workbook.Save();
}

But when I open the resulting "Book1.xlsx" file in MS Excel and check the column's value, it shows:

Width: 14.29 (105 pixels)

Also, when I set the column's value with MS Excel:

Width: 15 (110 pixels)

And then read that value with OpenXML SDK I get ~15.711.
The question is why, why is it different?

Upvotes: 2

Views: 4080

Answers (2)

Mario Z
Mario Z

Reputation: 4381

The difference may occur due to several variables which are taken into account when MS Excel is calculating column width (which it does so on load and on change); the Normal style's font settings, the machine's DPI settings, the MS Excel's ruler unit, etc.

Regarding the formula provided by JimSnyder, note that MS Excel has a slightly different column width calculation than the one specified by the standard, see here.

Upvotes: 3

JimSnyder
JimSnyder

Reputation: 157

This is from Vincent Tan's book SpreadsheetOpenXmlFromScratch:

// This is taken from the documentation for Column.Width in help file for Open XML SDK
// Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
fTruncWidth = Math.Truncate((sILT.ToCharArray().Count() * fMaxDigitWidth + 5.0) / fMaxDigitWidth * 256.0) / 256.0;

The formula should give you the width you need. As to why it is different, my best guess would be converting font points to window pixels.

Upvotes: 2

Related Questions