excel导出数据到SQLSERVER 数据库事务处理，文件上传
using cribms.common;
using System.Data.SqlClient;
using cribms.Rulecrib;
using cribms.RuleBase;
using cribms.Entitycrib;
using cribms.EntityBase;
using cribms.PersistBase;
using System.Text;
using Excel;
using System.IO;
using System.Xml
private void Button1_Click(object sender, System.EventArgs e)
{
HttpFileCollection _files = System.Web.HttpContext.Current.Request.Files;
string all_flilename="";
if (_files.Get(1).FileName.Trim() == "")
{
Response.Write("<script language='javascript'>alert('请你选择要上传的文件！');</script>");
return;
}
else
{
string path = Server.MapPath("upload");
//string filename = DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xls";
string filename = "12.xls";
//string all_flilename = path + "\\add_maindept\\" + filename;
all_flilename = path + filename;
_files.Get(1).SaveAs(all_flilename);
}
//string excelPath=File1.Value;
string excelPath=all_flilename;
Int32 FileLength = 0; //记录文件长度变量
HttpPostedFile UpFile = File1.PostedFile; //HttpPostedFile对象，用于读取图象文件属性
FileLength = UpFile.ContentLength; //记录文件长度
if (FileLength<=0)
{ //文件长度为零时
Response.Write("<script language='javascript'>alert('请你选择要上传的文件！');</script>");
return ;
}
//string excelPath=@"D:\crib\11.xls";
//string excelPath=@"D:\11.xls";
bool result = false;
//本地连接】
//string myConnectString = "Server=localhost;Database=crib;uid=sa;pwd=sa;";
string myConnectString =System.Configuration.ConfigurationSettings.AppSettings["dsn"];
SqlConnection objConnection = new SqlConnection(myConnectString);
//创建一个excel应用程序
Excel.Application objApplication = new Excel.Application();
//创建一个工作簿
Excel._Workbook objWorkbook = objApplication.Workbooks.Add(excelPath);
Excel._Worksheet sh = new WorksheetClass();
// try
// {
string strSql = string.Empty;
StringBuilder objBuilder = new StringBuilder();
string mubiaotable = string.Empty;
//如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
objApplication.Visible = false;
objApplication.UserControl = true;
// sheetName为Excel文件中Sheet的名子
string sheetName = string.Empty;
//然后通过它里面Excel.Application,Excel.Workbook,Excel.Worksheet,Excel.Range对像获得一些你想要的信息
for (int i = 1; i<= objWorkbook.Sheets.Count;i++)
{
//取得excel 的工作表
sh = ( Excel._Worksheet)objWorkbook.Sheets[i];
objConnection.Open();
objBuilder.Append(sh.Name);
mubiaotable = objBuilder.ToString();
objBuilder.Append("$");
//excel工作表名
sheetName = objBuilder.ToString();
//sqlserver的表名
// mubiaotable = objBuilder.ToString();
//把excel的工作表读出写入sqlserver表（sql语句经常出错）
//strSql=@"select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\11.xls',pro_newCrib$)";
//执行SQL生成DATASET,然后插入数据.
//strSql =" select * into "+mubiaotable+" from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+excelPath+"',"+sheetName+")";
strSql =" select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+excelPath+"',"+sheetName+")";
CInit cinit=new CInit();
System.Data.DataTable DT = new System.Data.DataTable();
try
{
DT =cinit.Rtdbtable(strSql);
}
catch(System.Exception ex1)
{
Response.Write("<script language='javascript'>alert('"+ex1.Message.ToString()+"');</script>");
return;
// throw new System.Exception(ex1.ToString());
}
SqlConnection Cn=new SqlConnection(ConfigurationSettings.AppSettings["dsn"]);
Cn.Open();
SqlTransaction transaction=Cn.BeginTransaction();//定义事务
int j=0;
try
{ SqlCommand commandinsert=new SqlCommand();
commandinsert.Connection = Cn;
commandinsert.Transaction=transaction;
commandinsert.CommandType = CommandType.Text;
string str="";
int k=0;
for(j=0;j<DT.Rows.Count;j++)
{
// if(Session["fstate"].ToString().Trim()=="")
// {
// strSql="select count(fquy) from pro_newCrib where fquy='"+DT.Rows[j]["fquy"].ToString().Trim()+"'";
// }
// else
// {
// strSql="select count(fquy) from pro_newCrib where fquy='"+DT.Rows[j]["fquy"].ToString().Trim()+"'and FSTATE ='"+Session["fstate"].ToString().Trim()+"'";
// }
// if(Session["fstate"].ToString().Trim()=="")
// {
// str="select count(fquy) from pro_NewCrib where fquy='"+DT.Rows[j]["fquy"].ToString().Trim()+"'";
// }
// else
// {
// str="select count(fquy) from pro_NewCrib where fquy='"+DT.Rows[j]["fquy"].ToString().Trim()+"'and FSTATE='"+Session["fstate"].ToString().Trim()+"'";
// }
if(Session["fstate"].ToString().Trim()=="")
{
str="select count(fquy) from pro_NewCrib where fquy='"+DT.Rows[j]["规格"].ToString().Trim()+"'";
}
else
{
str="select count(fquy) from pro_NewCrib where fquy='"+DT.Rows[j]["规格"].ToString().Trim()+"'and FSTATE='"+Session["fstate"].ToString().Trim()+"'";
}
commandinsert.CommandText = str;
k=(int)commandinsert.ExecuteScalar();
//int k=cinit.RowCountmethod(DT.Rows[j]["fquy"].ToString().Trim(),Session["fstate"].ToString().Trim());
//int k=0;
if(k==0)
{
// if(Session["fstate"].ToString().Trim()!=DT.Rows[j]["FSTATE"].ToString().Trim())
// {
// Response.Write("<script language='javascript'>alert('第"+(j+1)+"行输入的车间信息有错');</script>");
// transaction.Rollback();
// return;
//
// }
// str="insert into pro_newCrib(fkind,fclass,fname,fid,fcpy,fspy,fquy,fpce,fwhe,fsum,fper,fnum,ftime,A,B,C,D,E,F,G,H,I,J,fbow,fbro,fnote,fzl,mulu,yml,eml,FSTATE)"; //汇总表
// str+="values('"+DT.Rows[j]["fkind"].ToString().Trim()+"','"+DT.Rows[j]["fclass"].ToString().Trim()+"','"+DT.Rows[j]["fname"].ToString().Trim()+"','"+DT.Rows[j]["fid"].ToString().Trim()+"','"+DT.Rows[j]["fcpy"].ToString().Trim()+"','"+DT.Rows[j]["fspy"].ToString().Trim()+"','";
// str+=DT.Rows[j]["fquy"].ToString().Trim()+"','"+DT.Rows[j]["fpce"].ToString().Trim()+"','"+DT.Rows[j]["fwhe"].ToString().Trim()+"','"+DT.Rows[j]["fsum"].ToString().Trim()+"','"+DT.Rows[j]["fper"].ToString().Trim()+"','"+DT.Rows[j]["fnum"].ToString().Trim()+"','"+DT.Rows[j]["ftime"].ToString().Trim()+"','";
// str+=DT.Rows[j]["A"].ToString().Trim()+"','"+DT.Rows[j]["B"].ToString().Trim()+"','"+DT.Rows[j]["C"].ToString().Trim()+"','"+DT.Rows[j]["D"].ToString().Trim()+"','"+DT.Rows[j]["E"].ToString().Trim()+"','"+DT.Rows[j]["F"].ToString().Trim()+"','"+DT.Rows[j]["G"].ToString().Trim()+"','"+DT.Rows[j]["H"].ToString().Trim()+"','";
// str+=DT.Rows[j]["I"].ToString().Trim()+"','"+DT.Rows[j]["J"].ToString().Trim()+"','0','0','"+DT.Rows[j]["fnote"].ToString().Trim()+"','"+DT.Rows[j]["fzl"].ToString().Trim()+"','"+DT.Rows[j]["mulu"].ToString().Trim()+"','"+DT.Rows[j]["yml"].ToString().Trim()+"','"+DT.Rows[j]["eml"].ToString().Trim()+"','"+Session["FSTATE"].ToString().Trim()+"')";
//
str="insert into pro_newCrib(fkind,fclass,fname,fid,fcpy,fspy,fquy,fpce,fwhe,fsum,fper,fnum,ftime,A,B,C,D,E,F,G,H,I,J,fbow,fbro,fnote,fzl,mulu,yml,eml,FSTATE)"; //汇总表
str+="values('"+DT.Rows[j]["三级类别"].ToString().Trim()+"','"+DT.Rows[j]["四级类别"].ToString().Trim()+"','"+DT.Rows[j]["名称"].ToString().Trim()+"','"+DT.Rows[j]["物料号"].ToString().Trim()+"','"+DT.Rows[j]["生产厂家"].ToString().Trim()+"','"+DT.Rows[j]["供应商"].ToString().Trim()+"','";
str+=DT.Rows[j]["规格"].ToString().Trim()+"','"+DT.Rows[j]["单价"].ToString().Trim()+"','"+DT.Rows[j]["存放位置"].ToString().Trim()+"','"+DT.Rows[j]["进货数量"].ToString().Trim()+"','"+DT.Rows[j]["进货人"].ToString().Trim()+"','"+DT.Rows[j]["目前库存"].ToString().Trim()+"','"+DT.Rows[j]["进货日期"].ToString().Trim()+"','";
str+=DT.Rows[j]["A"].ToString().Trim()+"','"+DT.Rows[j]["B"].ToString().Trim()+"','"+DT.Rows[j]["C"].ToString().Trim()+"','"+DT.Rows[j]["D"].ToString().Trim()+"','"+DT.Rows[j]["E"].ToString().Trim()+"','"+DT.Rows[j]["F"].ToString().Trim()+"','"+DT.Rows[j]["G"].ToString().Trim()+"','"+DT.Rows[j]["H"].ToString().Trim()+"','";
str+=DT.Rows[j]["I"].ToString().Trim()+"','"+DT.Rows[j]["J"].ToString().Trim()+"','0','0','"+DT.Rows[j]["最低库存"].ToString().Trim()+"','"+DT.Rows[j]["总量"].ToString().Trim()+"','"+DT.Rows[j]["目录"].ToString().Trim()+"','"+DT.Rows[j]["一级类别"].ToString().Trim()+"','"+DT.Rows[j]["二级类别"].ToString().Trim()+"','"+Session["FSTATE"].ToString().Trim()+"')";
commandinsert.CommandText = str;
commandinsert.ExecuteNonQuery();
// str="insert into pro_newCrib1(fkind,fclass,fname,fid,fcpy,fspy,fquy,fpce,fwhe,fsum,fper,fnum,ftime,A,B,C,D,E,F,G,H,I,J,fbow,fbro,fnote,fzl,mulu,yml,eml,FSTATE)"; //汇总表
// str+="values('"+DT.Rows[j]["fkind"].ToString().Trim()+"','"+DT.Rows[j]["fclass"].ToString().Trim()+"','"+DT.Rows[j]["fname"].ToString().Trim()+"','"+DT.Rows[j]["fid"].ToString().Trim()+"','"+DT.Rows[j]["fcpy"].ToString().Trim()+"','"+DT.Rows[j]["fspy"].ToString().Trim()+"','";
// str+=DT.Rows[j]["fquy"].ToString().Trim()+"','"+DT.Rows[j]["fpce"].ToString().Trim()+"','"+DT.Rows[j]["fwhe"].ToString().Trim()+"','"+DT.Rows[j]["fsum"].ToString().Trim()+"','"+DT.Rows[j]["fper"].ToString().Trim()+"','"+DT.Rows[j]["fnum"].ToString().Trim()+"','"+DT.Rows[j]["ftime"].ToString().Trim()+"','";
// str+=DT.Rows[j]["A"].ToString().Trim()+"','"+DT.Rows[j]["B"].ToString().Trim()+"','"+DT.Rows[j]["C"].ToString().Trim()+"','"+DT.Rows[j]["D"].ToString().Trim()+"','"+DT.Rows[j]["E"].ToString().Trim()+"','"+DT.Rows[j]["F"].ToString().Trim()+"','"+DT.Rows[j]["G"].ToString().Trim()+"','"+DT.Rows[j]["H"].ToString().Trim()+"','";
// str+=DT.Rows[j]["I"].ToString().Trim()+"','"+DT.Rows[j]["J"].ToString().Trim()+"','0','0','"+DT.Rows[j]["fnote"].ToString().Trim()+"','"+DT.Rows[j]["fzl"].ToString().Trim()+"','"+DT.Rows[j]["mulu"].ToString().Trim()+"','"+DT.Rows[j]["yml"].ToString().Trim()+"','"+DT.Rows[j]["eml"].ToString().Trim()+"','"+Session["FSTATE"].ToString().Trim()+"')";
//
str="insert into pro_newCrib1(fkind,fclass,fname,fid,fcpy,fspy,fquy,fpce,fwhe,fsum,fper,fnum,ftime,A,B,C,D,E,F,G,H,I,J,fbow,fbro,fnote,fzl,mulu,yml,eml,FSTATE)"; //汇总表
str+="values('"+DT.Rows[j]["三级类别"].ToString().Trim()+"','"+DT.Rows[j]["四级类别"].ToString().Trim()+"','"+DT.Rows[j]["名称"].ToString().Trim()+"','"+DT.Rows[j]["物料号"].ToString().Trim()+"','"+DT.Rows[j]["生产厂家"].ToString().Trim()+"','"+DT.Rows[j]["供应商"].ToString().Trim()+"','";
str+=DT.Rows[j]["规格"].ToString().Trim()+"','"+DT.Rows[j]["单价"].ToString().Trim()+"','"+DT.Rows[j]["存放位置"].ToString().Trim()+"','"+DT.Rows[j]["进货数量"].ToString().Trim()+"','"+DT.Rows[j]["进货人"].ToString().Trim()+"','"+DT.Rows[j]["目前库存"].ToString().Trim()+"','"+DT.Rows[j]["进货日期"].ToString().Trim()+"','";
str+=DT.Rows[j]["A"].ToString().Trim()+"','"+DT.Rows[j]["B"].ToString().Trim()+"','"+DT.Rows[j]["C"].ToString().Trim()+"','"+DT.Rows[j]["D"].ToString().Trim()+"','"+DT.Rows[j]["E"].ToString().Trim()+"','"+DT.Rows[j]["F"].ToString().Trim()+"','"+DT.Rows[j]["G"].ToString().Trim()+"','"+DT.Rows[j]["H"].ToString().Trim()+"','";
str+=DT.Rows[j]["I"].ToString().Trim()+"','"+DT.Rows[j]["J"].ToString().Trim()+"','0','0','"+DT.Rows[j]["最低库存"].ToString().Trim()+"','"+DT.Rows[j]["总量"].ToString().Trim()+"','"+DT.Rows[j]["目录"].ToString().Trim()+"','"+DT.Rows[j]["一级类别"].ToString().Trim()+"','"+DT.Rows[j]["二级类别"].ToString().Trim()+"','"+Session["FSTATE"].ToString().Trim()+"')";
commandinsert.CommandText = str;
//SqlCommand commandinsert1=new SqlCommand(str,Cn);
//commandinsert1.Transaction=transaction;
commandinsert.ExecuteNonQuery();
if(Session["fstate"].ToString().Trim()!=DT.Rows[j]["所在车间"].ToString().Trim())
{
Response.Write("<script language='javascript'>alert('第"+(j+2)+"行输入的车间信息有错！');</script>");
transaction.Rollback();
return;
}
if(DT.Rows[j]["规格"].ToString().Trim()=="")
{
Response.Write("<script language='javascript'>alert('第"+(j+2)+"行输入的规格信息不能为空！');</script>");
transaction.Rollback();
return;
}
}
else
{
// str="update pro_newCrib set fkind='"+DT.Rows[j]["fkind"].ToString().Trim()+"',fclass='"+DT.Rows[j]["fclass"].ToString().Trim()+"',fname='"+DT.Rows[j]["fname"].ToString().Trim()+"',fid='"+DT.Rows[j]["fid"].ToString().Trim()+"',fcpy='"+DT.Rows[j]["fcpy"].ToString().Trim()+"',fspy='"+DT.Rows[j]["fspy"].ToString().Trim()+"',"; //汇总表
// str+="fquy='"+DT.Rows[j]["fquy"].ToString().Trim()+"',fpce='"+DT.Rows[j]["fpce"].ToString().Trim()+"',fwhe='"+DT.Rows[j]["fwhe"].ToString().Trim()+"',fsum=fsum+'"+Convert.ToInt32(DT.Rows[j]["fsum"].ToString().Trim())+"',fper='"+DT.Rows[j]["fper"].ToString().Trim()+"',fnum=fnum+'"+Convert.ToInt32(DT.Rows[j]["fnum"].ToString().Trim())+"',ftime='"+DT.Rows[j]["ftime"].ToString().Trim()+"',";
// str+="A='"+DT.Rows[j]["A"].ToString().Trim()+"',B='"+DT.Rows[j]["B"].ToString().Trim()+"',C='"+DT.Rows[j]["C"].ToString().Trim()+"',D='"+DT.Rows[j]["D"].ToString().Trim()+"',E='"+DT.Rows[j]["E"].ToString().Trim()+"',F='"+DT.Rows[j]["F"].ToString().Trim()+"',G='"+DT.Rows[j]["G"].ToString().Trim()+"',H='"+DT.Rows[j]["H"].ToString().Trim()+"',";
// str+="I='"+DT.Rows[j]["I"].ToString().Trim()+"',J='"+DT.Rows[j]["J"].ToString().Trim()+"',fbow=fbow+'"+Convert.ToInt32(DT.Rows[j]["fbow"].ToString().Trim())+"',fbro=fbro+'"+Convert.ToInt32(DT.Rows[j]["fbro"].ToString().Trim())+"',fnote=fnote+'"+Convert.ToInt32(DT.Rows[j]["fnote"].ToString().Trim())+"',fzl=fzl+'"+Convert.ToInt32(DT.Rows[j]["fzl"].ToString().Trim())+"',mulu='"+DT.Rows[j]["mulu"].ToString().Trim()+"',yml='"+DT.Rows[j]["yml"].ToString().Trim()+"',eml='"+DT.Rows[j]["eml"].ToString().Trim()+"',FSTATE='"+Session["FSTATE"].ToString().Trim()+"' where fquy='"+DT.Rows[j]["fquy"].ToString().Trim()+"'and FSTATE='"+Session["FSTATE"].ToString().Trim()+"'";
//
str="update pro_newCrib set fkind='"+DT.Rows[j]["三级类别"].ToString().Trim()+"',fclass='"+DT.Rows[j]["四级类别"].ToString().Trim()+"',fname='"+DT.Rows[j]["名称"].ToString().Trim()+"',fid='"+DT.Rows[j]["物料号"].ToString().Trim()+"',fcpy='"+DT.Rows[j]["生产厂家"].ToString().Trim()+"',fspy='"+DT.Rows[j]["供应商"].ToString().Trim()+"',"; //汇总表
str+="fquy='"+DT.Rows[j]["规格"].ToString().Trim()+"',fpce='"+DT.Rows[j]["单价"].ToString().Trim()+"',fwhe='"+DT.Rows[j]["存放位置"].ToString().Trim()+"',fsum=fsum+'"+Convert.ToInt32(DT.Rows[j]["进货数量"].ToString().Trim())+"',fper='"+DT.Rows[j]["进货人"].ToString().Trim()+"',fnum=fnum+'"+Convert.ToInt32(DT.Rows[j]["目前库存"].ToString().Trim())+"',ftime='"+DT.Rows[j]["进货日期"].ToString().Trim()+"',";
str+="A='"+DT.Rows[j]["A"].ToString().Trim()+"',B='"+DT.Rows[j]["B"].ToString().Trim()+"',C='"+DT.Rows[j]["C"].ToString().Trim()+"',D='"+DT.Rows[j]["D"].ToString().Trim()+"',E='"+DT.Rows[j]["E"].ToString().Trim()+"',F='"+DT.Rows[j]["F"].ToString().Trim()+"',G='"+DT.Rows[j]["G"].ToString().Trim()+"',H='"+DT.Rows[j]["H"].ToString().Trim()+"',";
str+="I='"+DT.Rows[j]["I"].ToString().Trim()+"',J='"+DT.Rows[j]["J"].ToString().Trim()+"',fbow=fbow+'"+Convert.ToInt32(DT.Rows[j]["外借数量"].ToString().Trim())+"',fbro=fbro+'"+Convert.ToInt32(DT.Rows[j]["报损数量"].ToString().Trim())+"',fnote='"+Convert.ToInt32(DT.Rows[j]["最低库存"].ToString().Trim())+"',fzl=fzl+'"+Convert.ToInt32(DT.Rows[j]["总量"].ToString().Trim())+"',mulu='"+DT.Rows[j]["目录"].ToString().Trim()+"',yml='"+DT.Rows[j]["一级类别"].ToString().Trim()+"',eml='"+DT.Rows[j]["二级类别"].ToString().Trim()+"',FSTATE='"+Session["FSTATE"].ToString().Trim()+"' where fquy='"+DT.Rows[j]["规格"].ToString().Trim()+"'and FSTATE='"+Session["FSTATE"].ToString().Trim()+"'";
//SqlCommand commandinsert2=new SqlCommand(str,Cn);
// commandinsert2.Transaction=transaction;
commandinsert.CommandText = str;
commandinsert.ExecuteNonQuery();
// str="insert into pro_newCrib1(fkind,fclass,fname,fid,fcpy,fspy,fquy,fpce,fwhe,fsum,fper,fnum,ftime,A,B,C,D,E,F,G,H,I,J,fbow,fbro,fnote,fzl,mulu,yml,eml,FSTATE)"; //汇总表
// str+="values('"+DT.Rows[j]["fkind"].ToString().Trim()+"','"+DT.Rows[j]["fclass"].ToString().Trim()+"','"+DT.Rows[j]["fname"].ToString().Trim()+"','"+DT.Rows[j]["fid"].ToString().Trim()+"','"+DT.Rows[j]["fcpy"].ToString().Trim()+"','"+DT.Rows[j]["fspy"].ToString().Trim()+"','";
// str+=DT.Rows[j]["fquy"].ToString().Trim()+"','"+DT.Rows[j]["fpce"].ToString().Trim()+"','"+DT.Rows[j]["fwhe"].ToString().Trim()+"','"+DT.Rows[j]["fsum"].ToString().Trim()+"','"+DT.Rows[j]["fper"].ToString().Trim()+"','"+DT.Rows[j]["fnum"].ToString().Trim()+"','"+DT.Rows[j]["ftime"].ToString().Trim()+"','";
// str+=DT.Rows[j]["A"].ToString().Trim()+"','"+DT.Rows[j]["B"].ToString().Trim()+"','"+DT.Rows[j]["C"].ToString().Trim()+"','"+DT.Rows[j]["D"].ToString().Trim()+"','"+DT.Rows[j]["E"].ToString().Trim()+"','"+DT.Rows[j]["F"].ToString().Trim()+"','"+DT.Rows[j]["G"].ToString().Trim()+"','"+DT.Rows[j]["H"].ToString().Trim()+"','";
// str+=DT.Rows[j]["I"].ToString().Trim()+"','"+DT.Rows[j]["J"].ToString().Trim()+"','0','0','"+DT.Rows[j]["fnote"].ToString().Trim()+"','"+DT.Rows[j]["fzl"].ToString().Trim()+"','"+DT.Rows[j]["mulu"].ToString().Trim()+"','"+DT.Rows[j]["yml"].ToString().Trim()+"','"+DT.Rows[j]["eml"].ToString().Trim()+"','"+Session["FSTATE"].ToString().Trim()+"')";
//
str="insert into pro_newCrib1(fkind,fclass,fname,fid,fcpy,fspy,fquy,fpce,fwhe,fsum,fper,fnum,ftime,A,B,C,D,E,F,G,H,I,J,fbow,fbro,fnote,fzl,mulu,yml,eml,FSTATE)"; //汇总表
str+="values('"+DT.Rows[j]["三级类别"].ToString().Trim()+"','"+DT.Rows[j]["四级类别"].ToString().Trim()+"','"+DT.Rows[j]["名称"].ToString().Trim()+"','"+DT.Rows[j]["物料号"].ToString().Trim()+"','"+DT.Rows[j]["生产厂家"].ToString().Trim()+"','"+DT.Rows[j]["供应商"].ToString().Trim()+"','";
str+=DT.Rows[j]["规格"].ToString().Trim()+"','"+DT.Rows[j]["单价"].ToString().Trim()+"','"+DT.Rows[j]["存放位置"].ToString().Trim()+"','"+DT.Rows[j]["进货数量"].ToString().Trim()+"','"+DT.Rows[j]["进货人"].ToString().Trim()+"','"+DT.Rows[j]["目前库存"].ToString().Trim()+"','"+DT.Rows[j]["进货日期"].ToString().Trim()+"','";
str+=DT.Rows[j]["A"].ToString().Trim()+"','"+DT.Rows[j]["B"].ToString().Trim()+"','"+DT.Rows[j]["C"].ToString().Trim()+"','"+DT.Rows[j]["D"].ToString().Trim()+"','"+DT.Rows[j]["E"].ToString().Trim()+"','"+DT.Rows[j]["F"].ToString().Trim()+"','"+DT.Rows[j]["G"].ToString().Trim()+"','"+DT.Rows[j]["H"].ToString().Trim()+"','";
str+=DT.Rows[j]["I"].ToString().Trim()+"','"+DT.Rows[j]["J"].ToString().Trim()+"','0','0','"+DT.Rows[j]["最低库存"].ToString().Trim()+"','"+DT.Rows[j]["总量"].ToString().Trim()+"','"+DT.Rows[j]["目录"].ToString().Trim()+"','"+DT.Rows[j]["一级类别"].ToString().Trim()+"','"+DT.Rows[j]["二级类别"].ToString().Trim()+"','"+Session["FSTATE"].ToString().Trim()+"')";
//SqlCommand commandinsert3=new SqlCommand(str,Cn);
// commandinsert3.Transaction=transaction;
commandinsert.CommandText = str;
commandinsert.ExecuteNonQuery();
if(Session["fstate"].ToString().Trim()!=DT.Rows[j]["所在车间"].ToString().Trim())
{
Response.Write("<script language='javascript'>alert('第"+(j+2)+"行输入的车间信息有错！');</script>");
transaction.Rollback();
return;
}
if(DT.Rows[j]["规格"].ToString().Trim()=="")
{
Response.Write("<script language='javascript'>alert('第"+(j+2)+"行输入的规格信息不能为空！');</script>");
transaction.Rollback();
return;
}
}
}
transaction.Commit();
Response.Write("<script language='javascript'>alert('EXCEL数据导入成功！');</script>");
this.DateShow();
}
catch(System.Exception ex1)
{
Response.Write("<script language='javascript'>alert('第"+(j+2)+"行错误为"+ex1.Message.ToString()+"');</script>");
//Response.Write("<script language='javascript'>alert('第"+(j+1)+"行错误为"+ex1.ToString()+"');</script>");
transaction.Rollback();
return;
// throw new System.Exception(ex1.ToString());
}
Cn.Close();
//
objBuilder.Remove(0,objBuilder.Length);
}
result = true;
objApplication.Quit();
// }
// catch(System.Exception ex)
// {
// throw new System.Exception(ex.Message.ToString());
// }
// //释放进程
// finally
// {
System.Runtime.InteropServices.Marshal.ReleaseComObject(objApplication);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sh);
objApplication = null;
objWorkbook = null;
sh = null;
GC.Collect();
objConnection.Close();
objConnection = null;
// }
}
}
