○●◎ 痞客邦 ◎●○

Imports System.Data.SqlClient
Imports System.Text
Imports System.IO

'== for Excel file Export ==
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 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 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

'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 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

'== 新增試算表 Sheet名稱。
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

      Dim HSRow As HSSFRow, HSCell As HSSFCell
      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

            HSCell.CellStyle = Style_Title
      Next

      '== 利用迴圈，把資料寫入 Excel各個儲存格裡面。
      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

            k = k + 1

      End While

      SqlCmd.Cancel()
      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()

參考:

http://blog.xuite.net/sugopili/computerblog/66186028-NPOI+2.0

http://www.dotblogs.com.tw/mis2000lab/archive/2010/05/07/npoi_excel_vb_asp_net.aspx.aspx

http://www.dotblogs.com.tw/mis2000lab/archive/2010/05/24/npoi_export_excel_datareader.aspx