How to auto-detect Excel column using OpenXML package
This code is to create an Excel spreadsheet using the openxml package. Please someone tell me how to automatically set the 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();
source to share
The auto-install logic is what is implemented by Microsoft Excel and is not 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.
To implement auto-fitting in your own code, you have to manually measure the text; you can use TextRenderer.MeasureText
or Graphics.MeasureString
with the appropriate format flags (disable prefix characters). This will give you the pixel size you will need to convert to Gated Column Excel. The formula for this:
width = Truncate ([{number of characters} * {maximum path width} + {padding 5 pixels}] / {maximum width) * 256) / 256
Taken from this article: Column class (DocumentFormat.OpenXml.Spreadsheet)
(The maximum character width can be determined by measuring the character width '0'
using the book's default font - reported it was confusing!)
Once you've got the cell width using this formula, you can find the maximum value and apply it to the property Column.Width
.
There are subtle differences in how Microsoft Excel renders text (versus how GDI / GDI + does), so this method is not 100% accurate - but it is sufficient for most purposes and you can always add an extra padding. to ensure a proper fit.
source to share