Printed area management

Aspose.Cells Product Family
1

Hi,

We currently use ASPOSE CELLS 8.3.1 to generate xlsx/pdf final files based on a model.

The process is :

  • Open the model

    • Select data to populate the model

  • Modify spreadsheet by deleting columns and/or lines depending of the data

  • Save as final file

I have an issue with “Printed Area” management when I’m deleting columns.

i.e., my model is a spreadsheet with 100 columns which printed area is whole 100 columns, 20 columns per page, so 5 pages.

If I delete 95 columns using Cells().deleteColumns(x,y,true), in the final file I still have 5 pages with 1 column by page.

Is there a way to manage the printed area and to reformat the output of the spreadsheet ?

Thank you
for your help!

Regards,

Alban Debruge.

2

You will find attached the model, and the output files I get after I launch the edition. I also enclose the old version of this edition that created the columns for 100 years anyway.


It is this old edition I need to modify:

The point here is to delete the last pages whose years do not have any value (neither for the first blue half part -1P- nor for the second green half part 2P).
As you can see, in this example, there were no values after 2039, so all the pages after 2039 must disappear. Furthermore, it works quite well, but I need to keep my two last columns because they contain the borders of my spreadsheets…

I already tried to copy them in two new columns I had inserted, or to reset the print area, nothing worked! These two columns are anchored to their page.


3

Hi Alban,

Thank you for sharing the sample spreadsheet.

By reviewing your problem description what I understand is that you wish to reduce the number of generated PDF pages by removing the columns (that has to be printed on one PDF page) from the Worksheet Modele_ExportCout. If this is correct then please note that the columns from A to H are frozen therefore these columns will repeat on every PDF page regardless of the fact that other columns are printed or not. If you delete the columns from I to AB, you still get a page with columns from A to H. You may confirm this in Excel manually as it is the default behavior. Moreover, Aspose.Cells APIs follow Excel’s guidelines and specifications therefore the behavior is similar with Aspose.Cells APIs as well.

Please correct me if I have misunderstood the requirement.

4

Hello Babar,


First, thank you for your message. We managed to solve our problem thanks to the Break Page Collection.

On the other hand, we noticed that Aspose does not react like Excel when you remove columns. In Excel, when a column has a Break Page linked, if you remove the column, it will also remove the Break Page. Whereas if you delete the same column with Aspose, the Break Page remains and is applied to the column that will replace the one you have deleted.

We know that Columns and Break Page Collections are not the same Objects in Aspose, but the functionality of the Cells.deleteColumns() method does not affect the Break Page that is linked to the removed Column.

Thank you again for your message.

Regards,

Alban.
5

Hi Alban,


It is good to know that you are able to overcome the problem shared earlier. Regarding your recent concerns, I believe Aspose.Cells APIs behave exactly as Excel application. I have verified it as well while using the latest revision of Aspose.Cells for Java 8.5.2.4.

The page breaks are not associated with columns but the page size set in the Page Setup of the worksheet. In simple words, the page breaks virtually split the worksheet according to the preset paper width & height. Deleting the columns before any vertical page breaks can either allow the right columns to occupy the vacant space or not depending on the width of columns and width of vacant space. For better understanding, please check the attached spreadsheets. If you manually delete the columns H & I from the Book1.xlsx, the columns from the right hand side will shift to left filling up the space whereas the vertical page break remains at it’s place. Same can be verified using the following piece of code.

Java

Workbook book = new Workbook(“D:/book1.xlsx”);
Cells cells = book.getWorksheets().get(0).getCells();
//Deleting columns H & I
cells.deleteColumns(CellsHelper.columnNameToIndex(“H”), 2, true);
book.save(“D:/output.xlsx”);


Now check the other sample Book2.xlsx and repeat the same process, that is; delete the columns H & I manually and using the code. This time the column from the right will not move to the left because it cannot fit the space.

I hope this helps.
6

Hi Babar,

I'm Eric, already discussed on several old posts, and collegue from Alban.

I think y don't discuss about the same things, same break pages with Alban.

Alban told you an issue on deleting columns where a manual brreak page is inserted.

Your response is about automatics break pages depending on colum width, page width and printed area.

In th attached spreadsheet, we inserted a manual break page on column G ("column order Change") by :

- selecting column G

- Page Setup menu

- Break Page option / Insert a Break Page

Then

- In Excel, if you delete column G ("column order Change"), the manual break page is deleted in the same time with column G and the automatic break page is on colum I ("User based Page Size")

- with Aspose.cells if you delete column G ("column order Change"), manual break page still on the new G column ("Column Hiding")

Hope thi will help you to unsderstand this issue.

Regards,

Eric

7

Hi Eric,


Thank you for elaborating the scenario further. Previously we were considering only the automatic page breaks therefore all the responses were in that context, however, now we have evaluated the scenario while using the latest version of Aspose.Cells for Java 8.5.2.4 against your provided spreadsheet and we have noticed that deleting the column G with aforesaid revision of the API makes the resultant spreadsheet corrupted beyond repair. This seems to be the problem of the API therefore we have logged this incident in our bug tracking system as CELLSJAVA-41482. We will soon analyze this issue and provide a fix for it.

That said, I have also checked your presented scenario (Deleting the column associated with custom page break does not remove the page break) with a few previous releases (v8.5.1, 8.5.0 & 8.3.1) and I believe you are correct. It seems that Aspose.Cells APIs do not behave exactly as Excel when comes to deleting the columns associated with page breaks. However, as I am not able to verify this scenario against the latest revision of the API, I am not able to log it for the product team’s review. I will discuss this matter with the product team to confirm if I have to wait for the fix of CELLSJAVA-41482 before logging the actual scenario for investigation or it can be carried out in parallel. I will keep you posted with updates in this regard.

Java

Workbook book = new Workbook(“D:/book3.xlsx”);
Cells cells = book.getWorksheets().get(0).getCells();
//Deleting column G
cells.deleteColumn(CellsHelper.columnNameToIndex(“G”), true);
book.save(“D:/output.xlsx”);

8

Hi Eric,


This is to update you that the issue logged earlier as CELLSJAVA-41482 has been resolved. We are expecting the next release of Aspose.Cells for Java in a couple of days. Moreover, we have discussed the original scenario with the concerned member of the product team and they are currently looking into it. As soon as we receive more updates in this regard, we will post here for your kind reference.
9

The issues you have found earlier (filed as CELLSJAVA-41482) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.