C # example code for converting EXCEL DataTable

Copy codeThe Code is as follows: // load the Excel file
Public DataSet LoadDataFromExcel (string filePath)
{
Try
{
String strConn;
// StrConn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + filePath + "; Extended Properties = 'excel 8.0; HDR = False; IMEX = 1 '";
StrConn = string. format ("Provider = Microsoft. ACE. OLEDB.12.0; Data Source = {0}; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1; '", filePath );
OleDbConnection OleConn = new OleDbConnection (strConn );
OleConn. Open ();
String SQL = "SELECT * FROM [Sheet1 $]"; // However, you can change the Sheet name, such as sheet2.

OleDbDataAdapter OleDaExcel = new OleDbDataAdapter (SQL, OleConn );
DataSet OleDsExcle = new DataSet ();
OleDaExcel. Fill (OleDsExcle, "Sheet1 ");
OleConn. Close ();
Return OleDsExcle;
}
Catch (Exception err)
{

Return null;
}
}

/// <Summary>
/// DataTable exports the Excel file directly. This method will open the DataTable data in Excel, and then manually Save the data to the exact location.
/// </Summary>
/// <Param name = "dt"> DataTable of the Excel file to be exported </param>
/// <Returns> </returns>
Public bool DoExport (System. Data. DataTable dt)
{
Microsoft. Office. Interop. Excel. Application app = new ApplicationClass ();
If (app = null)
{
Throw new Exception ("Excel cannot be started ");
}
App. Visible = true;
Workbooks wbs = app. Workbooks;
Workbook wb = wbs. Add (Missing. Value );
Worksheet ws = (Worksheet) wb. Worksheets [1];

Int cnt = dt. Rows. Count;
Int columncnt = dt. Columns. Count;

******************* *
Object [,] objData = new Object [cnt + 1, columncnt]; // create cache data
// Obtain the column title
For (int I = 0; I <columncnt; I ++)
{
ObjData [0, I] = dt. Columns [I]. ColumnName;
}
// Obtain specific data
For (int I = 0; I <cnt; I ++)
{
System. Data. DataRow dr = dt. Rows [I];
For (int j = 0; j <columncnt; j ++)
{
ObjData [I + 1, j] = dr [j];
}
}

// ********************* Write Excel *************** ***
Range r = ws. get_Range (app. Cells [1, 1], app. Cells [cnt + 1, columncnt]);
R. NumberFormat = "@";
// R = r. get_Resize (cnt + 1, columncnt );
R. Value2 = objData;
R. EntireColumn. AutoFit ();

App = null;
Return true;
}