A common DataGrid export Excel print Source functions

datagrid|excel| Printing | Export excel| function A common DataGrid export Excel print Source functions
In your spare time, write a function for new students to study



' Power By:landlordh
' Lieconme thinks Datagird's TableStyles (0) column width of one-fifth
' 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 ("No data to import!", Msgboxstyle.information, "system prompts")
Exit Function
End If

' Killexcel ()

' To add an Excel component to the reference first
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 report"
. 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 ' data column header Start row, (column header)
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
' Set column width, default to datagird column width of one-fifth
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
' Set the first column in the data area to the second row center
. 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