if (FileUpload1.HasFile)
{
string xlsFilePath = FileUpload1.PostedFile.FileName.ToString();//取得FileUpload中的文件完整路径,IE8有安全级别,可能得到的只是文件名,可以上网查询解决办法,就可以得到完整路径了
//把EXCEL当成数据库来查询得到数据
string connectionString;
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsFilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection conn = new OleDbConnection(connectionString);
String strQuery = "SELECT * FROM [Sheet1$]"; //可以更改工作表名称
OleDbDataAdapter da = new OleDbDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Sheet1");
DataTable dt = ds.Tables[0];
conn.Close();
if (dt.Rows.Count > 0)
{
//查询表ObjectMappingTable,看是否有数据,有的话全部删除了再进行插入操作
//SqlOperate.OperationTable("","")这个方法在一个类文件中,“集插入,删除,修改一体的操作数据库数据的方法”这篇文章写的就是这个方法
DataTable Table = SqlOperate.GetDataTable("select * from ObjectMappingTable");
if (Table.Rows.Count > 0)
{
SqlOperate.OperationTable("delete from ObjectMappingTable ", "ObjectMappingTable");
}
//循环插入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlOperate.OperationTable("insert into ObjectMappingTable(FactoryName,FactoryCode,DeviceName,DeviceCode,MeasuringObjectName,MeasuringObjectCode,InstrumentType,InstrumentCode,mailingAddress,SerialNum,CheckTemPoint) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')", "ObjectMappingTable");
}
}
else
{
this.Page.RegisterStartupScript("script_" + StringCom.GetRndString(20), "<script language=javascript>$.jBox.tip('没有数据要上传!','error');</script>");
}
}
else
{
this.Page.RegisterStartupScript("script_" + StringCom.GetRndString(20), "<script language=javascript>$.jBox.tip('请选择要上传的文件!','error');</script>");
}
{
string xlsFilePath = FileUpload1.PostedFile.FileName.ToString();//取得FileUpload中的文件完整路径,IE8有安全级别,可能得到的只是文件名,可以上网查询解决办法,就可以得到完整路径了
//把EXCEL当成数据库来查询得到数据
string connectionString;
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsFilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection conn = new OleDbConnection(connectionString);
String strQuery = "SELECT * FROM [Sheet1$]"; //可以更改工作表名称
OleDbDataAdapter da = new OleDbDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Sheet1");
DataTable dt = ds.Tables[0];
conn.Close();
if (dt.Rows.Count > 0)
{
//查询表ObjectMappingTable,看是否有数据,有的话全部删除了再进行插入操作
//SqlOperate.OperationTable("","")这个方法在一个类文件中,“集插入,删除,修改一体的操作数据库数据的方法”这篇文章写的就是这个方法
DataTable Table = SqlOperate.GetDataTable("select * from ObjectMappingTable");
if (Table.Rows.Count > 0)
{
SqlOperate.OperationTable("delete from ObjectMappingTable ", "ObjectMappingTable");
}
//循环插入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlOperate.OperationTable("insert into ObjectMappingTable(FactoryName,FactoryCode,DeviceName,DeviceCode,MeasuringObjectName,MeasuringObjectCode,InstrumentType,InstrumentCode,mailingAddress,SerialNum,CheckTemPoint) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')", "ObjectMappingTable");
}
}
else
{
this.Page.RegisterStartupScript("script_" + StringCom.GetRndString(20), "<script language=javascript>$.jBox.tip('没有数据要上传!','error');</script>");
}
}
else
{
this.Page.RegisterStartupScript("script_" + StringCom.GetRndString(20), "<script language=javascript>$.jBox.tip('请选择要上传的文件!','error');</script>");
}