Import and print database data in ASP. NET to excel

Prepared by: Liao Yunxiao, source: Tianji net, responsible editor: Fangzhou []

As we all know, web printing is quite difficult. There are three common web printing methods:

1. directly use the print function of IE. In general, this method can be extended, rather than simply calling Javascript: Print (). For example, you can use the followingCode:

<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 = print onclick = Document. All. webbrowser. execwb (6, 6)> <Input Type = button value = page setting 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 and does not have high requirements on the system. However, the disadvantage is that it has poor control capabilities, such as paging processing.

2. Use Crystal Reports or other third-party tools, such as Microsoft's reporting service. The printing of Crystal Reports or other third-party controls is generally exported to Excel, Word, PDF and so on before printing. The effect is good, but the programming is complicated and it is not easy to control, all these tools are charged.

3. Export the database data or the content to be printed to Excel and Word for printing. This method can be used on the server or client. For use on the server, you must install Word and Excel on the server. for use on the client, you must have certain requirements on the security settings of IE on the client. This method is highly adaptable and controllable. This document uses excel in ASP. NET as an example to describe how to export data to excel.

First, we will introduce how to use Excel on the server. To use Excel on the server, you must install Excel on the server and have certain access permissions. For example, you need to add <identity impersonate = "true"/> to Web. config. In this article, you must grant the write permission to the web directory.

Next, use vs. NET 2003 to create a VB. Net project and add references. Because we want to use Excel, we need to add an application about com. Here we add the Microsoft Excel Object Library, and then add the Code as follows:

Imports system. runtime. interopservices. Marshal Imports Office Private sub page_load (byval sender as system. Object, byval e as system. eventargs) handles mybase. Load 'Use com to process Excel 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 able Dim dT as datatable = ctype (application. Item ("mydatatable"), datatable) Sfile = server. mappath (request. applicationpath) & "\ myexcel.xls" 'Define the Template File 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 this sheet. Osheet. Name = "first sheet" Ocells = osheet. Cells 'Call dumpdata and import the data to excel Dumpdata (DT, ocells) 'Save Osheet. saveas (sfile) Obook. Close () 'Exit Excel and release the called 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 datatable content to the Excel cell. 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 corresponding cell 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, some Excel objects, such as application, workbook, sheets, and sheet, are defined. When using the Excel COM object, required. Then, we first define an Excel template file, and use Excel to open the template file first, and then call a custom process dumpdata. In this custom process, the data in the datatable is imported to the cells in Excel one by one. Readers can learn how to export the data in the datatable to excel in the above Code.ProgramAfter running the job, you can create an Excel file named myexcel.xls in the current job directory, for example:

{ This. src = 'HTTP: // www.yesky.com/image20010518/218370.jpg '; } "Hspace =" 3 "src =" http://www.yesky.com/image20010518/218370.jpg "align =" center "vspace =" 1 "border =" 1 ">

you may think the above Code is complicated because it is very effective for applications with high printing requirements. To export data only, you can also use a simple format, such as using the following code:

private sub page_load (byval sender as system. object, byval e as system. eventargs) handles mybase. load dim dT as datatable = ctype (application. item ("mydatatable"), datatable) response. contenttype = "application/MS-excel" response. addheader ("content-disposition", "inline?filename=test.xls") response. write (convertdttotdf (DT) end sub private function convertdttotdf (byval dT as datatable) as string dim Dr as datarow, ary () as object, I as integer dim icol as integer 'output Column Title for icol = 0 to DT. columns. count-1 response. write (DT. columns (icol ). tostring & vbtab) next response. write (vbcrlf) 'output data for each DR in DT. rows ary = dr. itemarray for I = 0 to ubound (ary) response. write (ary (I ). tostring & vbtab) next response. write (vbcrlf) next end function end class

In the above Code, first set the browser output type to application/ms-excel, and set the exceloutput type to output in the browser. Then, call the custom process, the custom process outputs the data in a able in the form of a string stream. The data in each datatable is separated by tabs and then output to the browser. The output result is as follows:

{ This. src = 'HTTP: // www.yesky.com/image20010518/218372.jpg '; } "Hspace =" 3 "src =" http://www.yesky.com/image20010518/218372.jpg "align =" center "vspace =" 1 "border =" 1 ">

The above method is simple, but it can also meet the basic requirements of data export. What should I do if I want to further modify it? Here we provide a method to bind the data to be exported to the DataGrid and then print the DataGrid. In this case, you can format the DataGrid to be printed and set the format and other attributes of the DataGrid. The Code is as follows:

Protected overrides sub render (byval writer as system. Web. UI. htmltextwriter) Dim dT as datatable = ctype (application. Item ("mydatatable"), datatable) Response. contenttype = "application/MS-excel" Response. addheader ("content-disposition", "inline?filename=test.xls ") Datagrid1.datasource = dt Datagrid1.databind () Datagrid1.rendercontrol (writer) End sub

The printed results are as follows:

{ This. src = 'HTTP: // www.yesky.com/image20010518/218381.jpg '; } "Hspace =" 3 "src =" http://www.yesky.com/image20010518/218381.jpg "align =" center "vspace =" 1 "border =" 1 ">

If you want to print it in word, you can also use the following method. You only need to change the code:

Response. contenttype = "application/MS-word" Response. addheader ("content-disposition", "inline?filename=test.doc ")

Finally, let's take a look at how to call the client EXCEL to print. by clicking the "print" button, the customer can automatically open the client Excel and import the content to be printed. To achieve this effect, you must set the client's IE browser, in which "security-Local intranet-Custom Level ", set "Download unsigned activx" to start or prompt. The Code is as follows:

<Script language = "VBScript"> Sub exportbutton_onclick Dim shtml, oexcel, obook Shtml = Document. All. Item ("datagrid1"). outerhtml Set oexcel = Createobject ("Excel. application ") Set obook = oexcel. workbooks. Add Obook. htmlproject. htmlprojectitems ("sheet1"). Text = shtml Obook. htmlproject. refreshdocument Oexcel. Visible = true Oexcel. usercontrol = true End sub </SCRIPT>

In code-behind code, you only need to write it like this:

Dim dT as datatable = ctype (application. Item ("mydatatable"), datatable) Datagrid1.datasource = dt Datagrid1.databind ()

When running the program, you only need to click the export to excel button. In this case, the IE browser will prompt whether activx control interaction is allowed. Select "yes ", you can open the Excel file on the client to print the file.

the preceding methods are commonly used in ASP. NET to operate on Excel. They have their own advantages and disadvantages. I hope you can select them based on your actual situation.