110,538
社区成员
发帖
与我相关
我的任务
分享求教DataSet赋值并写入Excel文件中
小弟的代码是通过OLEDB连接读取Excel文件，然后将其中的数据存入DataSet中，DataSet中的数据（A列到R列）传输给一个方法，返回一个字符串，字符串拆分成数组，然后将每一个数据元素保存到DataSet中，再将DataSet写入Excel的S列中。
请问第35行这样给DataSet赋值对吗？赋值后怎么将DataSet写入Excel的S列中，谢谢啦！
Excel的表结构如下图所示：
请问第35行这样给DataSet赋值对吗？赋值后怎么将DataSet写入Excel的S列中，谢谢啦！
private void openFile_Click(object sender, RoutedEventArgs e)
{
string path = "C:/Users/shuke/Desktop/Order Calculation20170707/calculation verification.xlsx";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 12.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
foreach (DataRow mDr in ds.Tables[0].Rows)
{
float tubeNomId = calTubeNomIdDf(mDr, ds);
float tubeNomOd = calTubeNomOdDf(mDr, ds);
float tubeNomMinWt = calTubeNomMinWtDf(mDr, ds);
float tubeNomAvgWt = calTubeNomAvgWtDf(mDr, ds);
float tubeAvgWt = calTubeAvgWtDf(tubeNomMinWt, tubeNomAvgWt, mDr, ds);
float tubeAvgOd = calTubeAvgOdDf(tubeNomId, tubeNomOd, tubeAvgWt, mDr, ds);
float tubeOdAfterSurplus = calTubeOdAfterSurplusDf(tubeAvgOd, mDr, ds);
float tubeWtAfterSurplus = calTubeWtAfterSurplusDf(tubeAvgWt, mDr, ds);
bool bousMaterial = chooseMaterialDf(mDr, ds);
float rLMin = calRLMinDf(mDr, ds);
float rLMax = calRLMaxDf(mDr, ds);
float tL = calTLDf(mDr, ds);
float fL = calFLDf(mDr, ds);
float pcs = calPcsDf(mDr, ds);
string materialGrade = findMGDf(mDr, ds);
string forgedHollowCom = createForgedHollowCom(tubeOdAfterSurplus, tubeWtAfterSurplus, tubeAvgOd, tubeAvgWt, bousMaterial, rLMin, rLMax, tL, fL, pcs, materialGrade);
string[] forgedHollowElements = forgedHollowCom.Split('|');
mDr["Ingot/billet size"] = forgedHollowElements[0];
}
}
Excel的表结构如下图所示：
...全文
请发表友善的回复…
发表回复
i_shrine 2017-07-11
- 打赏
- 举报
列名不可以带/和空格[/quote]
多谢！问题已经圆满解决了！
i_shrine 2017-07-11
- 打赏
- 举报
多谢！问题已经圆满解决了！
i_shrine 2017-07-10
- 打赏
- 举报
我写的表名应该没问题啊 DataTable dt = ds.Tables[0]; string sqlUpdate = "update "+ dt +" set "; 调试为什么一直报错呢？System.Data.OleDb.OleDbException: 'Syntax error in UPDATE statement.'？谢谢啦！
private void openFile_Click(object sender, RoutedEventArgs e)
{
string path = "C:/Users/shuke.liu/Desktop/Order Calculation/Order Calculation20170710/calculation verification.xlsx";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 12.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
string[] colName = new string[] { "Ingot/billet size", "Ingot/billet weight", "Ingot/billet PCS", "Piercing die", "Piercing mandrel", "Pierced length", "Forged OD hot", "Forged WT hot", "Forged L hot", "Forged OD cold", "Forged WT cold", "Forged L cold", "Forge mandrel", "Forge hammer", "Process", "Yield"};
string currID = "";
foreach (DataRow mDr in ds.Tables[0].Rows)
{
currID = mDr["Item"].ToString();
float tubeNomId = calTubeNomIdDf(mDr, ds);
float tubeNomOd = calTubeNomOdDf(mDr, ds);
float tubeNomMinWt = calTubeNomMinWtDf(mDr, ds);
float tubeNomAvgWt = calTubeNomAvgWtDf(mDr, ds);
float tubeAvgWt = calTubeAvgWtDf(tubeNomMinWt, tubeNomAvgWt, mDr, ds);
float tubeAvgOd = calTubeAvgOdDf(tubeNomId, tubeNomOd, tubeAvgWt, mDr, ds);
float tubeOdAfterSurplus = calTubeOdAfterSurplusDf(tubeAvgOd, mDr, ds);
float tubeWtAfterSurplus = calTubeWtAfterSurplusDf(tubeAvgWt, mDr, ds);
bool bousMaterial = chooseMaterialDf(mDr, ds);
float rLMin = calRLMinDf(mDr, ds);
float rLMax = calRLMaxDf(mDr, ds);
float tL = calTLDf(mDr, ds);
float fL = calFLDf(mDr, ds);
float pcs = calPcsDf(mDr, ds);
string materialGrade = findMGDf(mDr, ds);
string forgedHollowCom = createForgedHollowCom(tubeOdAfterSurplus, tubeWtAfterSurplus, tubeAvgOd, tubeAvgWt, bousMaterial, rLMin, rLMax, tL, fL, pcs, materialGrade);
string[] forgedHollowElements = forgedHollowCom.Split('|');
DataTable dt = ds.Tables[0];
string sqlUpdate = "update "+ dt +" set ";
for (int col = 0; col <= 15; col++)
{
if (col == 0)
{
sqlUpdate += colName[col] + "='" + forgedHollowElements[col] + "'";
}
else
{
sqlUpdate += "," + colName[col] + "='" + forgedHollowElements[col] + "'";
}
}
//UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
sqlUpdate += " where Item='"+currID+"'";
OleDbCommand oleComm = new OleDbCommand(sqlUpdate, conn);
//Executes an SQL statement against the Connection and returns the number of rows affected.
if (oleComm.ExecuteNonQuery() <= 0)
{
}
oleComm.Dispose();
}
}
秋的红果实 2017-07-10
- 打赏
- 举报
1、sql语句可以小写。
需要update语句，我那是给你举例如何用循环拼接set。。。字符串，完整点的如下（还需要自己动手哦）
string sqlUpdate = " update 表名 set ";
for (int col = 0; col <= 15; col++)
{
if (col == 0)
{
sqlUpdate += colName[col] + "='" + forgedHollowElements[col] + "'";
}
else
{
sqlUpdate += "," + colName[col] + "='" + forgedHollowElements[col] + "'";
}
}
另外，你excel表，增加的是Item列，那么
sqlUpdate += " where ID='"+currID+"'";
改为==>
sqlUpdate += " where Item='"+currID+"'";
2、需要单引号
秋的红果实 2017-07-10
- 打赏
- 举报
表名不是table1，是[sheet1$]
string sqlUpdate = "update [sheet1$] set ";
for (int col = 0; col <= 15; col++)
另外，字段名需要用[]扩起来，就像你的表名一样[sheet1$]
改成
string[] colName = new string[] { "[Ingot/billet size]", "[Ingot/billet weight]", "[Ingot/billet PCS]", "[Piercing die]", "[Piercing mandrel]", "[Pierced length]", "[Forged OD hot]", "[Forged WT hot]", "[Forged L hot]", "[Forged OD cold]", "[Forged WT cold]", "[Forged L cold]", "[Forge mandrel]", "[Forge hammer]", "[Process]", "[Yield]" };
i_shrine 2017-07-10
- 打赏
- 举报
谢谢你的指教，我还有一些问题想请教，因为我编写的程序运行时候报错了。
1. 我看了SQL UPDATE语句里，SET和WHERE都需要大写，在C#里小写可以吗？还有通过SET语句给DateTable赋值不需要用UPDATE吗？像这样 UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值，我运行程序时出现了以下的报警：
2. 请问这一句写的正确吗？currID前面要加单引号的吧？
sqlUpdate += " where ID='"+currID+"'";
修改后的代码如下：
private void openFile_Click(object sender, RoutedEventArgs e)
{
string path = "C:/Users/shuke.liu/Desktop/Order Calculation/Order Calculation20170710/calculation verification.xlsx";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 12.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
string[] colName = new string[] { "Ingot/billet size", "Ingot/billet weight", "Ingot/billet PCS", "Piercing die", "Piercing mandrel", "Pierced length", "Forged OD hot", "Forged WT hot", "Forged L hot", "Forged OD cold", "Forged WT cold", "Forged L cold", "Forge mandrel", "Forge hammer", "Process", "Yield"};
string currID = "";
foreach (DataRow mDr in ds.Tables[0].Rows)
{
currID = mDr["Item"].ToString();
float tubeNomId = calTubeNomIdDf(mDr, ds);
float tubeNomOd = calTubeNomOdDf(mDr, ds);
float tubeNomMinWt = calTubeNomMinWtDf(mDr, ds);
float tubeNomAvgWt = calTubeNomAvgWtDf(mDr, ds);
float tubeAvgWt = calTubeAvgWtDf(tubeNomMinWt, tubeNomAvgWt, mDr, ds);
float tubeAvgOd = calTubeAvgOdDf(tubeNomId, tubeNomOd, tubeAvgWt, mDr, ds);
float tubeOdAfterSurplus = calTubeOdAfterSurplusDf(tubeAvgOd, mDr, ds);
float tubeWtAfterSurplus = calTubeWtAfterSurplusDf(tubeAvgWt, mDr, ds);
bool bousMaterial = chooseMaterialDf(mDr, ds);
float rLMin = calRLMinDf(mDr, ds);
float rLMax = calRLMaxDf(mDr, ds);
float tL = calTLDf(mDr, ds);
float fL = calFLDf(mDr, ds);
float pcs = calPcsDf(mDr, ds);
string materialGrade = findMGDf(mDr, ds);
string forgedHollowCom = createForgedHollowCom(tubeOdAfterSurplus, tubeWtAfterSurplus, tubeAvgOd, tubeAvgWt, bousMaterial, rLMin, rLMax, tL, fL, pcs, materialGrade);
string[] forgedHollowElements = forgedHollowCom.Split('|');
string sqlUpdate = "set ";
for (int col = 0; col <= 15; col++)
{
if (col == 0)
{
sqlUpdate += colName[col] + "='" + forgedHollowElements[col] + "'";
}
else
{
sqlUpdate += "," + colName[col] + "='" + forgedHollowElements[col] + "'";
}
}
//UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
sqlUpdate += " where ID='"+currID+"'";
OleDbCommand oleComm = new OleDbCommand(sqlUpdate, conn);
//Executes an SQL statement against the Connection and returns the number of rows affected.
if (oleComm.ExecuteNonQuery() <= 0)
{
}
oleComm.Dispose();
}
}
新的Excel表结构：
walkuere 2017-07-10
- 打赏
- 举报
列名不可以带/和空格
i_shrine 2017-07-10
- 打赏
- 举报
我命名的字符串数组，string[] forgedHollowElements = forgedHollowCom.Split('|'); 也就是都是字符串，我运行了，然后得到的sqlUpdate是这样的，请问这个格式对吗？谢谢啦！
"update table1 set
Ingot/billet size='600',
Ingot/billet weight='4775',
Ingot/billet PCS='30',
Piercing die='600',
Piercing mandrel='400',
Pierced length='2151',
Forged OD hot='515.7',
Forged WT hot='71.9',
Forged L hot='6068',
Forged OD cold='510',
Forged WT cold='71.1',
Forged L cold='6001',
Forge mandrel='372',
Forge hammer='C',
Process='G',
Yield='1415'
where Item='A001'"
秋的红果实 2017-07-10
- 打赏
- 举报
//DataTable dt = ds.Tables[0]; //不是datatable，而是数据库的表
string sqlUpdate = "update [sheet1$] set ";
for (int col = 0; col <= 15; col++)
还有，需要关闭command和connection
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
myCommand.dispose();
strConn.close();
walkuere 2017-07-10
- 打赏
- 举报
你的字段肯定不全是字符串，字符串才可以 = ' xxx ' 否则就直接=
秋的红果实 2017-07-09
- 打赏
- 举报
1、ID就是能唯一标识该行的键，你插入item或者你原来表里不是有个ID列么，这列让他自增，1,2，然后向下拉动鼠标……
string[] colName = new string[] { "Ingot/billet size", "Ingot/billet weight", "Ingot/billet PCS", "Piercing die", "Piercing mandrel", "Pierced length", "Forged OD hot", "Forged WT hot", "Forged L hot", "Forged OD cold", "Forged WT cold", "Forged L cold", "Forge mandrel", "Forge hammer", "Process", "Yield"};
string currID="";
foreach (DataRow mDr in ds.Tables[0].Rows)
{
currID=该行第一列的值; //更新条件where后的值
float tubeNomId = calTubeNomIdDf(mDr, ds);
2、oleComm.ExecuteNonQuery() <= 0表示更新失败，里面你可以提示用户，某某行更新失败
i_shrine 2017-07-09
- 打赏
- 举报
private void openFile_Click(object sender, RoutedEventArgs e)
{
string path = "C:/Users/shuke/Desktop/Order Calculation20170707/calculation verification.xlsx";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 12.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
string[] colName = new string[] { "Ingot/billet size", "Ingot/billet weight", "Ingot/billet PCS", "Piercing die", "Piercing mandrel", "Pierced length", "Forged OD hot", "Forged WT hot", "Forged L hot", "Forged OD cold", "Forged WT cold", "Forged L cold", "Forge mandrel", "Forge hammer", "Process", "Yield"};
foreach (DataRow mDr in ds.Tables[0].Rows)
{
float tubeNomId = calTubeNomIdDf(mDr, ds);
float tubeNomOd = calTubeNomOdDf(mDr, ds);
float tubeNomMinWt = calTubeNomMinWtDf(mDr, ds);
float tubeNomAvgWt = calTubeNomAvgWtDf(mDr, ds);
float tubeAvgWt = calTubeAvgWtDf(tubeNomMinWt, tubeNomAvgWt, mDr, ds);
float tubeAvgOd = calTubeAvgOdDf(tubeNomId, tubeNomOd, tubeAvgWt, mDr, ds);
float tubeOdAfterSurplus = calTubeOdAfterSurplusDf(tubeAvgOd, mDr, ds);
float tubeWtAfterSurplus = calTubeWtAfterSurplusDf(tubeAvgWt, mDr, ds);
bool bousMaterial = chooseMaterialDf(mDr, ds);
float rLMin = calRLMinDf(mDr, ds);
float rLMax = calRLMaxDf(mDr, ds);
float tL = calTLDf(mDr, ds);
float fL = calFLDf(mDr, ds);
float pcs = calPcsDf(mDr, ds);
string materialGrade = findMGDf(mDr, ds);
string forgedHollowCom = createForgedHollowCom(tubeOdAfterSurplus, tubeWtAfterSurplus, tubeAvgOd, tubeAvgWt, bousMaterial, rLMin, rLMax, tL, fL, pcs, materialGrade);
string[] forgedHollowElements = forgedHollowCom.Split('|');
string sqlUpdate = "set";
for (int col = 0; col <= 15; col++)
{
if (col == 0)
{
sqlUpdate += colName[col] + "='" + forgedHollowElements[col] + "'";
}
else
{
sqlUpdate += "," + colName[col] + "='" + forgedHollowElements[col] + "'";
}
}
sqlUpdate += " where ID=该行的序号";//我要给每一行设置序号吗？现在我在Excel第一列插入了表头名为Item的一列，那么是不是where ID就可以等于该列的内容？
OleDbCommand oleComm = new OleDbCommand(sqlUpdate, conn);
if (oleComm.ExecuteNonQuery() <= 0)
{
//这里是空语句？
}
oleComm.Dispose();
}
秋的红果实 2017-07-08
- 打赏
- 举报
更正：
for(int col=18;col<=33;col++)
==>
for(int col=0;col<=15;col++)
以行为单位更新，一行（改变16个单元格的值）一次update
string[] colName = new string[] { "Col1", "Col2", "Col3", "Col4",..."Col16" };
存放从S列开头的表头名
秋的红果实 2017-07-08
- 打赏
- 举报
哦，这样就明白了，
用循环语句，如下
string[] colName = new string[] { "Col1", "Col2", "Col3", "Col4",..."Col16" };
foreach (DataRow mDr in ds.Tables[0].Rows)
{
float tubeNomId = calTubeNomIdDf(mDr, ds);
float tubeNomOd = calTubeNomOdDf(mDr, ds);
float tubeNomMinWt = calTubeNomMinWtDf(mDr, ds);
float tubeNomAvgWt = calTubeNomAvgWtDf(mDr, ds);
float tubeAvgWt = calTubeAvgWtDf(tubeNomMinWt, tubeNomAvgWt, mDr, ds);
float tubeAvgOd = calTubeAvgOdDf(tubeNomId, tubeNomOd, tubeAvgWt, mDr, ds);
float tubeOdAfterSurplus = calTubeOdAfterSurplusDf(tubeAvgOd, mDr, ds);
float tubeWtAfterSurplus = calTubeWtAfterSurplusDf(tubeAvgWt, mDr, ds);
bool bousMaterial = chooseMaterialDf(mDr, ds);
float rLMin = calRLMinDf(mDr, ds);
float rLMax = calRLMaxDf(mDr, ds);
float tL = calTLDf(mDr, ds);
float fL = calFLDf(mDr, ds);
float pcs = calPcsDf(mDr, ds);
string materialGrade = findMGDf(mDr, ds);
string forgedHollowCom = createForgedHollowCom(tubeOdAfterSurplus, tubeWtAfterSurplus, tubeAvgOd, tubeAvgWt, bousMaterial, rLMin, rLMax, tL, fL, pcs, materialGrade);
string[] forgedHollowElements = forgedHollowCom.Split('|');
string sqlUpdate = "set ";
for(int col=18;col<=33;col++)
{
if(col==18)
{
sqlUpdate+=colName[col]+"='"+forgedHollowElements[col]+"'";
}
else
{
sqlUpdate+=","+colName[col]+"='"+forgedHollowElements[col]+"'";
}
}
sqlUpdate += " where ID=该行的序号";
OleDbCommand oleComm=new OleDbCommand(sqlUpdate,con);
if(oleComm.ExecuteNonQuery()<=0)
{
}
oleComm.Dispose();
}
i_shrine 2017-07-08
- 打赏
- 举报
先谢谢你回答我的问题！
这个字符串forgedHollowCom拆分给forgedHollowElements数组，该数组内共有16个元素，这16个元素赋值给Excel表格的S2:AH2（表格格式如下图所示），我为了简化问题，因此只说了其中的第一个元素赋值给S列，请问这样的数组forgedHollowElements赋值给Excel表固定区域该怎么做？写16个诸如update [sheet1$] set [Ingot/billet size] =计算值 where ID=该行的序号这样的语句吗？谢谢啦！
秋的红果实 2017-07-08
- 打赏
- 举报
你的思路是对的，将数据读取到dataset，运算后再写入的excel的S列
赋值后怎么样将DataTable更新回原来的Excel文件呀？
==>
就是更新excel表，update [sheet1$] set [Ingot/billet size] =计算值 where ID=该行的序号
你表里的ID应该是主键，如excel表的自增列1，2,3,4,5……
至于35行，没看懂你的需求：“返回一个字符串，字符串拆分成数组，然后将每一个数据元素保存到DataSet”？
你35行mDr["Ingot/billet size"] = 处在
foreach (DataRow mDr in ds.Tables[0].Rows)
{
里面，表示某一行的S列
那么，你拆分出的数组元素，都存到这行的S列，还是下面行的S列？
i_shrine 2017-07-08
- 打赏
- 举报
还有如果我想看点这方面的基础书该看哪一本为好，我看了很多网上代码都不太懂，感觉基础知识太缺乏了
i_shrine 2017-07-08
- 打赏
- 举报
谢谢版主，新手请谅解，网上搜到的资料太杂搞得云里雾里的。
请问我的第35行这样给DataTable的某行某列赋值能行吗？赋值后怎么样将DataTable更新回原来的Excel文件呀？谢谢啦！
exception92 2017-07-08
- 打赏
- 举报
myCommand.Fill(ds, "table1");
-》这句是给ds赋值的。这些基础的实在不明白动手查一下而已。
