不调用Excel对象模型，直接用Response输出可以加批注的Excel。
\n
代码如下：
\n
using System;
using System.Text;
using System.Web;
using System.Web.UI;
\n
namespace WebTest
{
/**//// <summary>
/// ExcelWithComment 的摘要说明。
/// </summary>
public class ResponseExcelWithComment
{
/**//// <summary>
/// 当前 HttpResponse
/// </summary>
private static HttpResponse Response
{
get
{
return HttpContext.Current.Response ;
}
}
\n
/**//// <summary>
/// 用于构建整个网页内容的 StringBuilder
/// </summary>
private StringBuilder _htmlBuilder = new StringBuilder() ;
private StringBuilder _contentBuilder = new StringBuilder() ;
\n
/**//// <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”) ;
}
\n
if (authorName == null)
{
throw new ArgumentNullException(“authorName”) ;
}
\n
_fileName = fileName ;
_authorName = authorName ;
}
\n
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() ;
}
\n
/**//// <summary>
/// 为 Body 中的 Content添加行
/// </summary>
/// <param name=”line”></param>
public void AppendBodyContent(string line)
{
if (line != null)
{
_contentBuilder.Append(line) ;
}
_contentBuilder.Append(“\\r\\n”) ;
}
\n
/**//// <summary>
/// 为 整个Html 添加一行内容
/// </summary>
/// <param name=”line”></param>
private void AppendLine(string line)
{
if (line != null)
{
_htmlBuilder.Append(line) ;
}
_htmlBuilder.Append(“\\r\\n”) ;
}
\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″”>”);
\n
BuildHead();
BuildBody();
\n
AppendLine(“</html>”);
}
\n
/**//// <summary>
/// 写 <head></head> 部分
/// </summary>
private void BuildHead()
{
AppendLine(“<head>”);
\n
BuildMeta();
BuildLink();
BuildCSS();
BuildJavascript();
BuildExcelProperties();
\n
AppendLine((“</head>”));
}
\n
/**//// <summary>
/// 写 <body></body> 部分
/// </summary>
private void BuildBody()
{
AppendLine(“<body link=blue vlink=purple>”);
\n
AppendLine(_contentBuilder.ToString());
\n
//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>”);
\n
AppendLine(“</body>”);
}
\n
Head Write Method#region Head Write Method
\n
private int _styleIndex = 30 ;
private StringBuilder _styleBuilder = new StringBuilder() ;
\n
/**//// <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”) ;
\n
return “xl” + _styleIndex.ToString() ;
}
\n
/**//// <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\\”>”) ;
}
\n
/**//// <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\\”>”) ;
}
\n
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>
<DocumentProperties>
<LastAuthor>” + _authorName + @”</LastAuthor>
<LastSaved>” + DateTime.Now.ToUniversalTime().ToString(“yyyy-MM-ddThh:mm:ssZ”) + @”</LastSaved>
<Version>11.8107</Version>
</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) ;
}
\n
private void BuildJavascript()
{
AppendLine(@”<![if !supportAnnotations]><style id=”"dynCom”" type=”"text/css”"><!– –></style>
\n
<script language=”"JavaScript”"><!–
\n
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”";
}
}
}
\n
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″”;
}
}
}
\n
function msoBrowserCheck() {
ms=navigator.appVersion.indexOf(“”MSIE”");
vers = navigator.appVersion.substring(ms+5, ms+6);
ie4 = (ms>0) && (parseInt(vers) >=4);
return ie4
}
\n
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″”);
}
\n
// –>
</script>
<![endif]>”) ;
}
\n
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>
<shapedefaults v:ext=”"edit”" spidmax=”"1027″”/>
</xml><![endif]–><!–[if gte mso 9]><xml>
<shapelayout v:ext=”"edit”">
<idmap v:ext=”"edit”" data=”"1″”/>
</shapelayout></xml><![endif]–>”,
_fileName));
}
\n
#endregion
\n
About Comment#region About Comment
\n
/**//// <summary>
/// 批注的 Builder
/// </summary>
StringBuilder _commentBuilder = new StringBuilder() ;
\n
int curIndex = 0 ;
\n
/**//// <summary>
/// Shape Type
/// </summary>
const string SHAPE_TYPE = @”<v:shapetype id=”"_x0000_t202″” coordsize=”"21600,21600″” spt=”"202″” path=”"m,l,21600r21600,l21600,xe”">
<v:stroke joinstyle=”"miter”"/>
<v:path gradientshapeok=”"t”" connecttype=”"rect”"/>
</v:shapetype>” ;
\n
/**//// <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”) ;
}
\n
curIndex++ ;
\n
_commentBuilder.Append(string.Format(@”
\n
<div style=’mso-element:comment’><![if !supportAnnotations]>
\n
<div id=”"_com_{0}”" class=msocomtxt
onmouseover=”"msoCommentShow(‘_com_{0}’,\'_anchor_{0}’)”"
onmouseout=”"msoCommentHide(‘_com_{0}’)”" language=JavaScript><![endif]>
\n
<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]“” insetmode=”"auto”">
<v:fill color2=”"infoBackground [80]“”/>
<v:shadow on=”"t”" color=”"black”" obscured=”"t”"/>
<v:path 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]>
\n
<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>
\n
<![if !vml]></span><![endif]></div>
\n
<![if !supportAnnotations]></div>
\n
<![endif]></div>”,
curIndex,
(curIndex == 1 ? SHAPE_TYPE : “”),
row,
column,
_authorName,
comment)) ;
\n
return string.Format(@”{1}<![if !supportAnnotations]><span
class=msocomspan1><span class=msocomspan2 id=”"_anchor_{0}”"
onmouseover=”"msoCommentShow(‘_com_{0}’,\'_anchor_{0}’)”"
onmouseout=”"msoCommentHide(‘_com_{0}’)”" language=JavaScript><a
class=msocomanch href=”"#_msocom_{0}”" name=”"_msoanchor_{0}”">[1]</a></span></span><![endif]>”,
curIndex,
text) ;
}
\n
#endregion
}
}
\n
示例：
private void Button1_Click(object sender, System.EventArgs e)
{
string fileName = “Crude_Data” ;
string authorName = “Author Name” ;
ResponseExcelWithComment excel = new ResponseExcelWithComment(fileName, authorName) ;
\n
sqlConnection1.Open() ;
dataSet11 = new DataSet1() ;
sqlDataAdapter1.Fill(dataSet11.UserInformation) ;
sqlConnection1.Close() ;
\n
int curRow = 0 ;
int curCol = 0 ;
string style1 = “” ;
\n
StringBuilder tableBuilder = new StringBuilder() ;
tableBuilder.Append(@”<table>”) ;
tableBuilder.Append(“<tr>”) ;
\n
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>”) ;
\n
tableBuilder.Append(string.Format(“<td class={0}>”, style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, “Password”, “密码”)) ;
tableBuilder.Append(“</td>”) ;
\n
tableBuilder.Append(string.Format(“<td class={0}>”, style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, “Email”, “电子邮件”)) ;
tableBuilder.Append(“</td>”) ;
tableBuilder.Append(“</tr>”) ;
\n
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>”) ;
\n
tableBuilder.Append(string.Format(“<td class={0}>”, style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Password, userRow.Password)) ;
tableBuilder.Append(“</td>”) ;
\n
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>”) ;
}
\n
tableBuilder.Append(@”</table>”) ;
\n
excel.AppendBodyContent(tableBuilder.ToString()) ;
excel.WriteResponse() ;
}
\n \n