[转帖]复杂报表另存为的技巧一例
<p><span>向上级部门上报报表是很常见的，其中不乏有复杂报表，最近，在客户需求中有这样一例，每月都需上报报表（国家统一格式），虽然本地已有了</span><span lang="EN-US">MIS</span><span>，但每月上报不得不将报表打印出来，通过邮局邮寄给上级部门，现想将报表导出成单一的文件（常见文件格式，最好能用</span><span lang="EN-US">Office</span><span>打开），通过邮件上报。起初，马上就想到了数据窗口的</span><span lang="EN-US">Saveas</span><span>（），但是细分析，报表格式复杂，</span><span lang="EN-US">Saveas</span><span>根本实现不了，于是想到了编程写入</span><span lang="EN-US">Excel</span><span>中，同样，由于格式复杂，</span><span lang="EN-US">VBA</span><span>的编程量也很大，如合并列、拆分列等，于是想到了先生成</span><span lang="EN-US">WMF</span><span>文件，再贴到</span><span lang="EN-US">WORD</span><span>中的办法，（上级部门不要求数据可编辑），实现效果很好。代码如下</span><span lang="EN-US"><p></p></span></p><p></p><p></p><p><span lang="EN-US">if dw_1.rowcount() = 0 then return<p></p></span></p><p></p><p></p><p><span lang="EN-US">int li_ret,value<p></p></span></p><p></p><p></p><p><span lang="EN-US">oleObject lo_Word //ole</span><span>对象</span><span lang="EN-US"><br/>string docname,named<p></p></span></p><p></p><p></p><p><span lang="EN-US">value = GetFileSaveName("</span><span>另存为</span><span lang="EN-US">", docname, named, "DOC", &<p></p></span></p><p></p><p></p><p><span lang="EN-US">"Word</span><span>文档</span><span lang="EN-US">(*.DOC), *.DOC")<p></p></span></p><p></p><p></p><p><span lang="EN-US">IF value <> 1 THEN<p></p></span></p><p></p><p></p><p><span lang="EN-US">Return<p></p></span></p><p></p><p></p><p><span lang="EN-US">end if<p></p></span></p><p></p><p></p><p><span lang="EN-US">if FileExists(docname) then<p></p></span></p><p></p><p></p><p><span lang="EN-US">if messagebox('</span><span>提示</span><span lang="EN-US">','</span><span>文件已经存在要覆盖吗？</span><span lang="EN-US">',question!,yesno!,2) = 1 then<p></p></span></p><p></p><p></p><p><span lang="EN-US">filedelete(docname)<p></p></span></p><p></p><p></p><p><span lang="EN-US">else<p></p></span></p><p></p><p></p><p><span lang="EN-US">return<p></p></span></p><p></p><p></p><p><span lang="EN-US">end if<p></p></span></p><p></p><p></p><p><span lang="EN-US">end if<p></p></span></p><p></p><p></p><p><span lang="EN-US">//</span><span>生成</span><span lang="EN-US">WMF</span><span>临时文件，这种方法不完美，应该取</span><span lang="EN-US">Windows</span><span>的临时目录和空的临时文件</span><span lang="EN-US">ID<br/>if fileexists("C:\windows\temp\yyglgzyb.wmf") then filedelete ("C:\windows\temp \yyglgzyb.wmf" )<p></p></span></p><p></p><p></p><p><span lang="EN-US">dw_1.SaveAs("C:\windows\temp\yyglgzyb.wmf", WMF!, true)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//</span><span>打开</span><span lang="EN-US">Word</span><span>文件</span><span lang="EN-US"><p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word = CREATE OLEObject<p></p></span></p><p></p><p></p><p><span lang="EN-US">li_Ret = lo_Word.ConnectToObject("","word.application")<p></p></span></p><p></p><p></p><p><span lang="EN-US">IF li_Ret <> 0 THEN<p></p></span></p><p></p><p></p><p><span lang="EN-US">//</span><span>没有打开则新建</span><span lang="EN-US"><p></p></span></p><p></p><p></p><p><span lang="EN-US"> li_Ret = lo_Word.ConnectToNewObject("word.application")<p></p></span></p><p></p><p></p><p><span lang="EN-US"> if li_Ret <> 0 then<p></p></span></p><p></p><p></p><p><span lang="EN-US"> MessageBox('OLE</span><span>错误</span><span lang="EN-US">','OLE</span><span>无法连接，错误</span><span lang="EN-US">ID:' + string(li_Ret)+'</span><span>可能是</span><span lang="EN-US">Word</span><span>安装不正确！</span><span lang="EN-US">')<p></p></span></p><p></p><p></p><p><span lang="EN-US"> return <p></p></span></p><p></p><p></p><p><span lang="EN-US"> end if<p></p></span></p><p></p><p></p><p><span lang="EN-US">END IF<p></p></span></p><p></p><p></p><p><span lang="EN-US">/////////////////////VBA,</span><span>设置纸张为</span><span lang="EN-US">A3</span><span>横向</span><span lang="EN-US">/////////////////////////////<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_word.Documents.Add()<br/>lo_Word.ActiveDocument.PageSetup.LineNumbering.Active = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.Orientation = 1<p></p></span></p><p></p><p></p><p><span lang="EN-US">//</span><span>以下注释行为设置页边距，可以不用，使用缺省值。</span><span lang="EN-US"><p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.TopMargin = CentimetersToPoints(3.17)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.BottomMargin = CentimetersToPoints(3.17)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.LeftMargin = CentimetersToPoints(2.54)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.RightMargin = CentimetersToPoints(2.54)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.Gutter = CentimetersToPoints(0)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.HeaderDistance = CentimetersToPoints(1.5)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//lo_Word.ActiveDocument.PageSetup.FooterDistance = CentimetersToPoints(1.75)<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.PageWidth = 1190.55<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.PageHeight = 841.9<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.FirstPageTray = 0<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.OtherPagesTray = 0<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.SectionStart = 2<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.OddAndEvenPagesHeaderFooter = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.DifferentFirstPageHeaderFooter = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.VerticalAlignment = 0<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.SuppressEndnotes = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.MirrorMargins = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.TwoPagesOnOne = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.GutterOnTop = False<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.CharsLine = 99<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.PageSetup.LinesPage = 42<p></p></span></p><p></p><p></p><p><span lang="EN-US">///////////////////////////////////////////////////////////////////////<p></p></span></p><p></p><p></p><p><span lang="EN-US">////////////////////////VBA,</span><span>插入图片</span><span lang="EN-US">//////////////////////////////<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.Shapes.AddPicture("C:\windows\temp\yyglgzyb.wmf", False, True)<p></p></span></p><p></p><p></p><p><span lang="EN-US">//</span><span>保存</span><span lang="EN-US"><p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.ActiveDocument.saveas(docname)<p></p></span></p><p></p><p></p><p><span lang="EN-US">lo_Word.DisconnectObject()<p></p></span></p><p></p><p></p><p><span>曾有人说不会</span><span lang="EN-US">VBA</span><span>，其实你完全不需要去记这些方法、函数，你想要实现什么功能，可以用宏来录制你的操作，然后编辑宏，就可以看到</span><span lang="EN-US">VBA</span><span>的代码了，而且这些代码从字义上就可看出它是做什么的了。</span><span lang="EN-US"><p></p></span></p><p></p><p></p>
页:
[1]