By themhz


2012-02-01 12:55:25 8 Comments

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

4 comments

@Lorenzo Goldoni 2020-01-21 12:38:10

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

worksheet.DefaultColWidth = 25;

@Tyler Kalosza 2017-05-09 16:12:30

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

@Mubashar 2019-11-13 03:42:48

That's intuitive

@Mubashar 2013-07-28 08:34:14

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

@tibx 2019-11-26 20:03:54

(1 + 2 / 3) == 1; (7 / 256) == 0; (12 / 256 == 0)

@aoifeL 2012-02-02 14:37:42

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

@guanome 2013-05-22 13:25:33

I would add that if you want to autofit all the columns in a worksheet do this for (i = 1; i <= ws.Dimension.End.Column; i++) { ws.Column(i).AutoFit(); }

@Mubashar 2013-07-28 08:15:27

it works but setting different value for example i want to set width of column to 7.86 but it is setting to 7.14 and for 3.5 it is setting to 2.71

@Tevin 2014-07-15 16:56:12

A simpler way to autofit all columns is to use: ws.Cells[ws.Dimension.Address].AutoFitColumns()

@Магжан Куан 2016-06-09 06:23:57

column Width Format Pixels ?

@Baxter 2016-06-21 18:39:50

I am using EPPlus 4.0.5 and this worked for me: ws.Cells.AutoFitColumns(); just be sure to put that after all the cells are created.

@Jonah 2016-07-26 11:09:12

@MubasharAhmad I second this - when I try to set it to 4, it sets it to 3.29, which turns "2016" into "###". Pretty annoying.

@Mubashar 2016-07-27 01:09:16

@Jonah: Yeah please see the below answer how you can fix it.

@curiousBoy 2019-04-10 22:26:43

Important Note: If you are using a very large export data, explicitly setting each column's width like ws.Column(1).Width = 50; is waaaay faster than using AutoFitColumns. Just a reminder.

@curiousBoy 2019-04-16 17:06:54

Just a reminder: AutoFitColumns() function has some serious performance issues especially exported row count is around 500K

Related Questions

Sponsored Content

40 Answered Questions

28 Answered Questions

[SOLVED] What is a NullReferenceException, and how do I fix it?

30 Answered Questions

[SOLVED] How can I cast int to enum?

  • 2008-08-27 03:58:21
  • lomaxx
  • 1423764 View
  • 3250 Score
  • 30 Answer
  • Tags:   c# enums casting int

61 Answered Questions

[SOLVED] In C#, how do I calculate someone's age based on a DateTime type birthday?

  • 2008-07-31 23:40:59
  • Jeff Atwood
  • 621398 View
  • 1890 Score
  • 61 Answer
  • Tags:   c# .net datetime

32 Answered Questions

[SOLVED] How do I generate a random int number?

  • 2010-04-24 23:09:11
  • Rella
  • 2344771 View
  • 1958 Score
  • 32 Answer
  • Tags:   c# random

44 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1113125 View
  • 1911 Score
  • 44 Answer
  • Tags:   c# .net excel file-io

47 Answered Questions

[SOLVED] How do I update the GUI from another thread?

29 Answered Questions

[SOLVED] How to enumerate an enum

Sponsored Content