如何设置与 EPPlus 的 XLSX 单元宽度？

你好，我有这个代码，我创建了一个 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);
106456 次浏览
小开
最佳答案

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.

ws.Cells["A1:K20"].AutoFitColumns();
小开

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.

i found following code from this epp reported issue to get the closet possible column width as desired. 

//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;
}
小开

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:

    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;
}
小开

You can change the default width of all columns in the worksheet by simply changing its DefaultColWidth property:

worksheet.DefaultColWidth = 25;
小开

There is an easier way. Excel will quantize the passed in column widths to display 12ths below 1 and into 7ths above. This means a staircase result and many end values cannot be made (e.g. 3.5,4.5 etc).

To pre-compensate a width the following is sufficient.

IF DesiredWidth < 1 then

AdjustedWidth = 12/7 * DesiredWidth

ELSE

AdjustedWidth = DesiredWidth + 5/7

ENDIF

Write Worksheet.Column(i).Width = AdjustedWidth with EPPLUS

This is a monotonic adjustment and Excel does all of the quantizing on open/save.


提交答案