一、导出数据格式
二、实现代码
/// <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 < )
{
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 = ; i < dt.Columns.Count; i++)
{
fileHtml.AppendFormat("<td>{0}</td>", dt.Columns[i].ColumnName);
}
fileHtml.Append(" </tr>");
int rowspan = ;//要合并的列数
string flag = "";//rowspan="$flag"的初始值，后面用rowspan替换
for (var j = ; j < dt.Rows.Count; j++)
{
fileHtml.Append("<tr>");
if (dt.Rows[j][].ToString() == "小计" || dt.Rows[j][].ToString() == "监测费用合计" || dt.Rows[j][].ToString() == "总计")
{
fileHtml.AppendFormat("<td colspan=\"2\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][]);
var sumtotal = dt.Rows[j][].ToString();
if (dt.Rows[j][].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][].ToString() == "报告编制费" || dt.Rows[j][].ToString() == "车船费")
{
fileHtml.AppendFormat("<td colspan=\"2\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][]);
fileHtml.AppendFormat("<td colspan=\"7\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][]);
fileHtml.AppendFormat("<td style=\"text-align: center;\"><strong>{0}</strong></td>", dt.Rows[j][dt.Columns.Count - ]);
}
else if (dt.Rows[j][].ToString() == "人工费")
{ rowspan++;
//人工费第一列默认合并
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt.Rows[j][]);
fileHtml.AppendFormat("<td colspan=\"7\" style=\"text-align: center;\">{0}</td>", dt.Rows[j][]);
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt.Rows[j][dt.Columns.Count - ]); fileHtml.Replace("$" + flag, rowspan.ToString());
rowspan = ;
flag = ""; }
else
{
if (j > )
{
//如果当前列与上一列的值相等则合并，否则初始化rowspan和flag
if (dt.Rows[j][].ToString() == dt.Rows[j - ][].ToString())
{
rowspan++;
}
else
{
fileHtml.Replace("$" + flag, rowspan.ToString());
if (rowspan > )
{
rowspan = ;
flag = "";
}
}
}
for (var k = ; k < dt.Columns.Count; k++)
{
if (k == && rowspan == )
{
flag = dt.Rows[j][].ToString();
fileHtml.AppendFormat("<td rowspan=\"${0}\" style=\"text-align: center;\">{1}</td>", flag, dt.Rows[j][k]);
}
else if (k == && rowspan > )
{
fileHtml.Append("");
}
else
{
fileHtml.AppendFormat("<td style=\"text-align: center;\">{1}</td>", j, dt.Rows[j][k]);
}
} }
fileHtml.Append("</tr>"); }
if (rowspan > )
{
fileHtml.Replace("$" + flag, rowspan.ToString());
}
fileHtml.Append("</table>");
return fileHtml.ToString();
}