C# 如何使用 EPPlus 设置 XLSX 单元格宽度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9096176/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set XLSX cell width with EPPlus?
提问by themhz
Hello I have this code where i create an xlsx file and i need to pre set the width of the xlsx sheet cells. The actual problem is that when i open the excell i need to double click on the gap between the columns with the mouse in order to unwrap the columns and revieal the data that is hidden. Is there a way to do this programmaticaly with Epplus?
您好，我有这段代码，我在其中创建了一个 xlsx 文件，我需要预先设置 xlsx 工作表单元格的宽度。实际问题是，当我打开 Excel 时，我需要用鼠标双击列之间的间隙，以便展开列并查看隐藏的数据。有没有办法用 Epplus 以编程方式执行此操作？
using (ExcelPackage p = new ExcelPackage())
{
String filepath = "C://StatsYellowPages.csv";
DataSet ds = ExportCSVFileToDataset(filepath, "tblCustomers", "\t");
//Here setting some document properties
p.Workbook.Properties.Title = "StatsYellowPages";
//Create a sheet
p.Workbook.Worksheets.Add("Sample WorkSheet");
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.Name = "StatsYellowPages"; //Setting Sheet's name
//Merging cells and create a center heading for out table
ws.Cells[1, 1].Value = "StatsYellowPages";
ws.Cells[1, 1, 1, ds.Tables[0].Columns.Count].Merge = true;
ws.Cells[1, 1, 1, ds.Tables[0].Columns.Count].Style.Font.Bold = true;
ws.Cells[1, 1, 1, ds.Tables[0].Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
int colIndex = 1;
int rowIndex = 2;
foreach (DataColumn dc in ds.Tables[0].Columns) //Creating Headings
{
var cell = ws.Cells[rowIndex, colIndex];
//Setting the background color of header cells to Gray
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.Gray);
//Setting Top/left,right/bottom borders.
var border = cell.Style.Border;
border.Bottom.Style = ExcelBorderStyle.Thin;
border.Top.Style = ExcelBorderStyle.Thin;
border.Left.Style = ExcelBorderStyle.Thin;
border.Right.Style = ExcelBorderStyle.Thin;
//Setting Heading Value in cell
cell.Value = dc.ColumnName;
colIndex++;
}
foreach (DataRow dr in ds.Tables[0].Rows) // Adding Data into rows
{
colIndex = 1;
rowIndex++;
foreach (DataColumn dc in ds.Tables[0].Columns)
{
var cell = ws.Cells[rowIndex, colIndex];
//Setting Value in cell
cell.Value = dr[dc.ColumnName].ToString();
//Setting borders of cell
var border = cell.Style.Border;
colIndex++;
}
}
//Generate A File with Random name
Byte[] bin = p.GetAsByteArray();
string file = "c:\StatsYellowPages.xlsx";
File.WriteAllBytes(file, bin);
采纳答案by aoifeL
I find that setting the column widths after I have filled in all the data on the sheet works:
我发现在填写工作表上的所有数据后设置列宽有效：
ws.Column(1).Width = 50;
There is also the autoFitColumns method but this ignores cells with formulas and wrapped text so it did not work for me.
还有 autoFitColumns 方法，但这会忽略带有公式和换行文本的单元格，因此它对我不起作用。
ws.Cells["A1:K20"].AutoFitColumns();
回答by Mubashar
Actual Answer is already marked thats the right way of setting column width but there is one issue that is when document is opened first time in excel, it recalculates columns' width (dont know why) so as i mentioned in comment below the marked answer when i set column width to 7.86 its resets it to 7.14 and 10.43 to 9.7x.
实际答案已经被标记为设置列宽的正确方法，但是有一个问题是当文档第一次在 excel 中打开时，它会重新计算列的宽度（不知道为什么），所以正如我在标记答案下方的评论中提到的那样我将列宽设置为 7.86，将其重置为 7.14，将 10.43 重置为 9.7x。
i found following code from this epp reported issueto get the closet possible column width as desired.
我从这个 epp 报告的问题中发现以下代码可以根据需要获得最接近的可能列宽。
//get 7.14 in excel
ws.Column(1).Width = 7.86;
//get 7.86 in excel
ws.Column(1).Width = GetTrueColumnWidth(7.86);
public static double GetTrueColumnWidth(double width)
{
//DEDUCE WHAT THE COLUMN WIDTH WOULD REALLY GET SET TO
double z = 1d;
if (width >= (1 + 2 / 3))
{
z = Math.Round((Math.Round(7 * (width - 1 / 256), 0) - 5) / 7, 2);
}
else
{
z = Math.Round((Math.Round(12 * (width - 1 / 256), 0) - Math.Round(5 * width, 0)) / 12, 2);
}
//HOW FAR OFF? (WILL BE LESS THAN 1)
double errorAmt = width - z;
//CALCULATE WHAT AMOUNT TO TACK ONTO THE ORIGINAL AMOUNT TO RESULT IN THE CLOSEST POSSIBLE SETTING
double adj = 0d;
if (width >= (1 + 2 / 3))
{
adj = (Math.Round(7 * errorAmt - 7 / 256, 0)) / 7;
}
else
{
adj = ((Math.Round(12 * errorAmt - 12 / 256, 0)) / 12) + (2 / 12);
}
//RETURN A SCALED-VALUE THAT SHOULD RESULT IN THE NEAREST POSSIBLE VALUE TO THE TRUE DESIRED SETTING
if (z > 0)
{
return width + adj;
}
return 0d;
}
回答by Tyler Kalosza
Mubashar Ahmad's answer helped me, thank you for that. I wanted to include how I used it in my project. I have made it into an extension method and refactored it.
穆巴沙尔艾哈迈德的回答帮助了我，谢谢你。我想包括我如何在我的项目中使用它。我已经把它变成了一个扩展方法并重构了它。
Here is the implementation, which sets the cell width for the first column in the worksheet.
这是实现，它设置工作表中第一列的单元格宽度。
worksheet.Column(1).SetTrueColumnWidth(28);
Here is the extension method for setting a more accurate column width in EPPlus Excel files, note that this method must be inside of a static class:
这是在 EPPlus Excel 文件中设置更准确列宽的扩展方法，注意此方法必须在静态类内部：
public static void SetTrueColumnWidth(this ExcelColumn column, double width)
{
// Deduce what the column width would really get set to.
var z = width >= (1 + 2 / 3)
? Math.Round((Math.Round(7 * (width - 1 / 256), 0) - 5) / 7, 2)
: Math.Round((Math.Round(12 * (width - 1 / 256), 0) - Math.Round(5 * width, 0)) / 12, 2);
// How far off? (will be less than 1)
var errorAmt = width - z;
// Calculate what amount to tack onto the original amount to result in the closest possible setting.
var adj = width >= 1 + 2 / 3
? Math.Round(7 * errorAmt - 7 / 256, 0) / 7
: Math.Round(12 * errorAmt - 12 / 256, 0) / 12 + (2 / 12);
// Set width to a scaled-value that should result in the nearest possible value to the true desired setting.
if (z > 0)
{
column.Width = width + adj;
return;
}
column.Width = 0d;
}
回答by Lorenzo Goldoni
You can change the default width of all columns in the worksheet by simply changing its DefaultColWidthproperty:
您可以通过简单地更改其DefaultColWidth属性来更改工作表中所有列的默认宽度：
worksheet.DefaultColWidth = 25;
