ASP.NET EXCEL导入导出
导入:
protected void imbtnFU_Click(object sender, ImageClickEventArgs e) { if (this.FileUpload1.HasFile == false) { ScriptManager.RegisterClientScriptBlock(this, GetType(), "a", "<scirpt>alert('请选择要上传的文件!')</script>", false); return; } if (System.IO.Path.GetExtension(this.FileUpload1.FileName).ToLower() != ".xls") { ScriptManager.RegisterClientScriptBlock(this, GetType(), "a", "alert('上传的文件必须是Excel文件,并且Excel文件处于关闭状态!')", false); return; } string FileName = "PaiDan.xls"; string svrFileName = Server.MapPath("..") + "\\Files\\" + FileName; this.FileUpload1.SaveAs(svrFileName); string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + svrFileName + ";Extended Properties=Excel 8.0"; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]''", conn); DataSet ds = new DataSet(); adp.Fill(ds, "Book1"); if (ds.Tables[0].Rows.Count <= 0) { ScriptManager.RegisterClientScriptBlock(this, GetType(), "a", "<script>alert('文件没有可以导入的数据,请检查!')</script>", false); //GSRUtil.ShowMessage(this.Page, "文件没有可以导入的数据,请检查!"); return; } foreach (DataRow item in ds.Tables[0].Rows) { PaiDan pd = new PaiDan(); pd.Dealer = item["货物来源"].ToString(); pd.AcceptType = item["延保类型"].ToString(); pd.ServiceNum = item["服务类型号码"].ToString(); pd.YanbaoQiXian = item["延保期限"].ToString(); pd.PhonePrice = decimal.Parse(item["购机价格"].ToString()); pd.BuyDate = DateTime.Parse(item["购买日期"].ToString()); pd.Province = item["省"].ToString(); pd.City = item["市"].ToString(); pd.Company = item["公司名称"].ToString(); pd.Customer = item["姓名"].ToString(); pd.Telphone = item["固定电话"].ToString(); pd.MobilePhone = item["移动电话"].ToString(); pd.Sex = item["性别"].ToString(); pd.Address = item["地址"].ToString(); pd.IMIE = item["IMIE"].ToString(); pd.SpotNum = item["识别号"].ToString(); pd.Brand = item["品牌"].ToString(); pd.Model = item["型号"].ToString(); pd.Color = item["颜色"].ToString(); pd.Facade = item["外观"].ToString(); pd.Affix = item["附件"].ToString(); pd.Symptom = item["故障现象"].ToString(); pd.States = "已受理"; pd.QuoteState = "未报价"; pd.WxStates = "未维修"; pd.WxDate = DateTime.Parse("1900-01-01 00:00:00"); pd.ShouDate = DateTime.Parse("1900-01-01 00:00:00"); pd.PaiDate = DateTime.Parse("1900-01-01 00:00:00"); pd.OperatDate = DateTime.Now; pd.ExDate = DateTime.Parse("1900-01-01 00:00:00"); pd.BackDate = DateTime.Parse("1900-01-01 00:00:00"); pd.WxOkDate = DateTime.Parse("1900-01-01 00:00:00"); pd.Operatore = PublicFunction.GetLoginName(this.Page.Request); JIZHANGService.CreatePaiDanBLL().SaveEntity(pd); } this.gvPD.DataSource = JIZHANGService.CreatePaiDanBLL().GetItems(-1, " States='已受理' and Operatore='" + PublicFunction.GetLoginName(this.Page.Request) + "'", "Id Desc"); this.gvPD.DataBind(); }
导出:
XlsDocument xls = new XlsDocument(ToDataSet.ConvertToDataSet(BindPaiDans())); xls.FileName = "PaiDan"; xls.Workbook.Worksheets[0].Name = "派单详情"; xls.Workbook.Worksheets[0].Cells.Add(1, 2, "货物来源"); xls.Send();