C#巧用Excel模版变成把Table打印出来

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

文章来源：http://blog.csdn.net/gwblue/article/details/38865525

将一个做好的Excel模版，通过程序填上数据然后打印出来这个需求有两种方法一种是通过代码打开Excel模版然后填入数据然后再打印。

第二种方法就是我将要介绍的

1、将Excel设置好格式另存为HTML页将Excel转化为HTML的Table

2、将HTML用记事本打开把这个HTML页将<style></style>和<table></table>标签中的内容粘贴出来。

3、在程序中写入打印代码。

[java] view plain copy

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.IO;
using BLL;
using Model;
using System.Data;
public partial class WebUI_Order_OutExcel : System.Web.UI.Page
{
BT_XDMgr XDMgr = new BT_XDMgr();
DataTable dtOrderMainInfo = new DataTable();
DataTable dtOrderCongInfo = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["OrderID"] != null && Request.QueryString["CKFromID"] != null)
{
int OrderID =Convert.ToInt32( Request.QueryString["OrderID"].ToString().Trim());
int CKFromID = Convert.ToInt32(Request.QueryString["CKFromID"].ToString().Trim());
string strWhere = " and T_DLS_Order_CKFrom.OrderID=" + OrderID;
dtOrderMainInfo = XDMgr.CKList(strWhere);
dtOrderCongInfo = XDMgr.QueryCKCongInfobyCKFromID(CKFromID);
string strText = getExcelContent(dtOrderMainInfo,dtOrderCongInfo);
ExportExcel(this, "出库单表", strText);
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="page">操作页面</param>
/// <param name="fileName">导出的文件名称</param>
/// <param name="text">要导出的内容</param>
public void ExportExcel(Page page, string fileName, string text)
{
page.EnableViewState = false;
try
{
page.Response.ClearContent();
page.Response.Buffer = true;
page.Response.Charset = "GB2312";//设置字符集，解决中文乱码问题
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
page.Response.Write("<meta http-equiv=Content-Type content=\"text/html;charset=GB2312\">");//解决乱码问题
////解决HTTP头中文乱码问题
//string strExcelText = fileName;//Excel显示的内容
//string strEncode = System.Web.HttpUtility.UrlEncode(strExcelText, System.Text.Encoding.UTF8);//进行编码的格式,用gb2312出错
//page.Response.AddHeader("content-disposition", "attachment;filename=\"" + strEncode + ".xls\"");//对保存标题进行编码
//page.Response.ContentType = "application/excel";//设置输出格式
//page.Response.Write(getStyle());
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
htw.WriteLine(text);//将数据输出
page.Response.Write(sw.ToString());
//page.Response.Write("</body></html>");
page.Response.Flush();
page.Response.End();
}
catch
{
return;
}
finally
{
//恢复原来控件内容
page.EnableViewState = true;
}
}
//订单信息
private string getExcelContent(DataTable dtMainInfo,DataTable dtCongInfo)
{
StringBuilder strExcel = new StringBuilder();
//Table的样式
strExcel.Append(@"<html><head><style>");
strExcel.Append(" table{mso-displayed-decimal-separator:'\\.\';mso-displayed-thousand-separator:'\\,\';}");
strExcel.Append(" .font56428{color:windowtext;font-size:9.0pt; font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;}");
strExcel.Append(" .xl156428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:black;font-size:11.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:general;vertical-align:middle;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl676428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:10.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:center;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl686428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:12.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:general;vertical-align:bottom;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl696428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:12.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:general;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl706428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:black;font-size:10.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:'Times New Roman', serif;mso-font-charset:0;mso-number-format:General;text-align:center;vertical-align:bottom;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl716428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:12.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:general;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl726428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:black;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:'Times New Roman', serif;mso-font-charset:0;mso-number-format:General;text-align:general;vertical-align:bottom;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl736428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:center;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl746428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:right;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl756428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:center;vertical-align:middle;border:.5pt solid windowtext;background:white;mso-pattern:black none;white-space:nowrap;}");
strExcel.Append(" .xl766428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:center;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl776428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:18.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:center;vertical-align:bottom;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl786428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:black;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:'Times New Roman', serif;mso-font-charset:0;mso-number-format:General;text-align:right;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl796428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:general;vertical-align:middle;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl806428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:general;vertical-align:middle;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl816428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:black;font-size:11.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:center;vertical-align:middle;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl826428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:left;vertical-align:bottom;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl836428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:left;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl846428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:14.0pt;font-weight:700;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:left;vertical-align:middle;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl856428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:black;font-size:11.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:left;vertical-align:middle;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append(" .xl866428{padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:windowtext;font-size:12.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:宋体;mso-generic-font-family:auto;mso-font-charset:134;mso-number-format:General;text-align:left;vertical-align:middle;border:.5pt solid windowtext;mso-background-source:auto;mso-pattern:auto;white-space:nowrap;}");
strExcel.Append("</style>");
//Javascript的打印代码
strExcel.Append("<script type='text/javascript'>");
strExcel.Append("function doPrint() {");
strExcel.Append("var htmlcode=window.document.body.innerHTML;");
strExcel.Append("var sprnstr='';");
strExcel.Append("var eprnstr= '';");
strExcel.Append("var prnhtml=htmlcode.substr(htmlcode.indexOf(sprnstr)+17);");
strExcel.Append("prnhtml=prnhtml.substring(0,prnhtml.indexOf(eprnstr));");
strExcel.Append("window.document.body.innerHTML=prnhtml;");
strExcel.Append("window.print();");
strExcel.Append("window.document.body.innerHTML=htmlcode;");
strExcel.Append("}");
strExcel.Append("</script>");
strExcel.Append("</head><body ><input id='Button1' type='button' value='打印' onclick='doPrint()' target='_self'/>");
strExcel.Append(" <center>");
strExcel.Append("<table border=0 cellpadding=0 cellspacing=0 width=795 style='border-collapse:");
strExcel.Append("collapse;table-layout:fixed;width:598pt'>");
strExcel.Append("<col class=xl856428 width=170 style='mso-width-source:userset;mso-width-alt:");
strExcel.Append("5440;width:128pt'>");
strExcel.Append(" <col width=190 style='mso-width-source:userset;mso-width-alt:6080;width:143pt'>");
strExcel.Append("<col width=93 style='mso-width-source:userset;mso-width-alt:2976;width:70pt'>");
strExcel.Append("<col width=89 style='mso-width-source:userset;mso-width-alt:2848;width:67pt'>");
strExcel.Append("<col width=131 style='mso-width-source:userset;mso-width-alt:4192;width:98pt'>");
strExcel.Append("<col width=122 style='mso-width-source:userset;mso-width-alt:3904;width:92pt'>");
strExcel.Append("<tr height=30 style='height:22.5pt'>");
strExcel.Append("<td colspan=6 height=30 class=xl776428 width=795 style='height:22.5pt;");
strExcel.Append("width:598pt'>出库单</td>");
strExcel.Append("</tr>");
strExcel.Append("<tr height=22 style='mso-height-source:userset;height:16.5pt'>");
strExcel.Append("<td height=22 class=xl826428 style='height:16.5pt;border-top:none'>　</td>");
strExcel.Append("<td class=xl726428 style='border-top:none;border-left:none'>　</td>");
strExcel.Append("<td class=xl726428 style='border-top:none;border-left:none'>　</td>");
strExcel.Append("<td class=xl786428 style='border-top:none;border-left:none'>NO.</td>");
strExcel.Append("<td colspan=2 class=xl706428 style='border-left:none'>　"+dtMainInfo.Rows[0]["OrderCode"].ToString().Trim()+"</td>");
strExcel.Append("</tr>");
strExcel.Append("<tr height=25 style='height:22pt'>");
strExcel.Append("<td height=25 class=xl836428 style='height:22pt;border-top:none'>购货单位</td>");
strExcel.Append("<td class=xl676428 style='border-top:none;border-left:none'>"+dtMainInfo.Rows[0]["Company"].ToString().Trim()+"</td>");
strExcel.Append("<td class=xl736428 style='border-top:none;border-left:none'>制单日期</td>");
strExcel.Append("<td class=xl686428 style='border-top:none;border-left:none; vertical-align:middle;'>" + DateTime.Now.ToShortDateString() + "</td>");
strExcel.Append("<td class=xl736428 style='border-top:none;border-left:none'>出库日期</td>");
strExcel.Append("<td class=xl696428 style='border-top:none;border-left:none'>　</td>");
strExcel.Append("</tr>");
strExcel.Append(" <tr height=25 style='height:18.75pt'>");
strExcel.Append(" <td height=25 class=xl736428 style='height:18.75pt;border-top:none'>物料编码</td>");
strExcel.Append(" <td class=xl736428 style='border-top:none;border-left:none'>物料描述</td>");
strExcel.Append(" <td class=xl736428 style='border-top:none;border-left:none'>数量</td>");
strExcel.Append(" <td class=xl736428 style='border-top:none;border-left:none'>单价</td>");
strExcel.Append(" <td class=xl736428 style='border-top:none;border-left:none'>金额</td>");
strExcel.Append(" <td class=xl736428 style='border-top:none;border-left:none'>小计</td>");
strExcel.Append(" </tr>");
if (dtCongInfo.Rows.Count > 0)
{
for (int i = 0; i < dtCongInfo.Rows.Count; i++)
{
strExcel.Append("<tr height=30 style='mso-height-source:userset;height:22.5pt;text-align:center;'>");
strExcel.Append("<td height=30 class=xl866428 style='height:22.5pt;border-top:none;text-align:center;'>" + dtCongInfo.Rows[i]["SJWLBM"].ToString().Trim() + "</td>");
strExcel.Append("<td class=xl716428 style='border-top:none;border-left:none'>" + dtCongInfo.Rows[i]["LJMC"].ToString().Trim() + "</td>");
strExcel.Append("<td class=xl716428 style='border-top:none;border-left:none'>" + ((dtCongInfo.Rows[i]["SFNumber"] == null) ? "0" : dtCongInfo.Rows[i]["SFNumber"].ToString().Trim()) + "</td>");
strExcel.Append("<td class=xl716428 style='border-top:none;border-left:none'>" + ((dtCongInfo.Rows[i]["LJDJ"] == null) ? "0" : dtCongInfo.Rows[i]["LJDJ"].ToString().Trim()) + "</td>");
strExcel.Append("<td class=xl716428 style='border-top:none;border-left:none'>" + ((dtCongInfo.Rows[i]["Money"] == null) ? "0" : dtCongInfo.Rows[i]["Money"].ToString().Trim()) + "</td>");
strExcel.Append("<td class=xl716428 style='border-top:none;border-left:none'>　</td>");
strExcel.Append("</tr>");
}
}
strExcel.Append("<tr height=25 style='height:18.75pt'>");
strExcel.Append(" <td colspan=5 height=25 class=xl746428 style='height:18.75pt'>金额合计：</td>");
strExcel.Append("<td class=xl716428 style='border-top:none;border-left:none'>" + ((dtMainInfo.Rows[0]["Money"] == null) ? "0" : dtMainInfo.Rows[0]["Money"].ToString().Trim()) + "</td>");
strExcel.Append("</tr>");
strExcel.Append("<tr class=xl816428 height=25 style='height:18.75pt'>");
strExcel.Append("<td height=25 class=xl736428 style='height:18.75pt;border-top:none'>合同号</td>");
strExcel.Append("<td class=xl766428 style='border-top:none;border-left:none'>　</td>");
strExcel.Append("<td class=xl736428 style='border-top:none;border-left:none'>移库类型</td>");
strExcel.Append("<td class=xl766428 style='border-top:none;border-left:none'>　</td>");
strExcel.Append("<td class=xl756428 style='border-top:none;border-left:none'>费用化凭证号</td>");
strExcel.Append("<td class=xl766428 style='border-top:none;border-left:none'>" + ((dtMainInfo.Rows[0]["FYHPZH"] == null) ? "0" : dtMainInfo.Rows[0]["FYHPZH"].ToString().Trim()) + "</td>");
strExcel.Append("</tr>");
strExcel.Append("<tr class=xl816428 height=25 style='height:18.75pt'>");
strExcel.Append("<td height=25 class=xl836428 style='height:18.75pt;border-top:none'>发货方式</td>");
strExcel.Append("<td class=xl756428 style='border-top:none;border-left:none'>转库凭证号</td>");
strExcel.Append("<td class=xl766428 style='border-top:none;border-left:none'>" + ((dtMainInfo.Rows[0]["ZKPZH"] == null) ? "0" : dtMainInfo.Rows[0]["ZKPZH"].ToString().Trim()) + "</td>");
strExcel.Append("<td class=xl736428 style='border-top:none;border-left:none'>备注</td>");
strExcel.Append("<td colspan=2 class=xl766428 style='border-left:none'>　</td>");
strExcel.Append("</tr>");
strExcel.Append("<tr height=25 style='height:18.75pt'>");
strExcel.Append("<td height=25 class=xl846428 style='height:18.75pt'>制单：</td>");
strExcel.Append("<td class=xl796428>售后审核：</td>");
strExcel.Append("<td class=xl796428 colspan=2>储运审核：</td>");
strExcel.Append("<td class=xl796428>库管员：</td>");
strExcel.Append("<td class=xl806428></td>");
strExcel.Append("</tr>");
strExcel.Append("</table></center>");
strExcel.Append("</body></html>");
return strExcel.ToString();
}
}