藍色天空

新的生活

2009-06-10

dataSet匯製成excel

摘要:dataSet匯製成excel Sub

 

Private Sub excelFile(ByVal ds_data As Data.DataSet, ByVal filename As String)

        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook

        Try
            Dim i As Integer = 1
            xlBook = xlApp.Workbooks.Add(True)

            For Each valuetb As Data.DataTable In ds_data.Tables

                Dim osheet As Excel.Worksheet

                osheet = xlBook.Sheets.Add
                osheet.Name = valuetb.TableName          'tableName 指定成sheet Name
                osheet.Cells.NumberFormatLocal = "@"  '指定全cells為文字

                Dim rowIndex As Integer = 0
                Dim colIndex As Integer = 0

                rowIndex += 1

                '匯出欄位名稱
                For Each valuecol As Data.DataColumn In valuetb.Columns

                    colIndex += 1

                    osheet.Cells(rowIndex, colIndex) = valuecol.ColumnName
                    osheet.Cells(rowIndex, colIndex).Font.Bold = True       '粗體
                    osheet.Cells(rowIndex, colIndex).Font.ColorIndex = 5    '藍色
                
                Next

                '匯出資料列
                For Each value As Data.DataRow In valuetb.Rows
                    rowIndex += 1
                    colIndex = 0
                    For Each valuecol As Data.DataColumn In valuetb.Columns
                        colIndex += 1
                        osheet.Cells(rowIndex, colIndex) = common.FixDBNull(value(valuecol.ColumnName))

                    Next

                Next

                i += 1

            Next
            ''先判斷檔案是否存在，若存在就先刪除再存檔

            Dim filepath As String = Trim(filename)
            If System.IO.File.Exists(filepath) Then
                System.IO.File.Delete(filepath)
            End If

            xlBook.SaveAs(filepath)

            xlApp.Quit()
            xlApp.Visible = True
            xlBook = Nothing
            xlApp = Nothing

           KillProcess("EXCEL")

        Catch ex As Exception
            xlApp.Quit()
            xlApp.Visible = True
            xlBook = Nothing
            xlApp = Nothing
            GC.Collect()
            MessageBox.Show("程式發生錯誤" & ex.ToString, _
               "系統錯誤", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            KillProcess("EXCEL")
          
        End Try
    End Sub

    Private Sub KillProcess(ByVal PrcName)
        Try
            Dim proc As System.Diagnostics.Process
            For Each proc In System.Diagnostics.Process.GetProcessesByName(PrcName)
                MsgBox(proc.StartTime.ToString())
                proc.Kill()
            Next
        Catch ex As Exception

        End Try
    End Sub

各位大大可依自己的需求，改成自定的格式，小弟在此分享給大家嚕。

ps:美中不足的地方，裡面有個KillProcess 執行時會把所有已開啟的EXCEL執行緒關掉，高手們若有好的建議或修改歡迎大家的提供。