21

NPOI is a .NET port of the Java POI project, which allows one to read and write Microsoft Excel documents (as well as other Office formats). NPOI 1.2.2 has introduced support for 'autosizing' columns, whereby the column is set to the width of the widest cell entry in the column. However, there are many reports that this does not work. So is it possible?

Yellowfog
  • 2,323
  • 2
  • 20
  • 36
  • In my experience the `mySheet.AutoSizeColumn(columnIndex);` type use does work but not in all cases. If you have column formatted as table with Filter on, the AutoSize you do within Excel by double-clicking does width while compensating for Filter on pull-down icon. The code version ignores the Filter icon. It also doesn't work well on the last column with data or columns wider than a certain maximum width it AutoSizes to. – cusman Mar 16 '13 at 15:27

2 Answers2

38

I've posted this just to answer it, so as to provide a record. It is possible to make columns autosized using NPOI, but you have to add all the data in columns, rather than in rows. Once all the cells have been added to a column @ columnIndex you then call 

mySheet.AutoSizeColumn(columnIndex)

and move to the next column. I have found no other way to make this functionality work.

Yellowfog
  • 2,323
  • 2
  • 20
  • 36
  • 2
    I didn't add the data "in columns" as you suggested, but the method `AutoSizeColumn(columnIndex)` worked perfectly for me regardless. – JohnB Jul 09 '11 at 00:11
  • I think it must have been a bug that was fixed in a later version because I add all my data as rows and used the above method fine. – Alexander Van Atta Nov 14 '12 at 19:58
  • Sorry doesn't work here. Say just one column. Add the header row. Specify style. Add heading text. Create one more row. Specify style. Add data (a long number). Specify the `AutoSizeColumn()`. Excel file generated. Open it. The number has the 'E' in it - meaning the column is not wide enough. – Just a HK developer Jul 21 '17 at 06:44
  • Is that something specific to numbers? Ie. is it autosizing to scientific notation for long numbers? Do you get the same result if you add a long string instead of a long number? – Yellowfog Jul 21 '17 at 13:36
  • 1) I try add column first. (That is create row when entering each column.) This will make all columns but last become blank. When I add row and then add column (and auto size), things working nice. https://stackoverflow.com/questions/45231829/npoi-export-excel-all-columns-but-last-become-blank/45234717#45234717 – Just a HK developer Jul 25 '17 at 02:48
  • 2) Now I specify the size to be 12 (keeping 'Arial' as font), the auto size does not work perfectly. Width of some columns is just 1 or 2 characters short. One column with a row having long string gets truncated. NPOI version: 1.2.5.0. Excel produced is XLS (HSSF). Using C#. I think NPOI **cannot** take the font size into consideration when auto sizing. What is your option? – Just a HK developer Jul 25 '17 at 02:54
6

As already indicated by Yellowfog the following will work

mySheet.AutoSizeColumn(columnIndex)

However some abiguity appears in the posts. It seems to be a method that only works after you have completed entering data, styles etc. So that this will work

ISheet mySheet = hssfworkbook.CreateSheet("sheet1");
IRow row = mySheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("foo");
mySheet.AutoSizeColumn(0);

but the following will have no effect (because it has no information to auto size against)

ISheet mySheet = hssfworkbook.CreateSheet("sheet1");
mySheet.AutoSizeColumn(0);
IRow row = mySheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("foo");
user2338169
  • 61
  • 1
  • 1