Hello
I’m having a very annoying issue.
I have an excel document with two sheets
The first one contains a list of persons with a column containing the adressCode
The second one contains a list of adresses
I’m trying to achieve a VLOOKUP with aspose (everything works well in Excel)
private static Worksheet AddAdressesSheet(Workbook book)
{
using (Database dbObj = DBLayer.dbObj())
{
using (DataSet ds = dbObj.RunSQLReturnDataSet(“Select Code, City, Other Fields… FROM ADRESSES”))
{
Worksheet sheet = book.Worksheets.Add(“ADRESSES”);
ImportTableOptions options = new ImportTableOptions();
options.ConvertNumericData = true;
options.DateFormat = “dd/mm/yyyy”;
options.IsFieldNameShown = true;
sheet.Cells.ImportData(ds.Tables[0], 1, 0, options);
Aspose.Cells.Tables.ListObjectCollection listObjects = sheet.ListObjects;
int max = ds.Tables[0].Rows.Count+1;
listObjects.Add(1, 0, max, ds.Tables[0].Columns.Count - 1, true);
Range range = sheet.Cells.CreateRange(“B3”, “B” + max.ToString());
range.Name=“ADRESSES”;
return sheet;
}
}
}
private static Worksheet AddPersonsSheet(Workbook book)
{
using (Database dbObj = DBLayer.dbObj())
{
using (DataSet ds = dbObj.RunSQLReturnDataSet(“Select FirstName, LastName, AdressCode, OtherColumns…”))
{
Worksheet sheet = book.Worksheets.Add(“Persons”);
ImportTableOptions options = new ImportTableOptions();
options.ConvertNumericData = true;
options.DateFormat=“dd/mm/yyyy”;
options.IsFieldNameShown=true;
sheet.Cells.ImportData(ds.Tables[0], 1, 0,options);
Aspose.Cells.Tables.ListObjectCollection listObjects = sheet.ListObjects;
listObjects.Add(1, 0, 1 + ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count - 1, true);
return sheet;
}
}
}
public static byte[] DoExport()
{
Workbook book = new Workbook();
Worksheet persons = AddPersonsSheet(book);
Worksheet adresses = AddAdressesSheet(book);
book.Worksheets.RemoveAt(0);//Remove Sheet 1 (by default)
Aspose.Cells.Tables.ListObject tablePersons = persons.ListObjects[0];
tableVendeurs.DisplayName = “tablePersons”;
Aspose.Cells.Tables.ListObject tableAdresses = adresses.ListObjects[0];
tableAgences.DisplayName = “tableAdresses”;
for (int i=0;i<tablePersons.DataRange.RowCount;i++){
tablePersons.DataRange[i, 3].Formula = “=VLOOKUP([AdressCode];tableAdresses[[Code]:[City]];2;0)”;
}
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Save(ms, SaveFormat.Excel97To2003);
return ms.ToArray();
}
}
The issue i have is that the output formula is enclosed with Quotes and in uppercase
=VLOOKUP(’[AdressCode];TABLEADRESSES[[CODE]’:’[CITY]];2;0’)
I am using the latest version 7.7.2.0
Please advice, I’m considering buying this great component but I’m stuck there…