C#导出Excel

最新推荐文章于 2025-03-11 11:51:16 发布

半块菠萝

最新推荐文章于 2025-03-11 11:51:16 发布

阅读量2.2k

点赞数

分类专栏： C#语言文章标签： excel c#

本文链接：https://blog.youkuaiyun.com/qq_21703215/article/details/78531797

版权

C#语言专栏收录该内容

30 篇文章

订阅专栏

###方法一
优点：简单

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();
                }
            }
        }

方法四

/// <summary>
        /// 导出第一列为文本，其他为数字格式的Excel
        /// </summary>
        /// <param name="dt"></param>
        protected void Export(DataTable dt)
        {
            string fileName = HttpUtility.UrlEncode(this.Year.Text, 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";
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                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:0.00");
                for (int i = 0; i < dg.Items.Count; i++)
                {
                    dg.Items[i].Cells[0].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();
            }
        }

方法五

可以用于桌面端，先生存csv文件然后通过修改后缀改为xlsx

		StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);
        sw.Write(strxml);
        sw.Flush();
        sw.Close()

其中方格用英文“,”分割，换行用“\r\n”
文件保存时，路径 + 名称 + “.csv”

方法六

.net mvc模式下导出
https://blog.youkuaiyun.com/zhifeiya/article/details/70471124

    public FileResult ExportExcel()
    {
        var sbHtml = new StringBuilder();
        sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
        sbHtml.Append("<tr>");
        var lstTitle = new List<string> { "编号", "姓名", "年龄", "创建时间" };
        foreach (var item in lstTitle)
        {
            sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
        }
        sbHtml.Append("</tr>");

        for (int i = 0; i < 1000; i++)
        {
            sbHtml.Append("<tr>");
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i);
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>屌丝{0}号</td>", i);
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", new Random().Next(20, 30) + i);
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", DateTime.Now);
            sbHtml.Append("</tr>");
        }
        sbHtml.Append("</table>");

        //第一种:使用FileContentResult
        byte[] fileContents = Encoding.Default.GetBytes(sbHtml.ToString());
        return File(fileContents, "application/ms-excel", "fileContents.xls");

        //第二种:使用FileStreamResult
        var fileStream = new MemoryStream(fileContents);
        return File(fileStream, "application/ms-excel", "fileStream.xls");

        //第三种:使用FilePathResult
        //服务器上首先必须要有这个Excel文件,然会通过Server.MapPath获取路径返回.
        var fileName = Server.MapPath("~/Files/fileName.xls");
        return File(fileName, "application/ms-excel", "fileName.xls");
    }