We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Pivot Table using C#

Thread ID:

Created:

Updated:

Platform:

Replies:

104730 Aug 13,2012 08:30 AM Aug 15,2012 06:49 AM ASP.NET Web Forms (Classic) 1
loading
Tags: XlsIO
Abhinav Pandey
Asked On August 13, 2012 08:30 AM

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 [Syncfusion]
Replied On August 15, 2012 06:49 AM

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

 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;