C# 用sql语句保存excel
时间：2021-07-01 10:21:17
帮助过：45人阅读
static void SaveExcel(
string filePath,DataSet dt)
{
bool hasHeaders =
false;
string HDR = hasHeaders ?
"Yes" :
"No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf(
‘.‘)).ToLower() ==
".xlsx")
strConn =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
";Extended Properties=\"Excel 12.0;HDR=" + HDR +
";IMEX=0\"";
else
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
";Extended Properties=\"Excel 8.0;HDR=" + HDR +
";IMEX=0\"";
OleDbConnection cn =
new OleDbConnection(strConn);
cn.Open();
string sqlCreate;
OleDbCommand cmd;
//班组-时间-上卫生纸-下卫生纸-传导层-上表层-下表层-两侧-底层-弹性腰围-覆盖层-防水膜-前腰贴-左右腰贴-魔术扣-
sqlCreate =
"CREATE TABLE 断料([时间] VarChar,[班组] INTEGER,[上卫生纸] INTEGER,[下卫生纸] INTEGER,[传导层] INTEGER,[上表层] INTEGER,[下表层] INTEGER,[两侧] INTEGER,[底层] INTEGER,[弹性腰围] INTEGER,[覆盖层] INTEGER,[防水膜] INTEGER,[前腰贴] INTEGER,[左右腰贴] INTEGER,[魔术扣] INTEGER)";
//创建工作表
cmd =
new OleDbCommand(sqlCreate, cn);
cmd.ExecuteNonQuery();
sqlCreate =
"CREATE TABLE 接头([时间] VarChar,[班组] INTEGER,[上卫生纸] INTEGER,[下卫生纸] INTEGER,[传导层] INTEGER,[上表层] INTEGER,[下表层] INTEGER,[两侧] INTEGER,[底层] INTEGER,[弹性腰围] INTEGER,[覆盖层] INTEGER,[防水膜] INTEGER,[前腰贴] INTEGER,[左右腰贴] INTEGER,[魔术扣] INTEGER)";
//创建工作表
cmd =
new OleDbCommand(sqlCreate, cn);
cmd.ExecuteNonQuery();
sqlCreate =
"CREATE TABLE 接料([时间] VarChar,[班组] INTEGER,[上卫生纸] INTEGER,[下卫生纸] INTEGER,[传导层] INTEGER,[上表层] INTEGER,[下表层] INTEGER,[两侧] INTEGER,[底层] INTEGER,[弹性腰围] INTEGER,[覆盖层] INTEGER,[防水膜] INTEGER,[前腰贴] INTEGER,[左右腰贴] INTEGER,[魔术扣] INTEGER)";
//创建工作表
cmd =
new OleDbCommand(sqlCreate, cn);
cmd.ExecuteNonQuery();
foreach (DataRow row
in dt.Tables[
0].Rows)
{
//添加数据
int i =
2;
cmd.CommandText =
string.Format(
"INSERT INTO 断料 VALUES(‘{13}‘,{14}, {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})",
row.ItemArray[0+i], row.ItemArray[i +
1], row.ItemArray[i +
2], row.ItemArray[i +
3], row.ItemArray[i +
4], row.ItemArray[i +
5], row.ItemArray[i +
6], row.ItemArray[i +
7], row.ItemArray[i +
8], row.ItemArray[i +
9], row.ItemArray[i +
10], row.ItemArray[i +
11], row.ItemArray[i +
12], row.ItemArray[
0], row.ItemArray[
1]);
cmd.ExecuteNonQuery();
//添加数据
i =
2 +
32;
cmd.CommandText =
string.Format(
"INSERT INTO 接头 VALUES(‘{13}‘,{14}, {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})",
row.ItemArray[0 + i], row.ItemArray[i +
1], row.ItemArray[i +
2], row.ItemArray[i +
3], row.ItemArray[i +
4], row.ItemArray[i +
5], row.ItemArray[i +
6], row.ItemArray[i +
7], row.ItemArray[i +
8], row.ItemArray[i +
9], row.ItemArray[i +
10], row.ItemArray[i +
11], row.ItemArray[i +
12], row.ItemArray[
0], row.ItemArray[
1]);
cmd.ExecuteNonQuery();
i =
2 +
32+
32;
cmd.CommandText =
string.Format(
"INSERT INTO 接料 VALUES(‘{13}‘,{14}, {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})",
row.ItemArray[0 + i], row.ItemArray[i +
1], row.ItemArray[i +
2], row.ItemArray[i +
3], row.ItemArray[i +
4], row.ItemArray[i +
5], row.ItemArray[i +
6], row.ItemArray[i +
7], row.ItemArray[i +
8], row.ItemArray[i +
9], row.ItemArray[i +
10], row.ItemArray[i +
11], row.ItemArray[i +
12], row.ItemArray[
0], row.ItemArray[
1]);
cmd.ExecuteNonQuery();
}
cn.Close();
}
C# 用sql语句保存excel
标签：create headers blog dbconnect ring end xlsx val provider
