Excel Import and Export. net

/* Configure /*------------------------------------------------------------------------------------
* File name: excelctrol. CS
* File Function Description: Excel Import and Export
*
* Create an ID: Liu Jia Jun 20080722
*
* Modify the ID:
* Description:
*
*----------------------------------------------------------------------------------*/

Using system;
Using system. text;
Using system. collections;
Using system. configuration;
Using system. Data;
Using system. Data. oledb;
Using system. Data. sqlclient;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. htmlcontrols;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. IO;
Using wiscom. Data;
Using Excel = Microsoft. Office. InterOP. Excel;

Namespace wiscom. Urp. receivefreshman
{
/// <Summary>
/// Excel control and basic operations
/// </Summary>
Public class excelctrol
{

 

/// <Summary>
/// <Br> obtain the table name in Excel </BR>
/// <Br> 2008-07-22 ljx </BR>
/// </Summary>
/// <Param name = "filepath"> Excel file path </param>

Public static arraylist getexcelsheetname (string filepath)
{
Arraylist Al = new arraylist ();
String strconn;
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Try
{
Conn. open ();
Datatable sheetnames = conn. getoledbschematable
(System. Data. oledb. oledbschemaguid. Tables, new object [] {null, "table "});
Conn. Close ();
Foreach (datarow DR in sheetnames. Rows)
{
Al. Add (Dr [2]);
}
}
Catch (exception ex ){}

Return al;
}

/// <Summary>
/// <Br> Read dataset from an Excel file </BR>
/// <Br> 2008-07-22 ljx </BR>
/// </Summary>
/// <Param name = "filepath"> Excel file path </param>
/// <Param name = "sheetname"> table in an Excel file </param>
Public static dataset exceldataset (string filepath, string sheetname)
{
String strconn;
Dataset DS = new dataset ();
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Try
{
Oledbdataadapter oada = new oledbdataadapter ("select * from [" + sheetname + "]", strconn );
Oada. Fill (DS );
}
Catch (exception ex ){}
Return Ds;

}

/// <Summary>
/// Obtain the dataset of the dormitory information Excel File
/// <Br> 2008-07-22 ljx </BR>
/// </Summary>
/// <Param name = "filepath"> Excel file path </param>
/// <Param name = "sheetname"> table in an Excel file </param>
/// <Returns> </returns>
Public static dataset get_ssxx_dataset (string filepath, string sheetname) // read the Excel file to Dataset
{
String strconn;
Dataset DS = new dataset ();
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Try
{
Oledbdataadapter oada = new oledbdataadapter ("select student ID, name, school, building, Dormitory number from [" + sheetname + "]", strconn );
Oada. Fill (DS );

}
Catch (exception ex ){}
Return Ds;

}

Public static dataset get_studentinfo_dataset (string filepath, string sheetname) // read the Excel file to Dataset
{
String strconn;
Dataset DS = new dataset ();
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Try
{
Oledbdataadapter oada = new oledbdataadapter ("select student ID, name, gender, Department Code , Professional code, class code, student status, date of birth, source of birth, nationality, ID card number, ethnic code, political appearance code from ["+ sheetname +"] ", strconn );
Oada. Fill (DS );

}
Catch (exception ex ){}
Return Ds;

}

 

 

/// <Summary>
/// Obtain the dataset of the dormitory information Excel File
/// <Br> 2008-07-22 ljx </BR>
/// </Summary>
/// <Param name = "filepath"> file path </param>
/// <Param name = "sheetname"> table in an Excel file </param>
/// <Returns> </returns>
Public static dataset get_ssxx_dataset (string filepath, arraylist sheetname) // read the Excel file to Dataset
{
String strconn;
Dataset DS = new dataset ();
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Try
{
Oledbdataadapter oada = new oledbdataadapter ("select student ID, name, school, building, Dormitory number from [" + sheetname + "]", strconn );
Oada. Fill (DS );

}
Catch (exception ex ){}
Return Ds;

}

/// <Summary>
/// Export dataset in the Excel File Format [! The exported Excel file is not processed.]
/// <Br> 2008-07-22 ljx </BR>
/// </Summary>
/// <Param name = "strfilename"> export name </param>
/// <Param name = "DT"> dataset </param>
Public static void exportexcel (string strfilename, dataset DT)
{
// Clear the response cache content
Httpcontext. Current. response. Clear ();
Httpcontext. Current. response. Buffer = true;
Strfilename = strfilename + ". xls ";
// Determine the encoding format of Characters
Httpcontext. Current. response. appendheader ("content-disposition", "attachment; filename =" + httputility. urlencode (strfilename ));
Httpcontext. Current. response. contenttype = "aApplication/vnd. MS-excel ";
Httpcontext. Current. response. charset = "gb2312 ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. getencoding ("gb2312 ");
Gridview DV = new gridview ();
DV. datasource = DT;
// DV. rowdatabound + = gridview2_rowdatabound; // event
DV. databind ();

Try
{
DV. Page. enableviewstate = false;
}
Catch
{}
System. Io. stringwriter swbody = new system. Io. stringwriter ();
System. Web. UI. htmltextwriter hwbody = new system. Web. UI. htmltextwriter (swbody );
DV. rendercontrol (hwbody );
// Special settings for eliminating garbled characters, unconventional methods
String strexcel = "";
Strexcel = "";
Strexcel + = hwbody. innerwriter. tostring ();
Httpcontext. Current. response. Write (strexcel );
Httpcontext. Current. response. End ();
}

/// <Summary>
/// Export the control format)
/// Public override void verifyrenderinginserverform (Control)
/// Override verifyrenderinginserverform before the function must be called
/// </Summary>
/// <Param name = "CTL"> gridview Control </param>
/// <Param name = "FILENAME"> file output name </param>

Public static void toexcel (control CTL, string filename)
{
Httpcontext. Current. response. charset = "gb2312 ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. default;
Httpcontext. Current. response. contenttype = "application/MS-excel ";
Httpcontext. Current. response. appendheader ("content-disposition", "attachment; filename =" + "" + filename );
CTL. Page. enableviewstate = false;
System. Io. stringwriter Tw = new system. Io. stringwriter ();
Htmltextwriter hW = new htmltextwriter (TW );
CTL. rendercontrol (HW );
Httpcontext. Current. response. Write (TW. tostring ());
Httpcontext. Current. response. End ();
}

Public static void toexcel (string SQL, string filename)
{
// Add Response Header
Httpcontext. Current. response. Clear ();
Httpcontext. Current. response. addheader ("content-disposition", String. Format ("attachment?filename=02.16.csv", filename ));

Httpcontext. Current. response. contenttype = "application/MS-excel ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. default;
// Get data from database
Try
{

Sqldatareader DR = (sqldatareader) datahelper. getdatareader (SQL );
Stringbuilder sb = new stringbuilder ();
//
// Add Header
//
For (int count = 0; count <dr. fieldcount; count ++)
{
If (dr. getname (count )! = NULL)
SB. append (dr. getname (count ));
If (count <dr. fieldcount-1)
{
SB. append (",");
}
}
Httpcontext. Current. response. Write (sb. tostring () + "\ n ");
Httpcontext. Current. response. Flush ();
//
// Append data
//
While (dr. Read ())
{
SB = new stringbuilder ();

For (INT Col = 0; Col <dr. fieldcount-1; Col ++)
{
If (! Dr. isdbnull (COL ))
SB. append (dr. getvalue (COL). tostring (). Replace (",",""));
SB. append (",");
}
If (! Dr. isdbnull (dr. fieldcount-1 ))
SB. append (dr. getvalue (dr. fieldcount-1). tostring (). Replace (",",""));
Httpcontext. Current. response. Write (sb. tostring () + "\ n ");
Httpcontext. Current. response. Flush ();
}
Dr. Dispose ();
}
Catch (exception ex)
{
Httpcontext. Current. response. Write (ex. Message );
}
Finally
{

}
Httpcontext. Current. response. End ();
}

Public static void toexcel (Dataset ds, string strexcelfilename)
{

Excel. Application Excel = new excel. Application ();

// Excel. Workbook OBJ = new excel. workbookclass ();
// Obj. saveas ("C: \ zn.xls", Excel. xlfileformat. xlexcel9795, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null, null );

Int rowindex = 1;
Int colindex = 0;

Excel. application. workbooks. Add (true );

System. Data. datatable table = Ds. Tables [0];
Foreach (datacolumn Col in table. columns)
{
Colindex ++;
Excel. cells [1, colindex] = col. columnname;
}

Foreach (datarow row in table. Rows)
{
Rowindex ++;
Colindex = 0;
Foreach (datacolumn Col in table. columns)
{
Colindex ++;
Excel. cells [rowindex, colindex] = row [col. columnname]. tostring ();
}
}
Excel. Visible = false;

// Excel. activeworkbook. saveas (strexcelfilename + ". xls ", Excel. xlfileformat. xlexcel9795, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null, null );
Excel. activeworkbook. saveas (strexcelfilename + ". xls ", Excel. xlfileformat. xlexcel9795, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null, null );

// Wkbnew. saveas strbookname

// Excel. Save (strexcelfilename );
Excel. Quit ();
Excel = NULL;

GC. Collect (); // garbage collection
}

 

 

/// <Summary>
/// Delete an Excel file
// 2008-07-02 ljx
/// </Summary>
/// <Param name = "filepath"> file path </param>
Public static void delexcel (string filepath)
{

Fileinfo excelfile = new fileinfo (filepath );
Excelfile. Delete ();

}

 

 

 
}

}