How to export/update data in excel workbook

Hello. I am working over a web based project being developed in asp.net (C#.net),Sql Server 2005 and Crystal reports.About a week ago I was assigned a task in which I was asked to give such an option in reports that End user can export their reports not only this but they would be able to make their own reports in excel using our exported data from the reports.
Exporting a crystal report to excel is an easy task but giving an option to merge crystal report's data with an exported sheet is cumbersome. Then i developed a logic in which i gave end user multiple options.
1.Export with Format.
2.Export Without Format (For developing reports i.e. the one i am going to attach with this email)
3. Merge Data.(Cumbersome)

Option 1 is pretty straight and easy
option 2 is being done in the same way as option 1 but only data from datatable in the dataset is exported in sorted form based on fixed columns while the report is shown.
now here comes the most concerned option 3. What happens in this is when user opts this, an fileupload control is shown where end user selects our exported sheet via option 2, then presses the ok button. first the excel file is upload on the server because we will be processing this with the server side code so we do require its references for which it needs to be available at server. then we extract the data from the data table for the report being shown and adds it up with this excel workbook in sorted form. Rows that already exist will be updated and rows that don't exist will be inserted.

this is the whole process up till now occurring.
My concern is if i ask user to develop their reports in the next sheet of same workbook then how the data in their sheet will be reflected because rows would vary each time they export.
e.g.
First time when user exports
TrnID Account Amount
1 Purchase 20
2 Purchase 30
3 Purchase 30

three rows are shown and they make their reports in the next sheet of same workbook
e.g adding up the amount which in this case will give them 80 with purchase account.if they select the proper range but next time when they export there will be more rows e.g.

TrnID Account Amount
1 Purchase 20
2 Purchase 30
3 Purchase 30
4 Purchase 10
5 Purchase 10
6 Cash 20
7 Cash 30
8 Sales 20
9 Sales 10
now in the next sheet they end user has developed the reports
data should be reflected as following.

Purchase-------> 100
Cash -------> 50
Sales -------> 30

I need to know how to achieve this thing using your api. I need you to give me examples and guidance in that. If it is applicable then my company would gladly buy the api.
Looking farward for the response.
Thanks.

Hi,


Well, Aspose.Cells for .NET is a class library that is used to manage (create, manipulate, convert etc.) Excel spreadsheets at every level. I think as I could understand your scenario a bit, there are a some options that you may make use of them to accomplish your tasks.

1) Aspose.Cells provides some useful APIs that can directly import or extract data from number of sources, e.g DataTable, Arrays, List etc. I think you may use your own ADO.NET APIs to fill your desired data into a datatable and then use Aspose.Cells APIs to import that whole table in one step to fill data into the worksheets of the Excel file (Workbook). See the topic for your reference:
https://docs.aspose.com/display/cellsnet/Import+Data+into+Worksheet


Here, you may also add formulas to your desired cells to sum up the range of cells accordingly. See the topic on how to specify the formulas (e.g "=SUM(B1:B10)":
http://www.aspose.com/docs/display/cellsnet/Using+Formulas+Functions+to+Process+Data

You may even calculate the formulas at runtime (using Aspose.Cells APIs) and get the calculated results, etc.
http://www.aspose.com/docs/display/cellsnet/Calculating+Formulas


2) An alternative way can be make use of Smart Markers. Smart Markers provide means and lets you add specific pointers in the template file (formatted for your choice) cells, when you process the markers using Aspose.Cells APIs, the data is automatically filled and records are inserted (rows are inserted) in the columns based on your SQL query data. Moreover, you may add formulas and Group Data for your needs. See the complete topic for your reference:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers


Hope, this helps for your needs.

Thank you.