How to AutoFit excel column using OpenXML Package
The auto-fit logic is something which is implemented by Microsoft Excel, and is not a part of the OpenXML spreadsheet format. Auto-fit involves measuring the width (or height) of the value in each cell and finding the maximum value.
In order to implement auto-fit in your own code, you will have to manually measure the text; you can use TextRenderer.MeasureText
or Graphics.MeasureString
with appropriate format flags (disable prefix characters). This will give you a size in pixels, which you will need to convert to Excel's convoluted column width units. The formula for this is:
width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
Taken from this article: Column Class (DocumentFormat.OpenXml.Spreadsheet)
(Maximum Digit Width can be determined by measuring the width of the '0'
character using the workbook's default font - told you it was convoluted!)
Once you have obtained the cell widths using this formula, you can find the maximum value and apply it to the Column.Width
property.
There are subtle differences in the way Microsoft Excel renders text (compared to how GDI/GDI+ does it), so this method is not 100% accurate - but it is sufficient for most purposes and you can always add some extra padding to ensure a proper fit.
Prakash Gupta
Updated on July 09, 2022Comments
-
Prakash Gupta almost 2 years
This code to generate Excel spreadsheet Using openxml package. Please anyone tell how to auto fit its column width.
OpenXmlPackage.SpreadsheetDocument spreadsheetDocument = OpenXmlPackage.SpreadsheetDocument.Create(downloadFilePath, OpenXml.SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. OpenXmlPackage.WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); // Add a WorksheetPart to the WorkbookPart. workbookpart.Workbook = new OpenXmlSpreadsheet.Workbook(); int numDates = datesObject.Length; // Add Sheets to the Workbook. OpenXmlSpreadsheet.Sheets sheets = new OpenXmlSpreadsheet.Sheets(); OpenXml.UInt32Value sheetId = 1; OpenXmlPackage.WorksheetPart firstWorksheetPart = workbookpart.AddNewPart<OpenXmlPackage.WorksheetPart>(); firstWorksheetPart.Worksheet = new OpenXmlSpreadsheet.Worksheet(new OpenXmlSpreadsheet.SheetData()); // Append a new worksheet and associate it with the workbook. OpenXmlSpreadsheet.Sheet firstSheet = new OpenXmlSpreadsheet.Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(firstWorksheetPart), SheetId = sheetId, Name = "Summary" }; sheets.Append(firstSheet); sheetId++; OpenXmlSpreadsheet.SheetData firstSheetData = firstWorksheetPart.Worksheet.GetFirstChild<OpenXmlSpreadsheet.SheetData>(); DataTable summaryTable = new DataTable(); summaryTable.Clear(); summaryTable.Columns.Add("name"); summaryTable.Columns.Add("value"); DataRow _summaryInfo = summaryTable.NewRow(); _summaryInfo["name"] = "Clinic Name"; _summaryInfo["value"] = userInfo[0]; summaryTable.Rows.Add(_summaryInfo); int firstRowCount = summaryTable.Rows.Count; for (int rowNumber = 1; rowNumber <= firstRowCount; rowNumber++) { DataRow dataRow = summaryTable.Rows[rowNumber - 1]; OpenXmlSpreadsheet.Row contentRow = ExcelHandler.createContentRow(dataRow, rowNumber); firstSheetData.AppendChild(contentRow); } firstWorksheetPart.Worksheet.Save();
-
Emaborsa over 7 yearsWhat does the
Truncate
exactly do and why is it needed? -
Bradley Smith over 7 years@Emaborsa
Truncate
discards the decimal portion of the number, effectively rounding it down to the nearest integer. This is needed because pixels are expressed as whole numbers. -
Emaborsa over 7 yearsOK, but the
Column.Width
property takes adouble
(I think in points) and not anint
for the pixel... -
Bradley Smith over 7 yearsSorry, my last comment was wrong; the
Column.Width
property is measured in Excel's column width units (not pixels or points). So theTruncate
function is used purely because Microsoft decided to discard the decimal portion of the result before dividing by 256 - maybe this is for precision reasons? Anyway, it does not make a huge difference to the final figure. -
Lucy82 over 4 years@Prakash Gupta take a look at my answer. Posted code is in DOM method, but I created a solution with SAX approach too and results are outstanding - no memory drain (writing directly from DataReader) and much faster than Interop library. Only down-side is Autofit - you ave to read same data twice.