tmssoftware.com

Report run and keeping together

FLX TMS FlexCel Studio for .NET
#1

I'm wanting to use FlexCel for a report. What I'm struggling with is the new pages on printing to PDF. In essence I have a template sheet that takes up a bit less than a page. I want a PDF that shows a page for each of the data objects (name, dates etc). When I do a Report.Run(), it takes the data, reads the template, outputs a new XLSX file with the results, pretty much as I would expect. But to make the PDF, I have to load it again, and that doesn't seem to know about the additional pages and the KeepRows_1_data range is only on the first page. The PDF then ends up with new pages at random points as I presume it doesn't know the keep together now that was in the original XLSX file.

I figured I'd have the Run() make the PDF, or return the XlsFile object to pass to the PDF print, but it appears to be inaccessible directly.

I can't see an example that shows how this would happen, but maybe I've missed it.

I also note that Excel has an "insert page break" menu item, which in its printing it takes account of. I figured that might help, but it isn't replicated in the report run, so obviously isn't known.

Is this going to be possible, or should I give up and use something more appropriate? Thank you for your assistance.

#2

Additional info: I put a marker text at the top and bottom of the template report region, and then before the PDF generation I loop around and use a manual xls.KeepRowsTogether() for each in the result XLSX. With a xls.AutoPageBreaks(2,100); it then outputs pretty much as desired. For some reason it decided the output was wider, but I can scale to fix that. And some of the underlined cells are wonky and move to different lines than they should be. So the key is how I could get this to happen automatically, from the report run into the PDF export. Is that possible, or should I continue with this code?

#3

Hi,
Some remarks:

  1. KeepRows_1_ won't be replicated into every page, because that is not possible. You can only have 1 named range per sheet. What the KeepRows does is to automatically insert page breaks at the places where they are needed. So saving the file and reloading it shouldn't make a difference. It should know about the new page breaks, if keeprows added any. KeepTogether is indeed a FlexCel concept, not an Excel concept, so it can't be saved in the xlsx file, but it doesn't matter. Because KeepTogether should add manual page breaks to the xlsx file, so if you later load this file it should have the correct page breaks.

  2. You can run the report directly to pdf without reloading it, but as said in 1 this shouldn't matter. To avoid reloading, use the xlsfile object in the report. Something like:

            var xls = new XlsFile(template, true);

            using (var report = new FlexCelReport(true))
            {
                report.SetValue(...);
                ...
                report.Run(xls);
            }

            using (var pdf = new FlexCelPdfExport(xls, true))
            {
                pdf.Export("result.pdf");
            }
  1. FlexCel fully supports Excel manual page breaks (the "insert page break" in Excel), and in fact that's what keeprows does (Adding manual page breaks). But, it tries to mimic Excel's behavior as much as possible, and so it won't replicate it below, because Excel doesn't replicate them either when you copy cells. (you can try it yourself, add a manual page break in Excel, and try to copy it below, it is not possible).
    So to use page breaks in reports, we provide a <#page break> tag ( FlexCel Reports Tag Reference. | FlexCel Studio for VCL and FireMonkey documentation ) which will indeed be replicated.

If your data isn't confidential and you can send me the template, generated file and generated pdf I can give you a more detailed insight on what is happening. You can remove all important data from the report, and if you prefer you can send it to adrian@tmssoftware.com

Intelligent page breaks work fine, but they can be difficult to get right and might look like they didn't. That's why we even have a debbug mode for them: FlexCel Reports Designer Guide | FlexCel Studio for VCL and FireMonkey documentation and FlexCel API Developer Guide | FlexCel Studio for VCL and FireMonkey documentation

#4

As said, it should happen automatically with reports. Have you tried the example Intelligent page breaks in reports (C# / netframework) | FlexCel Studio for the .NET Framework documentation

If after running it, you reopen it and convert it to pdf with FlexCel, you should see the correct page breaks.

#5

By the way, just thinking out loud: Are you writing <#auto page breaks>: FlexCel Reports Tag Reference. | FlexCel Studio for the .NET Framework documentation
in the template? This is the equivalent of calling

xls.AutoPageBreaks(2,100);

with code. If you forget this tag, then intelligent page breaks will do nothing.

#6

Thanks for all that. I did have the auto page break tag in the file. And I think I missed the ability of the report to operate on an in memory sheet. I shall go experiment with the page break tag too - that may be the ideal.

#7

Okay, the page break tag works pretty well, ignoring all the keep togethers. However, I put it at the last line of my report range, which should then be repeated again and again. I'm getting extra lines after each primary range, which appears to relate to the detail section length. I shall email you the test project - should be easy to see what is happening, it isn't complex as yet. Thank you.

#8

I added text in row 38 of the sample I sent, and the e column ends up pushed down, due to the detail row, but the page break (with tag in column e) is where it would be without the extra detail rows, so appears to be processed in the wrong order or something.

#9

I think what happened is expected: If you don't move the full row down, the page break won't move either.

I am not sure if it can help here, but maybe it makes sense to make the range ReportDetails _ReportDetails_FIXED ? (see FlexCel Reports Designer Guide | FlexCel Studio for the .NET Framework documentation and even when the warning says to not use them in this master-detail, I think they should for this case )

If a fixed band can't be used here, the other solution is to try to convert the ReportDetails range to be a "2 underscore" ReportDetails range, so it inserts full rows and the results are more predictable. Inserting just parts of the row can cause problems like in this case: the full row is not inserted, so the page break won't move down. It makes sense, because some cells from the row moved down, but the others didn't, so you can't move the full thing down.

To convert the band to ReportDetails you would need to add some extra fields to the class ReportDetail, for example one "ColumnA" (or something more meaningful) that in the first row would say what you have in cell A8, in row 2 what you have in cell A9, etc. So in the final report in cell A9 you write <#ReportDetails.ColumnA> and it will fill the data you have there.

#10

Many thanks for this. Making _ReportDetails_FIXED works perfectly, and now that you said it, it fits with your fixed form example. That's what I'm basically wanting isn't it! So glad you were able to help me - Flexcel has solved so many problems over the years. Many thanks for all your work.

© tmssoftware.com