2 Replies Latest reply: May 15, 2014 10:13 AM by Olly Bond RSS

    Formatting Excel Spreadsheets


      We have Excel reports from time to time that require additional formatting that cannot be done from Monarch itself. For example, filtering may be desired on a summary export, colors and formats outside of what is available in Monarch, and so on.  I would like to get any feedback I can from other developers on how they accomplish this.[/FONT]


      Our solution has been to create an Excell Formatters MS Access database with several macros that call vba modules. Each module opens the intended report and does the necessary formatting. The macros are called from batch scripts that are called in the post export script portion of a job from Data Pump. That approach has been working great for 7+ years.[/FONT]


      We recently upgraded the server to Microsoft Server 2008 64 bit and these jobs stopped working.  There have been some hacks posted online for this, but they are not working on our server. It seems that Microsoft does not recomend this approach and will not support it:  /FONT[FONT=Times New Roman][SIZE=3][FONT=Trebuchet MS][URL]http://support.microsoft.com/kb/257757[/URL][/FONT][/SIZE][/FONT]


      [FONT=Segoe UI]Microsoft does not currently recommend, and does not support,Automation of Microsoft Office applications from any unattended,non-interactive client application or component (including ASP, ASP.NET, DCOM,and NT Services), because Office may exhibit unstable behavior and/or deadlockwhen Office is run in this environment.[/FONT][/B]


      So if the approach of automating several formatting options for Microsoft Office documents via a service is not recomdneded nor supported, and Monarch/Data Pump does not provide the formatting options our users are requesting, we have a problem that I don't see a solution for.[/FONT]


      How are the rest of you dealing with this challenge?[/FONT]

        • Formatting Excel Spreadsheets

          I thought I'd ask again, in case anyone has any input.  We have several automated spreadsheets where the users require various formatting options that are not available in Monarch.  Does anyone else have a similar business need, and how are you meeting this?  Thanks!

            • Formatting Excel Spreadsheets
              Olly Bond

              Hello Joey,


              Thanks for bouncing this - seems it passed us all by when you first posted. Is the formatting:


              - conditional (colors based on values, etc)

              - graphic (logos etc)

              - symbolic (e.g. Sparklines)


              Is the output to be:


              - emailed

              - printed

              - presented

              - queried interactively?


              DataPump may not be the whole answer, but the new Datawatch Server combines this with The XSLT options in Content Server, as well as the dashboards of the Panopticon product, so might be worth considering.


              If you want advice from someone who's not trying to sell you the technology, please drop me a line.


              Best wishes,