none
Setting column width in Excel RRS feed

  • Question

  • Consider the following sample code:
    using (var file = SpreadsheetDocument.Create("Sample.xlsx", SpreadsheetDocumentType.Workbook))
    {
        var wb = file.AddWorkbookPart();
        wb.Workbook = new Workbook();
        var ws = wb.AddNewPart<WorksheetPart>();
        ws.Worksheet = new Worksheet();
        file.WorkbookPart.Workbook.AppendChild<Sheets>(
            new Sheets(
                new Sheet() { Name = "Sample", SheetId = 1, Id = wb.GetIdOfPart(ws) }));
    
        ws.Worksheet.Append(
            new Columns(
                new Column() { Min = 1, Max = 1, Width = 10, CustomWidth = true },
                new Column() { Min = 2, Max = 2, Width = 20, CustomWidth = true }),
            new SheetData());
    
        wb.Workbook.Save();
    }

    The resulting "Sample.xlsx" file has the following:

    <x:cols>
        <x:col min="1" max="1" width="10" customWidth="1" />
        <x:col min="2" max="2" width="20" customWidth="1" />
    </x:cols>

    But when viewing in MS Excel I see the following (9.29 instead of 10 and 19.29 instead of 20):

    Aren't they supposed to be the same values? Why does MS Excel show slightly smaller values then ones that I specified?

    • Edited by Pusting Tuesday, September 19, 2017 2:08 PM
    Tuesday, September 19, 2017 2:06 PM

Answers

  • Hi Pusting,

    I have found below link to explain how to calculate column width and I think you could try to take reference to it.

    https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column(v=office.14).aspx

    Besides, this forum is for issues about using Open XML, so I would suggest you post threads on Office XML,ODF,and Binary File Formats forum for help.

    That forum discusses the technical content and implementation of the Office binary file formats (.doc, .xls, .ppt, .pst, etc.), the Open XML file formats (ISO/IEC 29500) (.docx, .xlsx, .pptx, etc.), the ODF 1.1 standard, and the Open Packaging Conventions APIs (ISO/IEC 29500-2). 

    I think the supporters in that forum are more professional about Open XML file formats.

    Best Regards,

    Terry

    • Marked as answer by Pusting Thursday, September 21, 2017 2:00 PM
    Thursday, September 21, 2017 9:32 AM

All replies

  • Hi Pusting,

    I could reproduce your issue.

    If you set the column width 10 manually and then you will see the column width become about 10.71 in XML.

    So I think this is normal result. I would suggest you plus 0.71 when you want to set column width.

    Best Regards,

    Terry

    Wednesday, September 20, 2017 8:37 AM
  • Thank you for the reply, I appreciate it.
    Yes I did vice-versa as well and I am using the values that MS Excel stored, but my question was:

    Aren't they supposed to be the same values? Why does MS Excel show slightly smaller values then ones that I specified?

    So I think this is normal result. I would suggest you plus 0.71 when you want to set column width.

    May I ask why is that normal?
    Again I don't have a problem of getting the desired width in MS Excel, I'm having a problem understanding the difference in those two values. I am under the assumption that they represent the same thing, which is why this behaviour seems odd and wrong.

    Wednesday, September 20, 2017 8:51 AM
  • Hi Pusting,

    I have found below link to explain how to calculate column width and I think you could try to take reference to it.

    https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column(v=office.14).aspx

    Besides, this forum is for issues about using Open XML, so I would suggest you post threads on Office XML,ODF,and Binary File Formats forum for help.

    That forum discusses the technical content and implementation of the Office binary file formats (.doc, .xls, .ppt, .pst, etc.), the Open XML file formats (ISO/IEC 29500) (.docx, .xlsx, .pptx, etc.), the ODF 1.1 standard, and the Open Packaging Conventions APIs (ISO/IEC 29500-2). 

    I think the supporters in that forum are more professional about Open XML file formats.

    Best Regards,

    Terry

    • Marked as answer by Pusting Thursday, September 21, 2017 2:00 PM
    Thursday, September 21, 2017 9:32 AM