Private Sub Button16_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button16.Click
Try
Dim OutFile As New SaveFileDialog
OutFile.Filter = "Microsoft Excel 活頁簿 (*.xls)|*.xls"
OutFile.RestoreDirectory = True
If OutFile.ShowDialog() = DialogResult.OK Then
If ExportDT2Excel(Me.dtReport, OutFile.FileName) = True Then
MsgBox("轉出Excel成功！", MsgBoxStyle.Information)
Else
MsgBox("轉出到Excel錯誤，請洽系統工程師！", MsgBoxStyle.Critical)
End If
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Function ExportDT2Excel(ByVal dt As DataTable, ByVal fileName As String) As Boolean
Try
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
GC.Collect()
oXL = New Excel.Application
oXL.Visible = False
oWB = CType(oXL.Workbooks.Add(System.Reflection.Missing.Value), Excel.Workbook)
oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
For j As Int16 = 0 To dt.Columns.Count - 1
oSheet.Cells(1, j + 1) = dt.Columns(j).ColumnName
Next
For i As Int16 = 0 To dt.Rows.Count - 1
For j As Int16 = 0 To dt.Columns.Count - 1
oSheet.Cells(i + 2, j + 1) = dt.Rows(i)(j).ToString
Next
Next
oXL.Visible = False
oXL.UserControl = False
oWB.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, Nothing, Nothing, False, False, Excel.XlSaveAsAccessMode.xlShared, False, False, Nothing, Nothing, Nothing)
oWB.Close(Nothing, Nothing, Nothing)
oXL.Workbooks.Close()
oXL.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
oSheet = Nothing
oWB = Nothing
oXL = Nothing
GC.Collect()
Return True
Catch ex As Exception
MsgBox(ex.ToString)
Return False
End Try
End Function
沒有留言:
張貼留言