导入EXCEL到数据库并删除EXCEL文件(死亡历险)

 

<asp:TemplateColumn HeaderText="導入">
<ItemTemplate>
<a href=<%#"import.aspx?filename="+DataBinder.Eval(Container.DataItem,"filename").ToString()+"&filemappath="+DataBinder.Eval(Container.DataItem,"filemappath").ToString()+"&filename2="+DataBinder.Eval(Container.DataItem,"filename2").ToString()+"&p_id="+DataBinder.Eval(Container.DataItem,"p_id").ToString()%>>導入</a>
</ItemTemplate>
</asp:TemplateColumn>

 

---------------------------------------------

 

<%@ import namespace="System" %>
<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.OleDb" %>
<%@ import namespace="System.IO" %>
<%@ import namespace="System.Configuration" %>
<%@ import namespace="System.Data.SqlClient" %>

 

<script language=C# runat=server>
private void Page_Load(Object sender, EventArgs e)
{
String filename=Request.Params["filename"];
String filemappath=Request.Params["filemappath"];
String filename2=Request.Params["filename2"];
String p_id=Request.Params["p_id"];
if(filename == "銷樣明細.xls")
{

 

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../uploadfile/"+filename2+"") + ";" +
"Extended Properties=Excel 8.0;";
try
{
OleDbConnection oleDbConnection = new OleDbConnection(sConnectionString);
oleDbConnection.Open();

 

//获取excel表
DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,   null);

 

//获取sheet名，其中[0][1]...[N]:   按名称排列的表单元素
string tableName = dataTable.Rows[0][2].ToString().Trim();
tableName = "[" + tableName.Replace(" ", "") + "]";
//利用SQL语句从Excel文件里获取数据

 

string query = "SELECT InvoiceNo,出貨日期,客戶,製單號碼,PO,目的港,ITEMNO,數量,單位,產品名稱,生產單位,件數,材積,貨櫃尺寸,貨櫃號碼 FROM " + tableName;

 

DataSet   dataset   =   new   DataSet();
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query,sConnectionString);

 

oleAdapter.Fill(dataset, "xymx");
SqlConnection sqlcon = new SqlConnection("server=localhost;database=produce;UID=sa;pwd=123456");
sqlcon.Open();
DataTable   dataTable1   =   new   DataTable();
SqlDataAdapter   sqlDA1   =   new   SqlDataAdapter(@"SELECT InvoiceNo,shipment_date,customer,orderno,PO,haven,ITEMNO,number,unit,product,department,piece,mt,hgsize,hgno,inputdate FROM xymx", sqlcon);
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
sqlDA1.Fill(dataTable1);

 

foreach   (DataRow   dataRow   in   dataset.Tables["xymx"].Rows)
{
//sql里数据dataRow1
DataRow dataRow1 = dataTable1.NewRow();

 

dataRow1["InvoiceNo"]   =   dataRow["InvoiceNo"];
dataRow1["shipment_date"]   =   dataRow["出貨日期"];
dataRow1["customer"]   =   dataRow["客戶"];
dataRow1["orderno"]   =   dataRow["製單號碼"];
dataRow1["PO"]   =   dataRow["PO"];
dataRow1["haven"]   =   dataRow["目的港"];
dataRow1["ITEMNO"]   =   dataRow["ITEMNO"];
dataRow1["number"]   =   dataRow["數量"];
dataRow1["unit"]   =   dataRow["單位"];
dataRow1["product"]   =   dataRow["產品名稱"];
dataRow1["department"]   =   dataRow["生產單位"];
dataRow1["piece"]   =   dataRow["件數"];
dataRow1["mt"]   =   dataRow["材積"];
dataRow1["hgsize"]   =   dataRow["貨櫃尺寸"];
dataRow1["hgno"]   =   dataRow["貨櫃號碼"];
dataRow1["inputdate"]   =   DateTime.Now.Date.ToShortDateString();
dataTable1.Rows.Add(dataRow1);
}
Response.Write("導入成功!"+"<br>");
Response.Write("新插入   " + dataset.Tables[0].Rows.Count + "   条记录"+"<br>");
Response.Write("总共   " + dataTable1.Rows.Count.ToString() + "   条记录"+"<br>");

 

//System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
//int intAsciiCode = (int)asciiEncoding.GetBytes(@"D:/webpro/warehouse/uploadfile/銷樣明細.xls")[0];
//string intAsciiCode =asciiEncoding.GetBytes(@"D:/webpro/warehouse/uploadfile/銷樣明細.xls").ToString();
//File.Delete(intAsciiCode);
//中文編碼問題還沒解決

 

sqlDA1.Update(dataTable1);

 

//DataTable   dataTable2   =   new   DataTable();
//SqlDataAdapter   sqlDA12   =   new   SqlDataAdapter(@"delete from uploadfile where p_id="+p_id+"", sqlcon);
//SqlCommandBuilder sqlCB12 = new SqlCommandBuilder(sqlDA12);
//sqlDA12.Fill(dataTable2);
//sqlDA12.Update(dataTable2); //刪除方法一

 

string CommandText;
SqlCommand myCommand = new SqlCommand();
myCommand.Connection=sqlcon;
myCommand.CommandText = "delete from uploadfile where p_id="+p_id+"";
myCommand.ExecuteNonQuery();  //刪除方法二

 

oleDbConnection.Close();

 

File.Delete(@filemappath);    //刪除文件夾裏上傳的文件
Response.Write("刪除成功!"+"<br>");

 

}
catch(Exception ex)
{
//Response.Write("數據導入失敗!"+"<br>");
throw new Exception(ex.Message);
}
}

 

//Response.Redirect("default.aspx");
}
</script>
<form runat="server">
<ASP:DataGrid id="ml" runat="server"
width="100%"
BorderColor="black"
BorderWidth="1"
GridLines="Both"
CellPadding="3"
CellSpacing="0"
Font-Name="宋体"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="#eeeeee"/>
</form>