C# EPPLUS: Autosizing columns to the max autosize not working

I'm generating a number of excel documents with EPPlus and I'm running into some difficulties with the formatting.

I have used

xlWorkSheet.Cells.AutoFitColumns();


and this seems to work for the data, but does not autosize for the column names. For instance I will open up the file and I can see the column names bunched up. I can also click on the border of a column and it will expand the column more. In fact I can do this about 5 times before it seems to reach its maximum for autosizing. Essentially I want to programmatically set each column so that it's at this maximum (I've even tried applying AutoFitColumns multiple times, however this made no difference).

Is there a way to do this?

Note that I can't just hardcode the column widths as I'm trying to reuse the same code for multiple files.

EDIT: Here's my code generalised

private void GeneralTest()
{
var ReportName = "myReport"
int ReportID = 123
var SelectedMonth = "April 2018"
var LAGroup = "all"
var fileName = $"{ReportName}_{SelectedMonth}_{LAGroup}.xlsx";
// Remove all illegal characters and replace with underscores
var disallowedCharacters = new { "/", @"", "?", "%", "*", ":", "|", """, "<", ">" };
foreach (var character in disallowedCharacters)
fileName = fileName.Replace(character, "_");

var filePath = $@"c:Test{fileName}";
if (File.Exists(filePath))
File.Delete(filePath);
FileInfo NewFile = new FileInfo(filePath);

using (ExcelPackage MyExcel = new ExcelPackage(NewFile))
{
var myTestdt = MyGetDataFunction(ReportID, DateTime.Parse(SelectedMonth));
ExcelWorksheet xlWorkSheet;

xlWorkSheet = MyExcel.Workbook.Worksheets.Add("Template");
xlWorkSheet.Name = "Template";

xlWorkSheet.Cells("A1").LoadFromDataTable(myTestdt, true);

var formatRange = xlWorkSheet.Cells(1, 1, 1, myTestdt.Columns.Count);

formatRange.Style.Font.Bold = true;
formatRange.Style.Fill.PatternType = Style.ExcelFillStyle.Solid;
formatRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#CDFFCC"));
formatRange.Style.Font.Size = 10;
formatRange.Style.Font.Name = "Arial";
formatRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
formatRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
formatRange.Style.WrapText = true;

formatRange.AutoFilter = true;

for (int index = 0; index <= myTestdt.Columns.Count - 1; index++)
{
if (myTestdt.Columns(index).DataType.Name == "DateTime")
{
formatRange = xlWorkSheet.Cells(2, index + 1, myTestdt.Rows.Count, index + 1);
formatRange.Style.Numberformat.Format = "dd/mm/yyyy";
}
}

xlWorkSheet.View.FreezePanes(2, 2);

xlWorkSheet.Cells.AutoFitColumns();

MyExcel.SaveAs(NewFile);
}
}


xlWorkSheet.Cells.AutoFitColumns();

2 Answers
2

I ran into almost the same problem and this did solved it for me:

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

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

This does AutoFit the columns to smaller & wider when there's need for it.

This is a simple class I wrote to create an Excel from a DataSet.

Please note that I intentionally avoided catching the exceptions because I do that in the caller.

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ds2Xlsx
{

public class Ds2ExcelEngine
{

public static void CreateExcel(
DataSet ds,
string path,
OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.Light9,
bool autofitColumns = true)
{
if (ds != null)
{
using (ExcelPackage ep = new ExcelPackage())
{
foreach (DataTable dt in ds.Tables)
{
AddTableWorksheet(tableStyle, autofitColumns, ep, dt);
}
ep.SaveAs(new System.IO.FileInfo(path));
}
}
}

private static void AddTableWorksheet(OfficeOpenXml.Table.TableStyles tableStyle, bool autofitColumns, ExcelPackage ep, DataTable dt)
{
ExcelWorksheet ew = ep.Workbook.Worksheets.Add(dt.TableName);
int row = 1;
int column = 1;
foreach (DataColumn dc in dt.Columns)
{
ew.Cells[row, column].Value = dc.Caption;
column++;
}

foreach (DataRow dr in dt.Rows)
{
column = 1;
row++;
foreach (DataColumn dc in dt.Columns)
{
ew.Cells[row, column].Value = dr[dc];
column++;
}
}

column = 1;
foreach (DataColumn dc in dt.Columns)
{
if (dc.DataType == typeof(DateTime))
{
ew.Cells[1, column, row, column].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
column++;
}

var excelTable = ew.Tables.Add(new ExcelAddressBase(1, 1, row, column - 1), $"tbl_{ dt.TableName }");
excelTable.TableStyle = tableStyle;
if (autofitColumns)
{
ew.Cells[ew.Dimension.Address].AutoFitColumns();
}
}
}

}


When I open the Excel I created, all the columns are shown correctly, either headers or content.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.