Implement dataset and Excel Conversion

Http://hi.baidu.com/smalltube/blog/item/58640e771d5caf1fb151b958.html

According to some information on the Internet, I wrote two classes to convert dataset and excel.

The Excel COM control must be introduced before use ..

Using system;
Using system. Data;
Using Excel;

Namespace datasetandexcel
{
/// <Summary>
/// Convert a dataset to an Excel Workbook
/// </Summary>

Public class dataset2workbook
{

Private dataset MDS = new dataset (); // store the data source
Private string mfilepath = "C: \ temp.xls"; // Excel file name, saved path

Public dataset2workbook (ref dataset ds, string filepath)
{
//
// Todo: add the constructor logic here
//
This. MDs = Ds;
This. mfilepath = filepath;
}

/// <Summary>
/// Convert a data table to a sheet in an Excel Workbook
/// </Summary>
/// <Param name = "TB"> data table to be converted (reference type) </param>
/// <Param name = "xsheet"> Target Sheet </param>
/// <Param name = "sheetname"> Sheet Name </param>
/// <Returns> </returns>
Private bool datatable2sheet (Ref system. Data. datatable TB, ref excel. _ worksheet xsheet, string sheetname)
{

Try
{
Int rowindex = 2;
Int colindex = 0;
If (sheetname = "")
{
Xsheet. Name = Tb. tablename;
}
Else
{
Xsheet. Name = sheetname;
}
Foreach (datacolumn tempcol in TB. columns)
{

Xsheet. cells [1, colindex + 1] = tempcol. columnname;

Rowindex = 2;
Foreach (datarow temprow in TB. Rows)
{
Xsheet. cells [rowindex, colindex + 1] = "'" + temprow [colindex]. tostring ();
Rowindex ++;
}
Colindex ++;
}

Return true;
}
Catch
{
Return false;
}
}
/// <Summary>
/// Convert the table in the specified data set into a sheet in the workbook
/// </Summary>
/// <Param name = "starpos"> data table start from 0 </param>
/// <Param name = "count"> Number of data tables to be converted </param>
/// <Returns> success returns true </returns>
Public bool convert (INT starpos, int count)
{
Try
{
System. Data. datatable temptable; // create a temporary table
Excel. Application xapp = new excel. Application ();
Xapp. Visible = false;
Object objopt = system. reflection. Missing. value;

Excel. Workbook xbook = xapp. workbooks. Add (true); // Add a new workbook
Excel. Sheets xsheets = xbook. sheets;
Excel. _ worksheet xsheet = NULL;
//
// Convert a certain number of datasets after the specified starting position
//
For (INT I = starpos, icount = 1; icount <= count & I <this. MDs. Tables. Count; I ++, icount ++)
{
Temptable = This. MDs. Tables [I];
//
// Create an empty sheet
//
Xsheet = (Excel. _ worksheet) (xbook. Sheets. Add (objopt, objopt ));

Datatable2sheet (ref temptable, ref xsheet ,"");

}

//
// Obtain and delete the default sheet.
//
// Excel. _ worksheet tempxsheet = (Excel. _ worksheet) (xsheets. get_item (1 ));
//
Excel. _ worksheet tempxsheet = (Excel. _ worksheet) (xbook. worksheets [count + 1]);
Tempxsheet. Delete ();
System. runtime. interopservices. Marshal. releasecomobject (tempxsheet );
Tempxsheet = NULL;
//
// Save
//
Xbook. Saved = true;
Xbook. savecopyas (this. mfilepath );
//
// Release resources
//
System. runtime. interopservices. Marshal. releasecomobject (xsheet );
Xsheet = NULL;
System. runtime. interopservices. Marshal. releasecomobject (xsheets );
Xsheets = NULL;

System. runtime. interopservices. Marshal. releasecomobject (xbook );
Xbook = NULL;
Xapp. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (xapp );
Xapp = NULL;
GC. Collect (); // forcibly destroy
Return true;
}
Catch
{
Return false;
}

}
/// <Summary>
/// Reload convert to convert all tables in the dataset to the sheet of the workbook
/// </Summary>
/// <Returns> </returns>

Public bool convert ()
{
Return this. Convert (0, this. MDs. Tables. Count );
}

}

/// <Summary>
/// Summary of workbook2dataset. Convert a workbook to a dataset
/// </Summary>
Public class workbook2dataset
{
Private string mfilepath = "";
Private dataset MDS = new dataset ();

Public workbook2dataset (string path, ref dataset DS)
{
//
// Todo: add the constructor logic here
//
This. MDs = Ds;
This. mfilepath = path;

}

/// <Summary>
/// Convert the specified sheet in the workbook to a table in the dataset.
/// </Summary>
/// <Param name = "POS"> position of the sheet in the workbook </param>
/// <Returns> success returns true </returns>
Public bool convert (int pos)
{
Bool r = false;
String strsql = "";
String sheetname = "";
System. Data. datatable tTable;
Oledbdataadapter objda;
//
// Create an Excel Process
//
Object OBJ = system. reflection. Missing. value;
Excel. applicationclass xxapp = new excel. applicationclass (); //. Application ();

Excel. Workbook xxbook = NULL;
Excel. _ worksheet xxsheet = NULL;

Try
{

//
// Open the Excel file and obtain the name of the specified Sheet
//
Xxbook = xxapp. workbooks. open (this. mfilepath, OBJ, OBJ); // Add a new workbook
Xxsheet = (Excel. _ worksheet) (xxbook. worksheets [POS]);
Sheetname = xxsheet. Name. tostring ();
//
// Release Excel Resources
//
System. runtime. interopservices. Marshal. releasecomobject (xxsheet );
Xxsheet = NULL;
GC. Collect ();
Xxbook. Close (false, OBJ, OBJ );
System. runtime. interopservices. Marshal. releasecomobject (xxbook );
Xxbook = NULL;
Xxapp. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (xxapp );
Xxapp = NULL;
//
// Create a data connection
//
Oledbconnection objconn = new oledbconnection (
"Provider = Microsoft. Jet. oledb.4.0; Data Source =" + this. mfilepath + "; extended properties = Excel 8.0 ;");

//
// Obtain the table in the workbook
//
Strsql = "select * from [" + sheetname + "$]";
TTable = new system. Data. datatable (sheetname );
//
// Fill the sheet in the table
//
Objda = new oledbdataadapter (strsql, objconn );
Objda. Fill (tTable );
This. MDs. Tables. Add (tTable );
//
// Destroy the connection
//
Objconn. Dispose ();
R = true;

}
Catch
{
R = false;
}

GC. Collect ();
Return R;
}
/// <Summary>
/// Convert all sheet in the workbook to Dataset
/// </Summary>
/// <Returns> </returns>
Public bool convert ()
{
Bool r = false; // Return Value
//
// Create an Excel Process
//
Object OBJ = system. reflection. Missing. value;
Excel. Application xapp = new excel. Application ();
Xapp. Visible = false;
Excel. workbook xbook = xapp. workbooks. open (this. mfilepath, false, false, OBJ, OBJ );//

Int COUNT = xbook. Sheets. count;
//
// Release resources
//
Xbook. Close (false, this. mfilepath, OBJ );
System. runtime. interopservices. Marshal. releasecomobject (xbook );
Xbook = NULL;
Xapp. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (xapp );
Xapp = NULL;
GC. Collect ();
For (INT I = 1; I <= count; I ++)
{
R = convert (I );
}

Return R;
// Return this. Convert (1, count );
}
}