NullReferenceException accessing Workbook Cells with NPOI

Question

I am using Oracle.ManagedDataAccess. I tried to output all the values into XML and it worked. However, I am having problem now writing those values into excel file using NPOI library.

I wrote webservice that takes data from Database and writes it to excel file.

I get error when assigning value from Database table to Excel cell.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.IO;
using System.Data;
using System.Configuration;
using System.Configuration.Assemblies;
using Oracle.ManagedDataAccess;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using NPOI;
using NPOI.XSSF.UserModel;


namespace Example
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]

    public class WebService2 : System.Web.Services.WebService
    {

        [WebMethod]
        public string dictToExcel(string dict_name)
        {

            string connectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
            OracleConnection connection = new OracleConnection(connectionString);

            connection.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connection;

            cmd.CommandText = "SELECT * from TB_EXAMPLE";
            cmd.CommandType = CommandType.Text;

            OracleDataReader dataReader = cmd.ExecuteReader();

            using (FileStream stream = new FileStream(@"filepath\new.xlsx", FileMode.Create, FileAccess.Write))
            {
                XSSFWorkbook wb;
                XSSFSheet sh;
                wb = new XSSFWorkbook();
                sh = (XSSFSheet)wb.CreateSheet("Report1");

                while (dataReader.Read())
                {
                    for (int i = 0; i < 2; i++)
                    {
                        var r = sh.CreateRow(i);
                        for (int j = 0; j < 2; j++)
                        {
                            wb.GetSheet("Report1").GetRow(i).GetCell(j).SetCellValue(dataReader.GetValue(j).ToString());
                        }
                    }
                    wb.Write(stream);
                }
            }           
            return "Done!";
            connection.Close();
        }
    }
}

I get this error:

System.NullReferenceException: Object reference not set to an instance of an object.

on this part of code:

wb.GetSheet("Report1").GetRow(i).GetCell(j).SetCellValue(dataReader.GetValue(j).ToString());

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — mybirthname, Oct 23 '16 at 14:50
It looks like you have the FileStream stream setup to point to the xlsx file. However, when you create the workbook 'wb = new XSSFWorkbook();' it doesn't seem to have a FileStream associated with it. Have you tried 'wb = new XSSFWorkbook(stream);' ? Just a guess! — JohnG, Oct 23 '16 at 16:19

T.S. · Accepted Answer · 2016-10-24T16:04:22.947

Your while loop is not correct, I think. It should be something like this:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("Report1");
var rowIdex = 0;

while (dataReader.Read())
{
    var row = sh.CreateRow(rowIdex);
    for (int colIndex = 0; colIndex < dataReader.FieldCount; colIndex++)
    {
        row.CreateCell(colIndex).
            SetCellValue(dataReader[colIndex].ToString()); 
    }
    rowIdex++;
}

2 Things:

I am not familiar with NPOI.XSSF library and may be not entirely correct. I used this as reference - https://www.tutorialspoint.com/apache_poi/apache_poi_cells.htm
Again, because I don't know about NPOI.XSSF, I don't know its value in creating excel sheet. But I can tell you that for creating Excel sheets, the easiest way I know is to use microsoft.ace.oledb.xx library. It allows to work with sheet just as if it was a data table. You could use Dataset load it from Oracle and then save it to Excel it couple easy steps. Or you could go one by one using reader and then using regular SQL syntax "Insert Into..." into your sheet.

NullReferenceException accessing Workbook Cells with NPOI

1 Answers1