ASP. NET import datatable to excel

Source: Internet
Author: User

The print function on the page today is to print the data in the gridview. The first step is to import the data in gridv to excel and then print it. However, I find a lot of information on the Internet.

There are several errors. The most clear one is clsid {......} Error. According to the netizens, I can't do it either.

Actually putCodeCopy it to a colleague's machine. Why? On the colleague's machine, the Office 2003 is installed on the 101m M. The Office on my computer is a green version. I also installed the 10 m Office 2003, and the result will be OK.

Procedure:
Add reference to Microsoft. Office. InterOP. Excel 11.0.0.0 v1.1.4322 under. net
In the project, Microsoft. Office. InterOP. Excel. dll is displayed in the bin.

Print class

Code
  ///   <Summary>
/// Export to excel
///   </Summary>
///   <Param name = "DT"> Datatable </Param>
///   <Param name = "filepath"> The path to be exported. It can be blank. </Param>
///   <Returns> </returns>
Public   Static   String Exportexcel (system. Data. datatable DT, String Filepath) // Import data in datatable to excel
{
If (Filepath =   Null   | Filepath =   "" )
{
Filepath = Httpcontext. Current. server. mappath ( " ~ / " );
Filepath = Filepath +   " Test.xls " ;
}
Application xlapp =   New Application ();
If (Xlapp =   Null )
{
Return   " Excel is not installed on your computer! " ;
}
Workbooks workbooks = Xlapp. workbooks;

Workbook workbook = Workbooks. Add (xlwbatemplate. xlwbatworksheet );
Worksheet = (Worksheet) Workbook. worksheets [ 1 ]; // Get sheet1

// Write Field
For ( Int I =   0 ; I < DT. Columns. Count; I ++ )
{
Worksheet. cells [ 1 , I +   1 ] = DT. Columns [I]. columnname;
}
// Write Value
For ( Int R =   0 ; R < DT. Rows. Count; R ++ )
{
For ( Int I =   0 ; I < DT. Columns. Count; I ++ )
{
Worksheet. cells [R +   2 , I +   1 ] = DT. Rows [r] [I];
}
}
Worksheet. Columns. entirecolumn. autofit (); // Adaptive column width.

Workbook. Saved =   True ;
Workbook. savecopyas (filepath );

Xlapp. Quit ();
GC. Collect (); // Forcible destruction

Httpcontext. Current. response. Buffer =   True ;
Httpcontext. Current. response. Clear ();
Httpcontext. Current. response. contenttype =   " Application/MS-Excel " ;
Httpcontext. Current. response. addheader ( " Content-Disposition " , " Attachment; filename = "   + Httputility. urlencode (filepath ));
Httpcontext. Current. response. writefile (filepath );
Httpcontext. Current. response. Flush ();
Httpcontext. Current. response. End ();

Return "";
}

The subsequent response is used to open the Excel file as a download.

 

 

PS: Export excel in HTML Mode

Export excel in HTML Mode

    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 = " + Utils. urlencode (filename) + " . Xls " );

Response. contentencoding = System. Text. encoding. getencoding ( " UTF-8 " );

// Response. contenttype specifies the file type which 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 ;


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

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 ();

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.