dev/excel-export/ExcelManualRegressionTest.R

In cbailiss/pivottabler: Create Pivot Tables

library(openxlsx)
library(pivottabler)

wb <- createWorkbook(creator = Sys.getenv("USERNAME"))

# 1:  special case
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$renderPivot()
addWorksheet(wb, "Sc1")
openxlsx::writeData(wb, sheet="Sc1", x="Sc1 Empty", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc1", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 2a:  single measure on columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
addWorksheet(wb, "Sc2a")
openxlsx::writeData(wb, sheet="Sc2a", x="Sc2a Only 1 Measure", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc2a", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 2b:  three measures on columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$defineCalculation(calculationName="TotalTrains1", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains2", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains3", summariseExpression="n()")
pt$renderPivot()
addWorksheet(wb, "Sc2b")
openxlsx::writeData(wb, sheet="Sc2b", x="Sc2b Only 3 Measures", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc2b", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 2c:  single measure plus rows
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
addWorksheet(wb, "Sc2c")
openxlsx::writeData(wb, sheet="Sc2c", x="Sc2c Measure plus Rows", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc2c", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 2d:  single measure plus rows and columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
addWorksheet(wb, "Sc2d")
openxlsx::writeData(wb, sheet="Sc2d", x="Sc2d Measure plus RowsXCols", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc2d", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 2e:  single measure plus rows and 2 sets of columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
addWorksheet(wb, "Sc2e")
openxlsx::writeData(wb, sheet="Sc2e", x="Sc2e Measure plus RowsX2Cols", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc2e", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 2f:  three measures plus rows and 2 sets of columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains1", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains2", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains3", summariseExpression="n()")
pt$renderPivot()
addWorksheet(wb, "Sc2f")
openxlsx::writeData(wb, sheet="Sc2f", x="Sc2f 3 Measures plus RowsX2Cols", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc2f", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 3a:  single measure on rows
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$addRowCalculationGroups()
pt$renderPivot()
addWorksheet(wb, "Sc3a")
openxlsx::writeData(wb, sheet="Sc3a", x="Sc3a Only 1 Measure", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc3a", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 3b:  three measures on rows
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$defineCalculation(calculationName="TotalTrains1", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains2", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains3", summariseExpression="n()")
pt$addRowCalculationGroups()
pt$renderPivot()
addWorksheet(wb, "Sc3b")
openxlsx::writeData(wb, sheet="Sc3b", x="Sc3b Only 3 Measures", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc3b", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 3c:  single measure plus columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$addRowCalculationGroups()
pt$renderPivot()
addWorksheet(wb, "Sc3c")
openxlsx::writeData(wb, sheet="Sc3c", x="Sc3c Measure plus Rows", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc3c", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 3d:  single measure plus rows and columns (produces identical output to 2d)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$addRowCalculationGroups()
pt$renderPivot()
addWorksheet(wb, "Sc3d")
openxlsx::writeData(wb, sheet="Sc3d", x="Sc3d Measure plus RowsXCols (same output as Sc2d)", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc3d", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 3e:  single measure plus rows and 2 sets of columns (produces identical output to 2e)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$addRowCalculationGroups()
pt$renderPivot()
addWorksheet(wb, "Sc3e")
openxlsx::writeData(wb, sheet="Sc3e", x="Sc3e Measure plus RowsX2Cols (same output as Sc2e)", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc3e", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# 3f:  three measures plus rows and 2 sets of columns
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains1", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains2", summariseExpression="n()")
pt$defineCalculation(calculationName="TotalTrains3", summariseExpression="n()")
pt$addRowCalculationGroups()
pt$renderPivot()
addWorksheet(wb, "Sc3f")
openxlsx::writeData(wb, sheet="Sc3f", x="Sc3f 3 Measures plus RowsX2Cols", colNames=FALSE, rowNames=FALSE, startCol=1, startRow=1)
pt$writeToExcelWorksheet(wb=wb, wsName="Sc3f", topRowNumber=3, leftMostColumnNumber=2, applyStyles=TRUE, mapStylesFromCSS=TRUE)
pt$renderPivot()

# finished
saveWorkbook(wb, file="C:\\Users\\Chris\\Desktop\\test.xlsx", overwrite = TRUE)