How to set "AutoSize" to Excel sheet column? (NPOI)

Question

According to How can columns be set to 'autosize' in Excel documents created with NPOI? I did so:

foreach (DataColumn column in dataTable.Columns)
{
   int rowIndex = 0;
   foreach (DataRow row in dataTable.Rows)
   {
      HSSFRow dataRow = sheet.CreateRow(rowIndex);
      dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
      rowIndex++;
   }
      sheet.AutoSizeColumn(column.Ordinal);
 }

But it doesn't work. How to do right?

Yellowfog · Accepted Answer · 2013-09-26T14:19:59.313

28

Here is some code that is working for me, using your loops:

    HSSFWorkbook spreadsheet = new HSSFWorkbook();

    DataSet results = GetSalesDataFromDatabase();

    //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
    HSSFSheet sheet1 = spreadsheet.CreateSheet("Sheet1");

    foreach (DataColumn column in results.Tables[0].Columns)
    {
        int rowIndex = 0;
        foreach (DataRow row in results.Tables[0].Rows)
        {
            HSSFRow dataRow = sheet1.CreateRow(rowIndex);
            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            rowIndex++;
        }
        sheet1.AutoSizeColumn(column.Ordinal);
    }

    //Write the stream data of workbook to the file 'test.xls' in the temporary directory
    FileStream file = new FileStream(Path.Combine(Path.GetTempPath(), "test.xls") , FileMode.Create);
    spreadsheet.Write(file);
    file.Close();

If it doesn't work for you then we need to look at the kind of data you're pushing out, see if there's a difference that makes a difference there. (I'm assuming that we don't have a version discrepancy or anything like that).

edited Sep 26 '13 at 14:19

answered Jul 11 '11 at 14:37

Yellowfog

2,323
2
20
36

1

I cannot see from your answer what the problem and solution was. You added code not present in the question, but that code seems irrelevant to autosizing. – Jul 06 '17 at 16:03
Yes, that is kind of the point of the example code. Hence the words after the code sample. – Yellowfog Jul 07 '17 at 13:12
1

Specifically, what was the problem you solved? Which line of code fixed it? – Jul 07 '17 at 14:08
1

Let me summarize the above. Person posts code saying his code not working. I post code saying my code is working. Since code samples very similar I raise the question of what else might be making the difference. Person doesn't get back to me. Answer magically gets accepted and upvoted. Story ends, everybody gets back to day job. – Yellowfog Jul 17 '17 at 11:05
Ah. I don't like it. The proposed solution is accepted as answer magically. There is no value added with this solution. :( – Just a HK developer Jul 21 '17 at 04:41
1

Thank you for sharing your feelings. – Yellowfog Jul 21 '17 at 13:39

score 3 · Answer 2 · answered Aug 11 '16 at 14:06

3

Just to add an extra bit to the answer by YellowFog. I found that I had to add all data to the sheet, then iterate through the columns, setting AutoSizeColumn(idx) for this to work correctly.

answered Aug 11 '16 at 14:06

Ads

2,084
2
24
34

How to set "AutoSize" to Excel sheet column? (NPOI)

2 Answers2

Linked