Intelligent Page Break Problem

FLX TMS FlexCel Studio for .NET
1

Hi,


FlexCel is a really, really useful and affordable product. I really like it and benefited a lot from it. Just that sometimes a lot of experimentation is required to understand things because the documentation is more of a "concept" approach. But so far so good. 

Now, I'm facing problems with the Intelligent Page breaks. Here's a screenshot showing my template (to let you see how I defined ranges), and an output example. In short, I want to keep information in the Yellow Band together.

I am using FlexCel Reports to generate the report. I want to keep full details of each Item together in one page (meaning, the item's ID, Desc, Colors, Sizes, and Prices) all in one page without breaking. I have studied the demo example and documentation. It looks pretty simple and straightforward but so far no matter what tried I can't get it working (I'm using version FlexCel 5.3 and Excel 2007).

I even tried using the API (ie I noticed one of the items got broken between line 224, and 227 after the report was generated). I tried an experiment by using KeepRowsTogether(224, 227, 1, true), then run AutoPageBreaks(), but there was no effect.

Sometimes the Size Column (together with Price) will have more rows than the Color column, and vice versa.I have tried adding KeepRows_2_XXX for Size Rows, and another KeepRows_2_YYY for Color Rows as well, but again there was no difference.

In short,  I want to make sure the full item details is kept within the same page (and not get broken in between page breaks).

Am I missing something? FYI, I have set about 10 rows of the top page (not shown here) as Print Titles. Does that affect the Intelligent Page Breaks?

Do I need to call AutoPageBreaks after FlexCel Report has been generated? Presumably not as the <AutoPageBreak> tag is in the sheet (I have multiple sheets for different types of items by the way, do I need to include the tag on every sheet?)

Thank you.

.



2

Hi,


Thanks for the kind words. I do agree using all the functionality in FlexCel can be difficult, but I don't think this is because we have "conceptual" documentation (as we also have "how to" documentation, in particular there are 2 demos in how to use intelligent page breaks, and also "reference" documentation, as you will see when pressing F1 on the API methods.

In my personal opinion the real problem is that Excel is a very complex thing with lots of possibilities, and any API that exposes all the complexity will end up being complex. It happens also with the OLE Automation, but you are less likely to note it because: 1) You have the macro recorder that will tell you what code you need. and 2) There are millions of webpages explaining everything just a google away.  In our case, we can't do much about 2), but we doprovide an APIMate tool to compensate the fact that you can't use a macro recorder, and well, in the rest, we try to answer all questions the best we can.


In the particular case of intelligent page breaks, it is even more complex, because this isn't a feature in Excel itself. It is an unique feature we do completely on our own, to "fake" widow/orphan control in an application (Excel) that doesn't support them.
so, how do we implement ipb's if Excel doesn't support them?  We have a 2 stage process. First stage, you mark all the rows you want to keep together (this is done with the KeepRows ranges). Second stage, at the end of the report, we calculate where all page breaks would naturally end, and if they end up breaking some KeepRows range, we will insert a hard page break before.  (algorithm is actually quite more complex than that, but this is the idea).

So, to answer the last question, yes, you need a KeepRows range for the first stage (mark the rows we want to keep together) and a <#auto page breaks> tag in every sheet to trigger the second stage (insert hard breaks where needed to keep the rows marked together). The API works similarly, you need to call 2 different method, one for marking and the other for adding the actual page breaks, and actually this is a case where the API was modeled after the reports and not the contrary. But you can't really mix API and reports in the same thing, or you will end up calling AutoPageBreaks() twice, once yourself, and the other by the report. The first AutoPageBreaks will add the hard page breaks, and the second won't do anything.

About the actual problem you are having, it is hard to see form a screen shot, and normally Ipbs aren't too complex, but there is indeed an issue that can cause unexpected results:

FlexCel is "printer independent" in that it prints the same everywhere (it uses GDI+ for printing). Excel uses GDI, and it can print differently(and show a different print preview) depending in the printer you have. It might happen in a particular printer that from FlexCel POV row 36 will fit in the page (so it doesn't add a hard page break at row 34), but when you print-preview it in Excel with your printer, the printer has bigger "hard margins" (the places of the paper the printer uses to grab the page, in which it can't print and which actually make the page smaller). So for Excel row 36 doesn't fit, and it adds a new page break just for it.

To verify this isn't the case, you should try to preview the file in FlexCel itself, or export it to pdf from FlexCel. If the page breaks are ok there, then you need to add some "safety margins" to the page breaks to make sure no matter the printer they will show fine in Excel.

You do this by changing the parameters in the <#auto page breaks> tag. From the docs:

"Calling this tag without parameters is equivalent to calling <#auto page breaks(20;95)>"

You might need to make that default "95" smaller. (If the problem is this, that is, FlexCel printing works ok, but Excel doesn't)

If this isn't the problem, I can't see much more that could be wrong in your file, so, would it be possible for you to mail me a template and the result you get to take a closer look?

Regards,
   Adrian.


Adrian Gallero2011-08-05 19:18:28
3
Hi Adrian, thanks for the quick reply. After reading your email I got new ideas and experimented more.

I have discovered that the main culprit is because of setting the Margins in Excel to be very small (I think the Top and Bottom ones). I used to set Top: 0.5, Bottom: 0.5, Left: 0.35, Right: 0.15.

After setting it to Top: 1, Bottom 1, Left:0.75, Right: 0.75 (following the IPB Demo Template), the output looks much better, although it still doesn't solve the problem (looks much better though).

There a still a few broken items  ( I get a couple/ a few of them).  It is about 3 breakage for a 13 page report, 2-3  for a 8 page report, 1 breakage for a 6 page report, sometimes 1 breakage for a 2 page report (I have about 12 reports). The breakage happened in the middle, or the end pages of the reports.

I compared Excel's print preview against FlexCel's print  preview (output as images), and also output as PDF. They were all identical. I still get breakages after tweaking the <#autopagebreak> tag to 20;90 or 30;85 etc.

The output in the IPD demo is exactly what I wanted (no breakage at all). I followed everything there, but despite marking all the rows occupied by the Master and Children as as KeepRows (as shown in screen shot), breakage still occurs. Does the IPB apply only to __Ranges__ and not _Ranges_ as indicated in the Children above?

I think just a level 1 keepRows as shown in the screenshot is sufficient (following the IPB demo). But to try everything possible to solve this problem, I also tried adding some level 2 keepRows (just selecting a few rows on the size columns), or selecting a few rows on the colors column, and also switching levels between the ranges but to no avail. 

This is a tricky problem indeed (cracking my head and getting stucked on this). :) Does it matter whether the file is an .xls or .xlsx (I tried both by the way, same results).

Hope there is a solution for this.

Thanks.

4

I don't really see a reason why it wouldn't work. Can you email me the template and an example of what goes wrong at adrian@tmssoftware.com ?


Thanks,
   Adrian.
5

Thanks for the files, I've got them.

In this particular screenshot I do see a problem (that I didn't see in the original screenshots you posted, that's why I didn't mention it).

The issue is, you have "KeepRows" ranges from rows 10 to 13, and for example the "idAccesories" range from 10 to 12 (smaller)
What will happen in this case, is that when the "IdAccessories" range gets filled, it will insert rows and expand the "KeepsRows" range along with everything else, and at the end (before actually making the page breaks) the KeepsRows range will take all the rows. So, FlexCel will try to keep all the rows in one page, and as that is impossible, it will just ignore the keepsrows.

For this to work, you need the keeprows range to be smaller or equal than the range  (the same range, as in the IPB demo, should work). But not bigger, since it would then expand to cover the full sheet.

As said, in your original screenshots you had both ranges the same, so I don't know if this is the problem, but I would like to make sure before investigating further. If the problem persists with smaller ranges I will dig deeper. But code for ipbs is a little complex due to all the things it has to account (margins, repeating ranges, etc), and so before digging down I would lie to be sure we aren't overlooking something simple ;)


By the way, completely unrelated to your problem, but I noticed you have a lot of sheets all of them with similar data. Unless you need to format them differently in Excel, I would try to do a multi-page report here. You can probably organize your data so it is all a single table where you have a field "productId" that identifies the kind of product (accessories, dress, etc). Then you define a table (let's call it ProductList) with 2 columns, ProductName and iProductId, and fill that table with the products on the sheets. Define a relationship between ProductList and your data table in ProductId.
 After that, you write <#ProductList.ProductName> in the sheet, and the report will be generated.

This will probably avoid you a lot of work maintaining similar sheets, and also give you more flexibility, since for example now you can filter your "ProductList" table and get more or less sheets in the final report. As said, this might not apply to your case, but I thought I would mention it in case it can help.

Regards,
    Adrian.

6

Hi Adrian


Thanks for the fast and helpful response again.

I have tried keeping the KeepRows range same as the ID range (Rows 10 to 12 for both) yesterday, and I still got the problem.

Now after your suggestions, I made KeepRows smaller - 10-11 (within the ID range 10 to 12), and the result is the same.

Perhaps it's because of issues with Ranges because they don't fill the whole rows down? I checked how many Rows the KeepRows range expanded after the results were out (they remained at Row 10 -11, not including rows below it even though they were part of the same item). Before that (yesterday), when KeepRows was 10-13 (bigger than ID), the KeepRows expanded to almost the entire page (which it's impossible to keep within a page as you said).

I noticed the IPB demo uses Range, which expands.Maybe that's why they work? Perhaps I should reorganise the tables again (combine the Color and Size child tables into one table, and use Ranges instead of Range to get around the problem)?

Some extra questions: Does it matter if I select the whole row (ie: 1:1) or just  a fraction (A1:K1). By the way I selected the whole row for KeepRows .

Thanks for the suggestion above (about the unrelated problem). I will look into it when I have more time later (I quickly prototyped the app to get all the main working things in place). Main things are there except for the Page Break problem that I'm having now.

7

Hi Adrian


I bring good news! Indeed, based on my testing, the IPB works only with Ranges  not Ranges (and you have to make sure not to include any Range inside the KeepRows range (even if the Range does not contain DataTable tags), else it won't work.

To quickly test the above theory, what I did was that I changed the Color range from a Range to a Range (and deleted the Range for Sizes, and associated size tags - else it won't work). And I kept the ID and KeepRows the same size (I also noticed there was no difference whether I selected the whole row, or just part of it).

The result was perfectly fine page breaks!

Perhaps the next version can also include Ranges(this is important for Master-Child Tables) where sometimes we fill up part of a row by not having to insert a full row which is not possible/affects adjacent columns). Or maybe this feature is already existing, and that my method of usage was incorrect?

I appreciate your help and time on this (it opened up new ideas). Thank you. 
8

One additional observation:  Works only with .xls files.

Breakage occurs if I save as .xlsx (2007). One a side note, it's also the case if I use a 2007 Header file in #includes (I get the 65536 error). To keep things save I'm sticking to .xls for now.
9

Well, first of all sorry for the delay, I couldn't get back earlier. I also wrote a loong explanation a moment ago, and when I pressed "submit" it disappeared in the air, so I might write a shorter one now.T(his should teach me to answer in a notepad first...)


About the first point, you are right, the issue is with "" ranges. The problem is, when you copy a full row (either with a "__" range or with a full "" range) all the attributes of that row are copied. If you have a "__" range in row 3, and row 3 is say twice the size and green, then when we fill the report, rows 4, 5, 6... will be a copy of row 3, also twice the size and green.

But if you have a "_" range in A3:C3, then we aren't copying/inserting full rows, The copied cells (not rows now) should be twice the size for A4:C4, but normal size for D4 and all other columns at the right. But you can't have a row where some part of the row has one height and other part has a different height. 

Same thing happens actually with "Keepsrows" which is an attribute of the row and is copied with it, exactly as row height. You can't have half of a row as "keep together" and the other half as not. You either have the full row together or none at all.  

So I am not really sure we can "fix" this since it is actually working as designed, when the full row isn't copied, the row attributes aren't copied either. I have been thinking in a solution that could be to have a "BreakHere" tag that would be the opposite of keep rows, that is, FlexCel would try to break there if possible. As it would be "false" by default, it won't matter that it isn't copied. But I am not sure it would work, and I need to think a little more on the idea.

About the issue with xlsx, there shouldn't be differences with xls in this regard, I will investigate and let you know.
10

Au update about xlsx, I think I found to problem. We will be issuing a new release with that fixed soon.

11

5.5.1 has been released and it should work fine with ipb's and xlsx. 

12

Thanks a lot! This will be very helpful.