We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Pb with graphic size

Hi,



In order to create Excel reports, we are using Aspose.Cells.8.5.2.



For this, we have an initial file, called model.xlsm, that the aspose program feed with data to obtain the final file, called result.xlsx.



When we feed the FIRST worksheet of the model, the number of lines between lines 15 and 34 can move, by inserting or deleting rows. Then calculations are made in column AC with formulas, in order to construct the graphic in column AE. That means the graphic is entirely construct with excel and not in Aspose.



But this graphic MUSTN’T MOVE in order to STAY IN THE PRINT AREA OF THE FIRST PAGE. That’s why we have set, in the model, a fixed height of 25,3 cm to this graphical zone, and check the property “Do not move or size with cells” (see in file ParamInit.png) . Thus in the print preview, the graphic is in the first page.



Then after executing the program that feed data, the first result is OK (file ResultOK.xlsx) but you can notice that the height of the graphical zone has moved and is now 25,13cm. But in the print preview, the graphic still stays in the first page.



Let’s come to the problem:

Cells in columns D and S have the property “wrap text” checked.

We are now feeding with data bigger than cells, so height of lines is expanding. We obtain the second result: file ResultKO.xlsx, with a graphic of 32,32 cm high, as you can also see in ParamFinal.png. So in this file, in the print preview, the graphic overflows the first page because it has moved with the size of cells, unlike the property checked…



I think it can be an Aspose bug because the height of the graphic should stay at 25,3 cm in the 3 cases, and I’ve noticed this:

- in model.xlsm, the bottom of the graphic is near line 66

- in ResultOK.xlsx, the bottom of the graphic is near line 67

- in ResultKO.xlsx, the bottom of the graphic is near line 68

so maybe Aspose use the line number to calculate the new position of the graphic…?



Thank you in advance for your reply.



Karine

Hi Fabienne,


Thank you for contacting Aspose support.

I have checked all of you provided spreadsheets, and I am able to notice the change in chart size between ResultOK.xlsx & ResultKO.xlsx. However, I was not able to properly investigate the matter due to the lack of source code that you are using to manipulate the spreadsheets with Aspose.Cells for Java API. It would be appropriate if you can provide an executable standalone sample application to create the aforesaid files.

That said, before you move forward to provide us the above requested, please give a try to the latest version of Aspose.Cells for Java 8.6.0.2 on your side to see if it makes any difference.

Hi Babar,



Here is a class created from our sources (I had to change lot of things with no database access and no environment of our architecture…). But it reproduces the problem.

My program is inserting 80 rows and fill the D column with a big label.

You have to change the path (line 48) in order to use the model given in the first post.

I obtain the attached Result file which graphic height is 83,16 cm.



Thank you for your help,



Karine

Hi Fabienne,


Thank you for your help.

We have evaluated the presented scenario while using the latest version of Aspose.Cells for Java 8.6.0.2, and we are able to notice the said problem, that is; the size of the chart increases regardless of the fact that the chart’s option “Don’t move or size with cells” is selected. We have logged this incident in our bug tracking system under the ticket CELLSJAVA-41491 for further investigation. Our product team will look into the details of this problem and we will keep you updated on the status of correction. We apologize for your inconvenience.

Hi again,


This is to inform you that we have analyzed the scenario logged earlier as CELLSJAVA-41491. Please note that the Aspose.Cells APIs do not auto fit row height for performances considerations while setting the data of cell if the row height is automatic .
Please call Worksheet.autoFitRows() method as demonstrated below. By the way, it is advised to use the Cells.deleteRows or Cells.insertRows instead of deleteRow and insertRow methods for performance considerations.


C#

Workbook workbook = new Workbook(modele);
Worksheet feuilleARemplir = workbook.getWorksheets().get(0);
Worksheet feuilleMetadonnees = workbook.getWorksheets().get(1); //-CDC18.2
workbook.getWorksheets().add();
Worksheet feuilleModele = workbook.getWorksheets().get(2);
feuilleModele.copy(feuilleARemplir);
ConditionalFormattingCollection fcs = feuilleModele.getConditionalFormattings();
fcs.clear();
Cells cellulesARemplir = feuilleARemplir.getCells();
Cells cellulesMetadonnees = feuilleMetadonnees.getCells(); //-CDC18.2
Cells cellulesModele = feuilleModele.getCells();
int startLine = cellulesMetadonnees.get(4, 2).getIntValue() - 1;
int nbColonnesDebut = cellulesMetadonnees.get(5, 2).getIntValue() - 1;
int nbColonnesTotal = cellulesMetadonnees.get(4, 2).getIntValue();
int nbLignesA = 80;
int nbLignesModele = 0;
if (nbLignesA == 0) {
for (int i = 0; i < 2; i++) {
cellulesARemplir.deleteRow(startLine + 1);
}
} else if (nbLignesA < nbLignesModele) {
int nbLignesASuppr = (nbLignesModele - nbLignesA);
for (int i = 0; i < nbLignesASuppr; i++) {
cellulesARemplir.deleteRow(startLine + 1);
}
} else if (nbLignesA > nbLignesModele) {
int nbLignesAAjouter = nbLignesA - nbLignesModele;
for (int i = 0; i < nbLignesAAjouter; i++) {
cellulesARemplir.insertRow(startLine + 1);
}
}
System.out.println(feuilleARemplir.getShapes().get(0).getHeightInch());
Range rADeb = cellulesModele.createRange(startLine + 1, 0, 1, nbColonnesDebut);

Range rAFin = cellulesModele.createRange(startLine + 1, nbColonnesDebut + 3, 1, nbColonnesTotal);

Range r;

// Ligne a laquelle on va commencer ?ins閞er les donn閑s,
int ligneCourante = startLine;
// On va lire maintenant toutes les lignes de ce ResultSet.
// Chaque ligne de ce ResultSet va correspondre ?une ligne du fichier
// Excel, dans ce fichier, les cellules comportent le nom de la colonne ?
// aller chercher dans le ResultSet.
while (ligneCourante <= nbLignesA) {
r = cellulesARemplir.createRange(ligneCourante, 0, 1, nbColonnesDebut);
r.copy(rADeb);
r = cellulesARemplir.createRange(ligneCourante, nbColonnesDebut + 3, 1, nbColonnesTotal);
r.copy(rAFin);

Cell cell = cellulesARemplir.get(ligneCourante, 3);
// On effectue le remplacement des cles TABLE de cette ligne.
cell.setValue(“Res xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”);
// A la fin de la lecture de cette ligne dans le ResultSet, on passe ?la ligne
// suivante dans le fichier Excel.
ligneCourante++;
}

System.out.println(feuilleARemplir.getShapes().get(0).getHeightInch());
AutoFitterOptions options = new AutoFitterOptions();
options.setOnlyAuto(true);
feuilleARemplir.autoFitRows(options);
// On a plus besoin des feuilles de modele et de meta donnees,
// donc on les supprime pour ne garder que la feuille avec les
// donnees.
workbook.getWorksheets().removeAt(2);
workbook.getWorksheets().removeAt(1);
workbook.calculateFormula();
workbook.save(“D:\Filetemp\dest.xlsm”);

Hi Babar,

any news on this issue ?

Regards,
Eric Hamel

Hi Eric,


The issue logged earlier as CELLSJAVA-41491 has been closed because it cannot be fixed. Reason being, Aspose.Cells APIs do not auto fit row height for performances considerations while setting the data of cell if the row height is automatic, where your provided sample contains such rows. The solution has already been shared in our previous post after testing it on our side. Please give it a try yourself as well, and let us know if you face any difficulty.