NPOI export excel all columns but last become blank

Question

I am investing the AutoSizeColumn() of NPOI Excel exporting. From this SO question, I know that when exporting a DataTable to excel, have to write all data inside one column before calling the AutoSizeColumn(). Example: (as from this SO answer:

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();

When I open the test.xls, only the last column has value. All those previous columns does not have anything in it! (The size of each column is adjusted however.)

FYI: 1) I use the code in GetTable() from https://www.dotnetperls.com/datatable 2) I am using C#.

score 1 · Accepted Answer · answered Jul 21 '17 at 09:59

When I open the test.xls, only the last column has value. All those previous columns does not have anything in it! (The size of each column is adjusted however.)

That's because you're looping through the column and (re)creating ALL the rows on each iteration. This row (re)creation loop effectively overwrites the old rows (with their previous cell value set) with a blank one. Thus all columns but last become blank.

Try switching the loop order so that rows are iterated first then columns:

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");

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

for(var i = 0; i< results.Tables[0].Columns.Count; i++)
{
  sheet1.AutoSizeColumn(i);
}

//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();

NPOI export excel all columns but last become blank

1 Answers1

Linked