Compare two workbooks and add a new column

Aspose.Cells Product Family
1

Hello


I have a workbook with names and locations and a second workbook with names and sales. I would like to check each name in the NamesandLocations workbook and add their location in the NamesandSales workbook. I suppose that this can be done in Aspose?
2

Any help with this? Please I need a solution urgently!


Thanks
3

Hi,


Thanks for your posting and considering Aspose.Cells.

Yes, it is do able, you need to read cell value from one workbook and add it into another workbook. Please share your expected excel file which you can create manually using MS-Excel and then we will share a sample code to you to achieve the same thing using Aspose.Cells API. Thanks for your cooperation in this regard and have a good day.

4

Hi


Find attached the expected result (screenshot and Excel).


Thanks
5

Hi,


Thanks for your posting and using Aspose.Cells.

Please see the following sample code. It fulfills your requirements. I have attached the output excel file generated by the code and console output for your reference.

C#
//Load both of your workbooks
Workbook wb1 = new Workbook(“NamesandLocations.xlsx”);
Workbook wb2 = new Workbook(“NamesandSales.xlsx”);

//Access both of your worksheets
Worksheet ws1 = wb1.Worksheets[0];
Worksheet ws2 = wb2.Worksheets[0];

//Insert new column B
ws2.Cells.InsertColumn(1);

//Specify find options, we want to find entire content
//and we want to search cell values
FindOptions opts = new FindOptions();
opts.LookAtType = LookAtType.EntireContent;
opts.LookInType = LookInType.Values;


//loop all values in column A
for(int i=0; i<=ws1.Cells.MaxDataRow; i++)
{
//Access column A cell in first worksheet
Cell cell1 = ws1.Cells[i, 0];

//Find the cell value in second worksheet
Cell cell2 = ws2.Cells.Find(cell1.StringValue, null, opts);

//Print both of the cell names
Debug.WriteLine(cell1.Name + “-” + cell2.Name);

//Get the region cell
Cell cellR = ws1.Cells[i, 1];

//Add the regiion cell value in second worksheet
ws2.Cells[cell2.Row, 1].PutValue(cellR.StringValue);
}

//Save the workbook in xlsx format
wb2.Save(“output.xlsx”);

Debug Output
A1-A1
A2-A3
A3-A4
A4-A5
A5-A6
A6-A7
A7-A2

6

Thanks, that helps.

7

Hi,


Thanks for your feedback and using Aspose.Cells.

It is good to know that the above code is helpful, however, there is a performance issue in the code which should be fixed.

For performance consideration, please do not use properties like

Cells.MaxRow, MaxDataRow, MaxColumn, MaxDataColumn, …etc.

inside the loop condition. Those properties are calculated at runtime because user’s operation may change them at any time. For your case, please define one variable for it and use the variable in the loop:

int maxRow = ws1.Cells.MaxDataRow;

for(int i=0; i<=maxRow; i++)
{
…
8

Thanks Faiz



I noticed the performance issue as the code was taking over 10 min to iterate through a fairly modest data (1440 rows). Apart from the issue mentioned, the other issue is that the “Find” function seems to be searching through the whole cells in the worksheet.

Is there a way of specifying a specific column where the Find function should look for data - as this will speed up the code?


Regards
9

Hi,


Thanks for your posting and using Aspose.Cells.

Please use the FindOptions.SetRange() method for your needs. Please see the following sample code, its sample excel file and the debug output for your reference.

As you can see in the debug output, “abc” was searched only in column B but not in other columns.

C#
//Load your sample excel file
Workbook wb = new Workbook(“sample.xlsx”);
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Specify find options, we want to find entire content
//and we want to search cell values
//Besides, we want to search only column B
FindOptions opts = new FindOptions();
opts.LookAtType = LookAtType.EntireContent;
opts.LookInType = LookInType.Values;
CellArea ca = CellArea.CreateCellArea(“B1”, “B20”);
opts.SetRange(ca);

Cell cell = null;

while((cell=ws.Cells.Find(“abc”, cell, opts))!=null)
{
Debug.WriteLine(cell.Name);
}

Debug Output
B2
B6
B10