We are using aspose.cell 8.2.2 version - Issue while converting XLSX to XLSB.
XLSX has certain formulas in sheet, after converting XLSX to XLSB, XLSB sheet is not showing/displaying any formulas.
XLSB is converted from XLSX but XLSB sheet is not having any formulas calculated in XLSB sheet.
Please help to resolve this issue.
@Mufaddal53 Your question is related to Aspose.Cells. I will move it into the dedicated forum. My colleagues from Aspose.Cells forum will help you shortly.
@Mufaddal53
Please try the latest version 23.2.
If you still has any issue ,pleae post your template file here, we will check it soon.
Thanks for your reply. I cannot attach XLSX template here because of confidential data.
As said earlier, I am using Aspose.cells 8.2.2 version and following code-
var workbook = new Workbook(strinputfilename);
workbook.Save(stroutputfilename, Aspose.Cells.SaveFormat.Xlsb);
Why formulas in XLSB after conversion is not working in my scenario ?
Do we have SAVE AS function or any other function to make it work with Version 8.2.2?
@Mufaddal53,
For locating the issue, you can use fake data to simulate confidential data, or delete some important data, leaving only the insignificant data that can reproduce the issue.
There are many enhancements and bug fixes between our latest version and the old one you are using. Maybe it was a bug in 8.2.2 and has been fixed in recent versions so we need to confirm that at first. And we cannot fix bugs based on old versions, so to solve the issue you have to upgrade Aspose.Cells to newer version. If the issue has been fixed in our latest version such as 23.2, you may upgrade to it. If the issue can be reproduced by our latest version, we will create one ticket for it and try to fix it soon(for such situation we need the template file to figure the issue out).
Please see attached XLSX file that we used as input and XLSB as output that we received as output.
I noticed that in XLSX formula was =IFERROR(C9/$C$6,0)
Aspose Support.zip (26.3 KB)
and XLSB it is =@IFERROR(C9/$C$6,0)
Why after conversion “@” is added to Formula ? may be reason why they are not calculated after conversion.
Following code used -
string stringBeforeChar = parameters.Substring(0, parameters.IndexOf("."));
string strinputfilename = "D:/Shared Files/Split/Input/" + parameters;
string stroutputfilename = "D:/Shared Files/Split/Output/" + stringBeforeChar + ".xlsb";
var workbook = new Workbook(strinputfilename);
workbook.CalculateFormula();
workbook.Save(stroutputfilename, Aspose.Cells.SaveFormat.Xlsb);I tried to extract files from your zipped archive but, it seems either it is corrupted/damaged or my current WinRAR tool cannot extract files inside your encrypted zipped archive. My WinRAR tool prompts a message (e.g., “Unsupported encryption method in … Dummy_Formula_File.xlsx”) Could you re-zip your input Excel file again (please don’t encrypt the archive) and re-share it here. We will check your issue soon.
Please see attached XLSX file.Aspose Support.zip (24.2 KB)
Let me know if it works and your resolution is very imp to us.
Have you received attached file for your analysis ?
Please help to troubleshoot issue. Our application already internally uses 8.2.2 version of Aspose.cells DLL and if any set of code can help us to solve the issue. If we can use DLL with other name so that it does not impact existing functionality.
We have tested your file and found the xlsb file generated with our latest version 23.2 works fine. There must be some bug in the old version 8.2.2 for converting or calculating formulas.
For the added “@” for formulas, it is new feature of ms excel for array formulas. If one array formula is saved as legacy array formula but it should be saved as dynamic array formula, ms excel will add “@” for it automatically when displaying the expression.
Dynamic array formula was not supported by the old version you are using either. For parsing/converting/calculating formulas, they are complicated features and we have made many bug fixes and enhancements for them. It is hard or even impossible to solve such kind of issues or find an workaround based on those old versions.
Anyways, you may try to replace the formula of IFERROR with IF function in your template file to see whether 8.2.2 can make it work for the generated xlsb. If not, we are afraid you have to upgrade to newer versions of Aspose.Cells. You may download and try the latest version 23.2 at first to make sure it can work for you before you perform the upgradation.
Our application is using Aspose.cells 8.2.2 version so cannot upgrade that version to latest as it may impact existing feature and functionality of application.
Can we upgrade or get DLL with some other name may be like Aspose1.cells and use that in our C# code so it will not interfere with existing DLL name- Aspose.cells.
@Mufaddal53
As we said in the previous post, we can not build fixes on the old version.
If you still want to work with old versioin 8.2.2, please change formula
=IFERROR(C9/$C$6,0)
as
=IF(ISERROR(C9/$C$6),0,C9/$C$6)
Can we replace @ in all XLSB Excel formulas with “” or blank with some code/function after XLSB file get converted from XLSX ?
In that way we can resolve this issue.
As of now, we have following code -
string stringBeforeChar = parameters.Substring(0, parameters.IndexOf("."));
string strinputfilename = “D:/Shared Files/Split/Input/” + parameters;
string stroutputfilename = “D:/Shared Files/Split/Output/” + stringBeforeChar + “.xlsb”;
var workbook = new Workbook(strinputfilename);
workbook.Save(stroutputfilename, Aspose.Cells.SaveFormat.Xlsb);
Please note, “@” against formulas is new feature of MS Excel for array formulas. If one array formula is saved as legacy array formula, MS Excel will automatically add “@” while displaying the expression. As we told you, dynamic array formula was not supported by the older version that you are using. Moreover, there may not be any workaround for the old versions to cope with it.
We again recommend you to kindly upgrade to latest APIs set of Aspose.Cells for .NET.
When I am manually replacing @ with “” or blank value, it is working and formulas are recalculated so i am thinking if we can get some existing aspose string replace function that can replace @ with “”/blank value.
I have also tried following but it is not working -
workbook.Replace("@", “”);
Please suggest.
We would again press on what we told you already. In older versions (e.g. 8.2.2), dynamic array formulas were not supported, the formulas were used to be saved as legacy array formula where as it should be saved as dynamic array formula (which newer versions support this feature). So, when using your older version, those array formulas were parsed and set but MS Excel will take them as legacy array formulas and would always add “@” for it automatically when displaying the expression. That’s why you may not skip or discard “@” using older version of Aspose.Cells for .NET.
Thanks for your prompt response. We are not able to replace Aspose.cell 8.2.2 version as application component is using that version and replacing might interrupt existing functionality.
Can we use two version of Aspose.cell dll at once in program, may be with different name ? and utilize 23.2 DLL with different name because i am not able to change the name of DLL and use it in our C# code.
@Mufaddal53,
We do not support referencing two versions of Aspose.Cells in a project. You need to update to the latest version to solve it. If you have any issues, please feel free to contact us.
We are not sure how will you use two dlls of the product with different names in your project even if it is available. Maybe you want to use the newer one to convert some special files such as the one contains IFERROR and dynamic array formulas? If so, we think you may create another project to reference to the newer version which can work for this case and do the conversion. Then, you may use the correctly generated xlsb file in your original project(which references to 8.2.2) to continue the process.
If this workaround cannot work for you either, we are afraid there is no better solution for your situation than upgrade the product.