C#导出涉及行列合并的复杂的Excel数据

一、导出数据格式

 

二、实现代码

 

        /// <summary>
        /// 导出经费统计excel表格
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="taskname"></param>
        /// <returns></returns>
        public ActionResult ExportFundDataToExcel(Guid taskid, string taskname)
        {
            var fileHtml = getFundDataCollect(taskid, taskname);
            if (fileHtml == "当前任务没有要导出的经费数据")
            {
                return Fail(fileHtml);
            }
            byte[] fileContents = Encoding.Default.GetBytes(fileHtml);
            return File(fileContents, "application/ms-excel", taskname + ".xls");
        }

        /// <summary>
        /// 获取经费统计html表格
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="taskname"></param>
        /// <returns></returns>
        public string getFundDataCollect(Guid taskid,string taskname)
        {
            var dt = ProjectFundCalculateService.getFundDataTabe(taskid);
            if (null == dt)
            {
                return "当前任务没有要导出的经费数据";
            }
            if (dt.Rows.Count < 1)
            {
                return "当前任务没有要导出的经费数据";
            }
            var fileHtml = new StringBuilder();
            fileHtml.Append("<table border=\"1\" style=\"table-layout:fixed;\" cellspacing='0' cellpadding='0'>");
            fileHtml.Append("<tr>");
            fileHtml.Append("<td colspan=\"10\" style=\"font-size: 16px; font-family: 宋体; text-align: center; height: 30px;\">");
            fileHtml.AppendFormat(" <strong> {0}经费概算</strong>", taskname);
            fileHtml.Append("</td>");
            fileHtml.Append(" </tr>");
            fileHtml.Append("<tr>");
            fileHtml.Append(" <td colspan=\"10\" style=\"font-size: 16px; text-align: center; height: 30px;\">");
            fileHtml.Append(" 计费依据：四川省物价局、四川省财政厅《关于调整环境监测服务收费标准的函》（川价函〔2007〕6号）附件“四川省环境监测服务收费标准”。");
            fileHtml.Append(" </td>");
            fileHtml.Append(" </tr>");
            fileHtml.Append(" <tr>");
            fileHtml.Append(" <td colspan=\"8\"></td>");
            fileHtml.Append(" <td colspan=\"2\" style=\"text-align: center;font-size: 12px;\">单位：元</td>");
            fileHtml.Append(" </tr>");
            fileHtml.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                fileHtml.AppendFormat("<td>{0}</td>", dt.Columns[i].ColumnName);
            }
            fileHtml.Append(" </tr>");
            int rowspan = 1;//要合并的列数
            string flag = "";//rowspan="$flag"的初始值，后面用rowspan替换
            for (var j = 0; j < dt.Rows.Count; j++)
            {
                fileHtml.Append("<tr>");
                if (dt.Rows[j][0].ToString() == "小计" || dt.Rows[j][0].ToString() == "监测费用合计" || dt.Rows[j][0].ToString() == "总计")
                {
                    fileHtml.AppendFormat("<td colspan=\"2\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][0]);
                    var sumtotal = dt.Rows[j][2].ToString();
                    if (dt.Rows[j][0].ToString() == "总计")
                    {
                        sumtotal = new EcanRMB().CmycurD(sumtotal) + "(￥" + sumtotal + "元)";
                    }
                    fileHtml.AppendFormat("<td colspan=\"8\" style=\"text-align: center;\"><strong>{0}</strong></td>", sumtotal);
                }
                else if (dt.Rows[j][0].ToString() == "报告编制费" || dt.Rows[j][0].ToString() == "车船费")
                {
                    fileHtml.AppendFormat("<td colspan=\"2\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][0]);
                    fileHtml.AppendFormat("<td colspan=\"7\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][2]);
                    fileHtml.AppendFormat("<td style=\"text-align: center;\"><strong>{0}</strong></td>", dt.Rows[j][dt.Columns.Count - 1]);
                }
                else if (dt.Rows[j][1].ToString() == "人工费")
                {

                    rowspan++;
                    //人工费第一列默认合并
                    fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt.Rows[j][1]);
                    fileHtml.AppendFormat("<td colspan=\"7\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][2]);
                    fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt.Rows[j][dt.Columns.Count - 1]);

                    fileHtml.Replace("$" + flag, rowspan.ToString());
                    rowspan = 1;
                    flag = "";

                }
                else
                {
                    if (j > 0)
                    {
                        //如果当前列与上一列的值相等则合并，否则初始化rowspan和flag
                        if (dt.Rows[j][0].ToString() == dt.Rows[j - 1][0].ToString())
                        {
                            rowspan++;
                        }
                        else
                        {
                            fileHtml.Replace("$" + flag, rowspan.ToString());
                            if (rowspan > 1)
                            {
                                rowspan = 1;
                                flag = "";
                            }
                        }
                    }
                    for (var k = 0; k < dt.Columns.Count; k++)
                    {
                        if (k == 0 && rowspan == 1)
                        {
                            flag = dt.Rows[j][0].ToString();
                            fileHtml.AppendFormat("<td rowspan=\"${0}\" style=\"text-align: center;\">{1}</td>", flag, dt.Rows[j][k]);
                        }
                        else if (k == 0 && rowspan > 1)
                        {
                            fileHtml.Append("");
                        }
                        else
                        {
                            fileHtml.AppendFormat("<td style=\"text-align: center;\">{1}</td>", j, dt.Rows[j][k]);
                        }
                    }

                }
                fileHtml.Append("</tr>");
               
            }
            if (rowspan > 1)
            {
                fileHtml.Replace("$" + flag, rowspan.ToString());
            }
            fileHtml.Append("</table>");
            return fileHtml.ToString();
        }