c#实现excel表数据导入到sql数据库指定表中
我现在有数据库A，数据库A内有已建好的表B，我想用C#实现excel表数据导入到数据库A中指定的表B中，这个如何实现？最好有代码加注释。谢谢
[解决办法]
基本数据库操作，连接数据库A，然后引用office的dll去操作excel，读取每个格的内容，insert到数据库，如果你不会操作数据库，参考http://www.cnblogs.com/dongdonghuihui/archive/2009/08/13/1545446.html
excel参考http://www.cnblogs.com/MR_ke/archive/2010/03/02/1676210.html
[解决办法]
刚做了这个内容，也贴下代码：
//string srcPath = System.Windows.Forms.Application.StartupPath + @"\UserInfo.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + srcPath + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable xlsTable = new DataTable();
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(xlsTable);
conn.Close();
//1、删除TB_TEMP表中的数据
sql.DeleteTbTemp();
//2、把xlsTable数据写入到TB_TEMP中。
try
{
using (OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["HZZP_JT2012"].ConnectionString))
{
connection.Open();
DataSet ds = new DataSet();
OleDbCommand command = new OleDbCommand("SELECT * FROM TB_TEMP", connection);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.SelectCommand = command;
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.Fill(ds, "TB_TEMP");
DataRow[] rows = xlsTable.Select();
foreach (DataRow row in rows)
{
int user_id;
if (int.TryParse(row["USER_ID"].ToString(), out user_id))
{
ds.Tables[0].Rows.Add(new object[] { row["USER_ID"], row["USER_NAME"], row["PHONE"], row["VILLAGE"], row["ADDRESS"], row["USER_MEMO"] });
}
}
try
{
if (ds.HasChanges())
{
adapter.Update(ds, "TB_TEMP");
}
}
catch (Exception err)
{
MessageBox.Show(this, err.Message, "保存失败！", MessageBoxButtons.OK);
}
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
[解决办法]
曾经写的代码
private void importAttachToSQl() { int rowsAffected = 0; string updateStr = ""; string cmd ="Select " + sqlColumnsForImport + " from " + sqlTableName; DataTable thisDataTable = new DataTable(); try { // DbConnection.isServerConnection(currentDatabase); using (SqlConnection thiscon = DbConnection.createConn()) { thiscon.Open(); SqlDataAdapter thisDataAdapter = new SqlDataAdapter(cmd, thiscon); thisDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //thisDataAdapter.Fill(thisDataTable); //--------------------------merge------合并---------------- //thisDataTable.Tables[sqlTableName].Merge(excelDataSet.Tables[excelSheetName], false, MissingSchemaAction.Ignore); dataGridview1.DataSource = new DataView( thisDataTable ); SqlCommand thisCmd = thiscon.CreateCommand(); thisCmd.CommandTimeout = 180; foreach (DataRow thisRow in tb_csvTable.Rows) { string[] fields = { "补发", "其它扣款", "扣住宿费", "其它" }; foreach (string field in fields) { if (thisRow[field] == null || thisRow[field].ToString().Trim().Length == 0) thisRow[field] = 0; } //assignmentForNull( fields ); updateStr = string.Format("update salary set 补发={0},其它扣款={1},扣住宿费={2},其它={3},附加类备注='{4}' where 工号='{5}'", thisRow["补发"], thisRow["其它扣款"], thisRow["扣住宿费"], thisRow["其它"], thisRow["附加类备注"], thisRow["工号"]); thisCmd.CommandText = updateStr; rowsAffected += thisCmd.ExecuteNonQuery(); } MessageBox.Show("影响行数: " + rowsAffected + "行 ", "导入成功", MessageBoxButtons.OK, MessageBoxIcon.Information); //导入完成后重置以下选项 codeShared_csvTable.bl_checkExistId = false; codeShared_csvTable.bl_checkRepeatedId = false; } } catch (Exception exc) { MessageBox.Show(exc.ToString(), "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error); } }