求大神解答！c#用sql语句读取更新Excel表，然后close（）子窗口整个程序就会直接关闭，没有报错

最新推荐文章于 2021-03-18 19:17:44 发布

quanliv1

最新推荐文章于 2021-03-18 19:17:44 发布

阅读量435

点赞数

文章标签：求解

本文链接：https://blog.csdn.net/quanliv1/article/details/79653547

版权

c#用sql语句读取更新Excel表，然后close（）子窗口整个程序就会直接关闭，没有报错。而且在别的电脑上能够运行，而且本电脑也重装过系统跟office也不行。

private void baocun_Click(object sender, EventArgs e)
{

//如果插入有浇次
if (_IsOrNolistTaskModel != null)
{//_IsOrNolistTaskModel里的任务列表就是添加的浇次任务
_allTaskModel.AddRange(_IsOrNolistTaskModel);
}
if (_allTaskModel == null)
{
_listTaskModel = null;
}
else
{
_listTaskModel = new List<TaskModel>(_allTaskModel);
}
if (pourCCSpeed != null)
staticLuciNum.ccSpeed = new Dictionary<string, double>(pourCCSpeed);
if (_listTaskModel != null)
{
//获得浇次信息
var newCastInform = (from n in _listTaskModel
group n by n.jiaoci into g
select new
{
jiao = g.Key,
castMachin = g.ToList().First().equipment,
steel = g.ToList().First().gangzhong,
start = g.OrderBy(n => n.StartDate).First().StartDate,
times = g.ToList().Count
}).ToList();
//获得无插入删除之前的浇次信息
var oldCastInform = getCastNameFromTable();
//找出插入的浇次
var insertTasks = newCastInform.Where(n => !oldCastInform.Exists(t => t.Equals(n.jiao))).ToList();
//找出更新的
var updateTasks = newCastInform.Where(n => oldCastInform.Exists(t => t.Equals(n.jiao))).ToList();
//找出删除的浇次
var deleteTasks = oldCastInform.Where(n => !newCastInform.Exists(t => t.jiao.Equals(n))).ToList();
//更改浇次Excel表
//插入信息
List<string> operatExcelStr = new List<string>();
if (insertTasks.Count > 0)
{
foreach (var insert in insertTasks)
{

string cmdStr = string.Format(@"insert into [Cast$] values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','plan')",
insert.jiao, insert.castMachin, "SLAB", insert.start, "HCH",
insert.steel, insert.times, "是");
if (!operatExcelStr.Contains(cmdStr))
operatExcelStr.Add(cmdStr);
}
}
//更新
if (updateTasks.Count > 0)
{
foreach (var update in updateTasks)
{
string cmdStr1 = "update [Cast$] set StartTime= '" + update.start + "',times= '" + update.times + "' where cast= '" + update.jiao + "'";
if (!operatExcelStr.Contains(cmdStr1))
operatExcelStr.Add(cmdStr1);
}
}
//删除
if (deleteTasks.Count > 0)
{
readExcel.DeleteRow(deleteTasks);
}
if (operatExcelStr.Count > 0)
{
readExcel.UpdateExcel(operatExcelStr);
}
}
Close();
}

这是子窗口的一个保存按钮里的代码，按下按钮就保存更新到excel里然后关闭窗口，

但运行到close就会整个程序关掉。

下面是读取excel的程序

public class readerExcel

{
private List<string> _listcastMach = new List<string> { "CC501", "CC502", "CC503" };
//静态字段获得当前路径
private static string filePath = System.Environment.CurrentDirectory + "\\test.xlsx";
private static string currentPath = System.Environment.CurrentDirectory;
private string connectionString =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}/test.xlsx;Extended Properties=Excel 12.0",
currentPath);
private string historyconnectionString =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}/history.xlsx;Extended Properties=Excel 12.0",
currentPath);
//链接Excel字符串
#region 读取excel
public DataSet readDataTable(string Address)
{
string _connectionString =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}/test.xlsx;Extended Properties=Excel 12.0",
currentPath);
//链接数据库
if (Address == "text")
{
_connectionString =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}/test.xlsx;Extended Properties=Excel 12.0",
currentPath);
}
if (Address == "history")
{
_connectionString =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}/history.xlsx;Extended Properties=Excel 12.0",
currentPath);
}

using (OleDbConnection conn = new OleDbConnection(_connectionString))
{
try
{
conn.Open();//打开数据库
List<string> listTableName = new List<string>();
//将每一个表的名字放入dataTable中
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获得表名存放在list中
for (int i = 0; i != dt.Rows.Count; i++)
{
listTableName.Add((string)dt.Rows[i]["Table_Name"].ToString());
}
using (OleDbCommand cmd = conn.CreateCommand())//创建sql语言命令
{
DataSet dataset = new DataSet();//语言返回的dataset;
foreach (string strSheetName in listTableName)//迭代列表名
{
string strExcel = string.Format("select * from [{0}]", strSheetName);
cmd.CommandText = strExcel;//读取表名语句
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dataset, strSheetName);//将读取的表存放在dataset中strSheetName是对应表名
}
return dataset;
}
}
catch (Exception e)
{

}
return null;
}

}

更新excel

public bool UpdateExcel(List<string> ListstrExcel)
{
bool update = false;
OleDbConnection conn = new OleDbConnection(connectionString);

try
{

conn.Open();//打开数据库
int num = 0;
foreach (string strExcel in ListstrExcel)
{
OleDbCommand cmd = new OleDbCommand(strExcel, conn);
int row = cmd.ExecuteNonQuery();//如果更新数据row>0
num += row;
}
if (num == ListstrExcel.Count)
{
update = true;

return update;
}
}
catch
{

}
finally
{
conn.Close();
}

return update;

}