C # How to export data from a dataset to an Excel file

[Size = x-large] using system;

Using system. Collections. Generic;
Using system. text;
Using system. Data;
Using system. Windows. forms;
Using system. reflection;

Namespace DMS
{
/// <Summary>
/// C # Excel operations
/// </Summary>
Class exceloperate
{
// Method 1
// Public bool datasettoexcel (Dataset dataset, bool isshowexcle)
//{
// Datatable = dataset. Tables [0];
// Int rownumber = datatable. Rows. count;
// Int columnnumber = datatable. Columns. count;

// If (rownumber = 0)
//{
// MessageBox. Show ("no data can be imported to an Excel file! ");
// Return false;
//}

/// Create an Excel Object
// Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

// Excel. application. workbooks. Add (true );
// Excel. Visible = isshowexcle; // whether to open the Excel file

//// Fill in data
// For (int c = 0; C <rownumber; C ++)
//{
// For (Int J = 0; j <columnnumber; j ++)
//{
// Excel. cells [C + 1, J + 1] = datatable. Rows [C]. itemarray [J];
//}
//}

// Return true;
//}

// Method 2
// Public bool datasettoexcel (Dataset dataset, bool isshowexcle)
//{
// Datatable = dataset. Tables [0];
// Int rownumber = datatable. Rows. count;

// Int rowindex = 1;
// Int colindex = 0;

// If (rownumber = 0)
//{
// Return false;
//}

/// Create an Excel Object
// Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

// Excel. application. workbooks. Add (true );
// Excel. Visible = isshowexcle;

/// Generate the field name
// Foreach (datacolumn Col in datatable. columns)
//{
// Colindex ++;
// Excel. cells [1, colindex] = col. columnname;
//}

//// Fill in data
// Foreach (datarow row in datatable. Rows)
//{
// Rowindex ++;
// Colindex = 0;
// Foreach (datacolumn Col in datatable. columns)
//{
// Colindex ++;
// Excel. cells [rowindex, colindex] = row [col. columnname];
//}
//}

// Return true;
//}

// Method 3 (fastest speed)
/// <Summary>
/// Export the data in the dataset to an Excel file
/// </Summary>
/// <Param name = "dataset"> input dataset </param>
/// <Param name = "isshowexcle"> whether to display the Excel file </param>
/// <Returns> </returns>
Public bool datasettoexcel (Dataset dataset, bool isshowexcle)
{
Datatable = dataset. Tables [0];
Int rownumber = datatable. Rows. Count; // The field name is not included.
Int columnnumber = datatable. Columns. count;
Int colindex = 0;

If (rownumber = 0)
{
Return false;
}

// Create an Excel Object
Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

// Excel. application. workbooks. Add (true );
Microsoft. Office. InterOP. Excel. Workbook workbook = excel. workbooks. Add (Microsoft. Office. InterOP. Excel. xlwbatemplate. xlwbatworksheet );

Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets [1];

Excel. Visible = isshowexcle;
// Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) excel. worksheets [1];

Microsoft. Office. InterOP. Excel. Range range;

// Generate the field name
Foreach (datacolumn Col in datatable. columns)
{
Colindex ++;
Excel. cells [1, colindex] = col. columnname;
}

Object [,] objdata = new object [rownumber, columnnumber];

For (INT r = 0; r <rownumber; r ++)
{
For (int c = 0; C <columnnumber; C ++)
{
Objdata [R, C] = datatable. Rows [r] [c];
}
// Application. doevents ();
}

// Write to excel
Range = worksheet. get_range (Excel. cells [2, 1], Excel. cells [rownumber + 1, columnnumber]);

// Range. numberformat = "@"; // set the cell to the text format
Range. value2 = objdata;
Worksheet. get_range (Excel. cells [2, 1], Excel. cells [rownumber + 1, 1]). numberformat = "yyyy-m-d h: mm ";

Return true;
}

// Method 4
// Public bool datasettoexcel (Dataset dataset, bool isshowexcle)
//{
// Datatable = dataset. Tables [0];
// Int rownumber = datatable. Rows. count;
// Int columnnumber = datatable. Columns. count;
// String stringbuffer = "";

// If (rownumber = 0)
//{
// MessageBox. Show ("no data can be imported to an Excel file! ");
// Return false;
//}

/// Create an Excel Object
// Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

// Excel. application. workbooks. Add (true );
// Excel. Visible = isshowexcle; // whether to open the Excel file

//// Fill in data
// For (INT I = 0; I <rownumber; I ++)
//{
// For (Int J = 0; j <columnnumber; j ++)
//{
// Stringbuffer + = datatable. Rows [I]. itemarray [J]. tostring ();
// If (j <columnnumber-1)
//{
// Stringbuffer + = "\ t ";
//}
//}
// Stringbuffer + = "\ n ";
//}
// Clipboard. Clear ();
// Clipboard. setdataobject (stringbuffer );
// (Microsoft. Office. InterOP. Excel. Range) excel. cells [1, 1]). Select ();
// (Microsoft. Office. InterOP. Excel. worksheet) excel. activeworkbook. activesheet). paste (missing. Value, missing. value );

// Clipboard. Clear ();

// Return true;
//}

// Public bool datasettoexcel (Dataset dataset, string filename, bool isshowexcle)
//{
// Datatable = dataset. Tables [0];
// Int rownumber = datatable. Rows. count;
// Int columnnumber = datatable. Columns. count;

// If (rownumber = 0)
//{
// MessageBox. Show ("no data can be imported to an Excel file! ");
// Return false;
//}

/// Create an Excel Object
// Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

// Microsoft. Office. InterOP. Excel. Workbook workbook = excel. application. workbooks. Add (true );

// Excel. Visible = false; // whether to open the Excel file

//// Fill in data
// For (INT I = 0; I <rownumber; I ++)
//{
// For (Int J = 0; j <columnnumber; j ++)
//{
// Excel. cells [I + 1, J + 1] = datatable. Rows [I]. itemarray [J];
//}
//}

/// String filename = path + "\" + datetime. Now. tostring (). Replace (':', '_') + ". xls ";

// Workbook. saveas (filename, missing. value, missing. value, missing. value, missing. value, missing. value, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, missing. value, missing. value, missing. value, missing. value, missing. value );

// Try
//{
// Workbook. Saved = true;
// Excel. usercontrol = false;
//// Excelapp. Quit ();
//}
// Catch (exception)
//{
// MessageBox. Show (exception. Message );
//}
// Finally
//{
// Workbook. Close (Microsoft. Office. InterOP. Excel. xlsaveaction. xlsavechanges, missing. Value, missing. value );

// Excel. Quit ();
//}

// If (isshowexcle)
//{
// System. Diagnostics. process. Start (filename );
//}
// Return true;
//}

// Public bool datasettoexcel (Dataset dataset, string filename, bool isshowexcle)
//{
// Datatable = dataset. Tables [0];
// Int rownumber = datatable. Rows. Count; // The field name is not included.
// Int columnnumber = datatable. Columns. count;
// Int colindex = 0;

// If (rownumber = 0)
//{
// MessageBox. Show ("no data can be imported to an Excel file! ");
// Return false;
//}

/// Create an Excel Object
// Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

/// Excel. application. workbooks. Add (true );
// Microsoft. Office. InterOP. Excel. Workbook workbook = excel. workbooks. Add (Microsoft. Office. InterOP. Excel. xlwbatemplate. xlwbatworksheet );

// Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets [1];

// Excel. Visible = isshowexcle;
/// Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) excel. worksheets [1];

// Worksheet. Name = "deflection data ";
// Microsoft. Office. InterOP. Excel. Range range;

/// Generate the field name
// Foreach (datacolumn Col in datatable. columns)
//{
// Colindex ++;
// Excel. cells [1, colindex] = col. columnname;
//}

// Object [,] objdata = new object [rownumber, columnnumber];

// For (INT r = 0; r <rownumber; r ++)
//{
// For (int c = 0; C <columnnumber; C ++)
//{
// Objdata [R, C] = datatable. Rows [r] [c];
//}
//// Application. doevents ();
//}

/// Write to excel
// Range = worksheet. get_range (Excel. cells [2, 1], Excel. cells [rownumber + 1, columnnumber]);

/// Range. numberformat = "@"; // set the cell to text format
// Range. value2 = objdata;
// Worksheet. get_range (Excel. cells [2, 1], Excel. cells [rownumber + 1, 1]). numberformat = "yyyy-m-d h: mm ";

/// String filename = path + "\" + datetime. Now. tostring (). Replace (':', '_') + ". xls ";

// Workbook. saveas (filename, missing. value, missing. value, missing. value, missing. value, missing. value, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, missing. value, missing. value, missing. value, missing. value, missing. value );

// Try
//{
// Workbook. Saved = true;
// Excel. usercontrol = false;
//// Excelapp. Quit ();
//}
// Catch (exception)
//{
// MessageBox. Show (exception. Message );
//}
// Finally
//{
// Workbook. Close (Microsoft. Office. InterOP. Excel. xlsaveaction. xlsavechanges, missing. Value, missing. value );

// Excel. Quit ();
//}

/// If (isshowexcle)
////{
/// System. Diagnostics. process. Start (filename );
////}
// Return true;
//}

/// <Summary>
/// Save the data in the dataset to an Excel file
/// </Summary>
/// <Param name = "dataset"> input dataset </param>
/// <Param name = "FILENAME"> Save the absolute path name of the Excel file. </param>
/// <Param name = "isshowexcle"> whether to open an Excel file </param>
/// <Returns> </returns>
Public bool datasettoexcel (Dataset dataset, string filename, bool isshowexcle)
{
Datatable = dataset. Tables [0];
Int rownumber = datatable. Rows. Count; // The field name is not included.
Int columnnumber = datatable. Columns. count;
Int colindex = 0;

If (rownumber = 0)
{
MessageBox. Show ("no data can be imported to an Excel file! ");
Return false;
}

// Create an Excel Object
Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

// Excel. application. workbooks. Add (true );
Microsoft. Office. InterOP. Excel. Workbook workbook = excel. workbooks. Add (Microsoft. Office. InterOP. Excel. xlwbatemplate. xlwbatworksheet );

Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets [1];

Excel. Visible = false;
// Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) excel. worksheets [1];

Microsoft. Office. InterOP. Excel. Range range;

// Generate the field name
Foreach (datacolumn Col in datatable. columns)
{
Colindex ++;
Excel. cells [1, colindex] = col. columnname;
}

Object [,] objdata = new object [rownumber, columnnumber];

For (INT r = 0; r <rownumber; r ++)
{
For (int c = 0; C <columnnumber; C ++)
{
Objdata [R, C] = datatable. Rows [r] [c];
}
// Application. doevents ();
}

// Write to excel
Range = worksheet. get_range (Excel. cells [2, 1], Excel. cells [rownumber + 1, columnnumber]);

// Range. numberformat = "@"; // set the cell to the text format
Range. value2 = objdata;
Worksheet. get_range (Excel. cells [2, 1], Excel. cells [rownumber + 1, 1]). numberformat = "yyyy-m-d h: mm ";

// String filename = path + "\" + datetime. Now. tostring (). Replace (':', '_') + ". xls ";

Workbook. saveas (filename, missing. value, missing. value, missing. value, missing. value, missing. value, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, missing. value, missing. value, missing. value, missing. value, missing. value );

Try
{
Workbook. Saved = true;
Excel. usercontrol = false;
// Excelapp. Quit ();
}
Catch (exception)
{
MessageBox. Show (exception. Message );
}
Finally
{
Workbook. Close (Microsoft. Office. InterOP. Excel. xlsaveaction. xlsavechanges, missing. Value, missing. value );

Excel. Quit ();
}

If (isshowexcle)
{
System. Diagnostics. process. Start (filename );
}
Return true;
}
}
} [/Size]