0

I've put the .AutoSizeColumn right before the write Method

int numberOfColumns = sheet.GetRow(rowcount - 1).PhysicalNumberOfCells;
for (int i = 0; i <= numberOfColumns; i++)
{
    sheet.AutoSizeColumn(i);
    GC.Collect();
} 

using (var fileData = new FileStream(@"C:\Temp\Contatti.xlsx", FileMode.Create))
{
   wb.Write(fileData);
}

this is an example of the result

Derpzilla
  • 1
  • 2
  • 3
  • How is `sheet.GetRow(rowcount - 1).PhysicalNumberOfCells;` giving you column count? – Techie Mar 07 '16 at 16:11
  • rowcount is the variable i use to count the number of row. - 1 because i set rowcount++; in each loop and i want to get the last "full" row [Link to image](http://i.imgur.com/LiowsKq.png) – Derpzilla Mar 07 '16 at 17:23
  • Does the cell contain formula? What's the benefit or GC.Collect? `i <= numberOfColumns` should be `i < numberOfColumns` as you are starting from 0. btw I've used EPPlus instead of NPOI and found it to be better. – Techie Mar 07 '16 at 18:56

1 Answers1

2

The problem also migh be, that PhysicalNumberOfCells can return 1, even if you have a cell lets say in 'Z' column. There is LastCellNum property,you i instead of PhysicalNumberOfCells:

        int lastColumNum = sheet.GetRow(0).LastCellNum;
        for (int i = 0; i <= lastColumNum; i++)
        {
            sheet.AutoSizeColumn(i);
            GC.Collect();
        }

        using (var fileData = new FileStream(@"D:\Contatti.xlsx", FileMode.Create))
        {
            wb.Write(fileData);
        }
VDN
  • 717
  • 4
  • 12
  • Yes, i've put it for all columns, not only for the first one. I'll edit the question to be more clear – Derpzilla Mar 07 '16 at 16:05
  • @VDN this is a better fit for comment as it's not a complete answer. – Techie Mar 07 '16 at 19:03
  • @Nimesh, I edited my answer, so now it looks more like an answer :) – VDN Mar 07 '16 at 20:57
  • I still have the problem. It's the first time i'm using .xlsx, usually i create .xls files without resize problems. Maybe i've to set a default cell style? – Derpzilla Mar 08 '16 at 08:47
  • one more thing you can try is to get the `LastCellNum` of each row, not only the first one as in my answer. Or at least the `LastCellNum` of the row you have problem with – VDN Mar 08 '16 at 08:58
  • @Derpzilla Can you also update which libraries are you using? I believe NPOI has multiple libraries for different document types (xls|xlsx). – Techie Mar 08 '16 at 12:10
  • That "GC.Collect();" makes me feel this is an incomplete/invalid solution. 100 cols => GC collections – Guy Langston Jan 16 '20 at 12:31