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();

转载于:https://www.cnblogs.com/dthom/articles/1814687.html