NPOI SetCellValue method doesn't write data to

问题:

I have some problem with writing data to Excel worksheet cells using NPOI. Here is my code:

        FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
        hssfworkbook = new HSSFWorkbook(file);
        ISheet sheet1 = hssfworkbook.GetSheet("Табель");
        for (int i = 0; i < table.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < table.Tables[0].Columns.Count; j++)
            {
                if (table.Tables[0].Rows[i][j].ToString() != "0")
                    sheet1.GetRow(i+5).GetCell(j).SetCellValue(table.Tables[0].Rows[i][j].ToString());
                else
                    sheet1.GetRow(i+5).GetCell(j).SetCellValue("");
            }
        }
        string save_path = Server.MapPath("~/templates/report_new.xls");
        FileStream save_file = new FileStream(save_path, FileMode.Create);
        hssfworkbook.Write(save_file);
        save_file.Close();

I know that the process of opening existing worksheet works, because I have a new saved worksheet named "report_new" after this code runs. It correctly opens an existing worksheet because the newly saved file has the same template inside. But it has no data I needed. So SetCellValue method in the loop doesn't work and doesn't write data to cells. I checked that my data source represented by the DataSet named "table" isn't empty, so data exists. But it doesn't write it to cells. What can be the problem here?

回答1:

Try one of these approaches to write into the cell of excel

   var row = sheet.CreateRow(0);
   row.CreateCell(j).SetCellValue(table.Tables[0].Rows[i][j].ToString());

    // or 
    row.Cells[j].SetCellValue(table.Tables[0].Rows[i][j].ToString());


    // Or 


    ISheet sheet1 = hssfworkbook.GetSheet("Табель");
    for (int i = 0; i < table.Tables[0].Rows.Count; i++)
    {  
            HSSFRow row = (HSSFRow)sheet1.GetRow(i+5);

        for (int j = 0; j < table.Tables[0].Columns.Count; j++)
        {
    if (table.Tables[0].Rows[i][j].ToString() != "0")
        row.GetCell(j).SetCellValue(table.Tables[0].Rows[i][j].ToString());
    else
        row.GetCell(j).SetCellValue("");
        }
    }

回答2:

You can create function to SetValue(sheet, col, row, value)

here is sample

SetCellValue(sheet, cellRowIndex, cellColumnIndex, value);

private static void SetCellValue(ISheet worksheet, int rowPosition, int columnPosition, String value)
        {
            IRow dataRow = worksheet.GetRow(rowPosition) ?? worksheet.CreateRow(rowPosition);
            ICell cell = dataRow.GetCell(columnPosition) ?? dataRow.CreateCell(columnPosition);
            cell.SetCellValue(value);
        }