代码改变世界

EXCEL导入导出自己整理的一些方法

2013-10-19 00:55  sql_manage  阅读(289)  评论(0编辑  收藏  举报
//导入Excel代码
protected DataTable ExcelHelper(string filePaht)
        {
            string sFilePath2003 = Server.MapPath("ExcelData/2003.xls");
            //string sFilePath2007 = Server.MapPath("ExcelData/2007.xlsx");
             
            // 支持Excel2003 和 Excel2007 的连接字符串
            // "HDR=yes;"是说第一行是列名而不是数据,"HDR=No;"正好与前面的相反。 
            // 如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
            string sConn = "provider=Microsoft.ACE.OleDb.12.0; Data Source ='" + sFilePath2003 + "';Extended Properties='Excel 12.0;HDR=yes;IMEX=1';";
            if (string.IsNullOrEmpty(sConn)) {  return null ; }
 
            DataTable dtExcel = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(sConn))
            {
                conn.Open();
                // 获取Excel的第一个SheetName
                string sSheetName = "";
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                if (dtSheet.Rows.Count > 0)
                    sSheetName = dtSheet.Rows[0]["Table_Name"].ToString();
                else
                    return null;
                // 获取Excel数据
                string sSql = string.Format("select * from [{0}]", sSheetName);
                OleDbDataAdapter adapter = new OleDbDataAdapter(sSql, conn);
                adapter.Fill(dtExcel);
                conn.Close();
            }
 
            return dtExcel;
        }

 

        /// <summary>
        /// 导出EXCEL,直接传入一个List对象
List<Students> list = new List<Students> { 
              new Students{name="xiaochun",sex="man",age="20"},
              new Students{name="xiaochun",sex="man",age="20"},
              new Students{name="xiaochun",sex="man",age="20"},
              new Students{name="xiaochun",sex="man",age="20"},
            };
            GridView gvw=new GridView ();
             gvw.AllowPaging=false;
             gvw.DataSource= list.Select(s => new { 姓名 = s.name, 性别 = s.sex, 年龄 = s.age });
             gvw.DataBind();

            ObjectToExcel(gvw);



/// <summary>
        /// 导出EXCEL[直接传入一个GridView]
        /// </summary>
        private void ObjectToExcel(System.Web.UI.WebControls.GridView gvw)
        {
            System.Web.UI.WebControls.GridView gvExport = gvw;
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;
            if (gvw.Rows.Count>0)
            {
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("财务报表", System.Text.Encoding.UTF8) + ".xlsx"); 
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                curContext.Response.Charset = "utf-8";
                strWriter = new System.IO.StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);

                gvExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();

            }
        }

 

/// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        private void ObjectToExcel<T>(List<T> list)
        {
            System.Web.UI.WebControls.GridView gvExport = null;
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;
            if (list != null)
            {
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("财务报表", System.Text.Encoding.UTF8) + ".xlsx"); 
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                curContext.Response.Charset = "utf-8";
                strWriter = new System.IO.StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);

                gvExport = new GridView();
                gvExport.DataSource = list;
                gvExport.AllowPaging = false;
                gvExport.DataBind();

                gvExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();

            }
        }