How to export data to excel in ASP. NET

1. Define document type and character encoding

Response. Clear (); Response. Buffer = true; Response. charset = "UTF-8 "; // The following line is very important. The attachment parameter indicates downloading as an attachment. You can change it to online. // Filename=fileflow.xls specifies the name of the output file. Note that the extension is consistent with the specified file type. It can be. Doc. xls. txt. htm. Response. appendheader ("content-disposition", "attachment?filename=fileflow.xls "); Response. contentencoding = system. Text. encoding. getencoding ("UTF-8 "); // Response. contenttype the specified file type can be application/MS-Excel application/MS-Word application/MS-TXT application/MS-HTML or other browsers can directly support documents Response. contenttype = "application/MS-excel "; This. enableviewstate = false;

2. Define an input stream

System. Io. stringwriter ostringwriter = new system. Io. stringwriter (); System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );

3. Bind the target data to the input stream output

This. rendercontrol (ohtmltextwriter ); // This indicates that the current page is output. You can also bind the DataGrid or other controls that support the obj. rendercontrol () attribute. Response. Write (ostringwriter. tostring ()); Response. End ();

Summary:This routine is tested and passed in Microsoft Visual Studio. NET 2003. It is applicable to C # and VB. When VB is used, this keyword is changed to me.

2nd types:

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:

Related Resources:
Http://www.codeproject.com/aspnet/DGExdel.asp