Creating Spreadsheets form SQL Server Data

Aspose.Cells Product Family
1

Hi all,

I have a .NET website where I am currently creating an Excel spreadsheet that is streamed to the customer using the MIME type for Excel. It gives me 1 workbook for all the data.

I want to have a "Cover Sheet" workbook with the summary information followed by "Detail" sheets containing more information about each item on the "Cover Sheet".

For example, the customer selects a group of Books from an online library. The first workbook shows the entire list of books selected along with summary information. The folowing sheets have more detailed info about each Book.

I am new to Excel so I may be using the wrong terminology, but I think my intention is fairly clear. The user will just download one XLS file with the information I have mentioned.

Is this something I can do with Apose Cells? I may wish to include imbeded images and hyperlinks.\Are there examples of this somewhere?

Thanks to all for anyh answers.

2

Hi,

Thanks for considering Aspose.

Well, yes, Aspose.Cells can perform your desired task with ease.

I think you may use ADO.NET components to establish the connection with SQL Server and then execute SQL query to fetch data from the data source to fill a dataset / datatable. Later you may use Aspose.Cells APIs like Cells.ImportDataTable() to extract the datatable to the worksheets.

Here is a sample code (with Access database, you may change it with SQL server accordingly):

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select CustomerID, City, CustomerID + '-'+ City from Customers",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, "A1");
ws.AutoFitColumns();
wb.Save("d:\\test\\mybook.xls",Aspose.Cells.FileFormatType.Excel2003);

For ref, please check some wiki documentation:

And for further ref, please check the source code of our online demos:

http://www.aspose.com/Products/Aspose.Cells/Demos/

Thank you.

3

Thanks for the reply Amjad...

I have downloaded the latest version of Aspose Cells for .NET and will actually try using a variation of your code example as a first try to see what I get.

It looks like I can create an ADO.NET Dataset consisting of several DataTables. The first table will be the summary info and the rest of the tables containing details. I then import each table into a separate worksheet. It really looks amazingly simple.

Thanks again...