在C#中导出与导入Excel的方法



导出Excel的方法：

     众所周知，操作Excel一般分两种，其一就是用类似连接数据库的方式，将EXCEL文件当作小型数据库操作，这种方式我想大多数程序员都很喜欢；另一种就是用Microsoft.Office.Interop.Excel组件,引用组件则需要using Microsoft.Office.Interop.Excel，以下就是引用组件的方法：

方法一：

 #region 导出Excel的方法(其是将页面的内容直接输出，是html格式)        /// <summary>        /// 导出Excel的方法        /// </summary>        /// <param name="dt"></param>        /// <param name="dd"></param>        /// <param name="Name1"></param>        public void Export(System.Data.DataTable dt, string[] dd, string Name)        {

            Response.ContentEncoding = Encoding.GetEncoding("GB2312");            //不是导出的excel文件名乱码是你自己把文件名编码了,是你用UrlEncode把文件名编码了,用HttpUtility.UrlDecode把文件名解码即可            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Name, Encoding.UTF8).ToString() + ".xls");

            Response.ContentType = "application/ms-excel";            this.EnableViewState = false;

            StringBuilder productHeader = new StringBuilder(@"<table border='1' cellpadding='1'><tr>");            StringBuilder productRow = new StringBuilder("");            DataRow[] dr = dt.Select("1=1");            int itemCount = dt.Rows.Count;            int columnCount = dt.Columns.Count;            string[] cc = dd;            for (int i = 0; i < cc.Length; i++)            {                productHeader.Append("<td >" + cc[i] +                                     "</td>");            }

            productHeader.Append("</tr>");            Response.Write(productHeader.ToString());

            for (int j = 0; j < itemCount; j++)            {                productRow.Append("<tr>");                for (int k = 0; k < columnCount; k++)                {                    if (k == columnCount - 2)                    {                        productRow.Append(@"<td style='text-align:left;width:75%;mso-number-format:'\@''>" + dr[j][k].ToString() +                                          "</td>");                    }                    else                    {                        if (k == 1)                        {                            productRow.Append(@"<td style='text-align: left;vnd.ms-excel.numberformat:@;'>" +                                              dr[j][k].ToString() + "</td>");                        }                        else                        {                            productRow.Append("<td  style='text-align: left;'>" + dr[j][k].ToString() + "</td>");                        }                    }                }                productRow.Append("</tr>");            }            productRow.Append("</table>");            Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=GB2312\"/>" +                productRow);            Response.End();        }        #endregion

方法二：

#region  导出Excel的方法        /// <summary>        /// 导出Excel的方法        /// </summary>        /// <param name="dt"></param>        /// <param name="dd"></param>        /// <param name="FileName"></param>        public void CreateExcel(System.Data.DataTable dt, string[] dd, string FileName)        {

            HttpResponse resp;            resp = Page.Response;

            resp.ContentEncoding = Encoding.GetEncoding("GB2312");

            resp.ContentType = "application/ms-excel";            this.EnableViewState = false;

            resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString() + ".xls");            string colHeaders = "", ls_item = "";            int i = 0;            DataRow[] myRow = dt.Select("1=1");            string[] cc = dd;            for (i = 0; i < cc.Length; i++)            {                if (i == cc.Length - 1)                {                    colHeaders += cc[i].ToString() + "\n";                }                else                {                    colHeaders += cc[i].ToString() + "\t";                }            }            resp.Write(colHeaders);            foreach (DataRow row in myRow)            {                for (i = 0; i < dt.Columns.Count; i++)                {

                    if (i == dt.Columns.Count - 1)                    {                        if (i == 1)                        {                            ls_item += "'" + row[i].ToString() + "\n";                        }                        else                        {                            ls_item += row[i].ToString() + "\n";                        }

                    }                    else                    {                        ls_item += "'" + row[i].ToString() + "\t";                    }

                }                resp.Write(ls_item);                ls_item = "";            }            //写缓冲区中的数据到HTTP头文件中            resp.End();        }        #endregion

导入Excel的方法：

   而导入Excel需要固定的模板，再导入方法界面初始化的时候写绑定FORM提交事件，然后直接读取Excel文件

      /// <summary>        /// 获取Excel文件        /// </summary>        /// <param name="context"></param>        /// <param name="file"></param>        /// <param name="dt"></param>        private void  UploadFormData(HttpContext context, HttpPostedFile file,ref DataTable dt)        {                        if (file!=null)            {                string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + file.FileName.Substring(file.FileName.LastIndexOf('.'));//file.FileName;                string savePath = Server.MapPath("~/file/");                file.SaveAs(savePath + fileName);

                DataOperator(fileName, savePath,ref  dt);                if (File.Exists(savePath + fileName))                {                    File.Delete(savePath + fileName);                }            }                   }        /// <summary>         /// 数据操作         /// </summary>         /// <param name="fileName"></param>         /// <param name="savePath"></param>         private void DataOperator(string fileName, string savePath, ref DataTable dt)        {            //2003（Microsoft.Jet.Oledb.4.0）            string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =  " + savePath + fileName + ";Extended Properties=Excel 8.0";            //2010（Microsoft.ACE.OLEDB.12.0）           // string myString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + fileName + ";Extended Properties='Excel 12.0;";            OleDbConnection oconn = new OleDbConnection(myString);            oconn.Open();            DataSet ds = new DataSet();            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn);            oda.Fill(ds);            oconn.Close();            dt=ds.Tables[0];        }