ASP.NET对EXCEL的导入导出

string FileName=emp.EmployeeID+"_"+Year+"_"+Month+".xls";

string path = Server.MapPath("../../") + @"upfile\" +FileName;

string WorkTable = Request["table"].ToString() + "$";

SqlConnection Myconn=new SqlConnection(ConnectionString);

string sql="select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE="+path+"',["+WorkTable+"])";

SqlCommand cmd=new SqlCommand(sql,Myconn);

Myconn.Open();

SqlDataAdapter adp=new SqlDataAdapter(cmd);

adp.Fill(ds);

Myconn.Close();

return ds;

读Excel,然后写到数据库里

string urlPath = HttpContext.Current.Request.ApplicationPath + "/upfile/";

string physicPath = HttpContext.Current.Server.MapPath(urlPath);

string fileName =emp.EmployeeID+".xls";

string path=physicPath + fileName;

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName +";Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();

cmd.Connection =conn;

try

{

cmd.CommandText="CREATE TABLE MonthBudget(序号 int,ID int,项目代码 varchar, 预算项目 varchar,去年同期预算金额 varchar,上月完成金额 varchar,本月预算金额 varchar,付款对象 varchar,合同情况 varchar,具体内容 varchar)";

cmd.ExecuteNonQuery ();

for(int i=0;i<ds.Tables[0].Rows.Count;i++)

{

int j=i+1;

DataRow dr=ds.Tables[0].Rows[i];

cmd.CommandText ="INSERT INTO [MonthBudget] (序号,ID,项目代码,预算项目,去年同期预算金额,上月完成金额) VALUES("+j+","+Convert.ToInt32(dr["BudgetItemID"])+",'"+dr["ItemCode"]+"','"+dr["ItemName"]+"','"+

dr["LastBudgetAmount"]+"','"+dr["ExpendedAmount"]+"')";

cmd.ExecuteNonQuery ();

}

conn.Close ();

HttpResponse response = HttpContext.Current.Response;

response.Clear();

response.WriteFile(physicPath + fileName);

string httpHeader="attachment;filename=MonthBudget.xls";

response.AppendHeader("Content-Disposition", httpHeader);

response.Flush();

System.IO.File.Delete(physicPath + fileName);//删除临时文件

response.End();

}

读DataSet写EXCEL