Unpredictable Excel Report Formatting

[Chris.Tanti]

Just purchased v9 and doing some experimenting.

You may have noticed the strange formatting that gets applied to reports saved in Excel.

I still havent been able to solve all the issues (random row hight, random centre justify columns, random row skipping), but one of the most annoying problems (columns jumping left or right out of alignment) seems to be more controllable if you switch to a decent fixed width font (as apposed to proportional).

I am currently using Lucida Console, although it doesnt want to translate bold formatting.

I could use a data export, but the default is XLv4 which our clients don't like and you can't create multi layered (header / detail line) styles with this.

I am have mixed success with the ADO Export, but need a bit more time to experiment (failing to pass on variables used in query and file name for some reason).

Also, Excel does not appear in the list of available formats in the genies (not a big problem, as you can code the selection in xbasic).

[NoeticCC]

Re: Unpredictable Excel Report Formatting

A good way of working if you intend to design a report for exporting to Excel is to make sure that columns that are on top of each other in the report are the same width, alignment OR at least that they more or less line up. E.g. it turns out neater if you make the title of your report wide enough to reach from the start of the first (leftmost) field to the end of the rightmost field. Also, make sure that columns that are placed "on top of each other" (e.g. in the detail and footer etc.) line up correctly, and that you make fields wide enough to fit the data in all rows.

[Chris.Tanti]

Re: Unpredictable Excel Report Formatting

Yes, that makes a difference, I try to make sure all elements in the group header/footer follow the same column widths as the detail sections but I still found that columns jumped, especially if I had dashes or brackets within a long text string. Switching to a fixed width seemed to sort it out to some extents.

I am also placing blank empty text boxes above group breaks (at fixed widths), to force a space above or below the breaks.

Do you get the occasional double height row? I have know Idea what causes this, the columns I can sort of understand, but the rows... And also it switches from left to cenre justify, defaulted to centre justify on the troublesome columns.

Not sure if the Problem is with the Amyuni Driver or A5, but its very frustrating, I can't currently send the files formatted in there current state, it would not look professional.

[NoeticCC]

Re: Unpredictable Excel Report Formatting

Yes, that makes a difference, I try to make sure all elements in the group header/footer follow the same column widths as the detail sections but I still found that columns jumped, especially if I had dashes or brackets within a long text string. Switching to a fixed width seemed to sort it out to some extents.

I am also placing blank empty text boxes above group breaks (at fixed widths), to force a space above or below the breaks.

Ooh good idea, I hadn't thought of doing that! :)

Do you get the occasional double height row? I have know Idea what causes this, the columns I can sort of understand, but the rows...

I have sometimes got this in grouped reports especially...

..it switches from left to cenre justify, defaulted to centre justify on the troublesome columns.

I think part of the problem is with columns where Excel can't decide on what data type you have, certainly I have had the same sort of problem with "just" Excel before, too... I really should test this a bit more but there's so much else to get on with!

[Chris.Tanti]

Re: Unpredictable Excel Report Formatting

Had a lot of help from Stan Mathews (on another thread) with this, but may be useful to others who want to produce a more consistent result with the Excel report "save as" function.

The following code will load an xls file, autofit the rows and columns, then left justify the sheet, and save the document.

dim oExcel as P
dim oWorkbook as P
dim oWorksheet1 as P
dim oRange as P
dim fn as C
fn = "c:\a.xls"

oExcel = ole.Create("Excel.Application")
oExcel.workbooks.open(fn)
oExcel.visible = .T.

oExcel.Columns("A:Z").Autofit()
oExcel.rows("1:2000").AutoFit()
oExcel.Sheets(1).Range("A1:Z2000").Select()
oExcel.Selection.HorizontalAlignment = -4131 
oExcel.DisplayAlerts = .F.
oExcel.ActiveWorkbook.SaveAs(fn,-4143)
oExcel.Workbooks.Close()
oExcel.DisplayAlerts = .T.

oExcel.visible = .F.
oExcel.quit()
delete oExcel

[Stan Mathews]

Re: Unpredictable Excel Report Formatting

I think this has been covered elsewhere but for the record....


oExcel.Selection.HorizontalAlignment = -4152 ' right align
oExcel.Selection.HorizontalAlignment = -4108 'center

oExcel.Selection.Font.FontStyle = "BOLD"
oExcel.Selection.Font.Size = 11
oExcel.Selection.NumberFormat = "0.00;[Red]-0.00"
oExcel.Selection.NumberFormat = "0.00%;[Red]-0.00%"

etc.