Can I set the automatic width in an open XML SDK open table without calculating individual widths?

Question

I am working on creating an Excel file from a large dataset using the Open XML SDK. I finally managed to get the functional columns of the node, which sets all the columns that will actually be used in the file. There is a "BestFit" property that can be set to true, but this apparently does nothing. Is there a way to automatically adjust these columns to "best fit" so that when someone opens this file, they already have a size of the right amount? Or am I forced to calculate how wide each column should be in advance, and set this in code?

Answer 1

As I understand the specification and this discussion of MSDN , BestFit indicates that the width was automatically calculated in Excel, but it does not tell Excel that it should calculate it again the next time it is opened.

As “goodol” indicates in this discussion, I think that the width can only be calculated when the column is displayed, as it depends on the content, font used, other style parameters ... Therefore, even if you want to pre-calculate the width yourself, keep in mind that this is only an estimate, and it may be wrong if the content contains a lot of "wide" characters. Or does the Open XML SDK do this for you?

Answer 2

I use EPPlus , which I highly recommend. It took a while to figure out how to do this using what I came up with:

 // Get your worksheet in "sheet" variable // Set columns to auto-fit for (int i = 1; i <= sheet.Dimension.Columns; i++) { sheet.Column(i).AutoFit(); }