error attempting to use NPOI to fill excel template

Using NPOI and attempting to follow a tutorial here: http://www.zachhunter.com/2010/05/npoi-excel-template/, I'm coming across an "Object reference not set to an instance of an object" error at this line:

sheet.GetRow(1).GetCell(1).SetCellValue("some test value")

when trying to use this code:

Imports System.IO
Imports System.Web.Security
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports NPOI.SS.Util
Imports NPOI.HSSF.Util
Imports NPOI.POIFS.FileSystem
Imports NPOI.HPSF



Partial Public Class NPOI_01
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    End Sub

    Public Shared Sub ExportDataTableToExcel(ByVal memoryStream As MemoryStream, ByVal fileName As String)
        Dim response As HttpResponse = HttpContext.Current.Response
        response.ContentType = "application/vnd.ms-excel"
        response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName))
        response.Clear()

        response.BinaryWrite(memoryStream.GetBuffer())
        response.[End]()
    End Sub

    Protected Sub DownloadReport_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim fs As New FileStream(Server.MapPath("spr_files\Book1.xls"), FileMode.Open, FileAccess.Read)

        Dim templateWorkbook As New HSSFWorkbook(fs, True)

        Dim sheet As HSSFSheet = templateWorkbook.GetSheet("Sheet1")

        sheet.GetRow(1).GetCell(1).SetCellValue("some test value")

        sheet.ForceFormulaRecalculation = True

        Dim ms As New MemoryStream()

        templateWorkbook.Write(ms)

        ExportDataTableToExcel(ms, "MyBook1Report.xls")
    End Sub


End Class

UPDATE I've found this format works, as seen on this blog post - http://www.leniel.net/2009/10/npoi-with-excel-table-and-dynamic-chart.html:

    Protected Sub DownloadReport_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim fs As New FileStream(Server.MapPath("spr_files\Book1.xls"), FileMode.Open, FileAccess.Read)

    Dim templateWorkbook As New HSSFWorkbook(fs, True)

    Dim sheet1 As HSSFSheet = templateWorkbook.GetSheet("Sheet1")
    Dim row1 As HSSFRow

    row1 = sheet1.CreateRow(1)
    row1.CreateCell(1).SetCellValue("some test value")
    'sheet1.GetRow(1).CreateCell(1).SetCellValue("some test value")

    sheet1.ForceFormulaRecalculation = True

    Dim ms As New MemoryStream()

    templateWorkbook.Write(ms)

    ExportDataTableToExcel(ms, "MyBook1Report.xls")
End Sub

Yet the 开发者_Go百科question is still open...why didn't the code in the first example work? Do you have to declare every new row of data? What happens when you have lots of rows of database data?

In your examples, the first uses sheet.GetRow(1) that grabs an existing row in a worksheet. The second uses sheet1.CreateRow(1), which creates a new row in a worksheet. Rows that have never been used/initialized don't exist and can't be accessed until they are created using CreateRow.

To prove this, you can make a worksheet with 1 row, and put a value in one cell. You can use GetRow(1) to grab the row, then try to use GetRow(10) on a row that does not exist. You'll get the object does not exist, because the row can not been created yet.