Geek Answers Handbook

How to add VB Excel Script to Excel Generated Jasper Report?

The current Excel report is generated using the Jasper Server / Jasper Report 3.8 report.

In my case, I would like to create Pivot Excel automatically using Excel (since Jasper Report Crosstab is not enough for my client requirement), so I have an idea to add VB Script, which will create a pivot table when the generated jasper report is opened for the first time.

I created VBA Script However, I could not find a way to integrate VBA with Jasper Report (generated) so that when creating the report, VBA was also included in the report.

Can someone tell me how to add VB Excel Script to Excel Generated Jasper Report?

Limit added:

  • List item

Data source (file containing Excel data) is protected

+4
source share
3 answers

I know that the question was answered and accepted, but I just can not resist to add an idea .....

Could it be that this question asks to put a basket in front of the horse?

You create an Excel REP file using a report generator. Then you want to use this data as the source of the Excel Pivot table. As I can see, there are several ways to do this:

  • Create another Excel PIV file containing a REP structure with some sample data to create Pivot definitions
  • Copy / Paste REP data replacing existing content every time you draw a new REP
  • update pivot

This can be automated in PIV by asking for the name of the source file, etc., so there really is no need to touch the REP in any way. As an alternative to copying / pasting, you can simply use the external link for the REP when specifying the data source for the pivot table and change this every time the user wants to parse a new REP.

Hope this helps

+2
source

I think MikeD has the right idea. I have never used Jasper, but if it is a CSV or Excel file, why not write a block in your macro script that opens the Jasper report, makes a pivot table, then saves it again.

Honestly years have passed since I did VBA, but this code was what I found on the Internet for opening books (ref: http://p2p.wrox.com/excel-vba/10510-opening-excel-file -vba.html )

Dim oExcel as Excel.Application Dim oWB as Workbook Set oExcel = new Excel.Application Set oWB = oExcel.Workbooks.Open(<pathToWorkbookHere>)

Forgive the syntax on this, as it was with 10 year old memories, but from there you could do something like

  oWB.Sheets(1).Cells.Copy ThisWorkBook.Sheets("PIVOTDATA").Cells.Paste() ThisWorkBook.Sheets("PIVOTTABLE").Cells.Refresh ThisWorkBook.Saveas("<path to new report>", xlExcel12)

If your reports are compiled by date, you can programmatically select either the latest version, download the folder and analyze all of them, or whatever. Your template file is the one that runs the script, and saves copies of itself in a "no macro" state, so your users never get a security warning.

If you correctly determine the file name programmatically, set the code to run “ON OPEN” in the template macro and run thisworkbook.close at the end of the script, all you need to do is create a batch file that automatically opens your book on the command line

excel.exe <template_filename>

then set up a cron job for Windows (called a scheduled task) to run automatically every day, immediately after running the jasper report.

Sorry if some of them are “hit and missed” in terms of syntax. I was no longer elbows in Excel VBA for more than 10 years, but at that time I was writing a system that generated hundreds of beautifully formatted Excel reports every day ... pivot tables and all that.

It is achievable, and you are on the right track.

+1
source

Looking at the answer on Jasper Tracker, it seems that this function is not supported by the jasper report and will not be included in the new function

this (adding VBA Script to Excel Generated Jasper Report) is such a special function of Excel that I am sure that we will never implement it. This is mainly because JasperReports is not an XLS generating library, but rather an Excel reporting tool.

See the link for more details.

0
source

More articles:


All Articles