_exportToExcel.DecodeExcelFormula(wb, data, decryptExpressionRange, decryptColumnName);
one of the value got changed in decryptExpressionRange, now not able to map with the excel column. Please Help, it is urgent Production Issue.
Which Aspose API/library you are using? Could you please provide more details with sample project and sample files (please zip the resource files prior attaching). We will check your issue/requirements and assist you soon.
ExcelAsposeHandlr.zip (604.5 KB)
The attached ExcelAsposeHandlr we are using. Let me give you a background about what we are doing from our application and what is the ask.
public string EncodeExcelFormula(string formula, Worksheet worksheet, string accountIdColumn, string dataDetailTypeIdRow)
and
public DataTable DecodeExcelFormula(Workbook workbook, DataTable dt, string decryptExpressionRange, string decryptColumnName)
we are using from our application. EncodeExcelFormula is encoding a expression and DecodeExcelFormula is decoding the same encoded expression.
Now all this encoding and decoding is happening based on a dataset defined in the decryptExpressionRange(“rngAcctIdDataDtlTypeId” is the defined range in the shared excel file). In the attachment if you open the excel file, you will be able to see the data defined in the “rngAcctIdDataDtlTypeId”.
So if the expression is “=O26+W337+W378”, after calling “EncodeExcelFormula” it will convert the expression as “=104_70003+110_70295+110_70336” and then we are saving it to the database. Now during fetching the data in the very same way we are calling “DecodeExcelFormula(Workbook workbook, DataTable dt, string decryptExpressionRange, string decryptColumnName)”. decryptColumnName is the column which we are trying to decode from the DataTable dt. So after calling the DecodeExcelFormula, the expression “=104_70003+110_70295+110_70336” will be changed to “=O26+W337+W378”, and we are using the same in the excel report.
Now the problem started when we changed some of the data in the decryptExpressionRange. we have changed the value in B16 to “1,2,3,4,5,6,7…”. Now we want as earlier it was decoding 100 as K, 101 as L and so on, It should do the same for 1 as K, 2 as L, 3 as M and so on.
We do not have any option to not change the data because based on this new data we have done lots of changes at our end and also released in the Production. So it is not possible to change “1,2,3,4…” to “100, 101, 102, 103…”. Please let me know if you need any other information, any help will be really appreciated.
Thank you for providing the details and code snippets.
It could be related to the logic and algorithms you’ve created for parsing and decoding. Please allow us some time to carefully study your scenario and assess your intricate situation before we can share our insights and provide you with an update. Hopefully, we will get back to you soon.
From the provided code, we found there is no entry and data to re-produce the issue. Would you please provide a runnable project with test data to reproduce the issue so we can investigate it and figure the issue out for you?
DecodeExcelFormula.zip (1.2 MB)
Hi @johnson.shi and @amjad.sahi Thanks a lot for looking into this.
Sorry, I cannot share the actual Project for compliance, also won’t be able to provide you the access of the database. But I am trying my best to explain the actual issue and providing you the required data so that you can reproduce the issue at your end.
We are calling DecodeExcelFormula(wb, data, decryptExpressionRange, decryptColumnName)
here wb is the workbook where the decryptExpressionRange and the decryptColumnName is defined.
In the above shared “DecodeExcelFormula.zip” file, you will find “Projections_SubSovTmplt.xlsm” file, which is the wb.
In the same zip you will get “DataTable_Data.xlsx” file, which is the data table “data” here.
And the value of decryptExpressionRange and decryptColumnName is “rngAcctIdDataDtlTypeId” and “ACCOUNT_EXPRESSION” respectively.
Now in the data table “data” which you will get in “DataTable_Data.xlsx” file. In 1137 row in “ACCOUNT_EXPRESSION” column (C1137) the value is “=5_70003+11_70295+11_70336”. After calling DecodeExcelFormula(wb, data, decryptExpressionRange, decryptColumnName) it should update the same value as “=O26+W337+W378” and in the same way for row 1138 (C1138) the value “=11_70003+12_70295+12_70336” should be updated to “=W26+X337+X378”. But currently it is not working as expectedly.
P.S: Earlier when the value was “=104_70003+110_70295+110_70336” and “=110_70003+111_70295+111_70336” and the workbook wb was “Projections_SubSovTmplt_old.xlsm” it was working as expectedly. Now the same should work with the new values.
The codes related to encoding and decoding are from aspose only. So I do not think our solution is needed to look into the issue. I believe with the provided data you should be able to re-generate the issue and can guide us.
This is something urgent. Please keep us updated about your findings.
Thanks a lot for all the help.
Thanks for providing sample Excel files.
While it may be challenging to evaluate your issue based on the resource files and code snippet you have provided so far, but we will make an effort to investigate the matter to determine whether your code requires some adjustments/tweaks or if it is a potential bug in the Aspose.Cells APIs. We will look into it and we need to find out. We have created the following new investigation ticket(s) in our internal issue tracking system and will either provide an updated code snippet to resolve the issue or offer a corrected version (if an issue found in the API) in accordance with the terms outlined in Free Support Policies.
Issue ID(s): CELLSNET-57551
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
We evaluated your issue according to your description and the provided resources. It seems to us you need to decode the encoded formula, such as “=5_70003+11_70295+11_70336”, according to the defined map in SavingConfig!B15:C16 where B15 is encoded row and C15 is the actual row, B16 is encoded column and C16 is the actual column.
For column map, in “Projections_SubSovTmplt.xlsm”, the encoded value 1 corresponds to column K, 2 to L, …; in “Projections_SubSovTmplt_old.xlsm”, the encoded value 100 corresponds to column K, 101 to L, …;
For the encoded result “=5_70003+11_70295+11_70336”, it should be produced with the map defined in “Projections_SubSovTmplt.xlsm”. We are afraid it is impossible to decode with a different map and get the correct result.
In fact in your DecodeExcelFormula(), the decoding logic has nothing to do with our component, our apis only provides the map data(by exporting the required data according to the defined named range). And by our test the ranges and values got for both template files you provided are correct.
By the way, there is problem in your logic of decoding. If the map is “1,2,…” to “K, L…”, then for the encoded value “11”, because it matches “1” according to your logic, so the first “1” will be replaced with “K”, instead of the expected result(according to your requirement, it should be replaced as whole “11” to “W”). Maybe it is just the problem you are experiencing?
Hi @johnson.shi This is the problem we are experiencing. I agree with you. But here the problem is that “ExcelAsposeHandlr”, where the decoding logic has written is locked. We are not able to debug it nor change it.
Do you have any idea or suggestions how we can alter this logic written in “ExcelAsposeHandlr”. Thanks a lot for all the help.
To solve the issue, we think at least you should be able to change one of the two parts: the map defined in the template file, or the logic of ExcelAsposeHandlr. We do not know how do you create the template file which contains the map. From the view of design, we think to decode the data, you have to use the map with which the data was encoded. When the map has been changed, the encoded data should be decoded firstly with the old map, and then encode it with the new map and update the encoded data(to the workbook of DataTable_Data.xlsx, for example).
If you cannot change ExcelAsposeHandlr, we are afraid you have to change the map data defined in rngAcctIdDataDtlTypeId to revert it to the old map. You may make another map and convert “100” to “1”, “101” to “2”, … then put the rebuilt map list data to the corresponding cell(B16).
Hi @johnson.shi and @amjad.sahi,
We have an expectation that as it is a one-to-one defined mapping so the same decoding can also be done with the new set of values.
Thanks a lot for all the help. We are trying to fix it from our end, will let you know if any further help is required.
You’re welcome. We hope you will be able to resolve your issue on your own. Please don’t hesitate to write to us again if you have any more questions or feedback.