C#导出Excel

方法一

优点：简单

private void SupplierData_ButtonClick(object sender, string ButtonName)        {            DataSet da = getDataSource(0,0);            DataTable dt = da.Tables[0];            string strxml = dbo.ExportCSV(dt);            string path = Server.MapPath("..//Resource//PlanExcel//");            if (!System.IO.Directory.Exists(path))            {                System.IO.Directory.CreateDirectory(path);            }            string fileName = "供应商列表-" + DateTime.Now.ToString("yyyyMMddhhmmss") + string.Format("{0}", Session["UserCode"]) + ".xls";            FileStream fs = File.Create(path + fileName);            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);            sw.Write(strxml);            sw.Flush();            sw.Close();            if (da != null)            {                da.Dispose();            }            string s = "../Resource/PlanExcel/" + fileName;            Response.Redirect(s);        }        // 将DataTable转换为string        public string ExportCSV(DataTable dt)        {            string con = "";            foreach (DataColumn dc in dt.Columns)            {                con += dc.ColumnName + ",";            }            con = con.TrimEnd(',') + Environment.NewLine;            for (int i = 0; i < dt.Rows.Count; i++)            {                for (int j = 0; j < dt.Columns.Count; j++)                {                    con += dt.Rows[i][j].ToString().Replace("\n", " ").Replace("\r\n", " ").Replace(",", "，") + ",";                }                con = con.TrimEnd(',') + Environment.NewLine;            }            return con;        }

方法二

优点：可配置excel格式
缺点：出现过中文乱码bug，而且没有找到问题原因

protected void Export_Click(object sender, EventArgs e)        {            if (this.start.Value != string.Empty && this.end.Value != string.Empty)            {                OvertimeMeal overtimeMeal = new OvertimeMeal();                DateTime dateTime = DateTime.Parse(this.start.Value);                DateTime dateTime2 = DateTime.Parse(this.end.Value);                DataTable dataTable = overtimeMeal.OvertimeMealManageExport(dateTime, dateTime2);                string fileName = "加班员工信息";                HttpResponse response = this.Page.Response;                response.ClearContent();                if (dataTable == null || dataTable.Rows.Count == 0)                {                    this.Page.ClientScript.RegisterStartupScript(typeof(string), Guid.NewGuid().ToString(), "<script>alert('没有所需的数据！')</script> ");                }                else                {                    DataGrid dataGrid = new DataGrid();                    dataGrid.DataSource = dataTable.DefaultView;                    dataGrid.DataBind();                    this.ExcelStyle(dataGrid);                    this.ExcelOutStream(dataGrid, fileName, response);                }            }        }        private void ExcelOutStream(DataGrid dg, string fileName, HttpResponse Response)        {            //将http流添加到数据流               Response.Charset = "UTF-8";     //设置中文编码格式            Response.HeaderEncoding = Encoding.Default;            Response.ContentEncoding = Encoding.Default;            if (fileName == "")            //文件名称处理            {                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyy-MM-dd") + ".xls", Encoding.UTF8).ToString());            }            else            {                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + DateTime.Now.ToString("yyyy-MM-dd") + ".xls", Encoding.UTF8).ToString());            }            Response.ContentType = "application/excel";             //设置输出类型格式            StringWriter sw = new StringWriter();                   //保存数据，输出数据            HtmlTextWriter htw = new HtmlTextWriter(sw);            dg.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); //设置Excel单元格格式为文本格式            dg.RenderControl(htw);            Response.Write(sw.ToString());            Response.End();        }        /// <summary>        /// 样式设计        /// </summary>        /// <param name="dg"></param>        private void ExcelStyle(DataGrid dg)        {            //初始化控件样式            TableItemStyle AlternatingStyle = new TableItemStyle();            TableItemStyle headerStyle = new TableItemStyle();            TableItemStyle itemStyle = new TableItemStyle();            AlternatingStyle.BackColor = Color.Transparent;            //内容背景颜色            headerStyle.BackColor = Color.Transparent;                 //标题背景颜色            headerStyle.Font.Bold = true;                              //设置粗体            headerStyle.HorizontalAlign = HorizontalAlign.Center;      //内容对齐方式            itemStyle.HorizontalAlign = HorizontalAlign.Center;        //标题对齐方式            //将样式添加到DataGrid中            dg.AlternatingItemStyle.MergeWith(AlternatingStyle);            dg.HeaderStyle.MergeWith(headerStyle);            dg.ItemStyle.MergeWith(itemStyle);            dg.GridLines = GridLines.Both;            dg.HeaderStyle.Font.Bold = true;        }

方法三

解决方法二中文乱码问题，但是对Excel的格式配置不如方法二

        protected void Export_Click(object sender, EventArgs e)        {            if (this.start.Value != string.Empty && this.end.Value != string.Empty)            {                var exec = new Execution.OvertimeMeal();                DateTime BeginTime = DateTime.Parse(this.start.Value);                DateTime EndTime = DateTime.Parse(this.end.Value);                DataTable dt = exec.OvertimeMealManageExport(BeginTime, EndTime);                string fileName = HttpUtility.UrlEncode("加班员工信息" + DateTime.Now.ToString("yyyy-MM-dd"), Encoding.UTF8).ToString();                HttpResponse Response = this.Page.Response;                Response.ClearContent();                if (dt == null || dt.Rows.Count == 0)                {                    this.Page.ClientScript.RegisterStartupScript(typeof(string), Guid.NewGuid().ToString(), "<script>alert('没有所需的数据！')</script> ");                    return;                }                else                {                    DataGrid dg = new DataGrid();                    dg.DataSource = dt.DefaultView;                    dg.DataBind();                    HttpContext.Current.Response.Clear();                    HttpContext.Current.Response.Charset = "UTF-8";// "UTF-8"或者"GB2312"                    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";//text/csv                    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;                    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");                    System.IO.StringWriter oSW = new System.IO.StringWriter();                    HtmlTextWriter oHW = new HtmlTextWriter(oSW);                    dg.Attributes.Add("style", "vnd.ms-excel.numberformat:@");                    dg.RenderControl(oHW);                    HttpContext.Current.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>" + oSW.ToString());                    HttpContext.Current.Response.End();                }            }        }