none
VB2008 利用NPOI匯出Excel檔疑問 RRS feed

  • 問題

  • 小弟 在網路上看到可利用NPOI組件來將由資料庫所查得的資料匯出成EXCEL檔,原程式是以VC寫的小弟有試著將他寫成VB.NET 但遇到問題如下:

     Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim ms As MemoryStream = New MemoryStream()
        ms = RenderDataTableToExcel(EXTable(0))
        Response.AddHeader("Content-Disposition", String.Format("attachment; filename=Download.xls"))
        Response.BinaryWrite(ms.ToArray())
        ms.Close()
        ms.Dispose()
      End Sub
    
      Public Function RenderDataTableToExcel(ByVal SourceTable As DataTable) As Stream
    
        Dim workbook As HSSFWorkbook = New HSSFWorkbook()
        Dim ms As MemoryStream = New MemoryStream()
        Dim sheet As HSSFSheet = workbook.CreateSheet()
        Dim headerRow As HSSFRow = sheet.CreateRow(0)
        'handling header.
        For Each column As DataColumn In SourceTable.Columns
          headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
        Next
        ' handling value.
        Dim rowindex As Integer = 1
    
        For Each row As DataRow In SourceTable.Rows
          Dim datarow As HSSFRow = sheet.CreateRow(rowindex)
          For Each column As DataColumn In SourceTable.Columns
            datarow.CreateCell(1).SetCellValue(row(column).ToString)
          Next
          rowindex += rowindex
        Next
        workbook.Write(ms)
        ms.Flush()
        ms.Position = 0
        sheet = Nothing
        headerRow = Nothing
        workbook = Nothing
        Return ms
      End Function
    
    

    以上是小弟試著寫的程式片段

    但他在這邊會出現 Response 未宣告! 目前小弟還不知他的來源!

    這是小歐U大大的資料

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

    這邊也是轉成了VB 但我仍摸不著"Response"這是如何呼叫出來的,請各位大大能不吝指教


    新手上路
    2011年7月28日 上午 11:28

解答

  • Hi:

    主要錯在sheet.CreateRow時,若沒有一個暫存變數的話,之後的資料會蓋過去

    還有輸出Workbook只要做一次就好了,不須寫在迴圈裡

    試試這樣呢?

     Dim workbook As HSSFWorkbook = New HSSFWorkbook()
     Dim File As FileStream = New FileStream("C:\T1.xls", FileMode.Create)
    
    
     For i As Integer = 0 To 3
     Dim sheet As HSSFSheet = workbook.CreateSheet(Table_Name(i))
      Dim headerRow = sheet.CreateRow(0)
    
     headerRow.CreateCell(0).SetCellValue(DataGridView1.Columns(0).HeaderCell.Value.ToString)
     headerRow.CreateCell(1).SetCellValue(DataGridView1.Columns(1).HeaderCell.Value.ToString)
     headerRow.CreateCell(2).SetCellValue(DataGridView1.Columns(2).HeaderCell.Value.ToString)
     headerRow.CreateCell(3).SetCellValue(DataGridView1.Columns(3).HeaderCell.Value.ToString)
     headerRow.CreateCell(4).SetCellValue(DataGridView1.Columns(4).HeaderCell.Value.ToString)
     headerRow.CreateCell(5).SetCellValue(DataGridView1.Columns(5).HeaderCell.Value.ToString)
     headerRow.CreateCell(6).SetCellValue(DataGridView1.Columns(6).HeaderCell.Value.ToString)
     headerRow.CreateCell(7).SetCellValue(DataGridView1.Columns(6).HeaderCell.Value.ToString)
     For rowIndex = 1 To EXTable(i).Rows.Count - 1
      Dim dataRow = sheet.CreateRow(rowIndex)
     For colIndex = 0 To EXTable(i).Columns.Count - 1
      Dim Buffer As String = EXTable(i).Rows(rowIndex - 1).Item(colIndex).ToString
      dataRow.CreateCell(colIndex + 1).SetCellValue(Buffer) '這邊個人認為應該不用colIndex+1,NPOI索引都是從0開始算,您再試試看吧
     Next
    
    
    
     Next
    
    
    
     Next
    
     workbook.Write(File)
     File.Close()
    

     

     


    Shadowと愉快なコード達
    Please correct me if my concept is wrong


    • 已標示為解答 eblue 2011年8月1日 上午 02:09
    2011年7月29日 下午 12:07

所有回覆

  • Response 是 WebForm 用的。

    視窗程式請直接寫入硬碟。


    論壇是網友平等互助 保證解答請至 微軟技術支援服務
    提問時,錯誤情境描述與錯誤訊息很重要,情境描述包含你做了什麼,預期的結果與實際發生的結果。一個最爛的問法範例:「我的電腦電腦怎麼不能開機?」誰知道你家是不是沒電還是你根本找不到電源鈕。
    2011年7月28日 上午 11:34
  • 直接寫入硬碟?是類似以前的做法嗎.套用excel 8.0 的做法嗎?

     


    新手上路
    2011年7月28日 下午 12:23
  • Hi:

    在Button的Click事件

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
      Dim workbook As HSSFWorkbook = New HSSFWorkbook()
      Dim fs = New FileStream("test.xls", FileMode.Create) '準備建立一個Excel檔
       Dim sheet As HSSFSheet = workbook.CreateSheet()
      Dim row As HSSFRow = sheet.CreateRow(0)
    
      row.CreateCell(0).SetCellValue("Hello")
      row = sheet.CreateRow(1)
      row.CreateCell(0).SetCellValue("World!!")
    
      workbook.Write(fs) '把該workbook寫到檔案裡
      fs.Close()
      row = Nothing
      sheet = Nothing
      workbook = Nothing
     End Sub
    

     Sample Code

    這樣就會在程式相同路徑產生一份Excel檔


    Shadowと愉快なコード達
    Please correct me if my concept is wrong
    2011年7月28日 下午 12:33
  • 您好

    1. 資料庫直接撈成 DataSet / DataTable,匯出成 Excel,請參考此發問 DataSet,DataTable 匯出 Excel 的問題...

    2. NPOI 請參考 NPOI、OpenXML SDK、OpenOffice.org SDK 寫入資料到 EXCEL 檔案

     

     


    歡迎參觀我的Blog.NET菜鳥自救會
    • 已提議為解答 Shadow .Net 2011年8月1日 下午 12:31
    2011年7月28日 下午 01:30
    版主
  • 大大們!小弟還是轉不出來,若以NPOI的方式來製作EXCEL,這是現在的程式碼

     

    Dim workbook As HSSFWorkbook = New HSSFWorkbook()
         For i As Integer = 0 To 3
           Dim sheet As HSSFSheet = workbook.CreateSheet(Table_Name(i))
           Dim rowIndex As Integer = 1
           Dim colIndex As Integer = 0
     
          sheet.CreateRow(0).CreateCell(0).SetCellValue(DataGridView1.Columns(0).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(1).SetCellValue(DataGridView1.Columns(1).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(2).SetCellValue(DataGridView1.Columns(2).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(3).SetCellValue(DataGridView1.Columns(3).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(4).SetCellValue(DataGridView1.Columns(4).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(5).SetCellValue(DataGridView1.Columns(5).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(6).SetCellValue(DataGridView1.Columns(6).HeaderCell.Value.ToString)
           sheet.CreateRow(0).CreateCell(7).SetCellValue(DataGridView1.Columns(6).HeaderCell.Value.ToString)
           For rowIndex = 1 To EXTable(i).Rows.Count - 1
             For colIndex = 0 To EXTable(i).Columns.Count - 1
               Dim Buffer As String = EXTable(i).Rows(rowIndex - 1).Item(colIndex).ToString
               sheet.CreateRow(rowIndex).CreateCell(colIndex + 1).SetCellValue(Buffer)
             Next
           Next
           Dim File As FileStream = New FileStream("C:\T1.xls", FileMode.Create)
           workbook.Write(File)
           File.Close()
         Next
    

    這樣的確是會匯出1個含有4各sheet的EXCEL檔,但是只會出現最後1個欄位的資料

    若報表共6各欄位有10筆資料,顯示出來的僅出現第6個欄位的資料而已!

    請問大大們是小弟哪裡弄錯了?


    新手上路
    2011年7月29日 上午 11:35
  • Hi:

    主要錯在sheet.CreateRow時,若沒有一個暫存變數的話,之後的資料會蓋過去

    還有輸出Workbook只要做一次就好了,不須寫在迴圈裡

    試試這樣呢?

     Dim workbook As HSSFWorkbook = New HSSFWorkbook()
     Dim File As FileStream = New FileStream("C:\T1.xls", FileMode.Create)
    
    
     For i As Integer = 0 To 3
     Dim sheet As HSSFSheet = workbook.CreateSheet(Table_Name(i))
      Dim headerRow = sheet.CreateRow(0)
    
     headerRow.CreateCell(0).SetCellValue(DataGridView1.Columns(0).HeaderCell.Value.ToString)
     headerRow.CreateCell(1).SetCellValue(DataGridView1.Columns(1).HeaderCell.Value.ToString)
     headerRow.CreateCell(2).SetCellValue(DataGridView1.Columns(2).HeaderCell.Value.ToString)
     headerRow.CreateCell(3).SetCellValue(DataGridView1.Columns(3).HeaderCell.Value.ToString)
     headerRow.CreateCell(4).SetCellValue(DataGridView1.Columns(4).HeaderCell.Value.ToString)
     headerRow.CreateCell(5).SetCellValue(DataGridView1.Columns(5).HeaderCell.Value.ToString)
     headerRow.CreateCell(6).SetCellValue(DataGridView1.Columns(6).HeaderCell.Value.ToString)
     headerRow.CreateCell(7).SetCellValue(DataGridView1.Columns(6).HeaderCell.Value.ToString)
     For rowIndex = 1 To EXTable(i).Rows.Count - 1
      Dim dataRow = sheet.CreateRow(rowIndex)
     For colIndex = 0 To EXTable(i).Columns.Count - 1
      Dim Buffer As String = EXTable(i).Rows(rowIndex - 1).Item(colIndex).ToString
      dataRow.CreateCell(colIndex + 1).SetCellValue(Buffer) '這邊個人認為應該不用colIndex+1,NPOI索引都是從0開始算,您再試試看吧
     Next
    
    
    
     Next
    
    
    
     Next
    
     workbook.Write(File)
     File.Close()
    

     

     


    Shadowと愉快なコード達
    Please correct me if my concept is wrong


    • 已標示為解答 eblue 2011年8月1日 上午 02:09
    2011年7月29日 下午 12:07
  • 感謝大大的提醒 ,原來要利用DataRow暫存!順帶一問!

    要如何設定欄位的超連結?可以在匯出時就設計好嗎?譬如Cell(1,0)=Sheet1, Cell(2,0)=Sheet2的方式!但似乎不是利用 "Hyperlinks"這個屬性!

     

    超連結做好囉!程式碼如下:

    Private Sub ExportScoretoXls()
        Dim workbook As HSSFWorkbook = New HSSFWorkbook()
        Dim File As FileStream = New FileStream("C:\T1.xls", FileMode.Create)
        Dim HILINK_Style As HSSFCellStyle = workbook.CreateCellStyle()
        For i As Integer = 0 To 3
          Dim sheet As HSSFSheet = workbook.CreateSheet(Table_Name(i))
          Dim HILINK As HSSFHyperlink = New HSSFHyperlink(SS.UserModel.HyperlinkType.DOCUMENT)
          Dim str As String
          If i < 3 Then
            str = Table_Name(i)
          Else
            str = Table_Name(0)
          End If
          HILINK.Address = str & "!A1"
          Dim headerRow = sheet.CreateRow(0)
          For coiIndex = 0 To EXTable(i).Columns.Count - 1
            Dim myrow = sheet.CreateRow(i + 1)
            headerRow.CreateCell(coiIndex).SetCellValue(DataGridView1.Columns(coiIndex).HeaderCell.Value.ToString)
          Next
          For rowIndex = 1 To EXTable(i).Rows.Count - 1
            Dim dataRow = sheet.CreateRow(rowIndex)
            Dim dataRow2 = sheet.CreateRow(rowIndex)
            dataRow.CreateCell(0).Hyperlink = (HILINK)
            dataRow2.CreateCell(0).CellStyle = (HILINK_Style)
            For colIndex = 1 To EXTable(i).Columns.Count - 1
              Dim Buffer As String = EXTable(i).Rows(rowIndex - 1).Item(colIndex).ToString
              dataRow.CreateCell(colIndex + 1).SetCellValue(Buffer)
            Next
          Next
        Next
        workbook.Write(File)
        File.Close()
    
    
      End Sub

    這樣會連結至每個Sheet 的 A1欄位,但目前的瓶頸在於 超連結的設定欄位 一樣 只會出現在最後一筆!而不是每個Cell(0)的位置,

    請教各位大大是否有小弟沒注意到的地方呢?

     


    新手上路

    2011年7月29日 下午 12:39
  • Hi:

    有沒有試過把

    CreateCell的函數結果再另外存成一個變數試試看

    例如:

    dim dataCell = dataRow.CreateCell(0)

    dataCell.Hyperlink = (HILINK)

    (想說會不會是一樣的問題...)


    Shadowと愉快なコード達
    Please correct me if my concept is wrong


    2011年8月1日 上午 02:28
  • 謝謝 大大您的回覆!改成您的做法後 超連結仍然只會出現在最後一筆!我try其他方法看看!


    新手上路

    試出來了!

    將這個迴圈程式碼改成這樣

     For rowIndex = 1 To EXTable(i).Rows.Count
            Dim HILINK As HSSFHyperlink = New HSSFHyperlink(SS.UserModel.HyperlinkType.DOCUMENT)
            HILINK.Address = str & "!A1"
            Dim dataRow = sheet.CreateRow(rowIndex)
            Dim dataRow2 = sheet.CreateRow(rowIndex)
            Dim DataCell = dataRow.CreateCell(0)
            DataCell.Hyperlink = HILINK
            DataCell.CellStyle = (HILINK_Style)
            DataCell.SetCellValue(rowIndex)
            For colIndex = 1 To EXTable(i).Columns.Count - 1
              Dim Buffer As String = EXTable(i).Rows(rowIndex - 1).Item(colIndex).ToString
              dataRow.CreateCell(colIndex).SetCellValue(Buffer)
            Next
          Next

    這樣就可以達到 A欄的每個CELL 均可設成超連結!

    2011年8月1日 上午 03:25