尝试使用 NPOI 填充 Excel 模板时出错
使用 NPOI 并尝试遵循此处的教程: http://www.zachhunter.com/2010/05/npoi-excel-模板/, 我遇到“对象引用未设置到对象实例” 此行错误:
sheet.GetRow(1).GetCell(1).SetCellValue("some test value")
尝试使用此代码时:
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
更新 我发现这种格式有效,如这篇博客文章所示 - 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
然而问题仍然悬而未决......为什么第一个示例中的代码不起作用吗? 您是否必须声明每行新数据?当你有很多时会发生什么 数据库数据行?
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 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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
在您的示例中,第一个使用
sheet.GetRow(1)
来获取工作表中的现有行。第二个使用sheet1.CreateRow(1)
,它在工作表中创建一个新行。从未使用/初始化的行不存在,并且在使用CreateRow
创建之前无法访问。为了证明这一点,您可以制作一个包含 1 行的工作表,并将一个值放入一个单元格中。您可以使用
GetRow(1)
获取该行,然后尝试对不存在的行使用GetRow(10)
。您将得到该对象不存在,因为尚未创建该行。In your examples, the first uses
sheet.GetRow(1)
that grabs an existing row in a worksheet. The second usessheet1.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 usingCreateRow
.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 useGetRow(10)
on a row that does not exist. You'll get the object does not exist, because the row can not been created yet.