public void InsertPivotTableToSheet(int year, int month)
{
_app.Connect();
IExcelWorkbook workbook = _app.GetActiveWorkbook();
IExcelSheet sheet = workbook.GetActiveSheet();
ExcelSheetData sheetData = sheet.GetSheetData();
Transaction transaction = new Transaction();
int firstCol = Transaction.GetIndex(() => transaction.Owner);
int lastCol = Transaction.GetIndex(() => transaction.Amount);
int numberOfRows = sheetData.Rows.Count;
ExcelRangeInfo dataRange = new ExcelRangeInfo(1, firstCol, numberOfRows, lastCol + 1);
ExcelRangeInfo insertRange = new ExcelRangeInfo(numberOfRows + 2, firstCol);
string[] rows =
{
transaction.GetName(() => transaction.TransactionType),
transaction.GetName(() => transaction.Category),
transaction.GetName(() => transaction.SubCategory)
};
string[] columns =
{
transaction.GetName(() => transaction.Owner)
};
string[] values =
{
transaction.GetName(() => transaction.Amount)
};
PivotTableData data = new PivotTableData
{
Range = dataRange,
InsertCell = insertRange,
Columns = columns.ToList(),
Rows = rows.ToList(),
Values = values.ToList(),
Name = "Test01",
StyleName = "PivotStyleMedium2"
};
sheet.InsertPivotTable(data);
}
