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

Source: Internet
Author: User
[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]

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.