Compare Cells from different data tables

Hi I'm pretty new to this component and I'm trying to find the best approach to my problem.

We have an excel workbook I need to load different sets of product data into into different ranges and ensure the data is on the right row by product number.

The product number is pre-populated in the excel book and not loaded from the database. Here is a extremely oversimplified layout of what I'm dealing with:

Database Table: Excel Layout
Product Range A Range B Product Range A Range B
1234 $4 24 1234 $4 24
1254 $2 87 5345 $2 56
2367 $1 100 Group Total sum() sum()
5345 $2 56
6234 $6 76 6234 $6 76
1254 $2 87
2367 $1 100
Group Total sum() sum()
Grand Total sum() sum()

We have 700+ products I need to load into several ranges from different tables (all linked by product number). Is there a better way then comparing on every single cell, or is there an easy way to find a matching row and insert values into the same row in each range?

Hopefully someone can suggest a pattern or method I can apply for each range.

Any advice would be great!

Thanks in advance!

Hi,

Thanks for considering Aspose.

Well, I think you may use overloaded version of Cells.ImportDataTable() method to extract data from the datatable to the worksheet. You can try to use ADO.NET components to connect to database, specify and execute SQL statement(s) / query based on the productid to fill the dataset with datatables based on the results of the query and then use Cells.ImportDataTable method to get the data to the cells in the worksheet. There are some other methods of Aspose.Cells.Cells class that you may also try for your need. i.e. ImportArray(), ImportDataRow(), ImportObjectArray() etc. And for finding any number or string in the cells of the worksheet, you may use Cells.FindNumber() and Cells.FindString() methods.

Following is sample code:

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select CustomerID, LastName, City from Customers where CustomerID=1",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds,"Customers");
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
// Import the datatable
ws.Cells.ImportDataTable(ds.Tables["Customers"], false, "A2");
ws.AutoFitColumns();
wb.Save("d:\\test\\extractdata.xls",Aspose.Cells.FileFormatType.Excel2003);

Thank you.

Thanks for quick reply!

I see how to load all the data from my query to excel. But I still don't see how to match the database product number to the excel row matching that number, make sense?

One idea I had is to load the data table to a new tab and write vlookups on the existing sheet. This sounds like it might work, but is there a better way?

Thanks again!

Hi,

Well, I think you may try to utilize Cells.FindNumber, Cells.FindString etc for your need. Both methods find the value in the worksheet and can return the resultant cell which contains the value.

Thank you.