Auto column width in EPPlus
C#.Netvb.netEpplusC# Problem Overview
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.
C# Solutions
Solution 1 - C#
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.
Solution 2 - C#
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.
Solution 3 - C#
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()
Solution 4 - C#
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();
}
Solution 5 - C#
I know is a little bit late but I've had the same problem today. If you have a worksheet.DefaultColWidth
defined, it won't work. I've removed that line and added Worksheet.cells.AutoFitColumns();
and it works now.
Solution 6 - C#
It's working just fine for me.
Try:
ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
ExcelPkg.SaveAs();
Solution 7 - C#
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.
Solution 8 - C#
Had to use worksheet.Column(1).AutoFit(0);
AutoFit() wasn't doing the trick.
Solution 9 - C#
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.
Solution 10 - C#
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()