[CLOSED] Command line file conversion utility for Excel output ?

It's nice to see that Gerry reads the postings here once in a while and gets quite animated when discussing statistical routines.

My problem is more mundane: We don't use Excel, but we do use Lotus Symphony spreadsheets - which opens regular Excel output with no issues. The issue, as most of you know, is that WebFocus doesn't produce pure Excel output with either EXL97 or EXL2K.

What I want, and have requested several times, is support for Open Document format, and I intend to badger whoever I can find at Summit to that end, but in the meantime I'd be satisfied with a command-line utility that would strip the html from the "Excel" output and give me plain old Excel.

I should point out that saving AS EXCEL works just fine - we can click on the output and it populates Symphony with no errors - because that's really just a .csv file with a .xls suffix. But if we want styling, as with EXL97, we get a crash with File-Open in Symphony. (We can Import into Symphony successfully, but that takes a lot of steps, and font sizes and postioning in headings are not preserved.)

This is long-winded, but does anyone know of a command-line file conversion utility that would allow me to take WebFocus EXL97 or EXL2K output and convert it to regular Excel prior to delivery to the user - while preserving the styling?

Thanks for your assistance - and Gerry, please get ready to be buttonholed about .ODS support at Summit ....

This message has been edited. Last edited by: Kerry,

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010

Doug

Expert

posted

Hide Post

Have you considered FORMAT EXCEL or FORMAT COM, etc...?

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Show Post

George Patton

Master

posted

Hide Post

Hi Doug. EXCEL and COM work. I originally wrote:

quote:
I should point out that saving AS EXCEL works just fine - we can click on the output and it populates Symphony with no errors - because that's really just a .csv file with a .xls suffix. But if we want styling, as with EXL97, we get a crash with File-Open in Symphony.

So far I've found one utility that does half the job - namely stripping out enough html so that Symphony doesn't choke. But it doesn't keep the correct font sizes or centre the headings etc. as in the original (it does preserve the colour, however). The utility is a Windoze program and our server is Linux so I think it means concocting a situation where the WF output is saved on the server, copied to the workstation, converted, and then placed on the user's desktop so they can double-click to open it. Not very pretty ....

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010

Ignored post by George Patton posted

Show Post

dhagen

Virtuoso

posted

Hide Post

George, would you be able to identify what has to change in an EXL2K formatted output so that symphony can open it properly?

For example, the following is the EXL2K output file:

<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">
  
<HEAD>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<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;}
tr
        {mso-height-source:auto;}
col
        {mso-width-source:auto;}
br
        {mso-data-placement:same-cell;}
.style21
        {color:blue;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:underline;
        text-underline-style:single;
        font-family:Arial;
        mso-generic-font-family:auto;
        mso-font-charset:0;
        mso-style-name:Hyperlink;
        mso-style-id:8;}
a:link
        {color:blue;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:underline;
        text-underline-style:single;
        font-family:Arial;
        mso-generic-font-family:auto;
        mso-font-charset:0;}
a:visited
        {color:purple;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:underline;
        text-underline-style:single;
        font-family:Arial;
        mso-generic-font-family:auto;
        mso-font-charset:0;}
.style0
        {mso-number-format:General;
        text-align:general;
        vertical-align:bottom;
        white-space:nowrap;
        mso-rotate:0;
        mso-background-source:auto;
        mso-pattern:auto;
        color:windowtext;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:none;
        font-family:Arial;
        mso-generic-font-family:auto;
        mso-font-charset:0;
        border:none;
        mso-protection:locked visible;
        mso-style-name:Normal;
        mso-style-id:0;}
td
        {mso-style-parent:style0;
        padding:1px;
        mso-ignore:padding;
        color:windowtext;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:none;
        font-family:Arial;
        mso-generic-font-family:auto;
        mso-font-charset:0;
        mso-number-format:General;
        text-align:general;
        vertical-align:bottom;
        border:none;
        mso-background-source:auto;
        mso-pattern:auto;
        mso-protection:locked visible;
        white-space:nowrap;
        mso-rotate:0;}
.xl100
        {mso-style-parent:style0;
        color:#000000;
        font-size:10.0pt;
        font-family:"ARIAL";
        white-space:normal;
        mso-font-charset:0;}
.xl100r
        {mso-style-parent:style0;
        color:#000000;
        font-size:10.0pt;
        font-family:"ARIAL";
        text-align:right;
        white-space:normal;
        mso-font-charset:0;}
.xl100D12_2M
        {mso-style-parent:style0;
        color:#000000;
        font-size:10.0pt;
        font-family:"ARIAL";
        text-align:right;
        mso-number-format:"\0022$\0022\#\,\#\#\#.00";
        white-space:normal;
        mso-font-charset:0;}
.xl100YMD
        {mso-style-parent:style0;
        color:#000000;
        font-size:10.0pt;
        font-family:"ARIAL";
        mso-number-format:"yy\/mm\/dd";
        white-space:normal;
        mso-font-charset:0;}
-->
</style>
<xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:Selected/>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>5520</x:WindowHeight>
  <x:WindowWidth>13260</x:WindowWidth>
  <x:WindowTopX>240</x:WindowTopX>
  <x:WindowTopY>105</x:WindowTopY>
  <x:DoNotAcceptLabelsInFormulas/>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml>
</HEAD>
  
<BODY>
  
<TABLE X:STR BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD class=xl100>PIN</TD>
<TD class=xl100>LASTNAME</TD>
<TD class=xl100>FIRSTNAME</TD>
<TD class=xl100>MIDINITIAL</TD>
<TD class=xl100>DIV</TD>
<TD class=xl100>DEPT</TD>
<TD class=xl100>JOBCLASS</TD>
<TD class=xl100>TITLE</TD>
<TD class=xl100r>SALARY</TD>
<TD class=xl100>HIREDATE</TD>
</TR>
<TR>
<TD class=xl100>000000010</TD>
<TD class=xl100>VALINO</TD>
<TD class=xl100>DANIEL</TD>
<TD class=xl100>A</TD>
<TD class=xl100>CORP</TD>
<TD class=xl100>MARKETING</TD>
<TD class=xl100>257PSB</TD>
<TD class=xl100>MARKETING EXECUTIVE</TD>
<TD X:NUM class=xl100D12_2M>55,500.00</TD>
<TD x:num="32701" class=xl100YMD>89/07/12</TD>
</TR>
<TR>
<TD class=xl100>000000020</TD>
<TD class=xl100>BELLA</TD>
<TD class=xl100>MICHAEL</TD>
<TD class=xl100>D</TD>
<TD class=xl100>SE</TD>
<TD class=xl100>MARKETING</TD>
<TD class=xl100>257PSB</TD>
<TD class=xl100>INDUSTRIAL MARKETER</TD>
<TD X:NUM class=xl100D12_2M>62,500.00</TD>
<TD x:num="33072" class=xl100YMD>90/07/18</TD>
</TR>
</TABLE>
  
</BODY>
  
</HTML>

"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott

Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004

Ignored post by dhagen posted

Show Post

George Patton

Master

posted

Hide Post

First of all, many thanks for your interest - it's much appreciated.

Unfortunately I can't identify what needs to be changed to make the EXL2K (xmh) output compatible with Symphony.

I have found a utility called Total HTML Converter that changes the .xmh to .xls enough so that Symphony doesn't crash. It worked on your example perfectly, but your example doesn't have any styling. On my example I show first of all the PDF output and then how it is rendered after conversion with Total HTML Converter and opened in Symphony.

Unfortunately Total HTML Converter produces binary output so I haven't been able to provide you with the difference in code.

Here is the PDF:

Here is the Symphony display of the same thing after saving AS EXL2K and then converting with Total HTML Converter. As you can see, colour doesn't render perfectly, a blank line is introduced and one of the column headings is offset. It isn't terrible, but it's not perfect, and I don't know yet what would happen with more columns, subtotals, subheadings, etc.

If I run the report AS EXL97 I get an "Unknown File Type" message from Windows, but I can still save it as a .xls. I can then import (several steps) it into Symphony and it looks like this (if I try to Open instead of Import, it crashes Symphony):

With a few clicks I can fix the heading - font size, position, etc. and it looks fine. But if there are a lot of subheadings it's a pain, because they are initially all in the first cell and the cells to the right are merged. So I have to first of all split the merged cells and then re-merge to include the first cell.

Here's my code:

TABLE FILE NEWORDER
SUM FOBOT BY CCODE
WHERE RECORDLIMIT EQ 20
HEADING
"THIS IS A HEADING"
FOOTING
"FOOTING"
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
   INCLUDE = problue ,$
ENDSTYLE
END

Thanks again for your help on this.

This message has been edited. Last edited by: George Patton,

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010

Ignored post by George Patton posted

Show Post

Please Wait. Your request is being processed...