We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Extra quote added in formula causes error

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…

Hello

me again…
with the R1C1 formula it seems to work properly…

tablePersons.DataRange[i, 11].R1C1Formula="=IF(ISBLANK(RC[-1]),"",VLOOKUP(RC[-1],Adresses!R3C1:R100C2,2,FALSE))";

Hi,


Thanks for providing us code segments and good to know that you were able to sort it out by using R1C1Formula instead.

Well, to properly investigate your issue (regarding Formula attribute) with your specific dataset for the list objects/ tables to consequently figure out your issue properly, we appreciate if you could create a sample console application (runnable), zip it and post it here with all the files to reproduce the issue on our end. Also, you may use MS Access database or dynamic DataTable in your codes at runtime. Moreover, kindly provide your template Excel file here. All this will help us to evaluate your issue thoroughly on our end.

Thank you.

Hello

the main issue is that I’m using a French Excel and I’m using a semi column to separate parameters in the formulas. It seems that changing to simple comas solve the issue…
Instead of
tablePersons.DataRange[i, 3].Formula = “=VLOOKUP([AdressCode];tableAdresses[[Code]:[City]];2;0)”;

I use this

tablePersons.DataRange[i, 3].Formula = “=VLOOKUP([AdressCode],tableAdresses[[Code]:[City]],2,0)”;

Hi,


Good to know that you have sorted it out now. Please see the document for your further reference:
http://www.aspose.com/docs/pages/viewpage.action?pageId=15565448

Thank you.