不调用Excel对象模型,直接用Response输出可以加批注的Excel。
代码如下:
using
System;
using System.Text;
using System.Web;
using System.Web.UI;
namespace WebTest
{
/**//// <summary>
/// ExcelWithComment 的摘要说明。
/// </summary>
public class ResponseExcelWithComment
{
/**//// <summary>
/// 当前 HttpResponse
/// </summary>
private static HttpResponse Response
{
get
{
return HttpContext.Current.Response ;
}
}
/**//// <summary>
/// 用于构建整个网页内容的 StringBuilder
/// </summary>
private StringBuilder _htmlBuilder = new StringBuilder() ;
private StringBuilder _contentBuilder = new StringBuilder() ;
/**//// <summary>
/// 准备输出的Excel的文件名,不含扩展名
/// </summary>
private readonly string _fileName ;
/**//// <summary>
/// Excel 作者
/// </summary>
private readonly string _authorName ;
private ResponseExcelWithComment(){}
public ResponseExcelWithComment(string fileName, string authorName)
{
if (fileName == null)
{
throw new ArgumentNullException("fileName") ;
}
if (authorName == null)
{
throw new ArgumentNullException("authorName") ;
}
_fileName = fileName ;
_authorName = authorName ;
}
public void WriteResponse()
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition","attachment;filename=" + _fileName + ".xls");
Response.ContentEncoding = Encoding.Default ;
BuildHtml();
Response.Write(_htmlBuilder.ToString()) ;
Response.Flush() ;
Response.End() ;
}
/**//// <summary>
/// 为 Body 中的 Content添加行
/// </summary>
/// <param name="line"></param>
public void AppendBodyContent(string line)
{
if (line != null)
{
_contentBuilder.Append(line) ;
}
_contentBuilder.Append("/r/n") ;
}
/**//// <summary>
/// 为 整个Html 添加一行内容
/// </summary>
/// <param name="line"></param>
private void AppendLine(string line)
{
if (line != null)
{
_htmlBuilder.Append(line) ;
}
_htmlBuilder.Append("/r/n") ;
}
private void BuildHtml()
{
AppendLine(@"<html xmlns:v=""urn:schemas-microsoft-com:vml""
xmlns:o=""urn:schemas-microsoft-com:office:office""
xmlns:x=""urn:schemas-microsoft-com:office:excel""
xmlns=""http://www.w3.org/TR/REC-html40"">");
BuildHead();
BuildBody();
AppendLine("</html>");
}
/**//// <summary>
/// 写 <head></head> 部分
/// </summary>
private void BuildHead()
{
AppendLine("<head>");
BuildMeta();
BuildLink();
BuildCSS();
BuildJavascript();
BuildExcelProperties();
AppendLine(("</head>"));
}
/**//// <summary>
/// 写 <body></body> 部分
/// </summary>
private void BuildBody()
{
AppendLine("<body link=blue vlink=purple>");
AppendLine(_contentBuilder.ToString());
//comment list
AppendLine(@"<div style='mso-element:comment-list'><![if !supportAnnotations]>
<hr class=msocomhide align=left size=1 width=""33%"">
<![endif]>");
AppendLine(_commentBuilder.ToString());
AppendLine("</div>");
AppendLine("</body>");
}
Head Write Method#region Head Write Method
private int _styleIndex = 30 ;
private StringBuilder _styleBuilder = new StringBuilder() ;
/**//// <summary>
/// 为单元格添加一种样式
/// </summary>
/// <param name="bgColor">背景色</param>
/// <param name="top">顶部是否闭合</param>
/// <param name="bottom">底部是否闭合</param>
/// <param name="left">左边是否闭合</param>
/// <param name="right">右边</param>
/// <param name="fontSize">文字大小</param>
/// <param name="bold">是否为粗体</param>
/// <returns>css类名</returns>
public string AddCellStyle(System.Drawing.Color bgColor, bool top, bool bottom, bool left, bool right, int fontSize, bool bold)
{
_styleIndex++ ;
_styleBuilder.Append(string.Format(@".xl{0}
{8}mso-style-parent:style0;
mso-pattern:auto none;
border-top:{1};
border-right:{2};
border-bottom:{3};
border-left:{4};
font-size:{5}pt;
{6}
background:{7};{9}",
_styleIndex,
top ? ".5pt solid black" : "none",
right ? ".5pt solid black" : "none",
bottom ? ".5pt solid black" : "none",
left ? ".5pt solid black" : "none",
fontSize,
bold ? "font-weight:700;" : "",
bgColor.Name,
"{",
"}")) ;
_styleBuilder.Append("/r/n") ;
return "xl" + _styleIndex.ToString() ;
}
/**//// <summary>
/// 写 Meta 部分
/// </summary>
private void BuildMeta()
{
AppendLine("<meta http-equiv=/"Content-Type/" content=/"text/html; charset=gb2312/"") ;
AppendLine("<meta name=ProgId content=Excel.Sheet>") ;
AppendLine("<meta name=Generator content=/"Microsoft Excel 11/">") ;
}
/**//// <summary>
/// 写 Linked File
/// </summary>
private void BuildLink()
{
AppendLine("<link rel=File-List href=/"" + _fileName + ".files/filelist.xml/">") ;
AppendLine("<link rel=Edit-Time-Data href=/"" + _fileName + ".files/editdata.mso/">") ;
AppendLine("<link rel=OLE-Object-Data href=/"" + _fileName + ".files/oledata.mso/">") ;
}
private void BuildCSS()
{
string css = @"
<!--[if !mso]>
<style>
v/:* {behavior:url(#default#VML);}
o/:* {behavior:url(#default#VML);}
x/:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:LastAuthor>" + _authorName + @"</o:LastAuthor>
<o:LastSaved>" + DateTime.Now.ToUniversalTime().ToString("yyyy-MM-ddThh:mm:ssZ") + @"</o:LastSaved>
<o:Version>11.8107</o:Version>
</o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:""/."";
mso-displayed-thousand-separator:""/,"";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
.font6
{color:black;
font-size:9.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
.font7
{color:black;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
.font8
{color:black;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
.font9
{color:black;
font-size:9.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:/5E38/89C4;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding:0px;
mso-ignore:padding;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:middle;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
white-space:normal;}
" + _styleBuilder.ToString()
+ @"
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>" ;
AppendLine(css) ;
}
private void BuildJavascript()
{
AppendLine(@"<![if !supportAnnotations]><style id=""dynCom"" type=""text/css""><!-- --></style>
<script language=""JavaScript""><!--
function msoCommentShow(com_id,anchor_id) {
if(msoBrowserCheck()) {
c = document.all(com_id);
a = document.all(anchor_id);
if (null != c) {
var cw = c.offsetWidth;
var ch = c.offsetHeight;
var aw = a.offsetWidth;
var ah = a.offsetHeight;
var x = a.offsetLeft;
var y = a.offsetTop;
var el = a;
while (el.tagName != ""BODY"") {
el = el.offsetParent;
x = x + el.offsetLeft;
y = y + el.offsetTop;
}
var bw = document.body.clientWidth;
var bh = document.body.clientHeight;
var bsl = document.body.scrollLeft;
var bst = document.body.scrollTop;
if (x + cw + ah/2 > bw + bsl && x + aw - ah/2 - cw >= bsl ) {
c.style.left = x + aw - ah / 2 - cw;
}
else {
c.style.left = x + ah/2;
}
if (y + ch + ah/2 > bh + bst && y + ah/2 - ch >= bst ) {
c.style.top = y + ah/2 - ch;
}
else {
c.style.top = y + ah/2;
}
c.style.visibility = ""visible"";
}
}
}
function msoCommentHide(com_id) {
if(msoBrowserCheck()) {
c = document.all(com_id)
if (null != c) {
c.style.visibility = ""hidden"";
c.style.left = ""-10000"";
c.style.top = ""-10000"";
}
}
}
function msoBrowserCheck() {
ms=navigator.appVersion.indexOf(""MSIE"");
vers = navigator.appVersion.substring(ms+5, ms+6);
ie4 = (ms>0) && (parseInt(vers) >=4);
return ie4
}
if (msoBrowserCheck()) {
document.styleSheets.dynCom.addRule("".msocomspan1"",""position:absolute"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""position:absolute"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""left:-1.5ex"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""width:2ex"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""height:0.5em"");
document.styleSheets.dynCom.addRule("".msocomanch"",""font-size:0.5em"");
document.styleSheets.dynCom.addRule("".msocomanch"",""color:red"");
document.styleSheets.dynCom.addRule("".msocomhide"",""display: none"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""visibility: hidden"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""position: absolute"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""top:-10000"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""left:-10000"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""width: 33%"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""background: infobackground"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""color: infotext"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-top: 1pt solid threedlightshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-right: 2pt solid threedshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-bottom: 2pt solid threedshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-left: 1pt solid threedlightshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""padding: 3pt 3pt 3pt 3pt"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""z-index: 100"");
}
// -->
</script>
<![endif]>") ;
}
private void BuildExcelProperties()
{
AppendLine(string.Format(@"<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>{0}</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>285</x:DefaultRowHeight>
<x:Selected/>
<x:DoNotDisplayGridlines/>
<x:Panes/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>8550</x:WindowHeight>
<x:WindowWidth>14940</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>45</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=""edit"" spidmax=""1027""/>
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=""edit"">
<o:idmap v:ext=""edit"" data=""1""/>
</o:shapelayout></xml><![endif]-->",
_fileName));
}
#endregion
About Comment#region About Comment
/**//// <summary>
/// 批注的 Builder
/// </summary>
StringBuilder _commentBuilder = new StringBuilder() ;
int curIndex = 0 ;
/**//// <summary>
/// Shape Type
/// </summary>
const string SHAPE_TYPE = @"<v:shapetype id=""_x0000_t202"" coordsize=""21600,21600"" o:spt=""202"" path=""m,l,21600r21600,l21600,xe"">
<v:stroke joinstyle=""miter""/>
<v:path gradientshapeok=""t"" o:connecttype=""rect""/>
</v:shapetype>" ;
/**//// <summary>
/// 添加批注
/// </summary>
/// <param name="row">被批注单元格从0开始所在的行索引</param>
/// <param name="column">被批注单元格从0开始所在的列索引</param>
/// <param name="text">单元格内容</param>
/// <param name="comment">批注内容</param>
/// <returns>增加了批注后的单元格内容</returns>
public string AddComment(int row, int column, string text, string comment)
{
if (row < 0)
{
throw new ArgumentOutOfRangeException("row") ;
}
if (column < 0)
{
throw new ArgumentOutOfRangeException("column") ;
}
if (text == null)
{
throw new ArgumentNullException("text") ;
}
if (comment == null)
{
throw new ArgumentNullException("comment") ;
}
curIndex++ ;
_commentBuilder.Append(string.Format(@"
<div style='mso-element:comment'><![if !supportAnnotations]>
<div id=""_com_{0}"" class=msocomtxt
οnmοuseοver=""msoCommentShow('_com_{0}','_anchor_{0}')""
οnmοuseοut=""msoCommentHide('_com_{0}')"" language=JavaScript><![endif]>
<div><![if !supportAnnotations]><a class=msocomhide href=""#_msoanchor_{0}""
name=""_msocom_{0}"">[{0}]</a><![endif]><!--[if gte mso 9]><xml>
{1}<v:shape id=""_x0000_s102{0}"" type=""#_x0000_t202"" style='position:absolute;
margin-left:87.75pt;margin-top:-12.75pt;width:96pt;height:59.25pt;z-index:1;
visibility:hidden' fillcolor=""infoBackground [80]"" o:insetmode=""auto"">
<v:fill color2=""infoBackground [80]""/>
<v:shadow on=""t"" color=""black"" obscured=""t""/>
<v:path o:connecttype=""none""/>
<v:textbox style='mso-direction-alt:auto'/>
<x:ClientData ObjectType=""Note"">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:AutoFill>False</x:AutoFill>
<x:Row>{2}</x:Row>
<x:Column>{3}</x:Column>
<x:Author>{4}</x:Author>
</x:ClientData>
</v:shape></xml><![endif]--><![if !vml]><span style='mso-ignore:vglayout'><![endif]>
<div v:shape=""_x0000_s102{0}"" style='padding:.75pt 0pt 0pt .75pt;text-align:left'
class=shape><font class=""font6"">{4}:</font><font class=""font7""><br>
{5}</font></div>
<![if !vml]></span><![endif]></div>
<![if !supportAnnotations]></div>
<![endif]></div>",
curIndex,
(curIndex == 1 ? SHAPE_TYPE : ""),
row,
column,
_authorName,
comment)) ;
return string.Format(@"{1}<![if !supportAnnotations]><span
class=msocomspan1><span class=msocomspan2 id=""_anchor_{0}""
οnmοuseοver=""msoCommentShow('_com_{0}','_anchor_{0}')""
οnmοuseοut=""msoCommentHide('_com_{0}')"" language=JavaScript><a
class=msocomanch href=""#_msocom_{0}"" name=""_msoanchor_{0}"">[1]</a></span></span><![endif]>",
curIndex,
text) ;
}
#endregion
}
}
using System.Text;
using System.Web;
using System.Web.UI;
namespace WebTest
{
/**//// <summary>
/// ExcelWithComment 的摘要说明。
/// </summary>
public class ResponseExcelWithComment
{
/**//// <summary>
/// 当前 HttpResponse
/// </summary>
private static HttpResponse Response
{
get
{
return HttpContext.Current.Response ;
}
}
/**//// <summary>
/// 用于构建整个网页内容的 StringBuilder
/// </summary>
private StringBuilder _htmlBuilder = new StringBuilder() ;
private StringBuilder _contentBuilder = new StringBuilder() ;
/**//// <summary>
/// 准备输出的Excel的文件名,不含扩展名
/// </summary>
private readonly string _fileName ;
/**//// <summary>
/// Excel 作者
/// </summary>
private readonly string _authorName ;
private ResponseExcelWithComment(){}
public ResponseExcelWithComment(string fileName, string authorName)
{
if (fileName == null)
{
throw new ArgumentNullException("fileName") ;
}
if (authorName == null)
{
throw new ArgumentNullException("authorName") ;
}
_fileName = fileName ;
_authorName = authorName ;
}
public void WriteResponse()
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition","attachment;filename=" + _fileName + ".xls");
Response.ContentEncoding = Encoding.Default ;
BuildHtml();
Response.Write(_htmlBuilder.ToString()) ;
Response.Flush() ;
Response.End() ;
}
/**//// <summary>
/// 为 Body 中的 Content添加行
/// </summary>
/// <param name="line"></param>
public void AppendBodyContent(string line)
{
if (line != null)
{
_contentBuilder.Append(line) ;
}
_contentBuilder.Append("/r/n") ;
}
/**//// <summary>
/// 为 整个Html 添加一行内容
/// </summary>
/// <param name="line"></param>
private void AppendLine(string line)
{
if (line != null)
{
_htmlBuilder.Append(line) ;
}
_htmlBuilder.Append("/r/n") ;
}
private void BuildHtml()
{
AppendLine(@"<html xmlns:v=""urn:schemas-microsoft-com:vml""
xmlns:o=""urn:schemas-microsoft-com:office:office""
xmlns:x=""urn:schemas-microsoft-com:office:excel""
xmlns=""http://www.w3.org/TR/REC-html40"">");
BuildHead();
BuildBody();
AppendLine("</html>");
}
/**//// <summary>
/// 写 <head></head> 部分
/// </summary>
private void BuildHead()
{
AppendLine("<head>");
BuildMeta();
BuildLink();
BuildCSS();
BuildJavascript();
BuildExcelProperties();
AppendLine(("</head>"));
}
/**//// <summary>
/// 写 <body></body> 部分
/// </summary>
private void BuildBody()
{
AppendLine("<body link=blue vlink=purple>");
AppendLine(_contentBuilder.ToString());
//comment list
AppendLine(@"<div style='mso-element:comment-list'><![if !supportAnnotations]>
<hr class=msocomhide align=left size=1 width=""33%"">
<![endif]>");
AppendLine(_commentBuilder.ToString());
AppendLine("</div>");
AppendLine("</body>");
}
Head Write Method#region Head Write Method
private int _styleIndex = 30 ;
private StringBuilder _styleBuilder = new StringBuilder() ;
/**//// <summary>
/// 为单元格添加一种样式
/// </summary>
/// <param name="bgColor">背景色</param>
/// <param name="top">顶部是否闭合</param>
/// <param name="bottom">底部是否闭合</param>
/// <param name="left">左边是否闭合</param>
/// <param name="right">右边</param>
/// <param name="fontSize">文字大小</param>
/// <param name="bold">是否为粗体</param>
/// <returns>css类名</returns>
public string AddCellStyle(System.Drawing.Color bgColor, bool top, bool bottom, bool left, bool right, int fontSize, bool bold)
{
_styleIndex++ ;
_styleBuilder.Append(string.Format(@".xl{0}
{8}mso-style-parent:style0;
mso-pattern:auto none;
border-top:{1};
border-right:{2};
border-bottom:{3};
border-left:{4};
font-size:{5}pt;
{6}
background:{7};{9}",
_styleIndex,
top ? ".5pt solid black" : "none",
right ? ".5pt solid black" : "none",
bottom ? ".5pt solid black" : "none",
left ? ".5pt solid black" : "none",
fontSize,
bold ? "font-weight:700;" : "",
bgColor.Name,
"{",
"}")) ;
_styleBuilder.Append("/r/n") ;
return "xl" + _styleIndex.ToString() ;
}
/**//// <summary>
/// 写 Meta 部分
/// </summary>
private void BuildMeta()
{
AppendLine("<meta http-equiv=/"Content-Type/" content=/"text/html; charset=gb2312/"") ;
AppendLine("<meta name=ProgId content=Excel.Sheet>") ;
AppendLine("<meta name=Generator content=/"Microsoft Excel 11/">") ;
}
/**//// <summary>
/// 写 Linked File
/// </summary>
private void BuildLink()
{
AppendLine("<link rel=File-List href=/"" + _fileName + ".files/filelist.xml/">") ;
AppendLine("<link rel=Edit-Time-Data href=/"" + _fileName + ".files/editdata.mso/">") ;
AppendLine("<link rel=OLE-Object-Data href=/"" + _fileName + ".files/oledata.mso/">") ;
}
private void BuildCSS()
{
string css = @"
<!--[if !mso]>
<style>
v/:* {behavior:url(#default#VML);}
o/:* {behavior:url(#default#VML);}
x/:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:LastAuthor>" + _authorName + @"</o:LastAuthor>
<o:LastSaved>" + DateTime.Now.ToUniversalTime().ToString("yyyy-MM-ddThh:mm:ssZ") + @"</o:LastSaved>
<o:Version>11.8107</o:Version>
</o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:""/."";
mso-displayed-thousand-separator:""/,"";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
.font6
{color:black;
font-size:9.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
.font7
{color:black;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
.font8
{color:black;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
.font9
{color:black;
font-size:9.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:/5E38/89C4;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding:0px;
mso-ignore:padding;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:middle;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
white-space:normal;}
" + _styleBuilder.ToString()
+ @"
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:SimSun;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>" ;
AppendLine(css) ;
}
private void BuildJavascript()
{
AppendLine(@"<![if !supportAnnotations]><style id=""dynCom"" type=""text/css""><!-- --></style>
<script language=""JavaScript""><!--
function msoCommentShow(com_id,anchor_id) {
if(msoBrowserCheck()) {
c = document.all(com_id);
a = document.all(anchor_id);
if (null != c) {
var cw = c.offsetWidth;
var ch = c.offsetHeight;
var aw = a.offsetWidth;
var ah = a.offsetHeight;
var x = a.offsetLeft;
var y = a.offsetTop;
var el = a;
while (el.tagName != ""BODY"") {
el = el.offsetParent;
x = x + el.offsetLeft;
y = y + el.offsetTop;
}
var bw = document.body.clientWidth;
var bh = document.body.clientHeight;
var bsl = document.body.scrollLeft;
var bst = document.body.scrollTop;
if (x + cw + ah/2 > bw + bsl && x + aw - ah/2 - cw >= bsl ) {
c.style.left = x + aw - ah / 2 - cw;
}
else {
c.style.left = x + ah/2;
}
if (y + ch + ah/2 > bh + bst && y + ah/2 - ch >= bst ) {
c.style.top = y + ah/2 - ch;
}
else {
c.style.top = y + ah/2;
}
c.style.visibility = ""visible"";
}
}
}
function msoCommentHide(com_id) {
if(msoBrowserCheck()) {
c = document.all(com_id)
if (null != c) {
c.style.visibility = ""hidden"";
c.style.left = ""-10000"";
c.style.top = ""-10000"";
}
}
}
function msoBrowserCheck() {
ms=navigator.appVersion.indexOf(""MSIE"");
vers = navigator.appVersion.substring(ms+5, ms+6);
ie4 = (ms>0) && (parseInt(vers) >=4);
return ie4
}
if (msoBrowserCheck()) {
document.styleSheets.dynCom.addRule("".msocomspan1"",""position:absolute"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""position:absolute"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""left:-1.5ex"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""width:2ex"");
document.styleSheets.dynCom.addRule("".msocomspan2"",""height:0.5em"");
document.styleSheets.dynCom.addRule("".msocomanch"",""font-size:0.5em"");
document.styleSheets.dynCom.addRule("".msocomanch"",""color:red"");
document.styleSheets.dynCom.addRule("".msocomhide"",""display: none"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""visibility: hidden"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""position: absolute"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""top:-10000"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""left:-10000"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""width: 33%"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""background: infobackground"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""color: infotext"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-top: 1pt solid threedlightshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-right: 2pt solid threedshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-bottom: 2pt solid threedshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""border-left: 1pt solid threedlightshadow"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""padding: 3pt 3pt 3pt 3pt"");
document.styleSheets.dynCom.addRule("".msocomtxt"",""z-index: 100"");
}
// -->
</script>
<![endif]>") ;
}
private void BuildExcelProperties()
{
AppendLine(string.Format(@"<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>{0}</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>285</x:DefaultRowHeight>
<x:Selected/>
<x:DoNotDisplayGridlines/>
<x:Panes/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>8550</x:WindowHeight>
<x:WindowWidth>14940</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>45</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=""edit"" spidmax=""1027""/>
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=""edit"">
<o:idmap v:ext=""edit"" data=""1""/>
</o:shapelayout></xml><![endif]-->",
_fileName));
}
#endregion
About Comment#region About Comment
/**//// <summary>
/// 批注的 Builder
/// </summary>
StringBuilder _commentBuilder = new StringBuilder() ;
int curIndex = 0 ;
/**//// <summary>
/// Shape Type
/// </summary>
const string SHAPE_TYPE = @"<v:shapetype id=""_x0000_t202"" coordsize=""21600,21600"" o:spt=""202"" path=""m,l,21600r21600,l21600,xe"">
<v:stroke joinstyle=""miter""/>
<v:path gradientshapeok=""t"" o:connecttype=""rect""/>
</v:shapetype>" ;
/**//// <summary>
/// 添加批注
/// </summary>
/// <param name="row">被批注单元格从0开始所在的行索引</param>
/// <param name="column">被批注单元格从0开始所在的列索引</param>
/// <param name="text">单元格内容</param>
/// <param name="comment">批注内容</param>
/// <returns>增加了批注后的单元格内容</returns>
public string AddComment(int row, int column, string text, string comment)
{
if (row < 0)
{
throw new ArgumentOutOfRangeException("row") ;
}
if (column < 0)
{
throw new ArgumentOutOfRangeException("column") ;
}
if (text == null)
{
throw new ArgumentNullException("text") ;
}
if (comment == null)
{
throw new ArgumentNullException("comment") ;
}
curIndex++ ;
_commentBuilder.Append(string.Format(@"
<div style='mso-element:comment'><![if !supportAnnotations]>
<div id=""_com_{0}"" class=msocomtxt
οnmοuseοver=""msoCommentShow('_com_{0}','_anchor_{0}')""
οnmοuseοut=""msoCommentHide('_com_{0}')"" language=JavaScript><![endif]>
<div><![if !supportAnnotations]><a class=msocomhide href=""#_msoanchor_{0}""
name=""_msocom_{0}"">[{0}]</a><![endif]><!--[if gte mso 9]><xml>
{1}<v:shape id=""_x0000_s102{0}"" type=""#_x0000_t202"" style='position:absolute;
margin-left:87.75pt;margin-top:-12.75pt;width:96pt;height:59.25pt;z-index:1;
visibility:hidden' fillcolor=""infoBackground [80]"" o:insetmode=""auto"">
<v:fill color2=""infoBackground [80]""/>
<v:shadow on=""t"" color=""black"" obscured=""t""/>
<v:path o:connecttype=""none""/>
<v:textbox style='mso-direction-alt:auto'/>
<x:ClientData ObjectType=""Note"">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:AutoFill>False</x:AutoFill>
<x:Row>{2}</x:Row>
<x:Column>{3}</x:Column>
<x:Author>{4}</x:Author>
</x:ClientData>
</v:shape></xml><![endif]--><![if !vml]><span style='mso-ignore:vglayout'><![endif]>
<div v:shape=""_x0000_s102{0}"" style='padding:.75pt 0pt 0pt .75pt;text-align:left'
class=shape><font class=""font6"">{4}:</font><font class=""font7""><br>
{5}</font></div>
<![if !vml]></span><![endif]></div>
<![if !supportAnnotations]></div>
<![endif]></div>",
curIndex,
(curIndex == 1 ? SHAPE_TYPE : ""),
row,
column,
_authorName,
comment)) ;
return string.Format(@"{1}<![if !supportAnnotations]><span
class=msocomspan1><span class=msocomspan2 id=""_anchor_{0}""
οnmοuseοver=""msoCommentShow('_com_{0}','_anchor_{0}')""
οnmοuseοut=""msoCommentHide('_com_{0}')"" language=JavaScript><a
class=msocomanch href=""#_msocom_{0}"" name=""_msoanchor_{0}"">[1]</a></span></span><![endif]>",
curIndex,
text) ;
}
#endregion
}
}
示例:
private
void
Button1_Click(
object
sender, System.EventArgs e)
{
string fileName = "Crude_Data" ;
string authorName = "Author Name" ;
ResponseExcelWithComment excel = new ResponseExcelWithComment(fileName, authorName) ;
sqlConnection1.Open() ;
dataSet11 = new DataSet1() ;
sqlDataAdapter1.Fill(dataSet11.UserInformation) ;
sqlConnection1.Close() ;
int curRow = 0 ;
int curCol = 0 ;
string style1 = "" ;
StringBuilder tableBuilder = new StringBuilder() ;
tableBuilder.Append(@"<table>") ;
tableBuilder.Append("<tr>") ;
style1 = excel.AddCellStyle(Color.Blue, true, true, true, true, 9, true) ;
tableBuilder.Append(string.Format("<td class={0}>", style1)) ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "User Name", "用户名")) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "Password", "密码")) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "Email", "电子邮件")) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append("</tr>") ;
string style2 = excel.AddCellStyle(Color.Yellow, true, true, false, false, 9, false) ;
foreach (DataSet1.UserInformationRow userRow in dataSet11.UserInformation)
{
curRow++ ;
curCol = 0 ;
tableBuilder.Append(string.Format("<td class={0}>", style2)) ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.UserName, userRow.UserName)) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Password, userRow.Password)) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Email, userRow.Email)) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append("</tr>") ;
}
tableBuilder.Append(@"</table>") ;
excel.AppendBodyContent(tableBuilder.ToString()) ;
excel.WriteResponse() ;
}
{
string fileName = "Crude_Data" ;
string authorName = "Author Name" ;
ResponseExcelWithComment excel = new ResponseExcelWithComment(fileName, authorName) ;
sqlConnection1.Open() ;
dataSet11 = new DataSet1() ;
sqlDataAdapter1.Fill(dataSet11.UserInformation) ;
sqlConnection1.Close() ;
int curRow = 0 ;
int curCol = 0 ;
string style1 = "" ;
StringBuilder tableBuilder = new StringBuilder() ;
tableBuilder.Append(@"<table>") ;
tableBuilder.Append("<tr>") ;
style1 = excel.AddCellStyle(Color.Blue, true, true, true, true, 9, true) ;
tableBuilder.Append(string.Format("<td class={0}>", style1)) ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "User Name", "用户名")) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "Password", "密码")) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "Email", "电子邮件")) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append("</tr>") ;
string style2 = excel.AddCellStyle(Color.Yellow, true, true, false, false, 9, false) ;
foreach (DataSet1.UserInformationRow userRow in dataSet11.UserInformation)
{
curRow++ ;
curCol = 0 ;
tableBuilder.Append(string.Format("<td class={0}>", style2)) ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.UserName, userRow.UserName)) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Password, userRow.Password)) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append(string.Format("<td class={0}>", style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Email, userRow.Email)) ;
tableBuilder.Append("</td>") ;
tableBuilder.Append("</tr>") ;
}
tableBuilder.Append(@"</table>") ;
excel.AppendBodyContent(tableBuilder.ToString()) ;
excel.WriteResponse() ;
}