Jason

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

OWC作为Office嵌入IE浏览器的插件确实不错,但OWC没有直接打印的功能,也没有可调用的打印接口。

根据网上相关资料,总结了以下3种在IE浏览器内打印的方法:

1. 将OWC的内容保存成htm文件,然后调用这个htm文件打印。

缺点:在服务器端设置相应文件夹的权限,定期清理生成的htm文件。另外由于是调用浏览器的打印功能,无法通过脚本设置打印参数。

最大的一个问题是,调用 window.showModalDialog后,父页面的OWC控件的内容会被清空,搞不清什么状况。

 1 protected void btnPrint_Click(object sender, EventArgs e)
 2     {
 3         try
 4         {
 5             //Export and save the OWC in HTM format.
 6             SpreadsheetClass spreadSheetClass = new SpreadsheetClass();
 7             spreadSheetClass.XMLData = hdnXML.Value;
 8             spreadSheetClass.Export(Server.MapPath("OWCXML.htm"), 
 9  Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone,
10  SheetExportFormat.ssExportHTML);
11 
12             //Inject Print function into HTM file 
13             StreamWriter streamWriter;
14             streamWriter = System.IO.File.AppendText(Server.MapPath("OWCXML.htm"));
15             streamWriter.WriteLine
16 ("<html><body onload='window.print()'><table><tr><td></td></tr></table></body></html>");
17             streamWriter.Flush();
18             streamWriter.Close();
19 
20             //Call Javascript to popup 'OWCXML.htm'
21             ClientScript.RegisterClientScriptBlock
22 (this.GetType(), "Key1", "<script>window.showModalDialog('OWCXML.htm','popup'); </script>");
23         }
24         catch (Exception ex)
25         {
26             throw ex;
27         }
28     }

具体参考:1. OWC -Office Web Component v11.0 Print Functionality

              2. Office Web Component v11.0 Spreadsheet And AJAX Interoperatibility Part 1

2. 做一个ActiveX插件,在页面加载插件

缺点:比较麻烦,并且需要配置IE浏览器的设置

参考:Word/Excel ActiveX Controls in ASP.NET

3. 通过VBScript在内存中打开Excel文件并调用Excel的打印功能,这是本文推荐的方式。

这种方式又分2种方案:

第一方案:服务器端已经生成Excel文件,通过VBScript直接读取服务器端的Excel,在客户端打印。

sub btnPrint_OnClick()
            Dim xlApp, wkbk, wksht
            'SpreadSheet1为OWC控件ID
            'On Error Resume Next
            Set xlApp = Nothing
            Set xlApp = CreateObject("Excel.Application")
          
            If Not (xlApp Is Nothing) Then
                'httpDownloadFile指向的是服务器端生成的Excel文件
                '如:http://xxxx.com/excel/demo.xls
                Set wkbk = xlApp.Workbooks.Open("<%=httpDownloadFile%>")
                'MsgBox("<%=httpDownloadFile %>>")
                
                'Get a reference to the first worksheet in the new book
                Set wksht = wkbk.Worksheets(1)

                'Call AutoFit over the used range to make sure
                'everything is visible.
                'wksht.UsedRange.Columns.AutoFit
        
                'Get the page setup object
                Set pgsetup = wksht.PageSetup

                '1 = xlPortrait,   2 = xlLandscape
                pgsetup.Orientation = 2 
                pgsetup.FitToPagesWide = 1
                '注意调用FitToPagesWide或者FitToPagesHeight,必须将Zoom置为False,否则不起作用
                pgsetup.Zoom = false
                pgsetup.CenterHorizontally = true
                'Value   Paper size 
                '1       Letter (8.5 x 11 in.) 
                '2       Letter Small (8.5 x 11 in.) 
                '3       Tabloid (11 x 17 in.) 
                '4       Ledger (17 x 11 in.) 
                '5       Legal (8.5 x 14 in.) 
                '6       Statement (5.5 x 8.5 in.) 
                '7       Executive (7.25 x 10.5 in.) 
                '8       A3 (297 x 420 mm) 
                '9       A4 (210 x 297 mm) 
                '10      A4 Small (210 x 297 mm) 
                '11      A5 (148 x 210 mm) 
                '12      B4 (250 x 354 mm) 
                '13      B5 (182 x 257 mm) 
                '14      Folio (8.5 x 13 in.) 
                '15      Quarto (215 x 275 mm) 
                '16      11 x 17 in. 
                '18      Note (8.5 x 11 in.) 
                '19      Envelope #9 (3.875 x 8.875 in.) 
                '20      Envelope #10 (4.125 x 9.5 in.) 
                '21      Envelope #11 (4.5 x 10.375 in.) 
                '22      Envelope #12 (4.25 x 11 in.) 
                '23      Envelope #14 (5 x 11.5 in.) 
                '24      C size sheet (17 x 22 in.) 
                '25      D size sheet (22 x 34 in.) 
                '26      E size sheet (34 x 44 in.) 
                '27      Envelope DL (110 x 220 mm) 
                '28      Envelope C5 (162 x 229 mm) 
                '29      Envelope C3 (324 x 458 mm) 
                '30      Envelope C4 (229 x 324 mm) 
                '31      Envelope C6 (114 x 162 mm) 
                '32      Envelope C65 (114 x 229 mm) 
                '33      Envelope B4 (250 x 353 mm) 
                '34      Envelope B5 (176 x 250 mm 
                '35      Envelope B6 (176 x 125 mm) 
                '36      Envelope (110 x 230 mm) 
                '37      Envelope Monarch (3.875 x 7.5 in.) 
                '38      6-3/4 Envelope (3.625 x 6.5 in.) 
                '39      US Std Fanfold (14.875 x 11 in.) 
                '40      German Std Fanfold (8.5 x 12 in.) 
                '41      German Legal Fanfold (8.5 x 13 in.) 
                '256     User-defined 
                pgsetup.PaperSize = 9

                 'Set UserControl to True and Visible to True
                'to bring Excel to the foreground and let
                'the user interact with it. Excel will not close
                'when the xlApp variable falls out of scope because
                'of these calls
                xlApp.UserControl = True
                'If U want PrintPreview, U need set Visible = true!
                xlApp.Visible = false
        
                'put the current worksheet into print preview
                'wksht.PrintPreview

                wksht.PrintOut
                
                xlApp.DisplayAlerts = False
                xlApp.Quit

                'release all our Excel objects
                'Set pgsetup = Nothing
                Set wksht = Nothing
                Set wkbk = Nothing
                Set xlApp = Nothing
            End If
        end sub

缺点:如果Excel比较大的话,页面加载OWC时会比较慢,另外点击打印按钮,VBScript去服务器端读取Excel同样比较慢。

第二方案:在页面上复制OWC控件的内容到内存中,然后创建Excel文件复制Sheet内容,打印。

 1 sub btnPrint_OnClick()
 2             Dim xlApp, wkbk, wksht
 3             'document.Form1.SpreadSheet1.ActiveSheet.Activate()
 4             '复制OWC中的内容 'document.Form1.SpreadSheet1.ActiveSheet.UsedRange.Copy 
 5             On Error Resume Next
 6             Set xlApp = Nothing
 7             Set xlApp = CreateObject("Excel.Application")
 8             
 9             If Not (xlApp Is Nothing) Then
10                 'Add a new workbook to the Excel instance
11                 Set wkbk = xlApp.Workbooks.Add()
12                 
13                 'Get a reference to the first worksheet in the new book
14                 Set wksht = wkbk.Worksheets(1)
15         
16                 'Paste the contents of the clipbard into that sheet
17                 wksht.Paste wksht.Range("a1")
18 
19                 'Call AutoFit over the used range to make sure
20                 'everything is visible.
21                 wksht.UsedRange.Columns.AutoFit
22         
23                 'Get the page setup object
24                 Set pgsetup = wksht.PageSetup
25 
26                 '1 = xlPortrait,   2 = xlLandscape
27                 pgsetup.Orientation = 2 
28                 pgsetup.FitToPagesWide = 1
29                 pgsetup.Zoom = false
30                 pgsetup.CenterHorizontally = true
31                 pgsetup.PaperSize = 9
32 
33                  'Set UserControl to True and Visible to True
34                 'to bring Excel to the foreground and let
35                 'the user interact with it. Excel will not close
36                 'when the xlApp variable falls out of scope because
37                 'of these calls
38                 xlApp.UserControl = True
39                 'If U want PrintPreview, U need set Visible = true!
40                 xlApp.Visible = false
41         
42                 'put the current worksheet into print preview
43                 'wksht.PrintPreview
44 
45                 wksht.PrintOut
46                 
47                 xlApp.DisplayAlerts = False
48                 xlApp.Quit
49 
50                 'release all our Excel objects
51                 'Set pgsetup = Nothing
52                 Set wksht = Nothing
53                 Set wkbk = Nothing
54                 Set xlApp = Nothing
55             End If
56         end sub

缺点:需要客户端设置IE选项,允许浏览器访问clipboard的内容,否则每次调用会有提示框弹出。

 参考:Code Walkthrough: Printing the PivotTable Component from Microsoft Internet Explorer

FILE: OWCHelpr.exe Demonstrates Automation to Excel for Printing a PivotTable Component on a Web Page

Hui’s Excel Report Printer

 打印时遇到的问题:

1. ActiveX component can't create object: 'Excel.Application'

原因:确保客户端已安装Excel并需要将网站URL加入到信任站点,并Enable所有有关ActiveX的设置。

2. Unable to set the PaperSize property of the PageSetup class

原因:确保客户端已安装打印机,如果安装正确仍有此问题,则需要配置一下打印机的设置。

默认的纸张来源为Paper input bin, 需要更改为Automatically Select.

posted on 2013-01-30 10:18  Jsang  阅读(2020)  评论(0编辑  收藏  举报