Using VLOOKUP formula with List Objects not working in .NET

Aspose.Cells Product Family
1
1. Please advise why VLOOKUP formula is not inserted correctly. It is rendered in the following way (with quotes):
=VLOOKUP('TABLE1[@COL1]','TABLE2[[COL11]':'[COL13]]',2,FALSE)
and doesn't provide necessary result:
=VLOOKUP(Table1[@col1],Table2[[col11]:[col13]],2,FALSE)

2. Also, please advise if Aspose.Cells supports listObject DisplayName with dots in VLOOKUP formulas:
Example: VLOOKUP(Table1.Test.[@col1],Table1.Test.[[col11]:[col13]],2,FALSE)

Code example:
------------------------------------
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];
string[,] importCells = new string[,] {{"col1", "col2", "col3", "col4"}, {"1", "1", "1", "1",}, {"2", "2", "2", "2",}};
worksheet.Cells.ImportArray(importCells, 1, 1);
string[,] importCells2 = new string[,] { { "col11", "col12", "col13", "col14" }, { "1", "1", "1", "1", }, { "2", "2", "2", "2", } };
worksheet.Cells.ImportArray(importCells2, 11, 1);
Aspose.Cells.Tables.ListObject listObject = worksheet.ListObjects[worksheet.ListObjects.Add(1, 1, 4, 4, true)];
listObject.DisplayName = "Table1";
Aspose.Cells.Tables.ListObject listObject2 = worksheet.ListObjects[worksheet.ListObjects.Add(11, 1, 14, 4, true)];
listObject2.DisplayName = "Table2";
listObject.DataRange[0, 3].Formula = "=VLOOKUP(Table1[@col1],Table2[[col11]:[col13]],2,FALSE)";
workbook.CalculateFormula();
workbook.Save("temp.xls");

Thank you in advance!
2

Hi,

Well, I manually tried your expected formulas i.e.
=VLOOKUP(Table1[@col1],Table2[[col11]:[col13]],2,FALSE)
and also:
=VLOOKUP(Table1.Test.[@col1],Table1.Test.[[col11]:[col13]],2,FALSE)
But MS Excel complains about the formulas which are not correct.

Could you create an Excel file manually in MS Excel having your desired ListObjects with formulas, post the file here, we will check it soon.

Thank you.

3

Please see excel file with Aspose.Cells and manual versions attached. Formulas are stored in the following cells:

Aspose.Cells generated formula (see code example in post above): E3
Manual: K3

Also, I’ve noticed that formulas with used listObject may get corrupted after new rows are inserted to worksheet and formula parameters are replaced with #Ref! (no life example so far).
4

Hi,

1) The formula should be “=VLOOKUP(Table1[[#This Row],[col1]],Table2[[col11]:[col13]],2,FALSE)”;

2) There is an issue in calculating this formula. We will fix it in this week.

5

Thanks!


Please advise when and how we can download version with fix?
6

Hi,

Let me know if the latest fix resolves your problem. If it does not solve the problem then you will have to wait till next week. Aspose.Cells
for .NET v5.3.1.2

Please note, this issue is logged as CELLSNET-25574.

7

Thanks, but it looks like fix hasn’t appeared in release.


To give you better vision of the problem for faster resolution I’ve prepared excel template, code and result excel file which contains issue (see attached).
In result sheet:
- Table names are trunkated
- Formulas contain #Ref!

It starts working only when I manually edit Table names and manually paste formula from the source code to T column.

Please let me know when we can expect these issues to be fixed.

Thank you in advance!
8

Hi,

Please download the Aspose.Cells for .NET v5.3.1.4 and save the file as Excel 2007 (xlsx) file.

See the following code.

C#


Workbook workbook = new Workbook(FileFormatType.Xlsx);


workbook.Worksheets.Add();


Worksheet worksheet = workbook.Worksheets[0];


string[,] importCells = new string[,] { { “col1”, “col2”, “col3”, “col4” }, { “1”, “1”, “1”, “1”, }, { “2”, “2”, “2”, “2”, } };


worksheet.Cells.ImportArray(importCells, 1, 1);


string[,] importCells2 = new string[,] { { “col11”, “col12”, “col13”, “col14” }, { “1”, “1”, “1”, “1”, }, { “2”, “2”, “2”, “2”, } };


worksheet.Cells.ImportArray(importCells2, 11, 1);


Aspose.Cells.Tables.ListObject listObject = worksheet.ListObjects[worksheet.ListObjects.Add(1, 1, 4, 4, true)];


listObject.DisplayName = “Table1”;


Aspose.Cells.Tables.ListObject listObject2 = worksheet.ListObjects[worksheet.ListObjects.Add(11, 1, 14, 4, true)];


listObject2.DisplayName = “Table2”;


listObject.DataRange[0, 3].Formula = “=VLOOKUP(Table1[[#This Row],[col1]],Table2[[col11]:[col13]],2,FALSE)”;


workbook.CalculateFormula();


//Assert.AreEqual(listObject.DataRange[0, 3].Value, “1”);


workbook.Save(@”C:\CellsNet25574.xlsx");

9

Thanks, but I still see issues here:


1) This fix doesn’t fix issues I provided in templates and source code in previous post (truncated table names and #Ref! in formula)

2) The code example you’ve provided doesn’t work if I use
listObject.DataRange[0, 3].SetSharedFormula("=VLOOKUP(Table1[[#This Row],[col1]],Table2[[col11]:[col13]],2,FALSE)", 2, 1);
instead of
listObject.DataRange[0, 3].Formula = “=VLOOKUP(Table1[[#This Row],[col1]],Table2[[col11]:[col13]],2,FALSE)”;

Let me know if you need any more details (now you have 2 live examples of the issue with templates and source codes).

Tanks for prompt response and looking forward for quick resolution of the rest issues!
10

Hi,

Please try this fix Aspose.Cells for .NET v5.3.1.5 and save the file as Excel 2007 Xlsx file. We do not support formula refer to Table in xls file now.

The shared formula could not refer to other table. It’s limitation of MS Excel, so you have to set formula one by one.

After closely checking your need, we think your formula could be simply as “=IFERROR(VLOOKUP(Table1.name.[a],Table2.name.[[a]:[b]],2,FALSE),2)”;

Please check the following code:

C#


Workbook workbook = new Workbook(ExcelTest.sourcePath + “SlateReport_New_to_aspose.xlsm”);


Worksheet worksheet = workbook.Worksheets[0];


int tableIdx1 = worksheet.ListObjects.Add(1, 0, 6, 19, true);


worksheet.ListObjects[tableIdx1].DisplayName = “Table1.name.”;


int tableIdx2 = worksheet.ListObjects.Add(9, 0, 13, 18, true);


worksheet.ListObjects[tableIdx2].DisplayName = “Table2.name.”;


Aspose.Cells.Tables.ListObject loMain = worksheet.ListObjects[tableIdx1];


String sFormula = “=IFERROR(VLOOKUP(Table1.name.[a],Table2.name.[[a]:[b]],2,FALSE),2)”;


Range range = loMain.DataRange;


for (int i = 0; i < range.RowCount; i++)

range[i, loMain.DataRange.ColumnCount - 1].Formula = sFormula;


Assert.AreEqual(range[0, loMain.DataRange.ColumnCount - 1].Formula, sFormula);


workbook.CalculateFormula();


workbook.Save(ExcelTest.destPath + “SlateReport_New_to_aspose_result.xlsx”);


11

Thanks! it works now.

12

The issues you have found earlier (filed as 25574) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.