Import EXCEL Data to dataset, and import data to dataset

Import EXCEL Data to dataset, and import data to dataset

I. Prerequisites

1. micorosoft office2007

2. VS2010 and Oracle 11

Ii. Interface

3. Internal code

(1) Obtain database connections and define global variables

Private static string connString = System. Configuration. ConfigurationSettings. deleettings ["connStr"];
DataSet dTable;

(2) Select an Excel file to import dataset

If (openFileDialog1.ShowDialog () = DialogResult. OK)
{
CtlPath. Text = openFileDialog1.FileName;
ExceltoDataSet (ctlPath. Text );
}

(3) Loading Excel file data

Public DataSet ExceltoDataSet (string path)
{
MessageBox. Show ("retrieving data... please wait ");
//
String strConn = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source =" + path + "; Extended Properties = 'excel 12.0; HRD = Yes; IMEX = 1 ';";
OleDbConnection conn = new OleDbConnection (strConn );
Conn. Open ();
System. Data. DataTable schemaTable = conn. GetOleDbSchemaTable (System. Data. OleDb. OleDbSchemaGuid. Tables, null );

String tableName = schemaTable. Rows [0] [2]. ToString (). Trim ();

String strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
StrExcel = "Select * from [" + tableName + "]";
MyCommand = new OleDbDataAdapter (strExcel, strConn );
Ds = new DataSet ();
Try
{
MyCommand. Fill (ds, tableName );
}
Catch (Exception ex)
{
MessageBox. Show (ex. Message );
}
DTable = ds;

If (ds! = Null)
{
Button2.Visible = true;


}
Return ds;


}

(4) display imported data

DataTable dt = dTable. Tables [0];

Try {

For (int I = 0; I> 0; I ++)

{

Dt. Rows. Remove (dt. Rows [I]);

}

DataGridView1.DataSource = dt;

} Catch (Exception ex)

{

Throw ex;

}

(5) import data to the database

 

Note: I didn't use a thread for this program, so when importing a large amount of data, the winform is suspended, but the program is still struggling. Please wait for the resurrection.

IV,
1. Before Import

2. After the import, click show data

5. Source Code download http://pan.baidu.com/s/1sj4U2i5

 

 

 

 

C # write data from data imported to DataSet in EXCEL

Protected void button#click (object sender, EventArgs e)
{
Try
{
String fileName = DateTime. Now. ToString ("yyyyMMddhhmmss") + "." + MsgBox. getFileLastName (File1.Value );
File1.PostedFile. SaveAs (System. Web. HttpContext. Current. Server. MapPath ("~ /Excel/"+ fileName ));

String conn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + Server. MapPath ("~ /Excel/"+ fileName) +"; Extended Properties = Excel 8.0 ";

OleDbConnection thisconnection = new OleDbConnection (conn );
Thisconnection. Open ();
String SQL = "select DISTINCT (brand) from [Sheet1 $]";
OleDbDataAdapter mycommand = new OleDbDataAdapter (SQL, thisconnection );
DataSet ds = new DataSet ();
Mycommand. Fill (ds );
For (int I = 0; I <ds. Tables [0]. Rows. Count; I ++)
{
DataTable dt2 = ConString. querySql ("select * from protype where name = '" + ds. tables [0]. rows [I] ["OEM"]. toString (). replace ("'", "") + "' and languageid = '" + Session ["language"] + "'"). tables [0];
If (dt2.Rows. Count <1)
{
ConString. executeSql ("insert protype (name, languageid) values ('" + ds. tables [0]. rows [I] ["OEM"]. toString (). replace ("'", "") + "', '" + Session ["language"] + "')");
}
}
Try
{
DataTable dt = ConString. QuerySql ("select * from protype"). Tables [0];
For (int I = 0; I & lt ...... remaining full text>

Use C # To import data from excel to dataset

Cn = New ADODB. Connection
Str = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & Me. ofdSelectExcel. FileName & "; Extended Properties =" "Excel 8.0; HDR = Yes """
Cn. Open (str)
'Get all SHEET names
CbSheet. Properties. Items. Clear ()
Dim rs1 As New ADODB. Recordset
Rs1 = cn. OpenSchema (ADODB. SchemaEnum. adSchemaTables)
While Not rs1.EOF
CbSheet. Properties. Items. Add (rs1.Fields ("TABLE_NAME"). Value)
Rs1.MoveNext ()
End While
Cn. Close ()

//// Obtain the SHEET Name of the selected EXCEL file.

An error occurred because your SHEET name in EXCEL was changed. The default value is SHEET1 $