Auto column width in EPPlus

Question

How to make columns to be auto width when texts in columns are long?

I use this code

 Worksheet.Column(colIndex).AutoFitColumn() 'on all columns'
 Worksheet.cells.AutoFitColumns()
 Worksheet.Column(colIndex).BestFit = True  'on all columns'

None of these methods are working

Are there any ways to make it work?

Note: Some of my texts use Unicode.

Worksheet.cells.AutoFitColumns(); will work, but it should be added just after you formatted and printed all data. — Suresh Kamrushi, Aug 14 '18 at 10:26

score 284 · Answer 1 · edited Nov 11 '15 at 21:34

284

Use AutoFitColumns, but you have to specify the cells, i assume the entire worksheet:

VB.NET

Worksheet.Cells(Worksheet.Dimension.Address).AutoFitColumns()

C#

Worksheet.Cells[Worksheet.Dimension.Address].AutoFitColumns();

Please note you need to call this method after filling the worksheet.

edited Nov 11 '15 at 21:34

Tariqulazam

4,535
1
34
42

answered Mar 26 '13 at 17:00

Tim Schmelter

450,073
74
686
939

14

Important thing is here that you need to call this method after filling the worksheet because Worksheet.Dimension property will return null if there's no data in the worksheet. – Arkadiusz Kałkus Jan 23 '15 at 10:22
@Landeeyo Thanks it helps for me. It is important that setting width after filling the content. – Lanka Apr 10 '15 at 08:58
4

AutoFitColumns method was not working for me when i was providing no parameters, when i add min and max width to it, it works perfectly. – Zeeshan Ajmal Apr 29 '15 at 09:53
4

This absolutely the correct answer - Pangan should mark it as such. – GDB Nov 21 '15 at 14:10
2

If you have calculated cells you must call `Worksheet.Calculate()` first so that the value for those cells can be determined. – MikeH Jan 07 '19 at 19:45
Important thing if you are running your code in linux based docker container and don't install manually the System.Drawing package the AutoFitColumns call will maybe causing an Exception. – totesz09 Oct 05 '22 at 05:50

score 45 · Answer 2 · edited Oct 18 '19 at 08:20

45

I have used this code with the version 3.1.3.0 of EPPlus and it is working:

worksheet.Column(1).AutoFit();

where a worksheet is the variable referencing the worksheet I have created in my code (not a class with a static method!).

Obviously you have to call this method after you have filled the columns.

edited Oct 18 '19 at 08:20

Jaymin

2,879
3
19
35

answered May 28 '13 at 15:52

Daniele Armanasco

7,289
9
43
55

1

getting error all the day down. "'System.Drawing is not supported on this platform." – Zen Of Kursat Mar 24 '18 at 12:34

johnny 5 · Answer 3 · 2019-01-04T15:53:53.393

28

Just wanted to point out you can fit cells with out specifying the range, just make sure to call this after you've formatted all columns etc:

worksheet.Cells.AutoFitColumns()

edited Jan 04 '19 at 15:53

answered Oct 25 '18 at 16:36

johnny 5

19,893
50
121
195

1

This worked for me. Do remember to call it after you've added the data. When I called it before data was added nothing happened. – mortb Nov 06 '19 at 11:38

score 16 · Answer 4 · answered Jun 30 '16 at 14:20

I know this is an old question, but I use the code below and it seems to directly address what you have tried to do.

using (var xls = new ExcelPackage())
{
    var ws = xls.Workbook.Worksheets.Add("Some Name");

    //**Add Column Names to worksheet!**
    //**Add data to worksheet!**

    const double minWidth = 0.00;
    const double maxWidth = 50.00;

    ws.Cells.AutoFitColumns(minWidth, maxWidth);

    return pkg.GetAsByteArray();
}

score 12 · Answer 5 · edited Jan 05 '18 at 12:14

12

I know is a little bit late but I've had the same problem today. If you have a worksheet.DefaultColWidthdefined, it won't work. I've removed that line and added Worksheet.cells.AutoFitColumns(); and it works now.

edited Jan 05 '18 at 12:14

wonea

4,783
17
86
139

answered Sep 18 '15 at 10:18

Jose

1,857
1
16
34

5

`sheet.Cells.AutoFitColumns()` is simpler, thanks for the tip! In ClosedXml, you do `sheet.Columns().AdjustToContents()` – nawfal Jun 26 '16 at 07:03

score 6 · Answer 6 · edited Oct 08 '18 at 11:19

6

It's working just fine for me.

Try:

ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
ExcelPkg.SaveAs();

edited Oct 08 '18 at 11:19

Prashant Pimpale

10,349
9
44
84

answered Jul 02 '18 at 09:13

leiit

97
1
8

score 5 · Answer 7 · answered Nov 15 '19 at 13:39

The .NET Core as a successor of .NET doesn't support anymore the function autofit cells with EPPplus library.

worksheet.Cells.AutoFitColumns();

or

worksheet.Column(1).AutoFit();

causes exception:

"System.Drawing is not supported on this platform."

The System.Drawing assembly is dependent on GDI and Windows specific libraries which have to be replaced by another solution. The solution for this issue is to me unknown.

score 3 · Answer 8 · edited Oct 18 '19 at 08:22

3

You will need to calculate the width. There is no autosizing function in the library that will work as you intend.

Autofitcolumn will not work with wrapped text and cells with formulas.

Look at http://epplus.codeplex.com/discussions/218294?ProjectName=epplus for examples of how you can solve the problem.

edited Oct 18 '19 at 08:22

Jaymin

2,879
3
19
35

answered Mar 26 '13 at 17:03

ffffff01

5,068
11
52
61

http://epplus.codeplex.com/discussions/218294?ProjectName=epplus I cannot find any working solution for this problem. – Pengan Mar 28 '13 at 06:22

score 3 · Answer 9 · answered Jul 23 '13 at 07:09

3

Had to use worksheet.Column(1).AutoFit(0); AutoFit() wasn't doing the trick.

answered Jul 23 '13 at 07:09

xtds

2,453
2
19
12

score 1 · Answer 10 · answered Dec 05 '13 at 20:21

1

I use this and is working well.

Dim objExcel As New ExcelPackage
Dim Sheet As ExcelWorksheet = objExcel.Workbook.Worksheets.Add("SheetName")
Sheet.Cells("B1:BN").AutoFitColumns()

answered Dec 05 '13 at 20:21

Jhonny Nina

579
5
5

Auto column width in EPPlus

10 Answers10

Linked