string strDirectory = string. Empty;
strDirectory = System.IO.Directory.GetCurrentDirectory() + "\\" + "Filename.xlsx";
string Connection String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strDirectory + "; Extended Properties = \"Excel 12.0;HDR=YES;IMEX=1\"";
using (OleDbConnection conn = new OleDbConnection(Connection String))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTableOleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
DataRow schemaRow = schemaTable. Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheet + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
daexcel.Fill(dt);
conn.Close();
}
2) 在更新到表格之前对数据表中的数据进行一些操作。
string strsqlst = string. Empty;
using (SqlConnection sqlConn = new SqlConnection(Connectionstring))
{
sqlConn.Open();
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
sb.AppendLine("DataTable content:");
foreach (DataRow row in dt.Rows)
{
if (row.ItemArray[0].ToString() == "")
break;
strsqlst = "Update table Set col1= " + row.ItemArray[4].ToString() + " ,col2= " + row.ItemArray[5].ToString() + " where <Condition>'";
cmd = new SqlCommand(strsqlst, sqlConn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
sqlConn.Close();
}
MERGE
之外,似乎没有什么用处。 - Drag and Dropvar tempData = dt.AsEnumerable().Select (row => new { col1 = row.Field<string>("ColumnName"), col2 = row.Field<string>("ColumnName"), conditionID = row.Field<decimal>("ColumnName") }).ToList();
,您可以将其插入到临时表中,并简单地执行以下操作:UPDATE DestinationTable SET ( DestinationTable.Column = TempTable.Column, ... ) FROM DestinationTable Dest INNER JOIN TempTable Temp ON TempTable.SomethingID = Temp.ID WHERE DestinationTable.Other condition
。 - Drag and Drop