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 ");
}
}