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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值