Export reports in the .NET environment Excel and Word
In the VB6 development environment, I use Excel to make a report, develop in the .NET environment, I use a crystal statement. However, VB.NET also exports reports to Excel and Word to produce a professional report.
The specific operation is as follows: (Note: First need to add a reference, select COM -> Select Microsoft Word 10.0 Object Library and Microsoft Excel 10.0 Object Library Components)
1. Create a DataTable first, as a source of data, or other data source can also be another data source.
Private function creATable () AS DataTable
DIM DT As New DataTable ()
Dt.columns.Add ("Column 1", gettype (string))
Dt.columns.Add ("Column 2", GetType (Integer))
Dt.columns.add ("Columns 3", GetType (String))
Dt.columns.Add ("Column 4", gettype (string))
Dim Row, Row1 AS Datarow
Row = DT.NEWROW ()
Row! Column 1 = "Row 1"
Row! Column 2 = 1
Row! Columns 3 = "D"
Row! Column 4 = "a"
Dt.Rows.Add (Row)
Row1 = DT.NEWROW ()
Row1! Column 1 = "Row 2"
Row1! Column 2 = 12
Row1! Column 3 = "B"
Row1! Column 4 = "C"
Dt.Rows.Add (Row1)
Return DT
END FUNCTION
2. Export content in the table to Excel
DIM XLAPP AS New Excel.Application ()
DIM XLBOOK AS Excel.Workbook
DIM Xlsheet as Excel.Worksheet
DIM RowIndex, Colindex As Integer
RowIndex = 1
COLINDEX = 0
XLbook = xlapp.workbooks (). add
Xlsheet = xlbook.worksheets ("sheet1")
DIM TABLE As New DataTable ()
Table = Creatable ()
'Assign the column name of the obtained table to the cell
DIM
COL
AS Datacolumn
DIM ROW AS DATAROW
For ve
COL
In Table.Columns
COLINDEX = COLINDEX 1
XLapp.cells (1, colIndex) = columnname
NEXT
'Get the form of the table, assign the value to the cell
For Each Row in Table.Rows
RowIndex = RowIndex 1
COLINDEX = 0
For ve
COL
In Table.Columns
COLINDEX = COLINDEX 1
XLapp.cells (RowIndex, ColIndex) = row (col.columnname)
NEXT
NEXT
With xlsheet
.Range (.cells (1, 1), .cells (1, ColIndex)). Font.name = "black body" set titled black characters
.Range (.cells (1, 1), .cells (1, ColIndex)). Font.bold = true
'Title font
.Range (.cells (1, 1), .cells (RowIndex, ColIndex). Borders.LineStyle = 1
'Set the table border pattern
End with
With xlsheet.pagesetup
.Lefter = "" & chr (10) & "&" "体 _GB2312, general" "& 10 company name:" '& gsmc
"体 _ _GB2312, convention" "Company Personnel" "" Song, Routine "" & Chr (10) & "&" "_gb2312, routine" & 10 days: "
.Rightheader = "" & chr (10) & "&" "体 _GB2312, regular" "& 10 units:"
Leftfooter = "&" "_gb2312, general" & 10 players: "
.Centerfooter = "&" "体 _GB2312, regular" "& 10 Table Date:"
.Rightfooter = "&" "体 _GB2312, general" "& 10" & p page total & n pages "
End with
XLapp.visible = true
3. Export the contents in the table to Word
Dim WordApp as new word.application ()
DIM MYDOC As Word.Document
Dim Otable as Word.Table
DIM RowIndex, Colindex As Integer
RowIndex = 1
COLINDEX = 0
Wordapp.Documents.add ()
mydoc = WordApp.activeDocument
DIM TABLE As New DataTable ()
Table = Creatable ()
Otable = mydoc.tables.add (ran: = mydoc.range (start: = 0, end: = 0), NumRows: = Table.Rows.count 1, NumColumns: = Table.columns.count
'Assign the column name of the obtained table to the cell
DIM
COL
AS Datacolumn
DIM ROW AS DATAROW
For ve
COL
In Table.Columns
COLINDEX = COLINDEX 1
Otable.cell (1, colIndex) .Range.insertafter (col.columnname)
NEXT
'Get the form of the table, assign the value to the cell
For Each Row in Table.Rows
RowIndex = RowIndex 1COLINDEX = 0
For ve
COL
In Table.Columns
COLINDEX = COLINDEX 1
Otable.cell (RowIndex, ColIndex) .Range.Insertafter (Row (col.columnname))
NEXT
NEXT
Otable.Borders.InSidelinesTyle = 1
Otable.Borders.outsidelinesTyle = 1
Wordapp.visible = TRUE
to sum up:
E_mail: lihonggen0@163.com
