Use Asp.net to export data by Excel page

Source: Internet
Author: User
Reference: reference a Microsoft Excel 5.0 object through COMProgramLibrary. After referencing, the bin folder contains three files: InterOP. Excel. dll, Microsoft. VBE. InterOP. dll, and office. dll.
// ==================================== Excelhelper (apply a template to output an Excel file and paging the data) ====================
Using system;
Using system. IO;
Using system. Data;
Using system. reflection;
Using system. diagnostics;
Using CFG = system. configuration;
Using Excel;
Namespace excelhelper
{

/// <Summary>
/// Function Description: Apply a template to output an Excel file and paging the data.
/// </Summary>
Public class excelhelper
{
Protected string templetfile = NULL;
Protected string outputfile = NULL;
Protected object missing = missing. value;
///
/// constructor, you must specify the full path of the template file and output file
///
/// excel template file path
// output Excel file path
Public excelhelper (string templetfilepath, string outputfilepath)
{< br> If (templetfilepath = NULL)
throw new exception ("the Excel template file path cannot be blank! ");
If (outputfilepath = NULL)
Throw new exception ("the output Excel file path cannot be blank! ");
If (! File. exists (templetfilepath ))
Throw new exception ("the Excel template file in the specified path does not exist! ");
This. templetfile = templetfilepath;
This. outputfile = outputfilepath;
}
/**/
/// <Summary>
/// Write the datatable data to an Excel file (apply the template and pagination)
/// </Summary>
/// <Param name = "DT"> datatable </param>
/// <Param name = "rows"> Number of rows of data written to each worksheet </param>
/// <Param name = "TOP"> Excel row index </param>
/// <Param name = "Left"> column index in Excel </param>
/// <Param name = "sheetprefixname"> for example, if the prefix is "sheet, sheet-2 "</param>
Public void datatabletoexcel (system. Data. datatable DT, int rows, int top, int left, string sheetprefixname)
{
Int rowcount = DT. Rows. Count; // The number of rows in the source able.
Int colcount = DT. Columns. Count; // Number of columns in the source datatable
Int sheetcount = This. getsheetcount (rowcount, rows); // Number of Worksheet
Datetime Beforetime;
Datetime aftertime;
If (sheetprefixname = NULL | sheetprefixname. Trim () = "")
Sheetprefixname = "sheet ";
// Create an application object and make it visible
Beforetime = datetime. now;
Excel. Application APP = new excel. applicationclass ();
App. Visible = true;
Aftertime = datetime. now;
// Open the template file and get the workbook object (all 13 parameters are online, and now 14 parameters are changed)
Excel. Workbook workbook = app. workbooks. Open (templetfile, missing,
Missing, missing, and missing );
// Obtain the worksheet object
Excel. worksheet = (Excel. worksheet) Workbook. Sheets. get_item (1 );
// Copy sheetCount-1 worksheet objects
For (INT I = 1; I <sheetcount; I ++)
{
(Excel. worksheet) Workbook. worksheets. get_item (I). Copy (missing, Workbook. worksheets);
}

For (INT I = 1; I <= sheetcount; I ++)
{
Int startrow = (I-1) * Rows; // record the index of the starting row
Int endrow = I * Rows; // record end row Index
// If it is the last worksheet, the index of the end row of the record is the number of rows of the source datatable.
If (I = sheetcount)
Endrow = rowcount;
// Obtain the worksheet object to write data and rename it
Excel. worksheet sheet = (Excel. worksheet) Workbook. worksheets. get_item (I );
Sheet. Name = sheetprefixname + "-" + I. tostring ();
// Write data in DT to Worksheet
For (Int J = 0; j <endrow-startrow; j ++)
{
For (int K = 0; k <colcount; k ++)
{
Sheet. cells [top + J, left + k] = DT. Rows [startrow + J] [K]. tostring ();
}
}
//// ================================= Write the text box data (an error is commented out) ==================
// Excel. textbox txtauthor = (Excel. textbox) sheet. textboxes ("txtauthor ");
// Excel. textbox txtdate = (Excel. textbox) sheet. textboxes ("txtdate ");
// Excel. textbox txtversion = (Excel. textbox) sheet. textboxes ("txtversion ");
// Txtauthor. Text = "kly. Net blog ";
// Txtdate. Text = datetime. Now. tow.datestring ();
// Txtversion. Text = "1.0.0.0 ";

Sheet. cells [rows + 1, left + 1] = "in total: 10000 ";
Sheet. cells [rows + 1, left + 5] = "encoding: 10000 ";
}

// Output the Excel file and exit
Try
{
// (11 parameters online, 12 parameters now)
Workbook. saveas (outputfile, missing, Excel. xlsaveasaccessmode. xlexclusive, missing, and missing );
Workbook. Close (null, null, null );
App. workbooks. Close ();
App. application. Quit ();
App. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (worksheet );
System. runtime. interopservices. Marshal. releasecomobject (workbook );
System. runtime. interopservices. Marshal. releasecomobject (APP );
Worksheet = NULL;
Workbook = NULL;
APP = NULL;
GC. Collect ();
}
Catch (exception E)
{
Throw E;
}
Finally
{
Process [] myprocesses;
Datetime starttime;
Myprocesses = process. getprocessesbyname ("Excel ");
// The Excel process ID is not obtained. Currently, only the process start time can be determined.
Foreach (process myprocess in myprocesses)
{
Starttime = myprocess. starttime;
If (starttime> Beforetime & starttime <aftertime)
{
Myprocess. Kill ();
}
}
}
}

/**/
/// <Summary>
/// Obtain the number of worksheet instances
/// </Summary>
/// <Param name = "rowcount"> total number of records </param>
/// <Param name = "rows"> Number of rows per worksheet </param>
Private int getsheetcount (INT rowcount, int rows)
{
Int n = rowcount % rows; // Remainder
If (n = 0)
Return rowcount/rows;
Else
Return convert. toint32 (rowcount/rows) + 1;
}
}
}

Example:
Public partial class _ default: system. Web. UI. Page
{

Excelhelper. excelhelper Excel = NULL;
Protected void page_load (Object sender, eventargs E)
{
Excel = new excelhelper. excelhelper (server. mappath ("Tempt/bjstock.xls"), server. mappath ("excelfile/test.xls "));
}
Protected void button#click (Object sender, eventargs E)
{
Excel. datatabletoexcel (dbhelpersql. dbhelpersql. getdatatable ("select * from Excel"), 2, 1, 1, "sheet ");
Response. Redirect ("excelfile/test.xls ");
}
}

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.