关于excel导入更新
//将指定路径的Excel导入到数据库中做更新,通过orderid
public string ImportToUpdateOrder(string path) {
string _SelectFromExcelOrder = "SELECT [工单号],[联系电话],[用户称呼],[投诉地点],[工单投诉内容],[投诉时间],[工单时限],[最终处理结果],[品牌],[客户重要程度]"
+ ",[客户感知],[具体现象],[投诉发生的场所],[投诉发生的场所详细],[区域],[镇区],[经度],[纬度],[所用小区]"
+ ",[移动信号强度],[电信C网信号强度],[联通G网信号强度],[设备类型]"
+ " ,[无需跟进原因],[投诉的原因],[详细原因1],[详细原因2],[详细原因3],[解决进度],[解决进度描述],[处理人员工单号],[是否属难点投诉],[投诉方式],[重复投诉],[重复投诉历史工单],[是否超时],[涉及的新站],[涉及的直放站],[规划时间],[当前建设进度],[方案实施部门],[是否人为引起],[代维公司],[是否现场测试],[故障定位],[备注]"
+ " FROM [data$] ";
string oleDbConnectionString = string.Empty;
string extensionName = Path.GetExtension(path);
if (extensionName.CompareTo(".xls") == 0)
oleDbConnectionString = String.Format(ExcelUtility._oleDbConnectionStringExcel8import, path);
else if (extensionName.CompareTo(".xlsx") == 0)
oleDbConnectionString = String.Format(ExcelUtility._oleDbConnectionStringExcel12, path);
else
return "文件格式错误";
OleDbConnection oleDbConnection = null;
OleDbDataReader oleDbDataReader = null;
SqlConnection sqlConnection = null;
Order order = new Order();
int i = 0;//更新记录数
string nerverUpdateList = string.Empty; //没有更新的工单号。
try {
//连接和读取Excel数据
oleDbConnection = ExcelUtility.GetOleDbConnection(oleDbConnectionString);
ExcelUtility.OpenOleDbConnection(oleDbConnection);
OleDbCommand oleDbCommand = ExcelUtility.GetOleDbCommand(_SelectFromExcelOrder, oleDbConnection);
oleDbDataReader = ExcelUtility.GetOleDbDataReader(oleDbCommand);
//连接数据库,开始事务
sqlConnection = DBUtility.GetSqlConnection();
DBUtility.OpenSqlConnection(sqlConnection);
SqlTransaction sqlTransaction = DBUtility.BeginTransaction(sqlConnection);
try {
//读取Excel数据,增加到数据库
while (oleDbDataReader.Read()) {
if (this.UpdateOrderByOrderId(order, sqlTransaction) > 0) {
i++;
}
else {
nerverUpdateList += order.orderId + ",";
}
//下一次可能没有给这两个值重新赋值,所以这里要清空。
order.compareMT = "";
order.compareMU = "";
}
DBUtility.CommitSqlTransaction(sqlTransaction);
}
catch (Exception ex1) {
try {
DBUtility.RollbackSqlTransaction(sqlTransaction);
}
catch (Exception ex2) {
return String.Format("事务回滚出错信息 Type:{0}.Message:{1}", ex2.GetType(), ex2.Message);
}
return "更新失败:" + order.orderId + "--:" + ex1.Message;
}
}
catch (Exception ex) {
return "更新失败:" + order.orderId + "--:" + ex.Message;
}
finally {
ExcelUtility.CloseOleDbDataReader(oleDbDataReader);
ExcelUtility.ColseOleDbConnection(oleDbConnection);
DBUtility.CloseSqlConnection(sqlConnection);
}
if (!string.IsNullOrEmpty(nerverUpdateList)) {
return "成功更新" + i + "条记录。" + "未做更新的工单:" + nerverUpdateList;
}
else {
return "成功更新" + i + "条记录。";
}
}
在运行是报错:至少有一个参数没有被制定值
我打断点试了是oleDbDataReader 获取不到值!
帮忙解决一下!