如何使用OpenXML包自动调整Excel列 [英] How to AutoFit excel column using OpenXML Package

问题描述

此代码使用openxml包生成Excel电子表格.请任何人告诉您如何自动调整其列宽.

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();

推荐答案

自动调整逻辑由Microsoft Excel实现，不是OpenXML电子表格格式的一部分.自动拟合包括测量每个单元格中值的宽度(或高度)并找到最大值.

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.

为了在您自己的代码中实现自动调整，您将必须手动测量文本；您可以使用带有适当格式标志(禁用前缀字符)的 TextRenderer.MeasureText 或 Graphics.MeasureString .这将为您提供以像素为单位的大小，您需要将其转换为Excel的卷积列宽单位.公式为:

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 =截断([{字符数} * {最大数字宽度} + {5像素填充}]/{最大数字宽度} * 256)/256

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

摘自本文:列类(DocumentFormat.OpenXml.Spreadsheet)

(最大数字宽度可以通过使用工作簿的默认字体来测量'0'字符的宽度来确定-告诉您它很复杂！)

(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!)

使用此公式获得像元宽度后，您可以找到最大值并将其应用于 Column.Width 属性.

Once you have obtained the cell widths using this formula, you can find the maximum value and apply it to the Column.Width property.

Microsoft Excel呈现文本的方式(与GDI/GDI +的方式相比)存在细微的差异，因此此方法不是100％准确的-但对于大多数用途而言已足够，并且您随时可以向其中添加一些额外的填充确保合适的身材.

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.

这篇关于如何使用OpenXML包自动调整Excel列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！