Hi,
We are using the Aspose.Cells.jar version 2.1.0.6. We have deployed our application in a machine which has SUN Solaris OS (Server system). We are facing some Formatting issue in a client system (Runs on Windows OS) placed at Onsite while accessing the application. Strangely, this issue occurs only in the above mentioned client system.
Problem Statement:
1) In Template Excel file a cell has Number format with 1 decimal place, after data filling the format of this Cell gets automatically changed into Custom (mm:ss) format.
2) In Template Excel file a cell has Number format with 9 decimal place, after data filling the format of this Cell gets automatically changed into Custom ([h]:mm:ss) format.
In our java code we are not explicitly setting any Style for the above cells.
Can this format change happen according to any Locale setting (like any Regional Settings for US or India)?
Is there any chance by which a client system will use any local libraries.
We surfed for possible solutions for this issue & stumbled upon the below given link which talks about "Regional Settings" causing a problem
Please refer the below link and kindly let us know if this is a possibility.
`http://msdn.microsoft.com/en-us/library/ms268748(VS.80).aspx`
Please help us to identify the root cause and rectify this issue.
Thanks in advance
Karuppiah M
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Please share your sample code, template and generated file here to show the issue. We will check it soon.
Thank You & Best Regards,
Hi Nausherwan,
We dont have any demo program to share because this issue in not replicable at offshore. At Onsite also its not replicable consistently.
I am sharing you the Template and DataFilledTemplate(which we got it from onsite) for your reference. (Please find the attached files)
Ponits for your reference (Issue Details):
1. Template.xls file >> The colored Cell against the "Plant Working days" Label is Number with Decimal place 1 format . Same Row, Cells against the "Plant Working days" in DataFilledTemplate.xls file, the format got changed into Custom (mm:ss) format.
2. Template.xls file >> The colored Cell against the "Combined Forward Days Supply,Non Fleet Forward Days Supply and Average Combined Fwd Sales" Labels are Number with Decimal place 9 format. Same Rows, Cells against the "Combined Forward Days Supply,Non Fleet Forward Days Supply and Average Combined Fwd Sales" in DataFilledTemplate.xls file, the format got changed into Custom ([h]:mm:ss) format.
Since this is show stopper at Production environment. Please analyze and help us to resolve this issue asap.
Thanks & Regards,
Karuppiah M
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Thank you for sharing the template file.
We have registered your issue in our internal issue tracking system with issue id: CELLSJAVA-16616. We will investigate your issue and get back to you soon.
Thank You & Best Regards,
Hi,
From your resultant file, we can see the number
format has been changed from the original file. However we cannot figure out
which operation would cause such change? And, because you are using some old
version and there have been many changes onwards from that version, please try our latest fix (attached). If you still find such issue, we need more
information, such as the APIs you have called/used to generate the resultant
file to help us figure the issue out.
Thank you.
Hi Amjad,
We dont know how the latest Aspose.Cells.jar file is going to resolve our issues, any way we are going to use the latest Aspose (version 2.2.0.2) which you have given. Right now we are doing the regression testing at offshore. All the exisiting functionality works then we will deploy the same to production.
Issues with the latest Aspose: As part of the Initial testing with the latest Aspose.Cells (version 2.2.0.2) at windows environment, the functionality which used to work fine seems to have some issues now, particularly in formatting issues like alignment and font bold.
The following piece of code is not working with the latest Aspose.Cells.jar but its working with the old version (version 2.1.0.6).
Cell.getStyle().setVAlignment(VerticalAlignmentType.CENTRED);
Cell.getStyle().setHAlignment(HorizontalAlignmentType.CENTRED);
Cell.getStyle().getFont().setBold(true);
PFA, the Attached demo program with the sample Template and Output files (genereate with latest and old Aspose jar) for your reference
1. DEMOASPOSETESTING.java >> Sample demo program
2. TestFormatChange.xls >> sample tempalte
3. FormatOutput-2.1.0.6.xls >> Output file generate by old version of aspose.cells
4. FormatOutput-2.2.0.2.xls >> Output file generate by latest version of aspose.cells
Please check at your end any other functinality missing in the latest version which used to work in 2.1.0.6 version.
Thanks & Regards
Karuppaih M
Hi,
Thanks for providing us details and sample files.
Well, this is not a regression bug by any means. Actually we have enhanced/changed (a bit for performance grounds) our internal style formatting model. We have already announced in our Release notes for the latest versions e.g
http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry221865.aspx
For your information, in old versions, Cell.getStyle()/Row.getStyle()/Column.getStyle() methods might cause Cell/Row/Column maintain their own Style object. Modification of the returned Style object for getStyle() later will change the Cell/Row/Column’s style directly. In the new versions, all Style objects that set to Cell/Row/Column will be gathered for performance considerations and Cell/Row/Column will only maintain a style reference. Modification of the returned Style object for getStyle() later will not change the Cell/Row/Column’s style. To make the modification take effect, users need to call setStyle() for Cell/Row/Column after the style is modified.
We have also update our documents too, see some documents for reference:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/configuring-alignment-settings.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/dealing-with-font-settings.html
So, you need to modify your code a bit now, e.g
public static void main(String[] args) throws IOException {
try {
String xlFileName = “FormatTemplate.xls”;
File objFile = new File(xlFileName);
Cell cell = null;
String value = “2010May11-RPT007-WNFMEO”;
objFile = new File(xlFileName);
FileInputStream objFIS = new FileInputStream(objFile);
Workbook workbook = new Workbook();
workbook.open(objFIS);
objFIS.close();
//For referece Number
NamedRange tempRange = workbook.getWorksheets().getRangeByName(“refNumber”);
Cell[][] tempCell = tempRange.getCells();
cell = tempCell[0][0];
cell.setValue(value);
cell.setStyle(copyHeaderStyle(cell.getStyle()));
Style style = cell.getStyle();
style.setVAlignment(VerticalAlignmentType.CENTRED);
style.setHAlignment(HorizontalAlignmentType.CENTRED);
Font font = style.getFont();
font.setSize(9);
style.setFont(font);
cell.setStyle(style);
//For Total Objective
value = “Total Objective”;
Cell tobObjCell = null;
NamedRange tempRangeForTotObj = workbook.getWorksheets().getRangeByName(“totalObjective”);
workbook.getWorksheets().getSheet(“TestSheet”).getCells().merge(tempRangeForTotObj.getStartRow(), tempRangeForTotObj.getStartColumn(), tempRangeForTotObj.getStartRow(),2);
Cell[][] tempCellsForObj = tempRangeForTotObj.getCells();
tobObjCell = tempCellsForObj[0][0];
tobObjCell.setValue(value);
tobObjCell.setStyle(copyHeaderStyle(tobObjCell.getStyle()));
style = tobObjCell.getStyle();
style.setVAlignment(VerticalAlignmentType.CENTRED);
style.setHAlignment(HorizontalAlignmentType.CENTRED);
font = style.getFont();
font.setSize(9);
style.setFont(font);
tobObjCell.setStyle(style);
tobObjCell = tempCellsForObj[0][3];
tobObjCell.setValue(10);
tobObjCell.setStyle(copyHeaderStyle(tempCellsForObj[0][0].getStyle()));
style = tobObjCell.getStyle();
style.setVAlignment(VerticalAlignmentType.CENTRED);
style.setHAlignment(HorizontalAlignmentType.CENTRED);
font = style.getFont();
font.setSize(9);
style.setFont(font);
tobObjCell.setStyle(style);
tobObjCell = tempCellsForObj[0][4];
tobObjCell.setValue(50);
tobObjCell.setStyle(copyHeaderStyle(tempCellsForObj[0][0].getStyle()));
style = tobObjCell.getStyle();
style.setVAlignment(VerticalAlignmentType.CENTRED);
style.setHAlignment(HorizontalAlignmentType.CENTRED);
font = style.getFont();
font.setSize(9);
font.setBold(true);
style.setFont(font);
tobObjCell.setStyle(style);
//…
Note: Again this change was only made for performance considerations.
Thanks for your understanding!
Hi Amjad,
Thanks for the detailed description. We will continue our rest of the testing along with these changes & let you know in case of any issues.
Thanks & Regards
Karuppiah M
Hi Amjad,
As part of our testing with the latest Aspose.Cells.jar (version 2.2.0.2), we are facing some issues while setting R1C1 Formula for all the cells in the NamedRange same thing is working with our old Aspse.Cells.jar (version 2.1.0.6). In our application so many places we are using R1C1 formula, everything is working fine except this report.
Lets Say we have a NamedRange called "TestSheet___unadjustedPlanRounded" which is internally having inner NamedRanges as "TestSheet___unadjustedPlanRounded.month", "TestSheet___unadjustedPlanRounded.value0", "TestSheet___unadjustedPlanRounded.value1"....."TestSheet___unadjustedPlanRounded.value12".
At run time the above inner NamedRanges are expanded and programatically we are setting r1c1 forumula for all the Cells in the inner NamedRanges.
End of the process all the inner NamedRange has the wrong r1c1 formula (='R[-38]C').. becuase of this wrong r1c1 formula set we are getting the wrong value as #NAME? and getting NumberFormat Exception when we do some arithmetic opration.
Same report is working with old Aspse.Cells.jar (version 2.1.0.6) and not working with the latest Aspose.Cells.jar (version 2.2.0.2). Please provide us the fix ASAP.
Attachments:
PFA, demo program for the same issue with the Excel template and its Output result excel files.
1) DEMOASPOSETESTINGNew.java - demo program to replicate this issue.
2) InputTemplate-demo.xls - Input excel Template
3) OutputReport-OldAspose-demo.xls - Output result excel file generate by using Aspse.Cells.jar (version 2.1.0.6)
4) OutputReport-LatestAspose-demo.xls - Output result excel file generate by using Aspse.Cells.jar (version 2.2.0.2)
Thanks & Regards,
Karuppaih M
Hi,
After an initial test using your code and template file, I can find the issue.
We will further look into it and get back to you soon.
I have logged it into our issue tracking system with an issue id: CELLSJAVA-17001.
Thank you.
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Please try the attached latest version of Aspose.Cells. We have fixed the issue of setting R1C1 formulas.
Thank You & Best Regards,
Hi Nausherwan,
With this latest Aspose version we are getting the below exception while do Workbook.calculateFormula().
java.lang.ClassCastException: com.aspose.cells.fv
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.Worksheet.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
Please make sure all the existing functionalities work fine & do not endup in any regression issue.
Thanks & Regards
Karuppiah M
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Thank you for the feedback.
We will further look into your issue and get back to you soon.
Thank You & Best Regards,
Hi,
Wee have fix the bug that may cause ClassCastException when calculating formulas. Please try the attached version.
Thank you.
Thanks Amjad,
We are testing and let you know if any other issues.
Thanks & Regards
Karuppiah M
The issues you have found earlier (filed as 17001) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
Hi,
We are using the latest Aspose.Cells (version 2.2.1.4). As part of the testing we found an issue while Cells.merge(...).
Issue description:
We have a NamedRange called "MMA_42___title" in the template Excel. In this tempalate all the cells in the Named Range are merged. Again the code we are doing merging which throws below error. (Refer the attached tempate MergeCellIssueTemplatge.xls)
java.lang.IllegalArgumentException: There is another merged area[startRow: 1, startColumn: 0, endRow: 1, endColumn: 3 that intersects given range.at com.aspose.cells.Cells.merge(Unknown Source) at devtest.DEMOASPOSETESTING.testMergeCellIssue(DEMOASPOSETESTING.java:196) at devtest.DEMOASPOSETESTING.main(DEMOASPOSETESTING.java:53)
Please find the attached sample Demo program(DEMOASPOSETESTING.java) for this issue. Also sending you the Template Excel(MergeCellIssueTemplatge.xls) and Result Excel (MergeCellsResult.xls) generate by older aspose.
Same kind of implementation is working fine the older Aspose.Cells (version 2.1.0.6)
Please let me know what cause this issue in the latest version which is not happending in older version of Aspose.Cells.
Thanks & Regards
Karuppiah
Hi,
It is not a bug of the new fix but an enhancement for
the merge() method. When saving the workbook to file formats such as,
Excel2007, if there are some merged areas that intersect with each
other, a warning or error message will be shown when opening such file into MS Excel. So we have added such restriction to avoid user adding
duplicated/intersected areas. In your template file, there has been one merged
area (A2:D2), so, it is not allowed to merge A2:B2 again. In fact this
operation is impossible in MS Excel too.
Thanks for your understanding!