Hi everyone,

I've just uploaded the latest version of my program for conversion of an
Info/Access report (mvBASE) to a graphical format (mostly via Excel/Open
Office or your web browser).

To use it, simply replace your list or sort command with spread or
sspread (to generate a spreadsheet).

You can get this either from Pick Source or direct from www.rushflat.co.nz

The latest version (1.1.0) includes greater support for Open Office, and
better formatting of headers and footers. The previous version (about a
month ago) added in xml formatting for Excel spreadsheets to set page
size and orientation.

I hope some of you find this useful.

Cheers,

Brian Speirs
--
************************************************** *
Brian Speirs
h: (04) 479 9032 c: (021) 265 5906
e: bss59REMOVETHIS (AT) paradise (DOT) net.nz

Brian Speirs wrote:
Very nice, Brian. I especially like the attention you've given to
OpenOffice and to the term settings, so as to give the correct page
layout. That's a trick I've only recently learned in Excel's
poorly-documented HTML mappings.

I've only had a quick look so far, and since I don't have a copy of
mvBASE, I can't yet try this out (I might do a conversion later). But I
do have one small suggestion that could bring considerable performance
benefits. Instead of using REFORMAT, why not just use SELECT/SSELECT
with output specifications? Then you can pull the data fields straight
out of the select list using READNEXT, with correlatives already
applied. You will, of course, need to apply the conversions, but the
same is true with REFORMAT.

The big win here is that you won't need the intermediate file, with all
of the sizing issues that it implies.

Again, kudos for the OpenOffice support. I firmly believe that OOo is
destined to supplant MS Office, largely due to their open document
formats and to their server-based architecture.

Luke

Luke Webber wrote:
There is no actual reformatting going on - it actually executes the
sort/list command, captures the output into a variable, and then
processes that variable.

However, if there is a select-list present, then it does copy the
item-ids to a separate file.

Likewise, I'm not actually applying any correlatives/conversions. As
I'm capturing the output of an Info/Access statement, all the processing
has already been done. But I've put a lot of effort into making the
spreadsheet display 1.00 as 1.00 rather than simply 1 (for example).

As for MS html, I've noted that you've referenced an Office 97 resource
kit here before. I've used the Office 2000 equivalent - that can be
found at

http://msdn.microsoft.com/library/de...ml/ofxml2k.asp

or search for 'ofhtml9' at microsoft.com.

Of course, it is not easy going through that resource, and I got a lot
of the formatting simply by doing 'Save As' and looking at the
(voluminous) output that Excel (and Open Office) generates.

Thanks for the support. It will continue to be a work in progress.

Cheers,

Brian
--
************************************************** *
Brian Speirs
h: (04) 479 9032 c: (021) 265 5906
e: bss59REMOVETHIS (AT) paradise (DOT) net.nz

Brian Speirs wrote:

I just realised that on re-reading. The problem, I suppose, is that your
variable might get awfully large, because it will include a lot of white
space. Processing the dictionaries to pull out the conversions and
applying them to a SELECT list will cost more programmer effort, but
will make the process quite a bit more efficient.

That's what mixed me up. A word of warning there, BTW. You've hard-coded
the file name, so that if two people run this program at the same time,
there will be trouble. Probably better to create the file on the fly,
something like this...

PORT = FIELD(OCONV('U50BB',' ',1))
FILE = 'EXPORT.TEMP,PORT'
EXECUTE 'CREATE-FILE DATA ':FILE:' 301,1' CAPTURING CRAP
OPEN FILE TO TEMP.FILE ELSE STOP 201,FILE
CLEARFILE FILE
....

Preciely. That's always the tricky part with #!$%&* Office, and the
chief reason that CSV imports aren't sufficient for this sort of thing.
The thread on assessing data types could easily realte back to this;
because of the way Excel handles imported data, it often mistakes text
for dates and so on.

Thanks. I actually have a copy, but sadly my main client still has a lot
of Office 97 about.

Yeah. And then trying to winnow out the chaff from the few kernels of
important stuff. Phew! <g>

These things always are. Good luck with it, and again, good on you for
including OpenOffice.

Cheers,
Luke

Bits snipped out.

Luke Webber wrote:
Good point.

Yes - I asked the question on assessing data types for use in this
program. I found that on my test printout, a comment got converted to a
date because it started with 1/2 followed by a space and then text.
You're not wrong!

Brian

Brian Speirs wrote:

Interesting that Excel suffers from precisely the same fallacy, isn't it?

Luke

It's great that this code is up at PickSource, but I see PickSource as
a more static resource and mvDevCentral.com as being more for active
projects like this. MVDC has forums bug and feature request trackers,
e-mail lists if people want to get notices about changes to projects,
etc. Have a look and decide if you want to put it there (too).

HTH
T

Brian Speirs <bss59REMOVE_THIS (AT) paradise (DOT) net.nz> wrote: