NPOI Library will not insert rows/cell data when used in a loop

Question

I am using the NPOI library within a c# console application to write some information to excel. The problem i seem to be facing is that the code will not run from within a a for loop (if i take the same code out of the for loop, it works and updates the spreadsheet).

I have stepped through the code, so i know it hits the createRow, SetCellValue etc in the loop, so i know its not an issue with the loop, just doesnt seem to want to do it in the loop. funny thing is i have almost identical code (VB.Net not C#) which works?

Sample C# Code

        using (var fs = new FileStream(templateName, FileMode.Open, FileAccess.Read))
        {
            var template = new HSSFWorkbook(fs, true);
            var sheet = template.GetSheet(sheetName);
            int rowCount = 1;
            int colCount = 0;

            colCount = 0;

            NPOI.SS.UserModel.Row rowIn = null;
            NPOI.SS.UserModel.Cell cellIn = null;

            //foreach (var item in items)
            for(int i = 0; i < items.Count; i++)
            {
                rowIn = sheet.CreateRow(i);
                cellIn = rowIn.CreateCell(1);
                cellIn.SetCellValue("A");
            }

//....rest of the code to save the file 
      }

The following VB.Net code works, it is different from the c# code where i have tried to change it to see if it would work, but previously the two was just a standard port.

Using fs As New FileStream(Server.MapPath("/exporttemplates/tender-export-template.xls"), FileMode.Open, FileAccess.Read)

            Dim templateWorkbook As New HSSFWorkbook(fs, True)
            Dim sheet As HSSFSheet = templateWorkbook.GetSheet("Products")

            Dim productsBids As List(Of TenderProductBid) = TenderProductBid.GetTenderProductBids(tbid.id)
            Dim i As Integer = 1

            For Each pb As TenderProductBid In productsBids

                Dim rowIn As NPOI.SS.UserModel.Row = sheet.CreateRow(i)
                rowIn.CreateCell(0).SetCellValue(pb.product.productName)
                rowIn.CreateCell(1).SetCellValue(pb.quantity)
                rowIn.CreateCell(2).SetCellValue(pb.bid)

                i += 1
            Next

'....rest of the code to save the file 
End Using

The documentation is not clear so it is not plain to see what exactly is wrong with the code for it work outside of a loop but no in it, when the loop is working and the information is being passed?

score 0 · Answer 1 · answered Jun 14 '12 at 16:00

0

Seems there is an issue with my loop somewhere, not sure where as the breakpoints are being hit and the values are there, the library doesn't seem to like foreach for some reason.

answered Jun 14 '12 at 16:00

Modika

6,192
8
36
44

score 0 · Answer 2 · answered Jun 14 '12 at 16:22

I'm not sure what your doing in the code above, your using a variable called "items" in the c# example but I don't see it declared anywhere.

Here is an basic example on how to read a XLS file with a worksheet called "sheet1", full of values in column A.

    FileStream fs = new FileStream(templateName, FileMode.Open, FileAccess.Read);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    Sheet sheet1 = workbook.GetSheet("Sheet1");

    List<string> items = new List<string>();

    for (int i = 1; i <= sheet1.LastRowNum; i++)
    {
        string item = sheet1.GetRow(i).GetCell(0).ToString();
        items.Add(item);
    }

Make sure you properly dispose of your stream (Using or TryCatchFinally).

score 0 · Answer 3 · answered Jan 30 '13 at 05:44

0

Try to change the FileAccess mode to "FileAccess.ReadWrite"

answered Jan 30 '13 at 05:44

Ullas

93
9

NPOI Library will not insert rows/cell data when used in a loop

3 Answers3