Export (Oracle) database tables to excel and generate files (C # implementation)

Export (Oracle) database tables to excel and generate files (C # implementation)

Keywords: C #, ASP. NET, Excel

Add a project reference:

1.. Net-> system. Data. oracleclient. dll

2. com-> Microsoft Excel 11.0 Object Library

CodeAs follows:

Using system;
Using system. IO;
Using system. Data;
Using system. reflection;
Using system. diagnostics;
Using system. configuration;
Using system. collections;
Using Excel;

Namespace thscjy
{
///

/// Apply the template to output the Excel file and generate the xls file and HTML file
/// Author: Liu Wen
/// Date created: 2006-8
///
Public class exportexcel
{
# Region variable Member member variables
Protected string templatefile = NULL;
Protected string excelfile = NULL;
Protected string htmlfile = NULL;
Protected object missing = missing. value;
Excel. applicationclass app;
Excel. Workbook book;
Excel. worksheet sheet;
Excel. Range range;
Private datetime Beforetime; // time before Excel start
Private datetime aftertime; // time after Excel startup
// Private int processid;
# Endregion

///

/// Constructor. Use an existing Excel Workbook as a template and specify the output path.
///
/// Excel template file path

/// Excel output file path

/// HTML output file path

Public exportexcel (string templatefile, string excelfile, string htmlfile)
{
If (templatefile = NULL)
Throw new exception ("the Excel template file path cannot be blank! ");

If (excelfile = NULL)
Throw new exception ("the Excel output file path cannot be blank! ");

If (htmlfile = NULL)
Throw new exception ("the HTML output file path cannot be blank! ");

If (! File. exists (templatefile ))
Throw new exception ("specified Path The Excel template file of does not exist! ");

This. templatefile = templatefile;
This. excelfile = excelfile;
This.html file = htmlfile;

// Create an Application Object
Beforetime = datetime. now;
APP = new applicationclass ();
// App. Visible = true;
// Processid = process. getcurrentprocess (). ID;
Aftertime = datetime. now;

// Open Template File to obtain the workbook object
Try
{
Book = app. workbooks. Open (templatefile, missing,
Missing, missing, and missing );
}
Catch (exception E)
{
Throw E;
}
// Obtain the worksheet object
Sheet = (Excel. worksheet) book. Sheets. get_item (1 );
}

# Region insert report Parameters
///

/// Input report parameters (for TJ statistics)
///
/// Reporting Unit

/// Year and month

/// Reporter

/// Filling date

/// Insert a data cell

Public void insertargs (string department, string date, string accountant, string datecreated, string cellid)
{
Sheet. get_range ("A3", missing). value2 = "Reporting Unit:" + department;
Sheet. get_range ("D3", missing). value2 = date;
Sheet. get_range (cellid, missing). value2 = "department director: Reporter:" + accountant + "contact number: submission time:" + datecreated;
// Sheet. get_range ("i8", missing). value2 = "filling date:" + datecreated;
}

///

/// Input report parameters (For Scheduler Planning)
///
/// Title

Public void insertargsjh (string name)
{
Sheet. get_range ("A1", missing). value2 = Name;
}

///

/// Input report parameters (For Scheduler Planning)
///
/// Title

/// Year

Public void insertargsjh (string name, string year)
{
Sheet. get_range ("A1", missing). value2 = Name;
Sheet. get_range ("D2", missing). value2 = year;
}

///

/// Input report parameters (For Scheduler Planning)
///
/// Title

/// Department

/// "Department" Cell ID

/// Year

/// "Year" Cell ID

Public void insertargsjh (string name, string department, string depcellid, string year, string yearcellid)
{
Sheet. get_range ("A1", missing). value2 = Name;
Sheet. get_range (depcellid, missing). value2 = Department;
Sheet. get_range (yearcellid, missing). value2 = year;
}

///

/// Input Reports (Scheduler)
///
/// Title

/// Water Injection

/// Gas Injection

/// Measure workload

Public void insertargsjh (string name, string water, string gas, string workload)
{
Sheet. get_range ("A1", missing). value2 = Name;
Sheet. get_range ("C2", missing). value2 = water;
Sheet. get_range ("E2", missing). value2 = gas;
Sheet. get_range ("G2", missing). value2 = workload;
}
# Endregion

# Region Excel export Method

///

// export the datatable data to excel (rows can be dynamically inserted)
/// datatable
// index of the inserted row
/// index of the inserted column
BR>
Public void datatabletoexcel (system. data. datatable DT, int rowindex, int colindex)
{< br> int rowcount = DT. rows. count; // Number of datatable rows
int colcount = DT. columns. count; // Number of datatable columns
int irow;
int icol;

// Export data to the corresponding cell
For (irow = 0; irow <rowcount; irow ++)
{
// Insert a new row
This. insertrows (sheet, irow + rowindex );
// Fill the current row
For (icol = 0; icol <colcount; icol ++)
{
Sheet. cells [irow + rowindex, icol + colindex] = DT. Rows [irow] [icol]. tostring ();
}
}
This. deleterows (sheet, rowcount + rowindex );

// This. outputfile ();

// Excel. querytables QTS = sheet. querytables;
// Excel. querytable Qt = QTS. Add (,,);
// QT. refreshstyle = excel. xlcellinsertionmode. xlinsertentirerows;
// QT. Refresh ();
}

///

/// Export datatable data to excel (rows can be dynamically inserted)
///
/// Datatable

/// Start cell of the inserted data

Public void datatabletoexcel (system. Data. datatable DT, string cellid)
{
Int rowindex = sheet. get_range (cellid, missing). row;
Int colindex = sheet. get_range (cellid, missing). column;
Int rowcount = DT. Rows. Count; // Number of datatable rows
Int colcount = DT. Columns. Count; // Number of datatable Columns
Int irow;
Int icol;

// Batch write using two-dimensional array
String [,] array = new string [rowcount, colcount];
For (irow = 0; irow <rowcount; irow ++)
{
For (icol = 0; icol <colcount; icol ++)
{
Array [irow, icol] = DT. Rows [irow] [icol]. tostring ();
}
}

For (irow = 0; irow <rowcount; irow ++)
{
This. insertrows (sheet, irow + rowindex );
}
This. deleterows (sheet, rowcount + rowindex );

Range = sheet. get_range (cellid, missing );
Range = range. get_resize (rowcount, colcount );
Range. value2 = array;
}

///

/// Export the datatable data to excel (fixed)
///
/// Datatable

/// Start cell of the inserted data

Public void datatabletoexcel2 (system. Data. datatable DT, string cellid)
{
Int rowcount = DT. Rows. Count; // Number of datatable rows
Int colcount = DT. Columns. Count; // Number of datatable Columns
Int irow;
Int icol;

// Batch write using two-dimensional array
String [,] array = new string [rowcount, colcount];
For (irow = 0; irow <rowcount; irow ++)
{
For (icol = 0; icol <colcount; icol ++)
{
Array [irow, icol] = DT. Rows [irow] [icol]. tostring ();
}
}

Range = sheet. get_range (cellid, missing );
Range = range. get_resize (rowcount, colcount );
Range. value2 = array;
}
# Endregion

///

/// Final call, Release related resources, and complete
///
Public void finalize ()
{
This. outputfile ();
GC. Collect ();
// This. killexcelprocess ();
}

///

// output the generated Excel and HTML File
//
private void outputfile ()
{< br> // if the file already exists, delete it, regenerate
If (file. exists (excelfile)
{< br> file. delete (excelfile);
}< br> If (file. exists (htmlfile)
{< br> file. delete (htmlfile);
}< br> try
{< br> book. saveas (excelfile, missing,
excel. xlsaveasaccessmode. xlexclusive, missing, and missing);

Book. saveas (htmlfile, Excel. xlfileformat. xlhtml, missing,
Excel. xlsaveasaccessmode. xlnochange, missing, missing );
}
Catch (exception E)
{
Throw E;
}
Finally
{
This. Dispose ();
}
}

///

/// Insert rows in the worksheet and adjust other rows to leave space
///
/// Current Worksheet

/// Index of the row to be inserted

Private void insertrows (Excel. worksheet sheet, int rowindex)
{
Range = (Excel. Range) sheet. Rows [rowindex, missing];
// Object range. insert (Object shift, object copyorigin );
// Shift: variant type. Optional. Specify Unit Format. It can be one of the following xlinsertshiftdirection constants:
// Xlshifttoright or xlshiftdown. If this parameter is omitted, Microsoft Excel determines the adjustment mode based on the region shape.
Range. insert (Excel. xlinsertshiftdirection. xlshiftdown, missing );
}

///

/// Delete a row from the worksheet
///
/// Current Worksheet

/// The row index to be deleted

Private void deleterows (Excel. worksheet sheet, int rowindex)
{
Range = (range) sheet. Rows [rowindex, missing];
Range. Delete (Excel. xldeleteshiftdirection. xlshiftup );
}

///

/// Exit Excel and release the called com Resource
///
Private void dispose ()
{
Book. Close (missing, missing, missing );
App. workbooks. Close ();
App. Quit ();

If (range! = NULL)
{< br> system. runtime. interopservices. marshal. releasecomobject (range);
range = NULL;
}< br> If (sheet! = NULL)
{< br> system. runtime. interopservices. marshal. releasecomobject (sheet);
Sheet = NULL;
}< br> If (book! = NULL)
{< br> system. runtime. interopservices. marshal. releasecomobject (book);
book = NULL;
}< br> If (app! = NULL)
{< br> system. runtime. interopservices. Marshal. releasecomobject (APP);
APP = NULL;
}

// System. gc. Collect ();
// GC. waitforpendingfinalizers ();
// This. killexcelprocess ();
// Process pro = process. getprocessbyid (processid );
// Pro. Kill ();
}

///

/// End the Excel Process
///
Private void killexcelprocess ()
{
Datetime starttime;
Process [] processes = process. getprocessesbyname ("Excel ");

// The Excel process ID is not obtained. Currently, only the process start time can be determined.
Foreach (Process in processes)
{
Starttime = process. starttime;
If (starttime> Beforetime & starttime <aftertime)
{
Process. Kill ();
}
}
}

}
}