Can I set auto-width on an Open XML SDK-generated spreadsheet without calculating the individual widths?

c# excel openxml-sdk column-width
Grace Note picture Grace Note · May 3, 2010 · Viewed 10.9k times · Source

I'm working on creating an Excel file from a large set of data by using the Open XML SDK. I've finally managed to get a functional Columns node, which specifies all of the columns which will actually be used in the file. There is a "BestFit" property that can be set to true, but this apparently does not do anything. Is there a way to automatically set these columns to "best fit", so that when someone opens this file, they're already sized to the correct amount? Or am I forced to calculate how wide each column should be in advance, and set this in the code?

Answer

Jan Fabry picture Jan Fabry · May 4, 2010

The way I understand the spec and this MSDN discussion, BestFit tells you the width was auto-calculated in Excel, but it does not tell Excel that it should calculate it again next time it is opened.

As "goodol" indicates in that discussion, I think the width can only be calculated when you display the column, since it depends on the contents, the font used, other style parameters... So even if you want to pre-calculate the width yourself, be aware that this is only an estimation, and it can be wrong if the contents contain lots of "wide" characters. Or does the Open XML SDK do this for you?

Related questions

Optimal way to Read an Excel file (.xls/.xlsx)

I know that there are different ways to read an Excel file: Iterop Oledb Open Xml SDK Compatibility is not a question because the program will be executed in a controlled environment. My Requirement : Read a file to a DataTable / …

Read More
Export DataTable to Excel with Open Xml SDK in c#

Sorry for my english please. I'm new in open xml sdk. My program have ability to export some data and DataTable to Excel file (template) In the template I insert the data to some placeholders. It's works very good, but …

Read More
open xml reading from excel file

I want to implement openXml sdk 2.5 into my project. I do everything in this link using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.IO.Packaging; static void Main(string[] args) { String fileName = @"C:\OPENXML\BigData.…

Read More