This project is read-only.

Editing sheet Object Not Set To Instance Error

Jun 20, 2011 at 9:31 AM

Hi

I have downloaded NPOI and I am doing a very basic example of loading in a Excel spreasheet and changing a cell.  For some reason I get a Object Not Set to an Instance Error.  I am using the below code and all I have done is gone to Excel and created a new .xls document called TestExcel and then I try and change a field.

Dim fs As New FileStream(Server.MapPath(“TestExcel.xls”), FileMode.Open, FileAccess.Read)
Dim templateWorkbook As New HSSFWorkbook(fs, True)
Dim sheet As HSSFSheet = templateWorkbook.GetSheet(“Sheet1″)
sheet.GetRow(1).GetCell(2).SetCellValue(“test”)
sheet.GetRow(1).CreateCell(1).SetCellValue(“test”)

What am i doing wrong?  Do I need to do something to the cell in Excel maybe?

Many thanks in advance

Jun 20, 2011 at 5:13 PM

You can't use a row or cell that does not exist (contains no data). Creating a sheet does not initialize a row/cell.  Change your methods from GetXXX to CreateXXX and it should solve your problem. 

Example below:

Dim fs As New FileStream(Server.MapPath("TestExcel.xls"), FileMode.Open, FileAccess.Read)
Dim templateWorkbook As New HSSFWorkbook(fs, True)
Dim sheet As HSSFSheet = templateWorkbook.GetSheet("Sheet1";)
Dim row As Row = sheet.CreateRow(1)
row.CreateCell(1).SetCellValue("Cell 1")
row.CreateCell(2).SetCellValue("Cell 2")

Jun 21, 2011 at 11:12 AM

You are a star!  That worked a treat!

Many thanks!