Pivot table and chart(C#)

 
top
Excel table

create an Excel table.

string tableDataInCSV = "Sales Person\tRegion\tSales Amount\t% Commission\n" +
"Joe\tNorth\t260\t10%\n" +
"Robert\tSouth\t660\t15%\n" +
"Michelle\tEast\t940\t15%\n" +
"Erich\tWest\t410\t12%\n" +
"Dafna\tNorth\t800\t15%\n" +
"Rob\tSouth\t900\t15%";
WorkBook workbook = new WorkBook();
//import table data
workbook.setCSVString(tableDataInCSV);

//add table to range A1:D7
Table table = workbook.addTable("DeptSales", 0, 0, 6, 3, TableBuiltInStyles.TableStyleMedium2);
//banded row
table.RowStripes = true;
//enable total row
table.setTotalRow(true, "Total");

//add new column(with structured references formula) to the table
table.addCalculatedColumn("Commission Amount", "DeptSales[[#This Row],[Sales Amount]]*DeptSales[[#This Row],[% Commission]]");
//set the column's total func(1-average 2-count 3-countNums 4-max 5-min 6-sum 7-stdDev 8-var)
table.setColumnTotalsFunc("Commission Amount", ExcelTotalsCalculation.Sum);

workbook.setText(11, 1, "Sales Total");
//using structured references formula in sheet
workbook.setFormula(11, 2, "SUM(DeptSales[Sales Amount])");

for (int i = 0; i < 5; i++)
workbook.setColWidthAutoSize(i, true);

workbook.writeXLSX("Table.xlsx");

 
top
Pivot table

create and set PivotTable Objects.

 try
{
WorkBook workBook = new WorkBook();
string csv = "Who\tWeek\tWhat\tAmount\n" +
"Joe\t3\tBeer\t18\n" +
"Beth\t4\tFood\t17\n" +
"Janet\t5\tBeer\t14\n" +
"Joe\t3\tFood\t12\n" +
"Joe\t4\tBeer\t19\n" +
"Janet\t5\tCar\t12\n" +
"Joe\t3\tFood\t19\n" +
"Beth\t4\tBeer\t15\n" +
"Janet\t5\tBeer\t19\n" +
"Joe\t3\tCar\t20\n" +
"Joe\t4\tBeer\t16\n" +
"Beth\t5\tFood\t12\n" +
"Beth\t3\tBeer\t16\n" +
"Joe\t4\tFood\t17\n" +
"Joe\t5\tBeer\t14\n" +
"Janet\t3\tCar\t19\n" +
"Joe\t4\tFood\t17\n" +
"Beth\t5\tBeer\t20\n" +
"Janet\t3\tFood\t18\n" +
"Joe\t4\tBeer\t14\n" +
"Joe\t5\tFood\t12\n" +
"Janet\t3\tBeer\t18\n" +
"Janet\t4\tCar\t17\n" +
"Janet\t5\tFood\t12\n" +
"Test\t4\tTester\t4\n" +
"Stephen\t8\tPerson\t7";

//set to cell B1 active
workBook.setActiveCell(1, 1);
workBook.setCSVString(csv);

//this will create new sheet for pivot table
workBook.insertSheets(1, 1);

BookPivotRangeModel model = workBook.getPivotModel();

//set pivot table linked to range
model.setList("Sheet1!$B$2:$E$28");

//set pivot table in the top-left location Sheet2!C3
model.setLocation(1, 2, 2);

//select Sheet2
workBook.Sheet = 1;

//make the pivot table active
workBook.setSelection("C3");

BookPivotRange pivotRange = model.getActivePivotRange();

BookPivotArea rowArea = pivotRange.getArea(BookPivotRange.row);
BookPivotArea columnArea = pivotRange.getArea(BookPivotRange.column);
BookPivotArea dataArea = pivotRange.getArea(BookPivotRange.data);

BookPivotField rowField = pivotRange.getField("Week");
rowArea.addField(rowField);
rowField = pivotRange.getField("Who");
rowArea.addField(rowField);
BookPivotField columnField = pivotRange.getField("What");
columnArea.addField(columnField);
BookPivotField dataField = pivotRange.getField("Amount");
dataArea.addField(dataField);

//workBook.write("Pivot2.xls");
workBook.writeXLSX("Pivot2.xlsx");
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}

 
top
Pivot table area formatting

Applied the formatting(font,fill,align etc) to specified area of the PivotTable.


BookPivotRange pivotRange = model.getActivePivotRange();

//...

RangeStyle rs = workBook.getRangeStyle();
rangeStyle.FontBold = true;
rangeStyle.FontItalic = true;

//fill background color with green
rangeStyle.Pattern = (short)1;
rangeStyle.PatternFG = Color.Green.ToArgb();

int selection = 0;//0-BlankColumnHeader 2-Data 3-all 4-TopLeft
pivotRange.setTableFormat(rs, selection);

 
top
Pivot table conditional formatting

Applied the conditional formatting(color scales,data bar,iconset etc) to specified area of the PivotTable.


BookPivotRange pivotRange = model.getActivePivotRange();

//...
 //set the conditional formatting for the pivot table
ConditionFormat conditionFormat = workBook.CreateConditionFormat();

// 3 color scales(green-yellow-red)
conditionFormat.setColorScale((short)3, new Color[] { Color.Green, Color.Yellow, Color.Red });

//data field index in the data area
int datafiled = 0;

// 0 - Selected cells 1- All cells showing "#DATAFIELD" values 3- All cells showing "#DATAFIELD" values for "#ROW_AREA_FIELDS" and "#COL_AREA_FIELDS"
int conditionType = 0;

pivotRange.setConditionalFormat(conditionFormat, datafiled, conditionType);

 
top
Pivot chart

Create a pivot table and then use that pivot table as the datasource to create a pivot chart..


 WorkBook workBook = new WorkBook();
int rowIndex = 0;
try
{
workBook.read("PivotTable.xls");

BookPivotRangeModel model = workBook.getPivotModel();

//select the sheet and range
workBook.Sheet = 0;
workBook.setSelection(0, 0, 0, 0);
BookPivotRange pivotRange = model.getActivePivotRange();
//fresh the pivot table
model.refreshRange(pivotRange);


ChartShape chart = workBook.addChart(5, 10, 12.6, 29.5);
chart.ChartType = ChartShape.Line;

//link the chart to the pivot table
chart.PivotSource = pivotRange;

workBook.writeXLSX("PivotTable.xlsx");
}
catch (Exception e)
{
}


 

 
top
Pivot table with external data source

Create a pivot table and then use that pivot table as the datasource to create a pivot chart..

PivotExDSSample.cs
public class PivotExDSSample
{

private static SXQuery Query
{
get
{
try
{
string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Northwind.mdb";
OleDbConnection myAccessConn = new OleDbConnection(strAccessConn);
myAccessConn.Open();
System.Data.OleDb.OleDbConnection conn = myAccessConn;
System.Data.OleDb.OleDbCommand st = conn.CreateCommand();
st.CommandText = "SELECT * FROM Orders";
System.Data.OleDb.OleDbDataReader rs = st.ExecuteReader();
System.Data.DataTable rsMetaData = rs.GetSchemaTable();
return new SXQuery(st, rs);
}
catch (System.Exception e)
{
Console.WriteLine(e.StackTrace);
}
return null;
}
}


[STAThread]
public static void Main()
{

WorkBook workBook = new WorkBook();
try
{

workBook.Sheet = 0;
BookPivotRangeModel model = workBook.getPivotModel();
model.setDataQuery(Query);

workBook.setSelection(7,5,7,5);

BookPivotRange pivotRange = model.getActivePivotRange();

model.refreshRange(pivotRange);

RangeArea rangeArea = pivotRange.getRangeArea();

BookPivotArea rowArea = pivotRange.getArea(BookPivotRange.row);
BookPivotArea columnArea = pivotRange.getArea(BookPivotRange.column);
BookPivotArea dataArea = pivotRange.getArea(BookPivotRange.data);
BookPivotArea pageArea = pivotRange.getArea(BookPivotRange.page);

BookPivotField rowField = pivotRange.getField("OrderID");
rowArea.addField(rowField);
rowField = pivotRange.getField("CustomerID");
rowArea.addField(rowField);

BookPivotField columnField = pivotRange.getField("OrderDate");
columnArea.addField(columnField);

BookPivotField dataField = pivotRange.getField("Freight");
dataArea.addField(dataField);

//BookPivotField pageField = pivotRange.getField("ShipRegion");
//pageArea.addField(pageField);

//workBook.write("PivotTableDS.xls");
workBook.writeXLSX("PivotTableDS.xlsx");
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}