Excel Converter

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Excel Converter

Postby Graham Smith » Tue Nov 25, 2008 4:10 pm

The two largest apps that I work on both use a number of exports and some of them can involve some rather substantial numbers of records. They also don’t lend themselves well to the kind of export that can be run directly from a form. So I end up writing a lot of procedural exports which can be slow and more than a little messy.

As a consequence, I'm constantly testing out ideas, and one thing I had not spent much time on is the Document Converter built into reports and procedures. So, I've been doing some testing and found that I can create fairly acceptable Excel exports if I follow a few rules.

The first thing to bear in mind is that while you can create a report (or procedure) that involves multiple forms and subforms, with subtotals and totals, if you output that to a spreadsheet, you may not like the result. Spreadsheets are best when they are simple row by row exports of the type you would do as a standard delimited text export.

Once your basic report is set up so that it is printing the data to screen that you want, save the report. To make sure that your output will fit without wrapping, I have found that using the font Arial Narrow 8pt gives takes up the minimum amount of width while still being readable on the screen. If you create a style sheet that defaults to this font for fields and labels, it will make laying out the report much easier .

Then pull down the File menu and select Print Setup. Click on "Specific Printer" and choose Ffenics. You can leave the rest of the settings alone.

Then pull down the Document menu and choose Print Options. Change "Print To" to Converter and "Format" to Excel. Provide an output file name (including the .xls extension) and select the action to take if the file already exists. Change "Paper Size" to A0, which is the largest paper size available (1189mm x 841mm or 46.81in x 33.11in). If the total width of the body of your report is less than 841mm, then change the layout to landscape. If you are really pressed for space, the margins can be reduced to 0.2 Lastly check the “Print without displaying Dialog” box. Save the report again.

Run the report and have a look at the Excel file that is created. You may have an extra row and/or column and you will probably have to select all the data and change the format and cell size, but basically, you should have a workable spreadsheet. If you find that two columns have merged in the output, then you may have to fiddle with the spacing on the report body.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Excel Converter

Postby KjetilUrne » Tue Nov 25, 2008 7:23 pm

Graham Smith wrote:If you find that two columns have merged in the output, then you may have to fiddle with the spacing on the report body.


Well, i didn't spend too much time on fiddling, but i wasn't even close to understand how i could avoid this when it first happened. Do you have something up your sleeve to share with us?
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Excel Converter

Postby Graham Smith » Tue Nov 25, 2008 7:32 pm

The only place where it happened to me was with a field where the data was all capital letters. I expanded the field on the body and pushed everything after it to the left and the problem went away.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Excel Converter

Postby Graham Smith » Tue Sep 04, 2012 6:36 pm

I am back to experimenting with this again and am looking for some input.

One thing that seems very puzzling is that some cells in a column end up centered while most are left justified, or vice versa. If I had some notion as to why this happens then it might shed a bit of light on a few other oddities.

Also, has anyone found a way to keep blank lines from appearing? Presumably these correspond to page breaks.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Excel Converter

Postby Fred Kingston » Tue Sep 04, 2012 7:08 pm

Excel formats numbers differently than text. If you want all columns formatted as text, then you need to armor the fields in double quotes. There is also a way to pass the format required by appending the data type to the field name field..

I believe, if you also use TXT as the filename extension, it will format all fields as text..

...but I probably misunderstood your question
Fred Kingston
 
Posts: 281
Joined: Sun Aug 01, 2010 10:54 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Excel Converter

Postby Graham Smith » Wed Sep 05, 2012 12:18 pm

Fred Kingston wrote:...but I probably misunderstood your question

Not surprisingly, yes.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Excel Converter

Postby Pete Tabord » Thu Sep 06, 2012 6:15 pm

I've seen the same thing with text columns - sorry, as yet no idea what causes it.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: Excel Converter

Postby DavidAGray » Thu Feb 11, 2016 8:30 pm

High ASCII characters embedded in the data can wreak all kinds of havoc when the text is imported into Excel.
DavidAGray
 
Posts: 10
Joined: Tue Jul 28, 2015 12:51 pm
Has thanked: 0 time
Been thanked: 0 time
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest

cron