NPOI导出所有列的excel，但最后变成空白 [英] NPOI export excel all columns but last become blank

问题描述

我正在投资NPOI Excel导出的 AutoSizeColumn().从这个问题，我知道将数据表导出到excel时，必须在调用 AutoSizeColumn()之前将所有数据写入一列.示例:(从此答案:

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

当我打开test.xls时，只有最后一列才有价值.前面所有这些列中都没有任何内容！(不过，每个列的大小已调整.)

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.)

仅供参考:1)我使用了 https://www.dotnetperls.com/中的GetTable()中的代码数据表2)我正在使用C#.

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

推荐答案

当我打开test.xls时，只有最后一列才有价值.前面所有这些列中都没有任何内容！(不过，每列的大小都已调整.)

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导出所有列的excel，但最后变成空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！