private void Addmanydata_click (object sender, RoutedEventArgs e)
{
OpenFileDialog OpenFileDialog = new OpenFileDialog ();
Openfiledialog.filter = "Excel file |*.xls";
if ((bool) Openfiledialog.showdialog ())
{
FileInfo FileInfo = new FileInfo (openfiledialog.filename);
string filePath = Fileinfo.fullname;
String connexcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FilePath + "; Extended Properties=excel 8.0 ";
using (OleDbConnection oledbconn = new OleDbConnection (Connexcel))
{
Oledbconn.open ();
Get Excel Table
DataTable dt = oledbconn.getoledbschematable (OleDbSchemaGuid.Tables, NULL);
Get table name for Excel table
String tablename = dt. ROWS[0][2]. ToString (). Trim ();
Remove Space
TableName = "[" + Tablename.replace ("'", "") + "]";
Use SQL statements to get data from Excel files
string query = @ "Select number, name, charitable labor, electronic process internship, operating system, computer composition, numerical analysis, network equipment and integration, dynamic website Development Experiment Week, dynamic website development, evenly divided, ranked from"; + tablename;
DataSet DataSet = new DataSet ();
using (OleDbCommand Oledbcomm = Oledbconn.createcommand ())
{
Oledbcomm.commandtext = query;
OleDbDataAdapter oleadapter = new OleDbDataAdapter (OLEDBCOMM);
Oleadapter.fill (DataSet);
}
String connstr = "Data source=heshuhua-pc;initial catalog=rsmsystem;integrated security=true";
Use SqlBulkCopy to BULK INSERT data
using (sqlbulkcopy SQLBC = new SqlBulkCopy (CONNSTR))
{
SQLBC. DestinationTableName = "T_stuscore";
SQLBC. Columnmappings.add ("School Number", "Stunum"), the first parameter corresponds to the column name in the database,
The second parameter corresponds to the column name of the corresponding table in the database
SQLBC. Columnmappings.add ("School Number", "stunum");
SQLBC. Columnmappings.add ("name", "Stuname");
SQLBC. Columnmappings.add ("Charitable labor", "activity");
SQLBC. Columnmappings.add ("Electronic Craft Practice", "elecact");
SQLBC. Columnmappings.add ("Operating System", "Oprationsystem");
SQLBC. Columnmappings.add ("Computer composition", "computermaded");
SQLBC. Columnmappings.add ("Numerical Analysis", "dataanalyze");
SQLBC. Columnmappings.add ("Network Equipment and Integration", "network");
SQLBC. Columnmappings.add ("Dynamic website Development Experiment Week", "Webweek");
SQLBC. Columnmappings.add ("Dynamic website development", "Webmake");
SQLBC. Columnmappings.add ("Evenly Divided", "Avscore");
SQLBC. Columnmappings.add ("Rank", "stupaiming");
SQLBC. WriteToServer (Dataset.tables[0]);
MessageBox.Show ("Data import successful!") ");
}
}
}
}
