在最近的项目中我学习到了一种生成excel表格的方法,先将所要生成的表格用excel做出来,然后将其另存为xml形式(注意是xml格式),然后将其用记事本打开,截取表头(将表头文件中的行替换为*号或者某个特定符合,以备以后替换)和表尾的代码另存为.txt文件备用,中间部分的代码就是在以后的生成中进行替换的。(说白了主要思路就是“替换”后生成)
下面将部分代码帖出来备忘:
Code
public static int ExportToExcel_RP3(System.Data.DataTable table)
{
HttpServerUtility _server = HttpContext.Current.Server;
string TemplateFiletop = _server.MapPath("~\\report\\report_model\\rp3top.txt");
string TemplateFileend = _server.MapPath("~\\report\\report_model\\rp3end.txt");//读取头、尾文件
StringBuilder str = new StringBuilder();//这是主要思路,用stringbuilder输出
try
{
StreamReader sr = new StreamReader(TemplateFiletop, Encoding.GetEncoding("GB2312"));
str.Append(sr.ReadToEnd());
sr.Dispose();
sr.Close();
}
catch
{
}//将头文件读入
foreach (DataRow row in table.Rows)
{
str.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"12.75\"");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"String\">"+"</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">"+row[6]+"</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">"+row[7]+"</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\" ss:Formula=\"=RC[1]+RC[2]+RC[3]+RC[4]+RC[6]+RC[7]+RC[8]\"><Data ss:Type=\"String\"></Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[8] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[9] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[10] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[11] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[12] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[13] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[14] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[15] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[16] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\" ss:Formula=\"=RC[1]+RC[2]+RC[3]+RC[4]+RC[5]+RC[6]+RC[7]+RC[8]\"><Data ss:Type=\"String\"></Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[17] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[18] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[19] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[20] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[21] + "</Data></Cell>");
str.Append("</Row>");
//foreach (DataColumn col in table.Columns)
//{
// str.Append("<Cell><Data ss:Type='String'>" + row[col.ColumnName].ToString() + "</Data></Cell>");
//}
}//循环输出行记录
try
{
StreamReader sr = new StreamReader(TemplateFileend, Encoding.GetEncoding("GB2312"));
str.Append(sr.ReadToEnd());
sr.Dispose();
sr.Close();
}
catch
{
}//读入尾文件
//替换输出的列数和行数
str.Replace("ss:ExpandedRowCount=\"60\"", "ss:ExpandedRowCount=\"" + (table.Rows.Count + 30) + "\"");
str.Replace("[$NT]", DateTime.Now.ToShortDateString());
//输出文档
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=BBResult_RP3.xls");
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;
HttpContext.Current.Response.ContentType = "application/excel";
HttpContext.Current.Response.Write(str);
HttpContext.Current.Response.End();
return 0;
}
public static int ExportToExcel_RP3(System.Data.DataTable table)
{
HttpServerUtility _server = HttpContext.Current.Server;
string TemplateFiletop = _server.MapPath("~\\report\\report_model\\rp3top.txt");
string TemplateFileend = _server.MapPath("~\\report\\report_model\\rp3end.txt");//读取头、尾文件
StringBuilder str = new StringBuilder();//这是主要思路,用stringbuilder输出
try
{
StreamReader sr = new StreamReader(TemplateFiletop, Encoding.GetEncoding("GB2312"));
str.Append(sr.ReadToEnd());
sr.Dispose();
sr.Close();
}
catch
{
}//将头文件读入
foreach (DataRow row in table.Rows)
{
str.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"12.75\"");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"String\">"+"</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">"+row[6]+"</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">"+row[7]+"</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\" ss:Formula=\"=RC[1]+RC[2]+RC[3]+RC[4]+RC[6]+RC[7]+RC[8]\"><Data ss:Type=\"String\"></Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[8] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[9] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[10] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[11] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[12] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[13] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[14] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[15] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[16] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\" ss:Formula=\"=RC[1]+RC[2]+RC[3]+RC[4]+RC[5]+RC[6]+RC[7]+RC[8]\"><Data ss:Type=\"String\"></Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[17] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[18] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[19] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[20] + "</Data></Cell>");
str.Append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"Number\">" + row[21] + "</Data></Cell>");
str.Append("</Row>");
//foreach (DataColumn col in table.Columns)
//{
// str.Append("<Cell><Data ss:Type='String'>" + row[col.ColumnName].ToString() + "</Data></Cell>");
//}
}//循环输出行记录
try
{
StreamReader sr = new StreamReader(TemplateFileend, Encoding.GetEncoding("GB2312"));
str.Append(sr.ReadToEnd());
sr.Dispose();
sr.Close();
}
catch
{
}//读入尾文件
//替换输出的列数和行数
str.Replace("ss:ExpandedRowCount=\"60\"", "ss:ExpandedRowCount=\"" + (table.Rows.Count + 30) + "\"");
str.Replace("[$NT]", DateTime.Now.ToShortDateString());
//输出文档
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=BBResult_RP3.xls");
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;
HttpContext.Current.Response.ContentType = "application/excel";
HttpContext.Current.Response.Write(str);
HttpContext.Current.Response.End();
return 0;
}