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.
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);
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?
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.