C # about Excel Export database practices

Using System;
Using System.Diagnostics;
Using System.Collections;
Using System.Data;
Using System.Web;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using Excel;
Using System.Data.OleDb;
Namespace Maticsoft.common
{
<summary>
To manipulate Excel to export a data report class
Copyright (C) Maticsoft
</summary>
public class Datatoexcel
{
Public Datatoexcel ()
{
}

#region A class that operates in Excel (requires Excel.dll support)

private int titlecolorindex = 15;
<summary>
Title Background color
</summary>
public int Titlecolorindex
{
set {Titlecolorindex = value;}
get {return titlecolorindex;}
}

Private DateTime Beforetime;//excel before start time
Private DateTime Aftertime;//excel Time after startup

#region Create an Excel sample
<summary>
Create an Excel sample
</summary>
public void Createexcel ()
{
Excel.Application Excel = new Excel.Application ();
Excel. APPLICATION.WORKBOOKS.ADD (TRUE);
Excel. Cells[1, 1] = "1th row 1th column";
Excel. Cells[1, 2] = "1th row 2nd column";
Excel. Cells[2, 1] = "2nd row 1th column";
Excel. Cells[2, 2] = "2nd row 2nd Column";
Excel. Cells[3, 1] = "3rd row 1th column";
Excel. Cells[3, 2] = "3rd row 2nd column";

Save
Excel. ActiveWorkbook.SaveAs ("./lizitt.xls", XLFILEFORMAT.XLEXCEL7, NULL, NULL, FALSE, FALSE, Excel.XlSaveAsAccessMode.xlNoChange, NULL, NULL, NULL, nulls, NULL);
Open the Display
Excel. Visible = true;
Excel. Quit ();
Excel=null;
Gc. Collect ();//garbage collection
}
#endregion

#region Export data from a DataTable as a report
<summary>
Export data from a DataTable as a report
</summary>
<param name= "DT" > Data to Export </param>
<param name= "Strtitle" > Export the title of the report </param>
<param name= "FilePath" > Path to save file </param>
<returns></returns>
public string Outputexcel (System.Data.DataTable dt, String strtitle, String FilePath)
{
Beforetime = DateTime.Now;

Excel.Application Excel;
Excel._workbook xBk;
Excel._worksheet xSt;

int rowIndex = 4;
int colindex = 1;

Excel = new Excel.applicationclass ();
XBk = Excel. Workbooks.Add (TRUE);
XSt = (excel._worksheet) xbk.activesheet;

Get column headings
foreach (DataColumn col in dt. Columns)
{
colindex++;
Excel. Cells[4, Colindex] = Col. ColumnName;

//sets the title format to center-align
Xst.get_range (Excel. Cells[4, Colindex], Excel. Cells[4, Colindex]). Font.Bold = true;
Xst.get_range (Excel. Cells[4, Colindex], Excel. Cells[4, Colindex]). HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
Xst.get_range (Excel. Cells[4, Colindex], Excel. Cells[4, Colindex]). Select ();
Xst.get_range (Excel. Cells[4, Colindex], Excel. Cells[4, Colindex]). Interior.ColorIndex = titlecolorindex;//19;//is set to light yellow, there are 56 kinds of
}

//Gets the data in the table
foreach (DataRow row in dt. Rows)
{
rowindex++;
Colindex = 1;
foreach (DataColumn col in dt. Columns)
{
colindex++;
if (Col. DataType = = System.Type.GetType ("System.DateTime"))
{
Excel. Cells[rowindex, Colindex] = (Convert.todatetime (row[col. ColumnName]. ToString ())). ToString ("Yyyy-mm-dd");
Xst.get_range (Excel. Cells[rowindex, Colindex], Excel. Cells[rowindex, Colindex]). HorizontalAlignment = excel.xlvalign.xlvaligncenter;//Sets the field format for the date type to be center-aligned
}
Else
if (col. DataType = = System.Type.GetType ("System.String"))
{
Excel. Cells[rowindex, Colindex] = "'" + row[col. ColumnName]. ToString ();
Xst.get_range (Excel. Cells[rowindex, Colindex], Excel. Cells[rowindex, Colindex]). HorizontalAlignment = excel.xlvalign.xlvaligncenter;//Sets the field format for the character type to be center-aligned
}
Else
{
Excel. Cells[rowindex, Colindex] = Row[col. ColumnName]. ToString ();
}
}
}

Load a Total row
int rowsum = RowIndex + 1;
int colsum = 2;
Excel. Cells[rowsum, 2] = "Total";
Xst.get_range (Excel. Cells[rowsum, 2], Excel. Cells[rowsum, 2]). HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Sets the color of the selected part
Xst.get_range (Excel. Cells[rowsum, Colsum], Excel. Cells[rowsum, Colindex]). Select ();
Xst.get_range (Excel. Cells[rowsum,colsum],excel. Cells[rowsum,colindex]). Interior.ColorIndex =assistant.getconfigint ("ColorIndex");//1;//Set to light yellow, total 56 kinds

Get the title of the entire report
Excel. Cells[2, 2] = strtitle;

Formatting the title of an entire report
Xst.get_range (Excel. Cells[2, 2], Excel. Cells[2, 2]). Font.Bold = true;
Xst.get_range (Excel. Cells[2, 2], Excel. Cells[2, 2]). Font.Size = 22;

Set the report table to fit the width
Xst.get_range (Excel. Cells[4, 2], Excel. Cells[rowsum, Colindex]). Select ();
Xst.get_range (Excel. Cells[4, 2], Excel. Cells[rowsum, Colindex]). Columns.AutoFit ();

Set the title of the entire report to be centered across columns
Xst.get_range (Excel. Cells[2, 2], Excel. Cells[2, Colindex]). Select ();
Xst.get_range (Excel. Cells[2, 2], Excel. Cells[2, Colindex]). HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

Draw Border
Xst.get_range (Excel. Cells[4, 2], Excel. Cells[rowsum, Colindex]). Borders.LineStyle = 1;
Xst.get_range (Excel. Cells[4, 2], Excel. Cells[rowsum, 2]). Borders[excel.xlbordersindex.xledgeleft]. Weight = excel.xlborderweight.xlthick;//Set the left line bold
Xst.get_range (Excel. Cells[4, 2], Excel. Cells[4, Colindex]). Borders[excel.xlbordersindex.xledgetop]. Weight = excel.xlborderweight.xlthick;//Set upper Edge Bold
Xst.get_range (Excel. Cells[4, Colindex], Excel. Cells[rowsum, Colindex]). Borders[excel.xlbordersindex.xledgeright]. Weight = excel.xlborderweight.xlthick;//Set the right line bold
Xst.get_range (Excel. Cells[rowsum, 2], Excel. Cells[rowsum, Colindex]). Borders[excel.xlbordersindex.xledgebottom]. Weight = excel.xlborderweight.xlthick;//Set Lower edge Bold

Aftertime = DateTime.Now;

Show effect
Excel. Visible=true;
Excel. Sheets[0] = "SSS";

Clearfile (FilePath);
string filename = DateTime.Now.ToString ("YYYYMMDDHHMMSSFF") + ". xls";
Excel. ActiveWorkbook.SaveAs (FilePath + filename, Excel.XlFileFormat.xlExcel7, NULL, NULL, FALSE, FALSE, Excel.XlSaveAsAccessMode.xlNoChange, NULL, NULL, NULL, nulls, NULL);

Open the Display
Excel. Visible = true;
Excel. Quit ();
Excel=null;
Gc. Collect ();//garbage collection
Wkbnew.saveas Strbookname;
Excel. Save (Strexcelfilename);

#region End the Excel process

You need to configure the DCOM object for Excel: DCOMCNFG

Excel. Quit ();
Excel=null;

Xbk.close (NULL, NULL, NULL);
Excel. Workbooks.close ();
Excel. Quit ();

Note: All the Excel objects used here do this, or the Excel process will not end
if (rng! = null)
//{
System.Runtime.InteropServices.Marshal.ReleaseComObject (RNG);
RNG = null;
//}
if (TB! = NULL)
//{
System.Runtime.InteropServices.Marshal.ReleaseComObject (TB);
TB = NULL;
//}
if (xSt! = null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject (xSt);
XSt = null;
}
if (xBk! = null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject (XBK);
XBk = null;
}
if (Excel! = null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject (Excel);
Excel = null;
}
Gc. Collect ();//garbage collection
#endregion

return filename;

}
#endregion

#region Kill Excel Process

<summary>
End Excel Process
</summary>
public void killexcelprocess ()
{
Process[] myprocesses;
DateTime StartTime;
myprocesses = Process.getprocessesbyname ("Excel");

Cannot get the Excel process ID, only to determine the process start time temporarily
foreach (Process myprocess in myprocesses)
{
StartTime = Myprocess.starttime;
if (StartTime > Beforetime && startTime < Aftertime)
{
Myprocess.kill ();
}
}
}
#endregion

#endregion

#region Export data from a DataTable as a report (without using an Excel object, using Com.excel)

#region using the example
/* Use Example:
* DataSet ds= (DataSet) session["Adbrowsehitdaylist"];
String excelfolder=assistant.getconfigstring ("Excelfolder");
String Filepath=server.mappath (".") + "\ \" +excelfolder+ "\ \";

To generate the Chinese table for the column
Hashtable namelist = new Hashtable ();
Namelist.add ("ADID", "Advertising Code");
Namelist.add ("Adname", "Advertising name");
Namelist.add ("Year", "Years");
Namelist.add ("Month", "Moon");
Namelist.add ("Browsum", "Display Number");
Namelist.add ("Hitsum", "Number of hits");
Namelist.add ("Browsinglip", "Independent IP display");
Namelist.add ("Hitsinglip", "Independent IP click");
Take advantage of Excel objects
Datatoexcel dte=new datatoexcel ();
String Filename= "";
Try
{
if (ds. Tables[0]. ROWS.COUNT>0)
{
Filename=dte. Dataexcel (ds. Tables[0], "title", Filepath,namelist);
}
}
Catch
{
DtE. Killexcelprocess ();
}

if (filename!= "")
{
Response.Redirect (excelfolder+ "\ \" +filename,true);
}
*
* */

#endregion

//<summary>
//Export data for DataTable as a report (without using an Excel object)
//</summary>
//<param name= "DT" > Data datatable</param>
//<param name= "strtitle" > title </param>
//<param name= "FilePath "> Path to the generated file </param>
//<param name=" NameList "></PARAM>
//<returns></returns
public string Dataexcel (System.Data.DataTable dt, String strtitle, String FilePath, Hashtable namelist)
{
COM. Excel.cexcelfile Excel = new COM. Excel.cexcelfile ();
Clearfile (FilePath);
String filename = DateTime.Now.ToString ("YYYYMMDDHHMMSSFF") + ". xls";
Excel. CreateFile (FilePath + filename);
Excel. PrintGridlines = false;

Com. Excel.cExcelFile.MarginTypes MT1 = COM. Excel.cExcelFile.MarginTypes.xlsTopMargin;
Com. Excel.cExcelFile.MarginTypes mt2 = COM. Excel.cExcelFile.MarginTypes.xlsLeftMargin;
Com. Excel.cExcelFile.MarginTypes mt3 = COM. Excel.cExcelFile.MarginTypes.xlsRightMargin;
Com. Excel.cExcelFile.MarginTypes MT4 = COM. Excel.cExcelFile.MarginTypes.xlsBottomMargin;

Double height = 1.5;
Excel. SetMargin (ref MT1, ref height);
Excel. SetMargin (ref mt2, ref height);
Excel. SetMargin (ref MT3, ref height);
Excel. SetMargin (ref MT4, ref height);

Com. Excel.cExcelFile.FontFormatting ff = COM. Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font = "Song Body";
Short fontsize = 9;
Excel. SetFont (ref font, ref fontsize, ref FF);

BYTE B1 = 1,
b2 = 12;
Short S3 = 12;
Excel. Setcolumnwidth (ref b1, ref B2, ref S3);

String Header = "header";
String footer = "Footer";
Excel. SetHeader (ref header);
Excel. Setfooter (ref footer);

Com. Excel.cExcelFile.ValueTypes vt = COM. Excel.cExcelFile.ValueTypes.xlsText;
Com. Excel.cExcelFile.CellFont CF = COM. Excel.cExcelFile.CellFont.xlsFont0;
Com. Excel.cExcelFile.CellAlignment CA = COM. Excel.cExcelFile.CellAlignment.xlsCentreAlign;
Com. Excel.cExcelFile.CellHiddenLocked CHL = COM. Excel.cExcelFile.CellHiddenLocked.xlsNormal;

Report title
int cellformat = 1;
int rowindex = 1,colindex = 3;
Object title = (object) strtitle;
Excel. WriteValue (ref VT, ref CF, ref CA, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);

int rowIndex = 1;//start line
int colindex = 0;

//Get column header
foreach (DataColumn colhead in dt. Columns)
{
colindex++;
String name = Colhead. Columnname.trim ();
Object namestr = (object) name;
IDictionaryEnumerator Enum = Namelist.getenumerator ();
while (Enum.movenext ())
{
if (Enum.Key.ToString (). Trim () = = name)
{
Namestr = enum.value;
}
}
Excel. WriteValue (ref VT, ref CF, ref CA, ref CHL, ref rowIndex, ref colindex, ref NAMESTR, ref CellFormat);
}

Get the data in the table
foreach (DataRow row in dt. Rows)
{
rowindex++;
Colindex = 0;
foreach (DataColumn col in dt. Columns)
{
colindex++;
if (Col. DataType = = System.Type.GetType ("System.DateTime"))
{
Object str = (object) (Convert.todatetime (Row[col). ColumnName]. ToString ())). ToString ("Yyyy-mm-dd");;
Excel. WriteValue (ref VT, ref CF, ref CA, ref CHL, ref rowIndex, ref colindex, ref str, ref CellFormat);
}
Else
{
Object str = (object) Row[col. ColumnName]. ToString ();
Excel. WriteValue (ref VT, ref CF, ref CA, ref CHL, ref rowIndex, ref colindex, ref str, ref CellFormat);
}
}
}
int ret = Excel. CloseFile ();

if (ret!=0)
//{
MessageBox.Show (This, "error!");
//}
Else
//{
MessageBox.Show (This, "Please open file c:\\test.xls!");
//}
Open the Display
return filename;

}

#endregion

#region to clean up outdated Excel files

private void Clearfile (string FilePath)
{
string[] Files = System.IO.Directory.GetFiles (FilePath);
if (Files.length > 10)
{
for (int i = 0; i <; i++)
{
Try
{
System.IO.File.Delete (Files[i]);
}
Catch
{
}

}
}
}
#endregion

Public DataSet execleds (string filenameurl)
{
Filenameurl = "D:/cypshow/file/test.xls";

String strconn = "provider=microsoft.jet.oledb.4.0;" + "Data source=" +filenameurl+ "; Extended properties= ' Excel 8.0; Hdr=yes; Imex=1 ' ";
OleDbConnection conn = new OleDbConnection (strconn);
OleDbDataAdapter Odda = new OleDbDataAdapter ("SELECT * from [sheet1$]", conn);
DataSet ds = new DataSet ();
Odda. Fill (DS);
return DS;
}

}
}

C # about Excel Export database practices