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.
Please see excel file with Aspose.Cells and manual versions attached. Formulas are stored in the following cells:
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.
Thanks!
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.
Thanks, but it looks like fix hasn’t appeared in release.
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");
Thanks, but I still see issues here:
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”);
Thanks! it works now.
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.