Excel formula not working

#1

Hello Team,

I have added below 2 formulas in cell using aspose cells API (sample excel is attached with this ticket),

Column Q on US tab:

cell.setFormula("=IFERROR(VLOOKUP(D"+rowCounter+",Global!D:BM,13,FALSE),\"\")");

Column Q on Global tab:

cell.setFormula("=IFERROR(VLOOKUP(D"+rowCounter+",USA!D:V,16,FALSE),\"0\")");

But on the Global tab the added formula gets local file download directory path added the formula and the formula value gets changed to,

=IFERROR(VLOOKUP(D5,'C:\Users\689997\Downloads\[USA]USA'!D:V,16,FALSE),"0")

Due to above change vlook function is not working.

I am attaching sample excel report file for your understanding. Please let us know why we’re getting this issue.

Regards,

Pavan

#2

Hi Pavan,


I like to inform you that issue mentioned by you is not related to Aspose.Slides. I am moving this query to Aspose.Cells forum and our colleagues from Aspose.Cells will contact you soon.

Best Regards,
#3

Hi Pavan,


Could you re-attach your template XLSX again here as I cannot download your attachment. Also paste your complete sample JAVA program (runnable) here to evaluate your issue properly on our end, we will check it soon.

Thank you.
#4

Hello Team,


PFA sample code and excel file.

Look for columns R, S, T etc on Global tab.

Regards,
Pavan
#5

Hi,


Thanks for the JSP file and output file.

Could you separate the issue and create a simple console program with a few lines of code that should load your template Excel file (you may attach it here) and specify/set the formula on the cells to reproduce the issue on our end, we will check it soon. This way will save our time and we might log a ticket for it to consequently figure it out soon.

Thank you.
#6

Hello Team,

We’re experiencing this particular issue on column R of Global tab in “Range_Plan_Report_1473847566484.xls” file. Code in file truRangePlanReport.jsp at line 2742 is producing this issue. As requested, I couldn’t replicate this issue in sample java file and would need your help to understand the reason why it’s showing up in attached truRangePlanReport.jsp file at line 2742?

Let me know if you need more details?


Regards,

Pavan

#7

Hi,


Thanks for the files.

Well, it is really hard to trace the issue using your big .jsp file, we might not execute it properly due to different custom objects used in your code. We again request a simple JAVA console program(runnable) to reproduce the issue on our end. Also, if you cannot find the issue in a separate program, then the issue might be with your own code, so you should debug it and figure out the issue by yourself.

Thank you.
#8

Hello Team,

As requested, I’ve created one sample program to explain this issue (PFA java program. Rename file to .java). I’ve used below formula in A1 column of USA tab,

cell.setFormula("=IFERROR(VLOOKUP(D5,Global!D:BM,7,FALSE),\"\")");

But in the generated excel file I see,

=IFERROR(VLOOKUP(D5,'C:\dump\temp\[Global]Global'!D:BM,7,FALSE),"")

If you notice an extra file path is added to formula due to which formula doesn’t work.

Same formula works fine on A1 of Global tab.

This happens when VLOOKUP is used in formula and the excel sheet used in formula is not created yet created when formula code is read by JIT complier.

We need this to work because in our report we’ve references to multiple tabs.

Kindly suggest fix over this issue.

Regards,

Pavan

#9

Hi Paven,


Thank you for sharing the code.

We have evaluated the scenario on our side, and we believe the presented behaviour is correct. Please note, you are inserting a formula which references a worksheet that does not exist at that time, therefore the API considers it as an external source. Please try the same scenario in Excel application. As soon as you insert the same formula in the formula bar, the Excel application will pop-up a dialog to select the external spreadsheet in order to resolve the reference for Global.

If you wish to handle this situation in code, please insert the worksheet Global before referencing it in any formula. Please check the following piece of code as well as its resultant spreadsheet as attached.

Java

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet0 = null;
int sheetIndex = 0;
Cells cells = null;
Cell cell = null;
Style style = null;

sheet0 = worksheets.get(0);
sheet0.setName(“USA”);
cells = sheet0.getCells();
cell = cells.get(“A1”);

sheetIndex = worksheets.add();
Worksheet sheet1 = worksheets.get(sheetIndex);
sheet1.setName(“Global”);

cell.setFormula(“IFERROR(VLOOKUP(D5,Global!D:BM,7,FALSE),”")");

workbook.save(dir + “MyBook.xlsx”);