This project has moved. For the latest updates, please go here.

PivotTable Source (Use LoadFromDataTable instead of LoadFromCollection)

Jul 18, 2011 at 9:09 PM

I've recently upgraded from 2.8.0.2 to 2.9.0.1 and am experimenting with the newly added PivotTable functionality.

I'm following Sample12 of the SampleApp as a reference.  Instead of utilizing LoadFromCollection to populate the source data for my PivotTable, I'm need to utilize LoadFromDataTable.  I notice that this method (LoadFromDataTable) returns void whereas LoadFromCollection, LoadFromArrays, and LoadFromText all return ExcelRangeBase which is utilized as a argument within the PivotTables.Add function. 

Since LoadFromDataTable does not return anything, what would be the best way to pass this as a source to the PivotTable?  I've tried Worksheet.Cells is the ExcelRange of the data added but have had no luck.

Any suggestions would be appreciated.

Thanks,

Mr. Nakia D. Young

Jul 19, 2011 at 11:46 PM

Hi, well I'm also new to this and is very good, but I think you need the range of cells if you do it with a datatable, of course it will not devuleve but you have the range you can do so, I guess which shall include :

var wsData = excel.Workbook.Worksheets.Add("Data");

wsData.Cells["A1"].LoadFromDataTable(tbl, true, OfficeOpenXml.Table.TableStyles.Medium6);

ExcelRangeBase dataRange = wsData.Cells["A1:AG" + (tbl.Rows.Count + 1)];

tbl is the Datatable

 

Jul 20, 2011 at 4:41 PM
Thanks @carlozsilva 
I was able to get the range utilizing the Dimensions of the Worksheet that was created as a result of loading from the datatable. 
var wsData = excel.Workbook.Worksheets.Add("Data"); 
wsData.Cells["A1"].LoadFromDataTable(tbl, true, OfficeOpenXml.Table.TableStyles.Medium6); 
var dataRange = wsData.Cells[wsData.Dimension.Address.ToString()]; 
var wsPivot = excel.Workbook.Worksheets.Add("Pivot"); 
var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "pName");
Jul 22, 2011 at 10:53 PM
Edited Jul 22, 2011 at 10:53 PM

Good, can you help me with my problem I have please?  

http://epplus.codeplex.com/discussions/265802