动态生成DataSet并填充值;并将DataSet导成Excel格式

本文链接：https://blog.youkuaiyun.com/weizhang_son_ding/article/details/4644643

本文介绍了一种使用 ASP.NET 将数据导出到 Excel 的方法，通过创建 DataSet 并填充数据，最终生成 Excel 文件供用户下载。该方法利用 HTML 和 XML 标记来构造 Excel 文件，并支持自定义列名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//调用ToExecelPage.aspx将hiToExcelValue的数据导成Excel格式

//ToExecelPage.aspx.cs在当中得到myform当中隐藏域当中的值;

string doLocationInfo = Request.Form["hiToExcelValue"];
CreateDataSet(doLocationInfo);

//调用方式:

//CreateDataSet("林凤娇*女*大四*22*165*O型*良好#鹏柜*男*大四*23*182*A型*良好");

//动态生成DataSet并根据传入字符串填充DataSet

private void CreateDataSet(string values)
    {
        //if (values.Trim().Length ==0)
        //{
        //    return;
        //}

        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        dt.Columns.Add("姓名", Type.GetType("System.String"));
        dt.Columns.Add("性别", Type.GetType("System.String"));
        dt.Columns.Add("班级", Type.GetType("System.String"));
        dt.Columns.Add("年龄", Type.GetType("System.String"));
        dt.Columns.Add("身高", Type.GetType("System.String"));
        dt.Columns.Add("血型", Type.GetType("System.String"));
        dt.Columns.Add("身体状态", Type.GetType("System.String"));

        string[] strArr = values.Split('#');//分割的规则,自己规定
        if (strArr.Length > 0)
        {
            for (int i = 0; i < strArr.Length;i++ )
            {
                string OneArrValue = strArr[i];
                //
                if (OneArrValue.Length >0) //My9756，劫警，2009-09-29，，．．．．．．
                {
                    string[] OneSpArr = OneArrValue.Split('*');//每个值之间的分隔规则

                    DataRow row = dt.NewRow();
                    row["姓名"] = OneSpArr[0];
                    row["性别"] = OneSpArr[1];
                    row["班级"] = OneSpArr[2];
                    row["年龄"] = OneSpArr[3];
                    row["身高"] = OneSpArr[4];
                    row["血型"] = OneSpArr[5];
                    row["身体状态"] = OneSpArr[6];
                    dt.Rows.Add(row);
                }

            }
        }

        ds.Tables.Add(dt);

         DtToExcel(ds,"0",true,"学生基本信息.xls");
    }

//将DataSet 导成Excel格式

protected void DtToExcel(DataSet ds, string tableName, bool containColumName, string fileName)
    {
        if (ds == null || (!ds.Tables.Contains(tableName) && ds.Tables.Count < int.Parse(tableName)))
        {
            return;
        }
        DataTable tb = !ds.Tables.Contains(tableName) ? ds.Tables[int.Parse(tableName)] : ds.Tables[tableName];

        StringBuilder strb = new StringBuilder();
        strb.Append(" <html xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
        strb.Append("xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
        strb.Append("xmlns=/"http://www.w3.org/TR/REC-html40/"");
        strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=gb2312'>");
        strb.Append(" <style>");
        strb.Append(".xl26");
        strb.Append(" {mso-style-parent:style0;");
        strb.Append(" font-family:/"宋体/", serif;");
        strb.Append(" mso-font-charset:0;");
        strb.Append(" mso-number-format:/"@/";}");
        strb.Append(" </style>");
        strb.Append(" <xml>");
        strb.Append(" <x:ExcelWorkbook>");
        strb.Append(" <x:ExcelWorksheets>");
        strb.Append(" <x:ExcelWorksheet>");
        strb.Append("    <x:Name>Sheet1 </x:Name>");
        strb.Append("    <x:WorksheetOptions>");
        strb.Append("    <x:DefaultRowHeight>20</x:DefaultRowHeight>");
        strb.Append("    <x:Locked>false </x:DefaultRowHeight>");
        strb.Append("    <x:Selected/>");
        strb.Append("    <x:Panes>");
        strb.Append("      <x:Pane>");
        strb.Append("      <x:Number>3 </x:Number>");
        strb.Append("      <x:ActiveCol>2 </x:ActiveCol>");
        strb.Append("      </x:Pane>");
        strb.Append("    </x:Panes>");
        strb.Append("    <x:ProtectContents>False </x:ProtectContents>");
        strb.Append("    <x:ProtectObjects>False </x:ProtectObjects>");
        strb.Append("    <x:ProtectScenarios>False </x:ProtectScenarios>");
        strb.Append("    </x:WorksheetOptions>");
        strb.Append(" </x:ExcelWorksheet>");
        strb.Append(" <x:WindowHeight>6750 </x:WindowHeight>");
        strb.Append(" <x:WindowWidth>10620 </x:WindowWidth>");
        strb.Append(" <x:WindowTopX>480 </x:WindowTopX>");
        strb.Append(" <x:WindowTopY>75 </x:WindowTopY>");
        strb.Append(" <x:ProtectStructure>False </x:ProtectStructure>");
        strb.Append(" <x:ProtectWindows>False </x:ProtectWindows>");
        strb.Append(" </x:ExcelWorkbook>");
        strb.Append(" </xml>");
        strb.Append("");
        strb.Append(" </head> <body> <table align=/"center/" style='border-collapse:collapse;table-layout:fixed'>");
        if (containColumName)
        {
            strb.Append("<tr>");
            //写列标题
            int columncount = tb.Columns.Count;
            for (int columi = 0; columi < columncount; columi++)
            {
                strb.Append(" <td > <b>" + tb.Columns[columi] + " </b> </td>");
            }

            strb.Append(" </tr>");
        }
        //写数据
        for (int i = 0; i < tb.Rows.Count; i++)
        {
            strb.Append(" <tr>");
            for (int j = 0; j < tb.Columns.Count; j++)
            {
                strb.Append(" <td class='xl26' style='white-space:nowrap'>" + tb.Rows[i][j].ToString() + " </td>");
            }
            strb.Append(" </tr>");
        }
        strb.Append(" </body> </html>");
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName,Encoding.UTF8)
);
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        this.EnableViewState = false;
        Response.Write(strb);
        Response.End();
    }