Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to AutoFit excel column using OpenXML Package

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();
like image 302
Prakash Gupta Avatar asked Jul 03 '15 01:07

Prakash Gupta


People also ask

How do you set a column to AutoFit?

Change the column width to automatically fit the contents (AutoFit) Select the column or columns that you want to change. On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Column Width.

What is the AutoFit command?

To adjust table width automatically, click AutoFit Window. Note: Row height automatically adjusts to the size of the content until you manually change it.

How do you AutoFit cell size to contents?

To AutoFit column width, select one, several or all columns on the sheet, go to the Home tab > Cells group, and click Format > AutoFit Column Width. To AutoFit row height, select the row(s) of interest, go to the Home tab > Cells group, and click Format > AutoFit Row Height.


1 Answers

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.

like image 174
Bradley Smith Avatar answered Nov 04 '22 17:11

Bradley Smith