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

拼吾爱程序人生.Net编程 Asp.Net 直接用Response输出可以加批注的Excel

1 / 1 页

跳转页

查看:854

striver

个人空间 相册
性别:
来自:拼吾爱
积分:7057
帖子:6969
注册: 2007-04-09

2008-02-01 08:31 | 只看楼主

树型| 收藏| 小中大复制帖子链接到剪贴板1F

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

文/tianzhijian 出处/CSDN

不调用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.C;
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 = @"

<style>

</style>" ;
AppendLine(css) ;
}

private void BuildJavascript()
{
AppendLine(@"<![if !supportAnnotations]><style id=""dynCom"" type=""text/css""></style>

<script language=""JavaScript"">
</script>
<![endif]>") ;
}

private void BuildExcelProperties()
{
AppendLine(string.Format(@"",
_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:crect""/>
</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
msoCommentShow('_com_{0}','_anchor_{0}')""
msoCommentHide('_com_{0}')"" language=JavaScript><![endif]>

<div><![if !supportAnnotations]><a class=msocomhide href=""#_msoanchor_{0}""
name=""_msocom_{0}"">[{0}]</a><![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}""
msoCommentShow('_com_{0}','_anchor_{0}')""
msoCommentHide('_com_{0}')"" language=JavaScript><a
class=msocomanch href=""#_msocom_{0}"" name=""_msoanchor_{0}"">[1]</a></span></span><![endif]>",
curIndex,
text) ;
}

#endregion
}
}

感谢原创者的辛勤劳动，希望对您有所帮助，转载请注明原出处。

您可能对 [Asp.Net] 的这些文章也感兴趣：

ASP.NET Mvc Preview 5 演示Demo #2 实现Membership角色权限控制

Asp.NET常用函数

ASP.net基于窗体的身份验证

Extending the GridView Control

资料验证的asp.net程序

如何在ItemDataBound内获得DataGrid的列名称HeaderText

ASP.NET中Cookie编程的基础知识(1)

用ASP.NET写你自己的代码生成器

用ASP.NET上传图片并生成带版权信息的缩略图

C#中数据库操作

striver

个人空间 相册
性别:
来自:拼吾爱
积分:7057
帖子:6969
注册: 2007-04-09

2008-02-01 08:31 | 只看楼主

树型| 收藏| 小中大复制帖子链接到剪贴板2F

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

示例：
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() ;
}

<<上一主题|下一主题>>

1 / 1 页

跳转页

快速回复帖子

标题

禁用 URL 识别
禁用表情
禁用 Discuz!NT 代码
使用个人签名

default

正在加载表情...

[完成后可按 Ctrl+Enter 无刷新发布]

拼吾爱程序人生.Net编程Asp.Net 直接用Response输出可以加批注的Excel

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

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

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

快速回复帖子

拼吾爱程序人生.Net编程 Asp.Net 直接用Response输出可以加批注的Excel