Asp. NET database data import Excel and print (1)

asp.net|excel| Print | data | Databases are known to be difficult to print on the web, and there are probably three ways to print on the Web:

1, direct use of IE printing function. In general, this method can be extended, rather than simply calling Javascript:print (), for example, you can use the following code:

<object
Id=webbrowser CLASSID=CLSID:8856F961-340A-11D0-A96B-00C04FD705A2 height=0 width=0>
</OBJECT>
<input
Type=button value= Print ONCLICK=DOCUMENT.ALL.WEBBROWSER.EXECWB (6,1)
<input
Type=button value= Direct Printing ONCLICK=DOCUMENT.ALL.WEBBROWSER.EXECWB (6,6)
<input
Type=button value= Page Setup ONCLICK=DOCUMENT.ALL.WEBBROWSER.EXECWB (8,1)
<input
Type=button value= Print Preview ONCLICK=DOCUMENT.ALL.WEBBROWSER.EXECWB (7,1)

This method can be applied to simple data printing, the system requirements are not high, but the disadvantage is that the ability to control is poor, such as the processing of paging and so on.

2, the use of Crystal Reports or other third-party tools, such as Microsoft's reporting service. Crystal Report or other third-party control of the printing, is generally exported to excel,word,pdf and so on to print, the effect is relatively good, but the programming is more complex, control is not convenient, and these tools are to be charged.

3, the data of the database or the content to be printed to Excel,word to print. This method can be used on either the server or the client. In the use of the service side, the requirements of the server to install Word,excel, in the client use, the requirements of the client in IE security settings have certain requirements. Using this method, the adaptability is relatively strong and the control is better. This article will use Excel as an example in asp.net to explain how to export data to excel in several ways.

First, introduce the way to use Excel on the server side. To use Excel on the server side, you must require that Excel be installed on the server side and require certain access rights. For example, you need to add <identity impersonate= "true"/> to Web.config. In this article, you give the Web directory writable permissions.

Next, use Vs.net 2003 to create a new vb.net project and add a reference. Since we are using Excel, we add a COM application, where we add the Microsoft Excel Object Library, and then add the following code:

Imports System.Runtime.InteropServices.Marshal
Imports Office
Private Sub Page_Load (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles MyBase.Load
' Working with excel in COM
Dim oexcel as New excel.application
Dim obooks as Excel.Workbooks, obook as Excel.Workbook
Dim osheets as Excel.Sheets, osheet as Excel.Worksheet
Dim Ocells as Excel.Range
Dim sfile As String, stemplate as String
' Define a DataTable
Dim dt as DataTable = CType (Application.item ("mydatatable"), DataTable)

Sfile = Server.MapPath (request.applicationpath) & "\myexcel.xls"
' Define stencil files
stemplate = Server.MapPath (request.applicationpath) & "\mytemplate.xls"
oExcel.Visible = False
Oexcel.displayalerts = False
' Define a new workbook
obooks = Oexcel.workbooks
oBooks.Open (Server.MapPath (Request.applicationpath) & "\mytemplate.xls") obook = Obooks.item (1)
Osheets = Obook.worksheets
osheet = CType (Osheets.item (1), Excel.Worksheet)

' Name the sheet
Osheet.name = "Sheet"
Ocells = Osheet.cells
' Call the DumpData procedure to import data into Excel
DumpData (DT, Ocells)
' Save
Osheet.saveas (sfile)
Obook.close ()

' Exit Excel and release the invoked COM resource
oExcel.Quit ()
ReleaseComObject (Ocells): ReleaseComObject (osheet)
ReleaseComObject (osheets): ReleaseComObject (obook)
ReleaseComObject (obooks): ReleaseComObject (oexcel)
oexcel = Nothing:obooks = Nothing:obook = Nothing
Osheets = Nothing:osheet = Nothing:ocells = Nothing
System.GC.Collect ()
Response.Redirect (sfile)
End Sub

' Export the contents of a DataTable to a cell in Excel
Private Function DumpData (ByVal dt as DataTable, ByVal Ocells as Excel.Range) as String
Dim Dr as DataRow, Ary () as Object
Dim irow As Integer, Icol as Integer

' Output column headers
For icol = 0 to dt. Columns.count-1
Ocells (2, Icol + 1) = dt. Columns (Icol). Tostring
Next

' Export data to the appropriate cells
For irow = 0 to dt. Rows.count-1
Dr = dt. Rows.item (IRow)
ary = Dr. ItemArray
For icol = 0 to UBound (ary)
Ocells (IRow + 3, Icol + 1) = ary (Icol). Tostring
Response.Write (ary (Icol). ToString & VbTab)
Next
Next
End Function
End Class

In the above code, first, you define some objects about Excel, such as Application,workbook,sheets,sheet, which are essential when using Excel COM objects. After that, we first defined an Excel template file, and in Excel to open the template file, and then call a custom process DumpData. In this custom process, you import the data from the DataTable into the cells in Excel. The reader can slowly appreciate how the data in the DataTable is exported to excel in the above code. After the program is running, you can generate an Excel file named Myexcel.xls in the current working directory, as shown in the following figure: