How to set xlsx cell width using EPPlus in C #

Question

Hello, I have this code where I create an xlsx file and I need to pre-set the cell width of the xlsx sheet. The actual problem is that when I open excell, I need to double-click the space between columns with the mouse to expand the columns and re-view the hidden data. Is there a way to do this programmatically using 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 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); 

Answer 1

I found that setting the column width after filling in all the data in a worksheet works:

 ws.Column(1).Width = 50; 

There is also an autoFitColumns method, but this ignores cells with formulas and wrapped text, so it does not work for me.

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

Answer 2

The actual answer has already been noted, this is the correct way to set the column width, but there is one problem that occurs when you first open the document in excel, it recounts the column width (I don’t know why), as I mentioned in the comment below when I set the column width to 7.86 , he resets it to 7.14 and 10.43 to 9.7x.

I found the following code from this epp message problem to get the column width scale 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; } 

Answer 3

Mubashar Ahmad’s answer helped me, thanks for that. I wanted to indicate how I used it in my project. I applied the extension method and reorganized it.

Here is an implementation that sets the cell width for the first column in a sheet.

  worksheet.Column(1).SetTrueColumnWidth(28); 

Here is an extension method to set a more accurate column width in EPPlus Excel files, note that this method must be inside 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; }