Pivot SQL data in Excel

Aspose.Cells Product Family
1

I am a prospective customer and I want to find out if Aspose.Cells has the functionality I am looking for.

I have an application with a SQL database where a forecast table has columns for Fiscal Period, Resource Code, Dollars, and Hours. I want to be able to pivot this data into a spreadsheet table using the fiscal periods as the column headers, the resource codes as the row headers, and the dollars (or hours) as the cell data. I would have the dollars and hours on separate speadsheet rows, repeating the Resource Code label. The user would be able to make changes in the cells, and then I would like to write the changed data back to the appropriate rows in the original database. It would be possible for new database rows to be inserted as a result. Does Aspose.Cells have a component that can do this easily, or otherwise can you show me code examples of how to do this?

2

Hi,

Thanks for considering Aspose.

Well, Aspose.Cells is a non-graphical spreadsheet reporting component that enables either .Net or Java applications to read and write Excel spreadsheets without utilizing Microsoft Excel. It can perform equally well on desktop or web server environments. Aspose.Cells is a very feature rich component that offers much more than just basic exporting of data. With Aspose.Cells developers can import/export data, format spreadsheets in every detail and at every level, import images, import charts, create charts, stream Excel data, save in various formats etc. For complete list of features please check:

For you need, Aspose.Cells can do every thing you require. It can import data from dataset, datatable, array, datacolumn, dataview and datareader etc. into the worksheets. It can also export data from a worksheet to a dataset or datatable for saving into the database. It has a number of APIs for the task.

For you need I write a sample code which imports data from a table into the first worksheet of a workbook and save it on the disk:

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\Northwind.mdb");

con.Open();

OleDbCommand cmd = new OleDbCommand("Select * from Employees",con);

OleDbDataAdapter da = new OleDbDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds,"Employees");

Workbook workbook = new Workbook();

workbook.Worksheets[0].Cells.ImportDataTable(ds.Tables["Employees"],true,0,0,15,15,false,"m/d/yy");

workbook.Save("d:\\importdt.xls");

Kindly see our online demos and check their source code (When you install the component(windows installer package) .msi The demos are also installed onto your pc ):

It is noted that Aspose.Cells is not a graphical component. If you want that users directly and visually view/insert the data and then save it. There is also our Visual .Net component named Aspose.Grid for manipulating spreadsheets and importing exporting data visually with a grid.

Please check the demos too.

Now the choice is yours.

Feel free to contact us any time.

Thank you

3

Hi,

And how to create Pivot Tables using Aspose.Cells,

Please check for creating a simple pivot table based on data:

Thank you.