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

Answer 1

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.