How to use vlookup formula,

[DanaldLu]

Hello, there are two excel sheets that should have same datas in it, base.xls and comp.xls, both has 6row and I want two compare them column by column. So I first copy the first two columns of base.xls to a temp workbook, and set the third column as vlookup from comp.xls, thus the second and the third column of temp workbook could be compared to see whether thy are the same data. But my code below don't work. When I open in excel the output0.xlsx , the MS Excel would delete the formula and there is no data for column 3th or 4th.

base.xls:
code name birthday sex
1 zhang 1990-2-1 female
2 zhang 1990-3-1 male
3 wang 1990-3-2 female
4 li 1992-3-1 male
5 zhao 1994-3-2 female
6 zhao 1993-3-3 female

comp.xls
code name birthday sex
1 zhang 1990-2-1 female
2 wang 1990-3-1 female
3 wang 1990-3-2 female
4 li 1992-3-2 male
5 zhao 1994-3-2 female
6 zhao 1993-3-3 female

if the vlookup formula work, the output.xlsx should like
code name name
1 zhang zhang
2 zhang wang
3 wang wang
4 li li
5 zhao zhao
6 zhao zhao

but there are only first two columns ( code and name) in output.xlsx in the end.

How should I write the VLOOKUP formula ?

Code: Select all

static void Main(string[] args)
    {       
        string file1 = @"E:\base.xls"; 
        string file2 = @"E:\comp.xls";
                                             
        Workbook workbook1 = new Workbook();
        workbook1.LoadFromFile(file1);   
        Worksheet sheet1 = workbook1.Worksheets[0];
 
        Workbook workbook2 = new Workbook();
        workbook2.LoadFromFile(file2);
        Worksheet sheet2 = workbook2.Worksheets[0];

        Workbook wb_temp1 = new Workbook();
        Worksheet st_temp1 = wb_temp1.Worksheets[0];

        sheet1.Columns[0].Copy(st_temp1.Columns[0]);
        sheet1.Columns[1].Copy(st_temp1.Columns[1]);

         int y = sheet1.Rows.Count();

        string cFormula = "=VLOOKUP(A:A,workbook2.Worksheets[0]!A:B, " ,+ 2 + ",0)"; 
        //string cFormula = "=VLOOKUP(A:A,sheet2!A:B, " ,+ 2 + ",0)";

        st_temp1.Range[1, 3, y, 3].Formula = cFormula;
        st_temp1Range[1, 4, y, 4].Value = st_temp1.Range[1, 3, y, 3].FormulaValue.ToString();
         
       wb_temp1.SaveToFile(@"E:\output.xlsx", ExcelVersion.Version2010);

        string currFormula = "=IF(B2=C2, 0, 1)";
        st_temp1.Range[1,4, y,4].Formula = currFormula;

        Spire.Xls.Collections.AutoFiltersCollection filters = st_temp1.AutoFilters;
        filters.Range = st_temp1.Range[1, 4, st_temp1.LastRow, 4];
        filters.AddFilter(0, "1");
        wb_temp1.SaveToFile( @"E:\outputfilter.xlsx", ExcelVersion.Version2010);
    }

[DanaldLu]

Besides, how to get the value of each formula of vlookup and IF(B=C,'true', 'false')? When I debug the codes, the formula value is always 0.
Also, how to copy the filter result to a new sheet ? I tried the sheet.copy() function, it always copy the whole sheet, not only the filtered result.

Regards

[Betsy.jiang]

Hi,

Thanks for your inquiry.
1. Vlookup Formula
Firstly, you need to add correct formulas to the corresponding cells. I tested your code, yet I found it cannot run, so I changed it to following code:

Code: Select all

        string file1 = filePath+ @"base.xls";
        string file2 = filePath + @"comp.xls";
                                             
        Workbook workbook1 = new Workbook();
        workbook1.LoadFromFile(file1);   
        Worksheet sheet1 = workbook1.Worksheets[0];
 

        Workbook wb_temp1 = new Workbook();
        Worksheet st_temp1 = wb_temp1.Worksheets[0];

        sheet1.Columns[0].Copy(st_temp1.Columns[0]);
        sheet1.Columns[1].Copy(st_temp1.Columns[1]);

         int y = sheet1.Rows.Count();

        string cFormula = @"=VLOOKUP(A2,'E:\[comp.xls]Sheet1'!$A$1:$B$7,2,0)";

        st_temp1.Range[2,3].Formula = cFormula;

        //copy formulas to corresponding cells
        st_temp1.Range[2, 3].Copy(st_temp1.Range["C3:C7"], true);

        wb_temp1.SaveToFile(@"output.xlsx", ExcelVersion.Version2010);

        string currFormula = "=IF(B2=C2, 0, 1)";
        st_temp1.Range[1,4, y,4].Formula = currFormula;

        string FValue = st_temp1.Range[1, 4, y, 4].FormulaValue.ToString();
 
        Spire.Xls.Collections.AutoFiltersCollection filters = st_temp1.AutoFilters;
        filters.Range = st_temp1.Range[1, 4, st_temp1.LastRow, 4];
        filters.AddFilter(0, "1");

        wb_temp1.SaveToFile( @"outputfilter.xlsx", ExcelVersion.Version2010);

Attached are my input and output files.

2. Get the value of each formula of vlookup and IF(B=C,'true', 'false'), and the formula value is always 0
Below code could get formula value. If your formula includes external links, I am afriad our product doesn't support updating the value. Besides, I found the formula "=IF(B=C,'true', 'false')" is wrong in MS Excel.

Code: Select all

string FValue = st_temp1.Range[1, 4, y, 4].FormulaValue.ToString();

If there is still the issue, please provide your file.

3. Sheet.copy() function
What is the parameters in the method Sheet.copy()? Note it will copy ranges you set in the method, I am afraid it cannot only copy filter result.
To help us investigate further, please provide your sample files and point out the ranges you want to copy.

BTW, we provide Chinese service, you could contact us in Chinese next time.

Sincerely,
Betsy
E-iceblue support team

[DanaldLu]

Thanks for your reply. Your code works well. But I still have some questions.

1st, with regards to "string cFormula = @"=VLOOKUP(A2,'E:\[comp.xls]Sheet1'!$A$1:$B$7,2,0)", is the string E:\[comp.xls] a must, and can't use workbook, since the comp.xls is loaded into the workbook? Will it be loaded again each time calcuate the formula?

2nd,When I create a new workbook and add a new sheet, the Lastrow OR Lastcolumn is always the maximum an excel file can hold, not the actral Lastrow/Lastcolumn utilized or filled. For example, n1, n2 are all 2147483647, not 1 or 0.

Code: Select all

Workbook wb_temp1 = new Workbook();
        Worksheet st_temp1 = wb_temp1.Worksheets[0];
        int n1 = st_temp1.Lastrow;
        int n2 = st_temp1.Rows.Count()

I also tried

Code: Select all

Workbook wb_temp1 = new Workbook();
        wb_temp1.Clear();
        Worksheet st_temp1 = wb_temp1.Worksheets.Add("sheet1");
        int n1 = st_temp1.Lastrow;
        int n2 = st_temp1.Rows.Count()

the result is the same. But if I load from an existing .xls file created with MS EXCEL, the Lastrow/LastColumn would be exactly correct number.

So, how to get the true count of columns or rows of a newly created worksheet?

3rd, how could I set different font color of the text in a single cell, some red, some black?

[Betsy.jiang]

Hi,

Thanks for your information.
1. Our Spire.XLS follows MS Excel standard. For the VLOOKUP formula, to achieve your target, in MS Excel it writes excel path rather than loading excel. So when you use Spire to add VLOOKUP formula, you just need to write same formula like in MS Excel. And our Spire cannot calculate the formula with external links. So when you open the result file, MS Excel will hint you update.

2. If you don't insert any value when creating a new workbook, the method LastRow/LastColumn will use default value. But if you add data in it, the method could return correct value.

Code: Select all

        Workbook wb_temp1 = new Workbook();
        Worksheet st_temp1 = wb_temp1.Worksheets[0];
        st_temp1.Range["A1"].Value = "2";
        //return 1
        int n1 = st_temp1.LastRow;
        //return 1
        int n2 = st_temp1.Rows.Count();

3. Please refer to following guide:
https://www.e-iceblue.com/Tutorials/Spi ... B.NET.html

Sincerely,
Betsy
E-iceblue support team