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

不调用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]>
<hr class=msocomhide align=left size=1 width=""33%"">
<![endif]>");
            AppendLine(_commentBuilder.ToString());
            AppendLine("</div>");


            AppendLine("</body>");
        }

        Head Write Method 

        About 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 @ 2006-12-13 21:48  karoc  阅读(2809)  评论(2)  编辑  收藏  举报