XLS report with footer

Hi

I’m trying to create a XLS report that will output an order document for a customer. Documentation does not mention how to make a footer for XLS, is there a way to do so ?

In the footer, there should be stuff like total amount, vat amount, discount rate, etc. that should be properly formatted, but also text and images.

Michael

Hi,

You can add variables to the XLSX template for header/footer. Variables should be inserted into sheet names or the header/footer of XLSX template having ${<BandName>.<variableName>} format.

Sample template: xslx_header (1).xlsx (6.3 KB)

You can see footer when you select Page Layout on the View tab in the MS Office. Add Headers and Footers in Excel (Easy Tutorial)

1 Like

Thanks for your feedback Andrey.

In fact I already tried this approach but it is close to impossible to have the footer looking like the rest of the document (a footer is not a grid of cells). In the end it looks weird.

Then I looked at XlsxFormatter class, paging seemed not too difficult to implement myself. After all paging only requires managing special bands (page data, header, footer). And by extension header & footer special bands. Then you have the generally accepted pattern for report generators.

But I found no way to make YARG use an override of this class.

I finally managed to get what I wanted, by implemeting paging directly in a service, managing page header, page footer and footer, and relying heavily on reporting.putEmptyRowIfNoDataSelected = false in order to fool YARG. But that was clearly not trivial.

It’s puzzling that YARG manages paging with/through HTML, with/through Jasper, but not for XLS (and for DOC if I saw right, did not test).

Without paging & footer, it’s ok to produce ad hoc lists of books, but when figures comes into play or if you want an official business document, it is getting difficult.

Michael

1 Like

Hi Andrey, I did try to use a variable in the header with an image, but it seems it does not work, the fileId is displayed.

The same works if i use the image in the header so I guess YARG cannot output images in header & footer. Or maybe I missed something ?

We are trying to migrate reports to CUBA from an old Excel/VBA solution for a client and are very close to succeed, this is probably the last obstacle to overcome.

image

image

image

1 Like

Hi,

Did you use XLSX for inline image into the header?
XLSX doesn’t support inline image into the header. I will check this problem and try to find any solution.
We’ll fix it in the next bugfix release if it possible.

Thank you,
Andrey

1 Like

Sorry Andrey, I was not clear. Everything works like a charm with XLSX (images are inlined properly in header & footer).

The problem is when converting to PDF, images in header & footer are lost. It seems that OpenOffice does not manage images in header & footer.

I tried to use OpenOffice to set a background image file positionned top left (workaround found on google) but when saving to XLS (not X)… Excel does not manage background image positionning and tiles the image all over the page.

My XLSX report was exactly like I wanted so I searched a way to simply convert XLSX to PDF without OpenOffice, which is very easy with VBScript on a Windows machine with Excel, but of course I needed for linux.

I found no simple way (well, except from Aspose.Cells which costs an arm). There might be a way with Apache POI + iText, but this is not trivial and I was running out of time to deliver.

So I put images on regular zones and used the same tricks I used previously to make them appear conditionnaly. It works, but reduces the usable space.

Michael

Hi Michael,

I’ve reproduced the problem with the image in the header. LibreOffice doesn’t store images in the header while editing or converting to PDF.

There is no simple way to convert from XLSX to PDF without LibreOffice. We’ve create issue for the feature XLSX to PDF/HTML conversation without LibreOffice · Issue #88 · cuba-platform/yarg · GitHub

1 Like

Thanks Andrey.

There is a middle path keeping LibreOffice/OpenOffice for PDF as long as one knows that:

  • margins especially top & bottom are different between XLSX & the converted PDF, this can lead to surprises
  • no image in header, but you can simulate a header with image using a regular report band
  • same issue and same workaround for footer

However, I think managing paging for XLSX/PDF would be quite higher priority because the workaround for that is not trivial.