Can an excel workbook serve as a data source for another workbook?

Hello. I am exporting data using Smart markers template from which my user creates other reports in another workbook. Up till now he has been using named range cell option(Programatically assigned) to reference the desired cell/cells to make the changes reflect. With each subsequent exports new rows come in the source which he has to refer manually which is quite a hectic job. What he wants is to make the exported workbook act as a data source for his reports workbook so that each time when changes occur in the source workbook e.g more rows were exported or data is changed in existing then in his reports workbook those changes should automatically reflect. he doesn’t have to do it manually.
He just has to set that sheet as report’s data source.
******Remember. Each time when data is exported new workbooks comes in which he will replace with the source.
For the sake of demonstration i am appending two workbooks.

AccountLedger(3) serves as data source
where CustomReports serves a client reports.
Now if you open AccountLedger(3) and CustomerReports , you will see Data "TrnID 6 and TrnID 8"From AccountLedger(3) are referenced manually using named range technique. now in a AccountLedger(3) after a new export a new row came which is not referenced yet . Now to show user how the grandtotal 650 in Custom reports came he has to referenced this new row as well. Now think of about 1000’s rows which he has to refer manually.
Or in other’s view If GrandTotal 650 came in the break up for which it became 650 should also be reflected in CustomReports
Please guide me as soon as possible. I am really starving for guidance in this coz i am really running out of time.
Thanks big thanks.

Hi,


I think your scenario is a bit complex. If you need to get the updated grand total (in your current workbook) dynamically referencing to other workbooks at runtime, you may use: Workbook.UpdateLinkedDataSource() method.

See the simple code below that works fine to get the updated grand total dynamically. Note: I have to change the formulas in your second workbook a bit (accordingly for my current directory here to test it), I have renamed it to “MyCustomReports.xlsx” which is also attached here.

Sample code:

Workbook book = new Workbook(“e:\test2\Accounts+Ledger+(3).xlsx”);
Workbook book2 = new Workbook(“e:\test2\MyCustomReports.xlsx”);
//Suppose some values are updated in a cell e.g M2
book.Worksheets[0].Cells[“M2”].PutValue(400);
book.CalculateFormula();
MessageBox.Show(book.Worksheets[0].Cells[“M5”].DoubleValue.ToString()); //750 - OK

book2.UpdateLinkedDataSource(new Workbook[] { book });
book2.CalculateFormula();
MessageBox.Show(book2.Worksheets[0].Cells[“G16”].StringValue); //750 - OK


Hope, this helps.

Thank you.

Thanks for you immediate response but my concern is about those rows which are not referenced but should come in the custom reports to show the break up of grandtotal.

this code will only work for those row which are referenced manually but in my case client does not want to add cell’s reference manually.

Hi,

You have to use your own code to get the summary cells in worksheet of the source workbook accordingly. I can see one option though, you have to do it by yourself using your codes, you will not assign named cells in the template file and also you will not add references for those cells in the other worksheets in the template file. The only thing you may do is you will do it after processing the markers in the cells and then finding the cells having e.g "=Subtotal()" formula string (you may use Cells.FindFormula, FindFormulaContains or Find methods etc. --- See the topic: http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data) to first find your desired formula Summary cells, then you will define the break ups e.g for formula i.e. "=SUBTOTAL(9,M2:M4)", the dependents cell are M2:M4, you may extract to get those cells by your code. Here you may also use Aspose.Cells APIs regarding Precedents and Dependents APIs, see the topic for your complete reference:

, then add reference to those cells in your destination worksheets in your other workbook dynamically. Note, in that case, adding reference to other worksheets would be dynamic and you cannot specify the references for the cells/ranges in the template file.

I have created a sample code for your reference. I assumed that you have processed the smart markers and the data is filled into the first worksheet already, now you may refer to my sample code to add references to the summary cells (you may add further codes to get the break up or precedents cells as well) in your other worksheet accordingly. I used an example that will add references to the second worksheet in the same book, you may update the codes accordingly for other workbook sheets.

Sample code:

//

// Your code goes here to process the smart markers in the template file
// Once you process all the markers and all the data is filled into the worksheet.
// Now you may do it dynamically i.e. to specify references to those Subtotal cells in other worksheets.
//


Workbook wb = new Workbook(@"e:\test2\MySimpleBook.xlsx");


//First you need to find out the cell that contains "Amount" string.
Aspose.Cells.Cell mcell = wb.Worksheets[0].Cells.Find("Amount", null, new FindOptions());
//Get the column index
int mcol = mcell.Column;

//Specify one column range
FindOptions findOptions = new FindOptions();
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = mcol;
ca.EndRow = wb.Worksheets[0].Cells.MaxDataRow;
ca.EndColumn = mcol;
findOptions.SetRange(ca);
findOptions.LookAtType = LookAtType.StartWith;
findOptions.LookInType = LookInType.OnlyFormulas;
Aspose.Cells.Cell foundcell;
Aspose.Cells.Cell prevcell;

wb.CalculateFormula();

foundcell = null;
prevcell = null;
int i = 2;
do
{
foundcell = wb.Worksheets[0].Cells.Find("=SubTotal(", prevcell, findOptions);

if (foundcell == null)
{

break;

}

else
{
wb.Worksheets[1].Cells[i, 2].Formula= "="+foundcell.Worksheet.Name + "!" + foundcell.Name;

//Add your own code here.
i++;

}
prevcell = foundcell;

} while (foundcell != null);


wb.Save(@"e:\test2\output.xlsx", SaveFormat.Xlsx);


Note: in the above example I used a single workbook, I specify references in the second worksheet with respect to the first worksheet in the workbook.

Hope, this helps and you may write your own codes to achieve your tasks.

thank you.

This code has given me more knowledge about using Aspose.Cell in efficient way but still it has not supported my requirements. It is in hands of end user how he designs his report.

Hey may use M17 as his first column for the report, he may use other function for summarizing, he may use different column names etc. so applying your given code in that scenario will be difficult to track down the columns and data.
I think if Aspose.Cell provides such a functionality that if the smart markers template workbook could be served as a data source for his other workbooks then things can be achieved. Setting as Data source means,he just specifies which column’s data he needs to map in his workbooks from smart marker template. now whenever the data source changes, the changes will automatically reflect in his workbooks as well.
Please guide me in that perspective. I am really starving out for guidance because i am running out of time.
Thanks Again.
Hi,

Thanks for your further feedback.


We have analyzed your requirements, I have also a discussion with other developers. I am afraid, the better way for you is to create reports dynamically by the APIs, you may provide a page to the clients, then your customers could select and know on how to export the report accordingly. It should be your developer's job to create the code segments accordingly and do it. The code segment I provided in my previous post is just an example (it is not concrete yet) on how to find out the summary formulated cells in a column and then get calculated values in other worksheets etc. You have to transform it, add your own codes and update the code segments accordingly for your needs.

Alternatively, we think you may use different smart markers in the template files for different users, we think it might be a good solution for you. The reason is we may not support linking one workbook with other workbook (Set Workbook as Data Source for other Workbooks) as MS Excel (97-2010) does not have any such feature at all and we follow MS Excel standards, so we may not provide such feature. For your custom needs, you have to do it by yourself using Smart Markers and your custom codes using .NET and Aspose.Cells APIs.

Hamd:
Hey may use M17 as his first column for the report, he may use other function for summarizing, he may use different column names etc................

I think you may use Smart Markers even in the users' files as well, e.g
For Customer A: Table.Column1, Table.Column2(subtotal).
For Customer B: Table.Column10, Table.Column2(subtotal).
and so on...
Doing so, each user can get his expected report accordingly.
we do not need to consider how to link to aother workbook. Also, mind you, you need to maintain your back end Data Source accordingly.

Moreover, seeing your files in your first post in the thread, we think the files for your customers are fixed and not dynamic, I am afraid, it is totally illogical to build any solution dynamically.

So, in short, we have an opinion, it would be better that different customers should use different template files accordingly.

We hope, you may devise your solution accordingly in the light of above discussion.

Thank you.