How to set XLSX cell width with EPPlus?
C#.NetEpplusC# Problem Overview
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?
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);
C# Solutions
Solution 1 - C#
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();
Solution 2 - C#
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;
}
Solution 3 - C#
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;
}
Solution 4 - C#
You can change the default width of all columns in the worksheet by simply changing its DefaultColWidth property:
worksheet.DefaultColWidth = 25;
Solution 5 - C#
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.