一個通用的Datagrid匯出Excel列印的源函數

來源:互聯網
上載者:User
datagrid|excel|列印|匯出excel|函數 一個通用的Datagrid匯出Excel列印的源函數
閑暇之餘,寫成函數,供新人研究學習



'Power by:Landlordh
'列寬預設為datagird的tablestyles(0)列寬的五分之一
'G2E(dg1)


Public Function G2E(ByVal dg As DataGrid)
Dim dt As New DataTable
Try
dt = CType(dg.DataSource, DataTable)
Catch ex As Exception
MsgBox(ex.Message)
Exit Function
End Try
Dim total_col As Integer = dt.Columns.Count
Dim total_row As Integer = dt.Rows.Count
If total_col < 1 Or total_row < 1 Then
MsgBox("沒有可供匯入的資料!", MsgBoxStyle.Information, "系統提示")
Exit Function
End If

'killEXCEL()

'要先在引用中添加EXCEL組件
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Try
GC.Collect()
xlBook = xlApp.Workbooks().Add
xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True

Try
With xlSheet.PageSetup
.RightMargin = 1
.LeftMargin = 1
.CenterHorizontally = True
.CenterHeader = "&24 報表"
.RightFooter = "&P of &N"
End With
Catch ex As Exception
MsgBox(ex.ToString)
Exit Function
End Try

Dim Col As Integer
Dim Row As Integer
Dim st_row As Integer = 5 '資料列頭開始行,(列頭)
Dim trueCol As Integer = 0
For Col = 0 To total_col - 1
If dg.TableStyles.Item(0).GridColumnStyles.Item(Col).Width > 0 Then trueCol += 1
Next

Dim TitleArray(4, 0) As Object
Dim HeaderArray(0, trueCol - 1) As Object
Dim DataArray(total_row - 1, trueCol - 1) As Object

TitleArray(0, 0) = "TO:"
TitleArray(1, 0) = "FORM:"
TitleArray(2, 0) = ""
TitleArray(3, 0) = ""
xlSheet.Range("A1").Resize(4, 1).Value = TitleArray

Dim i As Integer = 0
For Col = 0 To total_col - 1
If dg.TableStyles.Item(0).GridColumnStyles.Item(Col).Width > 0 Then
i += 1
HeaderArray(0, i - 1) = dt.Columns(Col).ColumnName
'設列寬,預設為datagird列寬的五分之一
xlSheet.Cells(st_row, i).ColumnWidth = dg.TableStyles.Item(0).GridColumnStyles.Item(Col).Width / 5
End If
Next
xlSheet.Range("A" & st_row).Resize(st_row, trueCol).Value = HeaderArray

For Row = 0 To total_row - 1
i = 0
For Col = 0 To total_col - 1
If dg.TableStyles.Item(0).GridColumnStyles.Item(Col).Width > 0 Then
i += 1
DataArray(Row, i - 1) = dt.Rows(Row).Item(Col)
End If
Next
Next
xlSheet.Range("A" & st_row + 1).Resize(total_row, trueCol).Value = DataArray

With xlSheet
.Range(.Cells(st_row, 1), .Cells(st_row, trueCol)).Font.Bold = True
.Range(.Cells(st_row, 1), .Cells(st_row, trueCol)).HorizontalAlignment = 3
.Range(.Cells(st_row, 1), .Cells(total_row + st_row, trueCol)).Borders.LineStyle = 1
'設定資料區第一列到第二列為置中
.Range(.Cells(st_row, 1), .Cells(total_row + st_row, 2)).HorizontalAlignment = 3
End With

xlApp.ActiveWorkbook.PrintPreview()
Catch ex As Exception
xlSheet = Nothing
xlApp.DisplayAlerts = False
xlBook.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoClose)
xlBook.Close()
xlBook = Nothing
xlApp.Quit()
xlApp.DisplayAlerts = True
xlApp = Nothing
GC.Collect()
MsgBox(ex.ToString)
Exit Function
End Try
xlSheet = Nothing
xlApp.DisplayAlerts = False
xlBook.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoClose)
xlBook.Close()
xlBook = Nothing
xlApp.Quit()
xlApp.DisplayAlerts = True
xlApp = Nothing
GC.Collect()
End Function




相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。