How to import data to excel by Dataset

 

/**/ /**/ /**/ ///   <Summary>
/// Import all data in dataset to excel
/// You need to add COM: Microsoft Excel Object Library
/// Using Excel;
///   </Summary>
///   <Param name = "filepath"> File Path </Param>
///   <Param name = "ds"> Dataset to be stored </Param>
Public   Static   Void Exporttoexcel ( String Filepath, dataset DS)
{
Object Omissing = System. reflection. Missing. value;
Excel. applicationclass xlapp =   New Excel. applicationclass ();
Try
{
// Open an Excel file. The following are Office 2000.
Excel. Workbook xlworkbook =
Xlapp. workbooks. Open (filepath, omissing, omissing,
Omissing,
Omissing,
Omissing,
Omissing );

Excel. worksheet xlworksheet;
// Loop all datatable
For ( Int I = 0 ; I < DS. Tables. Count; I ++ )
{
// Add a new sheet
Xlworksheet = (Excel. worksheet) xlworkbook. worksheets. Add (omissing, omissing, 1 , Omissing );
 
// Use tablename as the newly added Sheet Name
Xlworksheet. Name = DS. Tables [I]. tablename;
 
// Retrieves all values in this able and stores them in stringbuffer.
String Stringbuffer =   "" ;
 
For ( Int J = 0 ; J < DS. Tables [I]. Rows. Count; j ++ )
{
For ( Int K = 0 ; K < DS. Tables [I]. Columns. Count; k ++ )
{
Stringbuffer + = DS. Tables [I]. Rows [J] [K]. tostring ();
If (K < DS. Tables [I]. Columns. Count -   1 )
Stringbuffer + =   "   " ;
}
Stringbuffer + =   "   " ;
}
// Using System clipboard
System. Windows. Forms. clipboard. setdataobject ( "" );
 
// Place stringbuffer into the clipboard
System. Windows. Forms. clipboard. setdataobject (stringbuffer );
 
// Select the first cell in the sheet page (subscript starts from 1)
(Excel. Range) xlworksheet. cells [ 1 , 1 ]). Select ();
 
// Paste the content in the clipboard in sheet.
Xlworksheet. paste (omissing, omissing );
 
// Clear system clipboard
System. Windows. Forms. clipboard. setdataobject ( "" );
}
 
// Save and close this workbook
Xlworkbook. Close (Excel. xlsaveaction. xlsavechanges, omissing, omissing );
System. runtime. interopservices. Marshal. releasecomobject (xlworkbook );
Xlworkbook =   Null ;
}
Catch (Exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
//Release COM Object
Xlapp. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (xlapp );
GC. Collect ();
}
}