NPOI 產生Excel檔
http://tankhylin.pixnet.net/blog/post/164676363-%5Basp.net%5D-npoi-%E7%94%A2%E7%94%9Fexcel%E6%AA%94
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
Imports System.Text
Imports System.IO
'== for Excel file Export ==
Imports NPOI.HSSF.UserModel
Imports NPOI.HPSF
Imports NPOI.POIFS.FileSystem
Imports NPOI.HSSF.UserModel
Imports NPOI.HPSF
Imports NPOI.POIFS.FileSystem
Dim strSql As New StringBuilder, SqlCmd As SqlCommand, dr As SqlDataReader
Dim workbook As HSSFWorkbook = New HSSFWorkbook()
Dim ms As MemoryStream = New MemoryStream() '==需要 System.IO命名空間
Dim ms As MemoryStream = New MemoryStream() '==需要 System.IO命名空間
'設定檔案摘要(原始檔案部份)
Dim dsi As NPOI.HPSF.DocumentSummaryInformation = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation()
dsi.Company = "XXX公司"
workbook.DocumentSummaryInformation = dsi
'設定檔案摘要(說明部份)
Dim si As NPOI.HPSF.SummaryInformation = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation()
si.Subject = "XXX報表"
workbook.SummaryInformation = si
Dim dsi As NPOI.HPSF.DocumentSummaryInformation = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation()
dsi.Company = "XXX公司"
workbook.DocumentSummaryInformation = dsi
'設定檔案摘要(說明部份)
Dim si As NPOI.HPSF.SummaryInformation = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation()
si.Subject = "XXX報表"
workbook.SummaryInformation = si
'TITLE STYLE ----
Dim Font_Title As HSSFFont = workbook.CreateFont()
With Font_Title
.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index
.FontName = HSSFFont.FONT_ARIAL
.FontHeightInPoints = 10
.Boldweight = NPOI.SS.UserModel.FontBoldWeight.BOLD
End With
Dim Font_Title As HSSFFont = workbook.CreateFont()
With Font_Title
.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index
.FontName = HSSFFont.FONT_ARIAL
.FontHeightInPoints = 10
.Boldweight = NPOI.SS.UserModel.FontBoldWeight.BOLD
End With
Dim Style_Title As HSSFCellStyle = workbook.CreateCellStyle()
With Style_Title
'儲存格網底顏色
.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index
.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND
'儲存格框線
.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN
.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN
.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN
.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN
.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.SetFont(Font_Title)
End With
With Style_Title
'儲存格網底顏色
.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index
.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND
'儲存格框線
.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN
.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN
.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN
.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN
.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.SetFont(Font_Title)
End With
'BODY STYLE ----
Dim Font_Body As HSSFFont = workbook.CreateFont()
With Font_Body
.Color = NPOI.HSSF.Util.HSSFColor.BLACK.index
.FontName = HSSFFont.FONT_ARIAL
.FontHeightInPoints = 10
End With
Dim Font_Body As HSSFFont = workbook.CreateFont()
With Font_Body
.Color = NPOI.HSSF.Util.HSSFColor.BLACK.index
.FontName = HSSFFont.FONT_ARIAL
.FontHeightInPoints = 10
End With
Dim Style_Body As HSSFCellStyle = workbook.CreateCellStyle()
With Style_Body
'儲存格網底顏色
.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index
.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND
'儲存格框線
.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN
.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN
.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN
.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN
.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.SetFont(Font_Body)
End With
With Style_Body
'儲存格網底顏色
.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index
.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND
'儲存格框線
.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN
.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN
.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN
.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN
.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index
.SetFont(Font_Body)
End With
'== 新增試算表 Sheet名稱。
Dim u_sheet1 As HSSFSheet = workbook.CreateSheet("表1")
Using cnSql As New SqlConnection(gstrConnDB)
cnSql.Open()
Dim u_sheet1 As HSSFSheet = workbook.CreateSheet("表1")
Using cnSql As New SqlConnection(gstrConnDB)
cnSql.Open()
strSql.Length = 0
strSql.AppendLine("Select distinct DATE,A,B,C ")
strSql.AppendLine("From TABLE ")
strSql.AppendLine("Where Substring(Deliver_Dt, 1, 6) = '" & gstrQryDate & "' ")
strSql.AppendLine("Order by Deliver_Dt")
SqlCmd = New SqlCommand(strSql.ToString, cnSql)
dr = SqlCmd.ExecuteReader
strSql.AppendLine("Select distinct DATE,A,B,C ")
strSql.AppendLine("From TABLE ")
strSql.AppendLine("Where Substring(Deliver_Dt, 1, 6) = '" & gstrQryDate & "' ")
strSql.AppendLine("Order by Deliver_Dt")
SqlCmd = New SqlCommand(strSql.ToString, cnSql)
dr = SqlCmd.ExecuteReader
Dim HSRow As HSSFRow, HSCell As HSSFCell
HSRow = u_sheet1.CreateRow(0)
For i As Integer = 0 To 21
HSCell = HSRow.CreateCell(i)
HSRow = u_sheet1.CreateRow(0)
For i As Integer = 0 To 21
HSCell = HSRow.CreateCell(i)
Select Case i
Case "0"
HSCell.SetCellValue("日期")
Case "1"
HSCell.SetCellValue("AAA")
Case "2"
HSCell.SetCellValue("BBB")
Case "3"
HSCell.SetCellValue("CCC")
End Select
Case "0"
HSCell.SetCellValue("日期")
Case "1"
HSCell.SetCellValue("AAA")
Case "2"
HSCell.SetCellValue("BBB")
Case "3"
HSCell.SetCellValue("CCC")
End Select
HSCell.CellStyle = Style_Title
Next
Next
'== 利用迴圈，把資料寫入 Excel各個儲存格裡面。
Dim k As Integer = 1
While dr.Read()
Dim k As Integer = 1
While dr.Read()
HSRow = u_sheet1.CreateRow(k)
For i = 0 To (dr.FieldCount - 1)
HSCell = HSRow.CreateCell(i)
HSCell.SetCellValue(dr.GetValue(i).ToString())
HSCell.CellStyle = Style_Body
Next
For i = 0 To (dr.FieldCount - 1)
HSCell = HSRow.CreateCell(i)
HSCell.SetCellValue(dr.GetValue(i).ToString())
HSCell.CellStyle = Style_Body
Next
k = k + 1
End While
SqlCmd.Cancel()
dr.Close()
dr.Close()
End Using
workbook.Write(ms)
ms.Flush()
ms.Position = 0
Dim myFile As System.IO.FileStream = System.IO.File.Open("D:\BOOK_" & gstrQryDate & ".xls", IO.FileMode.OpenOrCreate)
ms.WriteTo(myFile)
ms.Close()
ms.Dispose()
ms.Flush()
ms.Position = 0
Dim myFile As System.IO.FileStream = System.IO.File.Open("D:\BOOK_" & gstrQryDate & ".xls", IO.FileMode.OpenOrCreate)
ms.WriteTo(myFile)
ms.Close()
ms.Dispose()
參考:
沒有留言:
張貼留言