Use a template to export an Excel file [original]

C #-Doce

 

Using system;
Using system. Data;
Using system. diagnostics;
Using system. IO;
Using system. reflection;

Public class excelhelper
{
Protected string templetfile = NULL;
Protected string outputfile = NULL;
Protected object missing = missing. value;

/// <Summary>
/// Constructor. You must specify the complete path of the template file and output file.
/// </Summary>
/// <Param name = "templetfilepath"> excel template file path </param>
/// <Param name = "outputfilepath"> output Excel file path </param>
Public excelhelper (string templetfilepath, string outputfilepath)
{
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"> dataset set, where the number of tables must be equal to the sheetname array </param>
/// <Param name = "rows"> Number of rows of data written to each worksheet </param>
/// <Param name = "TOP"> row index </param>
/// <Param name = "Left"> column index </param>
/// <Param name = "isrrwritecolumn"> whether to overwrite the column name </param>
/// <Param name = "sheetname"> worksheet name, which must correspond to the number of datatables in the dataset set </param>
Public void datatabletoexcel (Dataset ds, int rows, int top, int left, bool isrrwritecolumn, Params string [] sheetname)
{
Int tablecount = Ds. Tables. count;
If (tablecount! = Sheetname. length)
{
Throw new exception ("the number of datatable and worksheet do not match ");
}
Datetime Beforetime;
Datetime aftertime;

// Create an Application Object
Beforetime = datetime. now;
Excel. Application APP = new excel. applicationclass ();
App. Visible = false;
Aftertime = datetime. now;

// Open the Template File
Excel. Workbook workbook = app. workbooks. _ open (templetfile, missing,
Missing, missing, and missing );

Excel. worksheet sheet = NULL;
For (INT I = 1; I <tablecount; I ++)
{
(Excel. worksheet) Workbook. worksheets. get_item (I). Copy (missing, Workbook. worksheets [I]);
}

# Region writes the source datatable data to excel
Datatable dt = NULL;
For (INT I = 1; I <= tablecount; I ++)
{
Dt = Ds. Tables [I-1];
Int irows = DT. Rows. Count, icols = DT. Columns. count;
// Obtain the worksheet object to write data and rename it
Sheet = (Excel. worksheet) Workbook. worksheets. get_item (I );
Sheet. Name = sheetname [I-1];
If (isrrwritecolumn)
{
For (int K = 0; k <icols; k ++)
{
Sheet. cells [1, left + k] = DT. Columns [K]. columnname;
}
}

Object [,] dataarray = new object [irows, icols];
For (Int J = 0; j <irows; j ++)
{
For (int K = 0; k <icols; k ++)
{
Dataarray [j, k] = DT. Rows [J] [k];
}
}
// Write data in DT to Worksheet
Sheet. get_range (sheet. cells [top, left], sheet. cells [irows, icols]). value2 = dataarray;
}
# Endregion

# region output the Excel file and exit
try
{
workbook. savecopyas (outputfile);
// workbook. saveas (outputfile, missing, Excel. xlsaveasaccessmode. xlexclusive,
// missing, and missing);
workbook. close (false, null, null);
app. workbooks. close ();
app. application. quit ();
app. quit ();

System. runtime. interopservices. Marshal. releasecomobject (sheet );
System. runtime. interopservices. Marshal. releasecomobject (workbook );
System. runtime. interopservices. Marshal. releasecomobject (APP );

Sheet = NULL;
Workbook = NULL;
APP = NULL;

GC. collect ();
}< br> catch (exception e)
{< br> // throw E;
}< br> finally
{< br> process [] myprocesses;
datetime starttime;
myprocesses = process. getprocessesbyname ("Excel");

// The Excel process ID is not obtained. Currently, only the start time of the process can be determined.
foreach (process myprocess in myprocesses)
{< br> starttime = myprocess. starttime;

If (starttime> Beforetime & starttime <aftertime)
{
Myprocess. Kill ();
}
}
}
# Endregion
}
}

PartCodeReference: http://www.cnblogs.com/lingyun_k/archive/2005/07/12/191740.aspx