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

转自:http://www.cnblogs.com/karoc/archive/2006/12/13/591472.html

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 MethodHead Write Method

About CommentAbout Comment

}

调用

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

}

posted @ 2007-05-29 16:40 WEBBER 阅读(355) 评论(0) 收藏举报

刷新页面返回顶部

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

公告