using System;
using System.Text;
using System.Web;
using System.Web.UI;
namespace WebTest
{
/**////
/// ExcelWithComment 的摘要说明。
///
public class ResponseExcelWithComment
{
/**////
/// 当前 HttpResponse
///
private static HttpResponse Response
{
get
{
return HttpContext.Current.Response ;
}
}
/**////
/// 用于构建整个网页内容的 StringBuilder
///
private StringBuilder _htmlBuilder = new StringBuilder() ;
private StringBuilder _contentBuilder = new StringBuilder() ;
/**////
/// 准备输出的Excel的文件名，不含扩展名
///
private readonly string _fileName ;
/**////
/// Excel 作者
///
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() ;
}
/**////
/// 为 Body 中的 Content添加行
///
///
public void AppendBodyContent(string line)
{
if (line != null)
{
_contentBuilder.Append(line) ;
}
_contentBuilder.Append("\r\n") ;
}
/**////
/// 为 整个Html 添加一行内容
///
///
private void AppendLine(string line)
{
if (line != null)
{
_htmlBuilder.Append(line) ;
}
_htmlBuilder.Append("\r\n") ;
}
private void BuildHtml()
{
AppendLine(@"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("");
}
/**////
/// 写 部分
///
private void BuildHead()
{
AppendLine("
");
BuildMeta();
BuildLink();
BuildCSS();
BuildJavascript();
BuildExcelProperties();
AppendLine((""));
}
/**////
/// 写 部分
///
private void BuildBody()
{
AppendLine("
");
AppendLine(_contentBuilder.ToString());
//comment list
AppendLine(@"
");
AppendLine(_commentBuilder.ToString());
AppendLine("");
AppendLine("");
}
Head Write Method#region Head Write Method
private int _styleIndex = 30 ;
private StringBuilder _styleBuilder = new StringBuilder() ;
/**////
/// 为单元格添加一种样式
///
/// 背景色
/// 顶部是否闭合
/// 底部是否闭合
/// 左边是否闭合
/// 右边
/// 文字大小
/// 是否为粗体
/// css类名
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() ;
}
/**////
/// 写 Meta 部分
///
private void BuildMeta()
{
AppendLine(" AppendLine("") ;
AppendLine("") ;
}
/**////
/// 写 Linked File
///
private void BuildLink()
{
AppendLine("") ;
AppendLine("") ;
AppendLine("") ;
}
private void BuildCSS()
{
string css = @"
" ;
AppendLine(css) ;
}
private void BuildJavascript()
{
AppendLine(@"
") ;
}
private void BuildExcelProperties()
{
AppendLine(string.Format(@"",
_fileName));
}
#endregion
About Comment#region About Comment
/**////
/// 批注的 Builder
///
StringBuilder _commentBuilder = new StringBuilder() ;
int curIndex = 0 ;
/**////
/// Shape Type
///
const string SHAPE_TYPE = @"
" ;
/**////
/// 添加批注
///
/// 被批注单元格从0开始所在的行索引
/// 被批注单元格从0开始所在的列索引
/// 单元格内容
/// 批注内容
/// 增加了批注后的单元格内容
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(@"
onmouseover=""msoCommentShow('_com_{0}','_anchor_{0}')""
onmouseout=""msoCommentHide('_com_{0}')"" language=JavaScript>
",
curIndex,
(curIndex == 1 ? SHAPE_TYPE : ""),
row,
column,
_authorName,
comment)) ;
return string.Format(@"{1} class=msocomspan1> onmouseover=""msoCommentShow('_com_{0}','_anchor_{0}')""
onmouseout=""msoCommentHide('_com_{0}')"" language=JavaScript> class=msocomanch href=""#_msocom_{0}"" name=""_msoanchor_{0}"">[1]",
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(@"") ;
tableBuilder.Append("") ;
style1 = excel.AddCellStyle(Color.Blue, true, true, true, true, 9, true) ;
tableBuilder.Append(string.Format("", style1)) ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "User Name", "用户名")) ;
tableBuilder.Append("") ;
tableBuilder.Append(string.Format("", style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "Password", "密码")) ;
tableBuilder.Append("") ;
tableBuilder.Append(string.Format("", style1)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, "Email", "电子邮件")) ;
tableBuilder.Append("") ;
tableBuilder.Append("") ;
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("", style2)) ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.UserName, userRow.UserName)) ;
tableBuilder.Append("") ;
tableBuilder.Append(string.Format("", style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Password, userRow.Password)) ;
tableBuilder.Append("") ;
tableBuilder.Append(string.Format("", style2)) ;
curCol++ ;
tableBuilder.Append(excel.AddComment(curRow, curCol, userRow.Email, userRow.Email)) ;
tableBuilder.Append("") ;
tableBuilder.Append("") ;
}
tableBuilder.Append(@"") ;
excel.AppendBodyContent(tableBuilder.ToString()) ;
excel.WriteResponse() ;
}