FoxyPreviewer: Generated excel (through OBJECT TYPE 13) shows errors!

[Rajesh Karunakaran]

Dear friends,

I use FoxyPreviewer to produce excel files from FRXs as in

lcReportFullName = 'MyReport.frx'
lcOutPutFilePath = 'd:\temp\'
lcOutPutFileName = FORCEEXT(JUSTSTEM(pFrxName), 'xls')

DO FoxyPreviewer.App
REPORT FORM (lcReportFullName) OBJECT TYPE 13 TO FILE (ADDBS(lcOutPutFilePath) + (lcOutPutFileName))

However, when it runs, it throws errors (something like 'lapages not found' etc.). When I click each one and pass through, it creates a file but unreadable. Also, it says JRE required for OpenOffice. Even though I have OpenOffice installation, I am not specifiying the 'PREVIEW' keyword in the REPORT FORM.... command and hence I was not expecting anything on OpenOffice/Excel.

I have downloads for v299z30 and v299z38 (the versions). Tried both and they give different kinds of errros!

Anyone tried Excel and experienced such things?

Thanks,
Rajesh

 

[Mike Lewis]

Rajesh,

I've never seen this problem when exporting to Excel.

The only suggestion I can make is to try different settings of oFoxyPreviewer's lExcelConvertToXLS property. I'm not sure about this, but I think that determines whether the output goes to XLS or XLSX. It might not make any difference, but it would only take a moment to try it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

something like 'lapages not found' etc.

You should really be more precise about errors and this one sounds like it's a variable used in the report and expected to be set before running it. So not just one of those report variables the report creates and initializes itself. By name convention even an array of pages. Could also be something FoxyPreviewer uses, but then I doubt there's a bug in it that only shows up when you use the OBJECT clause of the REPORT command.

FoxyPreviewer usually does not get used with OBJECT but determines what to do by the file extension of the output file. So I'd try to simply do

REPORT FORM (lcReportFullName) TO FILE (ADDBS(lcOutPutFilePath) + (lcOutPutFileName))

Chriss

 

[Mike Lewis]

this one sounds like it's a variable used in the report ... Could also be something FoxyPreviewer uses

Chris, I thought of that as well. I did a Code References on the Foxy source code and found some references to laPages in the AfterReport method of the Excellistener object.

This is the relevant code:

SELECT MIN(nPageNo), MAX(nPageNo) FROM (this.cOutputAlias) INTO ARRAY laPages
	lnMinPage = laPages(1)
	lnMaxPage = laPages(2)

If the SELECT does not produce a result, then laPages would not exist, which would explain the errors in the following two lines. But I can't see how that could happen in this case. In any case, its hard to believe there is a bug in FoxyPreviewer in what must surely be a frequenly used bit of code.

Perhaps laPages also exists somewhere else in Rajesh's code, as you suggested. In any case, Rajesh, I think you need to give us the full wording of the error message, along with the procedure or method name where it occurs and the line number.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

It might also simply be, that FoxPreviewer does not support XLS anymore but just XLSX.

I found an old reference in a blog stating to use certain OBJECT TYPE numbers for certain output file types (

https://vfpimaging.blogspot.com/2011/04/the-simplest-way-to-create-your-pdf_5218.html)

But that's from 2011 and for version 2.38.

This has changed, I'm pretty sure, you should not use any specific object type numbers anymore, at all. It's the main foxyprviewer.app that decides which reportlistner to use to create output of the type specified by the filename extension. And try to use xlsx there. Or go with the lExcelConvertToXLS option Mike pointed out.


Chriss

 

[Rajesh Karunakaran]

Mike/Chriss,

Now, when I restarted, those intermediary errors are not coming!

Then, I played with the below properties but couldn't find any difference in process or result.

_screen.oFoxyPreviewer.lExcelConvertToXLS = <tried .T. and .F.>
_screen.oFoxyPreviewer.cExcelDefaultExtension = <tried 'XLS' and 'XML' as given in the documentation>

However, after showing 100%, it takes around 1 minute to finalise the file so that I can see the file in the folder.

When I open in OpenOffice, it says
"OpenOffice requires a Java runtime environment (JRE) to perform this task. Please
install a JRE and restart OpenOffice". When I click OK on this, it says, "General Input/Output error" and OpenOffice is closed.

When I open the file in Microsoft Excel, it says
"The file you're tring to open is in a different format..... Do you want to open the file now?"
When I click Yes, it opens the file! The data appears to be okay!

So, now the main problem is that long waiting, after 100% finished by FoxyPreviewer progress bar, to actually get the file in the folder.

Chriss,

My version is 299 (from the filename). I think, it's 2.99.

FoxyPreviewer usually does not get used with OBJECT but determines what to do by the file extension of the output file

This is available only in version 3 which is actually a paid version as I saw in the online documentation.

Rajesh

 

[Rajesh Karunakaran]

Mike/Chriss,

Also, it seems, the errors are coming up when the underlying cursor for the FRX doesn't have any records. For a preview, it's not a problem as it just doesn't show anything. I am generating the report data from within the INIT of report DataEnvironment. So, I need to think about a way to determine if the report has any data at all, before going for FoxyPreviewwer excel generation.

Rajesh

 

[Mike Lewis]

In all my applications, as soon as I have created the cursor for a report, the first thing I do is to check that the cursor contains some data. If it doesn't, I tell the user, then go no further with the report. This has nothing to do with FoxyPreviewer or any other reporting system. It's something I always do. (In fact, the behaviour is built into my base Report Launch form class.)

I know some developers prefer to produce an empty report in these cases. I'm not sure if that is a better approach or not.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

And by the way, Rahesh, what you have just described is consistent in what I said in my previous post: "If the SELECT does not produce a result, then laPages would not exist, which would explain the errors in the following two lines."

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

It is, Mike. It is consistent.

And sorry, Rajesh, I think you're right the feature I thought is available is needing version 3.0

That laPages are addressed, even if _tally=0 is a bug, anyway. It may be better than the bhaviour of the standard report engine to neither error nor show anything just like a main.prg without a read events and no modal main form started just vanishes.

Chriss

 

[ggreen61]

I would post this error message on Foxite website - Cesar frequently accesses that site. FoxPreviewer 2.99 only produces XLS files with no support for XLSX.

Greg

 

[Rajesh Karunakaran]

Hi all,

Now, it creates the excel and MS excel opens it, after showing message similar to '...File not in expected format... Still want to open it?' though! However, OpenOffice shows errors. While generating Excel, in some cases, even after showing 100% (in the process progress bar), the progress bar stays/hangs and takes considerable time to finish and finalise the file. I am checking with a few both simple and complex reports.

Mike,

In all my applications, as soon as I have created the cursor for a report, the first thing I do is to check that the cursor contains some data. If it doesn't, I tell the user, then go no further with the report.

Yes, I also do that, obviously. Anyway, in this case, my PREVIEWing, PDF, EXCEL for any report are separate options, ie, user just choose from RadioBox options and Run it. So, when I run for EXCEL, as I am generating the data for reporting from within the FRX itself (in its INIT method), there is no way to tell the user something like 'No data for reporting!'. In case of PREVIEW, it just doesn't show anything (maybe a just blinking). But, FoxyPreviewer is not (at least in version 2.99) handling this situation and hence throws errors!

Greg,

Are you referring that great person who created the fantastic FoxyPreviewer? If yes, that would be great, if he can update version 2.99 with required modifications. In fact, I don't think I would go for the paid version 3.0 anytime soon and so many others as well!

Thank you,
Rajesh

 

[ggreen61]

Rajesh--

Yes, Cesar is the person that created FoxyPreviewer. However, I know that he is not adding any new features to V2.99 and instead adding new features to V3.0 now. I think he will only fix any bugs that may occur in V2.99.

Another alternative that you can try is my class that I am currently developing. This class has been available for creating workbooks without the use of automation of Excel as a project on VFPx. The link to it is:

https://github.com/ggreen86/XLSX-Workbook-Class

The latest release (in beta) now has a listener class that I am developing that will generate an xlsx file from a report. Read the release notes for more information. I am currently working on the support for multiple detail rows in a report. So, if your report does not have multiple detail rows, then it might work for you.

There are methods in the class VFPxWorkbookXlsx for creating a workbook directly from a grid or a table (cursor). These are SaveTableAsWorkbook, SaveTableToWorkbookEx, SaveGridAsWorkbook, and SaveGridAsWorkbookEx. The 'Ex' versions write directly to the xlsx file; whereas the other methods write to internal cursors that hold the xlsx cell definitions in order to allow for more manipulation of the content before generating the xlsx file. The grid versions use the formatting of the grid to format the xlsx cell values; the table versions does not provide any cell formatting other than data type.

Greg

 

[Rajesh Karunakaran]

Hello Greg,

Thank you very much for the link. Awesome! I will certainly download and test this very soon.

I am currently working on the support for multiple detail rows in a report. So, if your report does not have multiple detail rows, then it might work for you

You mean multiple 'detail rows' or 'detail bands'?

Rajesh

EDIT: I just checked the "dltMultiLine.Frx" report file from R39 Beta 11. Now, I understood what you meant by "multiple detail rows".

 

[ggreen61]

The current version that is available on VFPx is nearly feature complete. All bands are being processed now. The only feature that I plan on adding is the ability to set the base sheet name as a directive in the comments field of the table.

Greg