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

导出Excel

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

     #region 方法一(其是将页面的内容直接输出，是html格式)
        /// <summary>
        /// 方法一(其是将页面的内容直接输出，是html格式)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dd"></param>
        /// <param name="Name"></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  方法二
        /// <summary>
        /// 方法二
        /// </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文件即可，具体实现如下所以：

        #region 获取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);
                }
            }

        }
        #endregion

        #region 数据操作
        /// <summary>
        /// 数据操作
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="savePath"></param>
        /// <param name="dt"></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];
        }
        #endregion