直接用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>");
        }


        
#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;}
tr
    {mso-height-source:auto;
    mso-ruby-visibility:none;}
col
    {mso-width-source:auto;
    mso-ruby-visibility:none;}
br
    {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;}
td
    {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;}
rt
    {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>

<script language=""JavaScript""><!--

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
    }

}



示例:
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, truetruetruetrue9true) ;
            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, truetruefalsefalse9false) ;
            
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() ;
        }

Feedback

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值