C # Read Excel data from source code to the program-SQL Server-to Dataset

1. Read-Only Excel Data to the program for display:

Using system;
Using system. Collections. Generic;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. text;
Using system. Windows. forms;
Using Microsoft. Office. InterOP. Excel;
Using system. reflection;

Namespace exceldemo
{
Public partial class form1: Form
{
Public form1 ()
{
Initializecomponent ();

}

Private void button#click (Object sender, eventargs E)
{
Microsoft. Office. InterOP. Excel. applicationclass APP = new applicationclass ();
App. Visible = false;

Workbookclass W = (workbookclass) app. workbooks. Open (@ "C:/Documents and Settings/qqq/desktop/002.xls", // environment. currentdirectory +
Missing. Value, missing. Value, missing. value,

Missing. Value, missing. Value, missing. value,

Missing. Value, missing. Value, missing. value,

Missing. Value, missing. Value, missing. value,

Missing. Value, missing. value );

Object missing = type. missing;
Sheets sheets = W. worksheets;
Worksheet datasheet = NULL;
Foreach (worksheet sheet in sheets)
{
If (sheet. Name = "recovered_sheet1 ")
{
Datasheet = sheet;
Break;
}
}
If (null = datasheet)
{
MessageBox. Show (this, "No sheet." With the name recovered_sheet1 .");
Return;
}

Range = datasheet. get_range ("A8", "n35 ");

System. array values = (system. array) range. formula;
If (values! = NULL)
{
Int len1 = values. getlength (0 );
Int len2 = values. getlength (1 );

For (INT I = 1; I <= len1; I ++)
{
This. textbox1.text + = "/R/N ";
For (Int J = 1; j <= len2; j ++)
{
If (values. getvalue (I, j). tostring (). Length = 0)
This. textbox1.text + = "/T ";
This. textbox1.text + = "/t" + values. getvalue (I, j). tostring ();
}
}
}

App. Quit ();
APP = NULL;
}
}
}

2. Read data to/from dataset and write data to SQL Server:

Using system;
Using system. Collections. Generic;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. text;
Using system. Windows. forms;
Using Microsoft. Office. InterOP. Excel;
Using system. Data. oledb;

Namespace exceldemo
{
Public partial class form2: Form
{
Public form2 ()
{
Initializecomponent ();
}

/// <Summary>
/// Read the Excel document
/// </Summary>
/// <Param name = "path"> file name </param>
/// <Returns> Returns a dataset </returns>
Public dataset exceltods (string path)
{
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Conn. open ();
String strexcel = "";
Oledbdataadapter mycommand = NULL;
Dataset DS = NULL;
Strexcel = "select * from [recovered_sheet1 $]";
Mycommand = new oledbdataadapter (strexcel, strconn );
DS = new dataset ();
Mycommand. Fill (DS );
Return Ds;
}

/// <Summary>
/// Write the Excel document
/// </Summary>
/// <Param name = "path"> file name </param>
// Public bool savefp2toexcel (string path)
//{
// Try
//{
// String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
// Oledbconnection conn = new oledbconnection (strconn );
// Conn. open ();
// System. Data. oledb. oledbcommand cmd = new oledbcommand ();
// Cmd. Connection = conn;
/// Cmd. commandtext = "Update [sheet1 $] Set Name = '2017-01-01 'where employee ID = 'date '";
/// Cmd. executenonquery ();
// For (INT I = 0; I <fp2.sheets [0]. rowcount-1; I ++)
//{
// If (fp2.sheets [0]. cells [I, 0]. Text! = "")
//{
// Cmd. commandtext = "insert into [sheet1 $] (employee ID, name, department, title, date, time) values ('" + fp2.sheets [0]. cells [I, 0]. text + "','" +
// Fp2.sheets [0]. cells [I, 1]. text + "','" + fp2.sheets [0]. cells [I, 2]. text + "','" + fp2.sheets [0]. cells [I, 3]. text +
// "','" + Fp2.sheets [0]. cells [I, 4]. text + "','" + fp2.sheets [0]. cells [I, 5]. text + "')";
// Cmd. executenonquery ();
//}
//}
// Conn. Close ();
// Return true;
//}
// Catch (system. Data. oledb. oledbexception ex)
//{
// System. Diagnostics. Debug. writeline ("Excel writing error:" + ex. Message );
//}
// Return false;
//}

Private void form2_load (Object sender, eventargs E)
{
Dataset DS = exceltods (@ "C:/Documents and Settings/qqq/desktop/002.xls ");
Int x = Ds. Tables. count;
System. Data. datatable dt = new system. Data. datatable ();
DT. Columns. addrange (
New datacolumn [] {

New datacolumn ("col1", typeof (string )),
New datacolumn ("col2", typeof (string )),
New datacolumn ("col3", typeof (string )),
New datacolumn ("col4", typeof (string )),
New datacolumn ("col5", typeof (string )),
New datacolumn ("col6", typeof (string )),
New datacolumn ("col7", typeof (string )),
New datacolumn ("col8", typeof (string )),
New datacolumn ("col9", typeof (string )),
New datacolumn ("col10", typeof (string )),
New datacolumn ("col11", typeof (string )),
New datacolumn ("col12", typeof (string )),
New datacolumn ("col13", typeof (string )),
New datacolumn ("col14", typeof (string ))
});

For (INT I = 0; I <Ds. Tables [0]. Rows. Count; I ++)
{

If (Ds. Tables [0]. Rows [I] [13]. tostring (). Length <= 0)
Continue;
Datarow DR = DT. newrow ();

Dr. itemarray = Ds. Tables [0]. Rows [I]. itemarray;

DT. Rows. Add (DR );

}
This. Maid = DT;
This. Maid = false;

}

}

}