Pivot Table using C#

[Abhinav Pandey]

Hi,

I am using Syncfusion XlsIO (v4.0.30319). Could you please help me with a sample code on how to generate Pivot Table using System.Data.DataTable as source. I want to export the Pivot in an excel format. Currently, I am using the following to achieve the results but I am not able to:

/// <summary>

/// Saves pivot data to a new worksheet

/// </summary>

/// <param name="workbook">Workbook</param>

/// <param name="dataTable">DataTable</param>

private void savePivotDataToWorkBookSyncfusion(IWorkbook workbook, System.Data.DataTable dataTable, IApplication excel)

{

IPivotTable pivotTable = null;

IRange rangeOfPivotData = null;

//IRange destinationOfPivotData = null;

IPivotField desk = null;

IPivotField salesTrader = null;

IPivotField parentName = null;

IPivotField ageBucket = null;

IPivotField tradeID = null;

string pivotTableName = dataTable.TableName;

IWorksheet worksheet;

worksheet = workbook.Worksheets.Create(dataTable.TableName);

int iRow = 0;

foreach (DataRow r in dataTable.Rows)

{

iRow++;

for (int i = 1; i < dataTable.Columns.Count + 1; i++)

{

if (iRow == 1)

{

// Add the header the first time through

worksheet[iRow, i].Text = dataTable.Columns[i - 1].ColumnName;

}

worksheet[iRow + 1, i].Text = r[i - 1].ToString();

}

}

IRange pivotLocation = worksheet.Range[dataTable.Rows.Count, dataTable.Columns.Count];

rangeOfPivotData = worksheet.Range[1, 1, dataTable.Rows.Count, dataTable.Columns.Count];

worksheet.PivotTables.Add(pivotTableName, pivotLocation, workbook.PivotCaches.Add(rangeOfPivotData));

//worksheet.PivotTables.Add(

//// Select a range of data for the Pivot Table.

//rangeOfPivotData = worksheet.Range. get_Range("A1", "S" + (iRow + 1).ToString());

//rangeOfPivotData.EntireRow.Hidden = true;

//int space = iRow + 5;

//// Select location of the Pivot Table.

//destinationOfPivotData = worksheet.get_Range("A" + space, useDefault);

//workbook.PivotTableWizard(XlPivotTableSourceType.xlDatabase, rangeOfPivotData, destinationOfPivotData, pivotTableName, true, true, true, true, useDefault, useDefault, false, false, XlOrder.xlDownThenOver, 0, useDefault, useDefault);

//rangeOfPivotData..Style.Font.Size = 8;

//// Set variables for used to manipulate the Pivot Table.

pivotTable = (IPivotTable)worksheet.PivotTables[pivotTableName];

pivotTable.Options.IsSaveData = true;

pivotTable.Options.ShowFieldList = false;

// set the properties, orientation of pivot fields

this.setPivotFieldPropertiesSyncfusion(pivotTableName, desk, salesTrader, parentName, ageBucket, tradeID, pivotTable);

}

/// <summary>

/// Sets the pivot table field properties

/// </summary>

/// <param name="pivotTableName"></param>

/// <param name="desk"></param>

/// <param name="salesTrader"></param>

/// <param name="parentName"></param>

/// <param name="ageBucket"></param>

/// <param name="tradeID"></param>

/// <param name="pivotTable"></param>

private void setPivotFieldPropertiesSyncfusion(string pivotTableName, IPivotField desk, IPivotField salesTrader, IPivotField parentName, IPivotField ageBucket, IPivotField tradeID, IPivotTable pivotTable)

{

if (!pivotTableName.Equals("Unmatched SPG Trades"))

{

desk = ((IPivotField)pivotTable.Fields[16]);

desk.Axis = Syncfusion.XlsIO.PivotAxisTypes.Row;

}

salesTrader = ((IPivotField)pivotTable.Fields[5]);

parentName = ((IPivotField)pivotTable.Fields[4]);

ageBucket = ((IPivotField)pivotTable.Fields[2]);

tradeID = ((IPivotField)pivotTable.Fields[11]);

// Format the Pivot Table.

//pivotTable.Format(XlPivotFormatType.xlReport2);

//pivotTable.SaveData = true;

// Set Sales Region as a Row Field.

salesTrader.Axis = Syncfusion.XlsIO.PivotAxisTypes.Row;

// no subtotals for sales/trader

salesTrader.Subtotals = Syncfusion.XlsIO.PivotSubtotalTypes.None;

parentName.Axis = Syncfusion.XlsIO.PivotAxisTypes.Row;

ageBucket.Axis = Syncfusion.XlsIO.PivotAxisTypes.Column;

tradeID.Axis = Syncfusion.XlsIO.PivotAxisTypes.Data;

　

　

// color and formatting

//foreach (IPivotField rowField in pivotTable.RowFields)

//{

// //IRange rowFieldRange = rowField.DataRange;

// // rowFieldRange.Interior.Color = XlRgbColor.rgbWhite;

//}

}

[Johnson Monohar M]

Hi Abhinav Pandey,

Thank you for using Syncfusion products.

XlsIO supports to set dataTable as a data source by importing data table into the sheet with the following code snippets. Kindly follow the provided code snippets and achieve your requirement.

sheet0.ImportDataTable(dataTable0, true, 1, 1,true );

IWorksheet pivot_table_worksheet1 = workbook.Worksheets[1];

pivot_table_worksheet1.Name = "Pivot 1"

IPivotCache item_cache1 = workbook.PivotCaches.Add(sheet0[sheet0.Range.AddressLocal]);

IPivotTable item_pivot_table1 = pivot_table_worksheet1.PivotTables.Add(

"PivotTable1", pivot_table_worksheet1["A1"], item_cache1);

Also, we have provided you a sample which illustrates the above requirement in the following link.

Sample Link : CreateSpreadSheet.zip

Please let us know if you need any clarification or if the provided sample resolved your requirement.

Thanks,
Johnson