直接用Response输出可以加批注的Excel

最新推荐文章于 2024-01-04 11:18:36 发布

weixin_30251829

最新推荐文章于 2024-01-04 11:18:36 发布

阅读量153

点赞数

文章标签： ui javascript ruby ViewUI

原文链接：http://www.cnblogs.com/karoc/archive/2006/12/13/591472.html

版权

不调用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]>

<![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;}

{mso-height-source:auto;

mso-ruby-visibility:none;}

col

{mso-width-source:auto;

mso-ruby-visibility:none;}

{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;}

{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;}

{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>

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 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() ;

}

转载于:https://www.cnblogs.com/karoc/archive/2006/12/13/591472.html

weixin_30251829

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
直接用Response输出可以加批注的Excel

不调用Excel对象模型，直接用Response输出可以加批注的Excel。代码如下：usingSystem;usingSystem.Text;usingSystem.Web;usingSystem.Web.UI;namespaceWebTest{/**////<summary>///ExcelWithComment的摘要说明。///&lt...
复制链接

扫一扫