现在的位置: 首页 > 综合 > 正文

C# 导出和导入excel

2017年12月07日 ⁄ 综合 ⁄ 共 3666字 ⁄ 字号 评论关闭
#region  导出Excel
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="page">请求的页面this</param>
        /// <param name="dataTable">导出的数据源</param>
        /// <param name="fileName">保存文件名称</param>
        /// <returns>布尔值</returns>
        public bool ExportExcel(Page page, DataTable dataTable, string fileName)
        {
            try
            {
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@";
                page.EnableViewState = false;
                HttpContext.Current.Response.Charset = "UTF-8";
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
                //输出列名
                for (int i = 0; i < dataTable.Columns.Count; i++)
                    HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "\t");
                HttpContext.Current.Response.Write("\r\n");
                //输出数据
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "\t");
                    }
                    HttpContext.Current.Response.Write("\r\n");
                }
                //输出当前缓存内容
                //HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

 #region 导出Excel 自定义格式
        /// <summary>
        /// 导出Excel  
        /// 1.文本:vnd.ms-excel.numberformat:@
        /// 2.日期:vnd.ms-excel.numberformat:yyyy/mm/dd
        /// 3.数字:vnd.ms-excel.numberformat:#,##0.00
        /// 4.货币:vnd.ms-excel.numberformat:¥#,##0.00
        /// 5.百分比:vnd.ms-excel.numberformat: #0.00%
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool Export(string fileName, DataTable dt)
        {
            try
            {
                HttpResponse resp;
                resp = System.Web.HttpContext.Current.Response;
                resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
                resp.AppendHeader("Content-Type", "application/ms-excel");

                StringBuilder colHeaders = new StringBuilder();
                StringBuilder ls_item = new StringBuilder();
                DataRow[] myRow = dt.Select();
                int cl = dt.Columns.Count;

                colHeaders.Append(" <html><head> \n ");
                colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> \n ");
                colHeaders.Append(" </head> \n ");
                colHeaders.Append(" <body> \n ");
                colHeaders.Append(" <table border='1'> ");
                colHeaders.Append(" <tr> ");
                //输出列名
                for (int i = 0; i < dt.Columns.Count; i++)
                    colHeaders.Append("<td  style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>");
                colHeaders.Append("</tr> ");
                resp.Write(colHeaders.ToString());
                foreach (DataRow row in myRow)
                {
                    ls_item.Append("<tr>");
                    for (int i = 0; i < cl; i++)
                    {
                        if (i == (cl - 1))
                        {
                            ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n");
                        }
                        else
                        {
                            ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>");
                        }
                    }
                    ls_item.Append("</tr>");
                }
                ls_item.Append(" </table> \n ");
                ls_item.Append(" </body> \n ");
                ls_item.Append(" </html>");
                resp.Write(ls_item.ToString());
                resp.End();
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 导入Excel
        public string ImportExcel(string[] list, string filePath)
        {
            string isXls = System.IO.Path.GetExtension(filePath).ToLower();//System.IO.Path.GetExtension获得文件的扩展名
            if (isXls != ".xls")
                return "请选择Excel文件导入!";
            DataSet ds = ExecleDataSet(filePath);//调用自定义方法
            DataRow[] dr = ds.Tables[0].Select();//定义一个DataRow数组
            int rowsnum = ds.Tables[0].Rows.Count;
            if (ds.Tables[0].Rows.Count == 0)
                return "Excel无数据!";
            return "";
        }

        //OleDB连接读取Excel中数据
        public DataSet ExecleDataSet(string filePath)
        {
            string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(OleDbConnection);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            odda.Fill(ds);
            conn.Close();
            return ds;
        }
        #endregion

抱歉!评论已关闭.