NPOI Auto resize column

Question

I am trying to write some contents to excel file using NPOI. But while using the auto resize column method is causing a error "Parameter not valid". This happens only for sheets with huge data. Below is code which I used to do the job.

public void CloseDatabaseLogFile()
{
    try
    {
        FileStream sw = File.Create(excelSheetPath);

        oSheet.AutoSizeColumn(0);
        oSheet.SetColumnWidth(1, 8400);

        oSheet.AutoSizeColumn(2);
        oSheet.AutoSizeColumn(3);
        oSheet.AutoSizeColumn(4);
        oSheet.AutoSizeColumn(5);
        oSheet.AutoSizeColumn(6);
        oSheet.AutoSizeColumn(7);
        oSheet.AutoSizeColumn(8);
        oSheet.AutoSizeColumn(9);
        oSheet.AutoSizeColumn(10);

        workbook.Write(sw);
        sw.Close();
    }
    catch (Exception e)
    {
        throw e;
    }
}

Answer 1

You can resolve this issue using GC.Collect()(Garbage Collector) between AutoSize calls. This is not the best solution, but it seems that NPOI (2.0.1) is not disposing the BitMap objects that are needed for the AutoSize feature.

I had the same issue here while autosizing 10 columns in a large Excel file. See my fixed code below:

int numberOfColumns = sheet.GetRow(rowOffSet).PhysicalNumberOfCells;
for (int i = 1; i <= numberOfColumns; i++)
{
    sheet.AutoSizeColumn(i);
    GC.Collect(); // Add this line
}

Without the GC.Collect(), I had the following error:

System.ArgumentException: Parameter is not valid.
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
   at NPOI.SS.Util.SheetUtil.GetCellWidth(ICell cell, Int32 defaultCharWidth, DataFormatter formatter, Boolean useMergedCells)
   at NPOI.SS.Util.SheetUtil.GetColumnWidth(ISheet sheet, Int32 column, Boolean useMergedCells)
   at NPOI.XSSF.UserModel.XSSFSheet.AutoSizeColumn(Int32 column, Boolean useMergedCells)

Answer 2

There have two solutions: 1.

for (int i = 0; i < columnNames.Count; i++)
{
    sheet.AutoSizeColumn(i);
}

2.

for (int i = 0; i < columnNames.Count; i++)
{
    sheet.SetColumnWidth(i, (columns[columnNames[i]].ToString().Length) * 2 * 256);
}