OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel(*.xls)|*.xls|所有文件|*.*";
if (ofd.ShowDialog() == DialogResult.OK)
{
FileInfo fileInfo = new FileInfo(ofd.FileName);
string path = fileInfo.FullName;
string connExcel = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
try
{
OleDbConnection ole = new OleDbConnection(connExcel);
ole.Open();
DataTable dataTable = ole.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tableName = dataTable.Rows[0][2].ToString().Trim();
tableName = "[" + tableName.Replace("'", "") + "]";
string query = "SELECT * FROM " + tableName;
DataSet dataSet = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, connExcel);
adapter.Fill(dataSet,"articles");
//将获得的表插入到数据库中
DataTable sqlTable = new DataTable();
SqlConnection conn=new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=bookshop;Integrated Security=True");
SqlDataAdapter sqlAdapter = new SqlDataAdapter(@"select UserId,UserName,Author,PublishDate,AuthorAddress,[Content],StorePath from articles", conn);
SqlCommandBuilder sbCB = new SqlCommandBuilder(sqlAdapter);
sqlAdapter.Fill(sqlTable);
foreach (DataRow dataRow in dataSet.Tables["article"].Rows)
{
DataRow row1 = sqlTable.NewRow();
row1["UserId"] = dataRow["UserId"];
row1["UserName"] = dataRow["UserName"];
row1["Author"] = dataRow["Author"];
row1["PublishDate"] = dataRow["PublishDate"];
row1["AuthorAddress"] = dataRow["AuthorAddress"];
row1["Content"] = dataRow["Content"];
row1["StorePath"] = dataRow["StorePath"];
sqlTable.Rows.Add(row1);
}
txtFileName.Text = "新插入" + sqlTable.Rows.Count + "条记录";
sqlAdapter.Update(sqlTable);
ole.Close();
}
catch(Exception ex)
{
throw new Exception( ex.Message);
}