Pivot Tables — create, aggregate, change aggregation method, delete

# pivot-sample.py — pivot tables: same verification as the Node.js sample (pyosbxl)
import pyosbxl

enums = pyosbxl.enums

app = pyosbxl.App()
wb = app.createWorkBook("pivot-py.xlsx")
ws = wb.openWorkSheet("Sheet1")

# ---------- 1. Source data ----------
# 10 rows of Region / Product / Quantity
headers = ["Region", "Product", "Quantity"]
src_data = [
    ["East", "Apple", 10], ["East", "Orange", 20], ["West", "Apple", 30],
    ["West", "Orange", 15], ["East", "Apple", 25], ["North", "Orange", 40],
    ["North", "Apple",  5], ["West", "Apple", 10], ["East", "Orange", 30],
    ["North", "Apple", 20],
]
for i, h in enumerate(headers):
    col = chr(65 + i)
    ws.getRange(f"{col}1:{col}1").setValue(h)
for ri, row in enumerate(src_data):
    for ci, v in enumerate(row):
        col = chr(65 + ci)
        r = ri + 2
        cell = ws.getRange(f"{col}{r}:{col}{r}")
        if isinstance(v, int):
            cell.setNumberValue(v)
        else:
            cell.setValue(v)
src_total = sum(row[2] for row in src_data)
print(f"[1] Source data: {len(src_data)} rows, quantity total = {src_total}")

# ---------- 2. Create the pivot table (rows=Region, values=Quantity sum) ----------
pt = ws.getPivotTables().addPivotTable("SalesSummary", "A1:C11", "E1")

rf = pt.getField("Region")
rf.setFieldType(enums.XlPivotFieldType.PivotFieldTypeRow)
df = pt.getField("Quantity")
df.setFieldType(enums.XlPivotFieldType.PivotFieldTypeData)
df.setDataFieldSubTotalsMethod(enums.XlPivotFieldSubtotalsMethodType.SubtotalsMethodTypeSum)

pt.setFields(pt.getPivotTableSetting(), enums.XlPivotTableLayoutType.TabularForm,
             [rf], [], [], [df])
pt.refreshData()
pt.refresh()
print("[2] Created PivotTable (rows=Region, values=Quantity sum, layout=tabular form)")

# ---------- 3. Read back the results and cross-check ----------
# Pivot output: E1=header, E2 onward=row labels, F2 onward=values, last row=grand total.
# The grand-total label ("総計") is written by the library itself, not by our data.
pivot_rows = {}
for r in range(2, 11):
    label = ws.getRange(f"E{r}:E{r}").getValue().get(f"E{r}")
    val   = ws.getRange(f"F{r}:F{r}").getValue().get(f"F{r}")
    if label is not None and label != "":
        pivot_rows[label] = float(val)
        print(f"    {label} = {val}")
grand_total = pivot_rows["総計"]
# To match node's Number display (205), print as int when the value is integral
gt = int(grand_total) if grand_total == int(grand_total) else grand_total
print(f"[3] Check: grand total {gt} === source total {src_total} → {'OK' if grand_total == src_total else 'NG'}")

# ---------- 4. Change the aggregation method to Average ----------
df.setDataFieldSubTotalsMethod(enums.XlPivotFieldSubtotalsMethodType.SubtotalsMethodTypeAverage)
pt.setFields(pt.getPivotTableSetting(), enums.XlPivotTableLayoutType.TabularForm,
             [rf], [], [], [df])
pt.refreshData()
pt.refresh()
print("[4] Changed subtotal method from Sum to Average")
for r in range(2, 11):
    label = ws.getRange(f"E{r}:E{r}").getValue().get(f"E{r}")
    val   = ws.getRange(f"F{r}:F{r}").getValue().get(f"F{r}")
    if label is not None and label != "":
        print(f"    {label} = {val}")

# ---------- 5. Get field information ----------
fields = pt.getFields()
print(f"[5] getFields: {len(fields)} fields → " + ", ".join(f.getCaption() for f in fields))
print(f"    getLayoutType = {int(pt.getLayoutType())} (TabularForm={int(enums.XlPivotTableLayoutType.TabularForm)})")

# ---------- 6. Delete the pivot table ----------
ws.getPivotTables().removePivotTable("SalesSummary")
try:
    ws.getPivotTables().getPivotTable("SalesSummary")
    print("[6] Removal failed")
except Exception:
    print("[6] removePivotTable → removal confirmed OK")

wb.save()
wb.close()

// pivot-sample.java — pivot tables: same verification as the Node.js sample (javaosbxl)
import com.osboffice.osbxl.*;
import com.osboffice.osbxl.dto.*;
import com.osboffice.osbxl.enums.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args) {
        AppWrapper app = new AppWrapper();
        WorkBookWrapper wb = app.createWorkBook("pivot-java.xlsx");
        WorkSheetWrapper ws = wb.openWorkSheet("Sheet1");

        // ---------- 1. Source data ----------
        // 10 rows of Region / Product / Quantity
        String[] headers = {"Region", "Product", "Quantity"};
        String[][] srcData = {
            {"East", "Apple", "10"}, {"East", "Orange", "20"}, {"West", "Apple", "30"},
            {"West", "Orange", "15"}, {"East", "Apple", "25"}, {"North", "Orange", "40"},
            {"North", "Apple",  "5"}, {"West", "Apple", "10"}, {"East", "Orange", "30"},
            {"North", "Apple", "20"},
        };
        for (int i = 0; i < headers.length; i++) {
            String col = String.valueOf((char) ('A' + i));
            ws.getRange(col + "1:" + col + "1").setValue(headers[i]);
        }
        int srcTotal = 0;
        for (int ri = 0; ri < srcData.length; ri++) {
            for (int ci = 0; ci < srcData[ri].length; ci++) {
                String col = String.valueOf((char) ('A' + ci));
                int r = ri + 2;
                String addr = col + r + ":" + col + r;
                if (ci == 2) {   // the Quantity column is entered as numbers
                    int v = Integer.parseInt(srcData[ri][ci]);
                    ws.getRange(addr).setNumberValue(v);
                    srcTotal += v;
                } else {
                    ws.getRange(addr).setValue(srcData[ri][ci]);
                }
            }
        }
        System.out.println("[1] Source data: " + srcData.length + " rows, quantity total = " + srcTotal);

        // ---------- 2. Create the pivot table (rows=Region, values=Quantity sum) ----------
        PivotTableWrapper pt = ws.getPivotTables().addPivotTable("SalesSummary", "A1:C11", "E1");

        PivotFieldObjectWrapper rf = pt.getField("Region");
        rf.setFieldType(XlPivotFieldType.PivotFieldTypeRow);
        PivotFieldObjectWrapper df = pt.getField("Quantity");
        df.setFieldType(XlPivotFieldType.PivotFieldTypeData);
        df.setDataFieldSubTotalsMethod(XlPivotFieldSubtotalsMethodType.SubtotalsMethodTypeSum);

        List<PivotFieldObjectWrapper> empty = new ArrayList<>();
        pt.setFields(pt.getPivotTableSetting(), XlPivotTableLayoutType.TabularForm,
                     Arrays.asList(rf), empty, empty, Arrays.asList(df));
        pt.refreshData();
        pt.refresh();
        System.out.println("[2] Created PivotTable (rows=Region, values=Quantity sum, layout=tabular form)");

        // ---------- 3. Read back the results and cross-check ----------
        // Pivot output: E1=header, E2 onward=row labels, F2 onward=values, last row=grand total.
        // The grand-total label ("総計") is written by the library itself, not by our data.
        Map<String, Double> pivotRows = new LinkedHashMap<>();
        for (int r = 2; r <= 10; r++) {
            String label = ws.getRange("E" + r + ":E" + r).getValue().get("E" + r);
            String val   = ws.getRange("F" + r + ":F" + r).getValue().get("F" + r);
            if (label != null && !label.isEmpty()) {
                pivotRows.put(label, Double.parseDouble(val));
                System.out.println("    " + label + " = " + val);
            }
        }
        double grandTotal = pivotRows.get("総計");
        // To match node's Number display (205), print as long when the value is integral
        String gt = (grandTotal == Math.rint(grandTotal))
            ? String.valueOf((long) grandTotal) : String.valueOf(grandTotal);
        System.out.println("[3] Check: grand total " + gt + " === source total " + srcTotal
            + " → " + (grandTotal == srcTotal ? "OK" : "NG"));

        // ---------- 4. Change the aggregation method to Average ----------
        df.setDataFieldSubTotalsMethod(XlPivotFieldSubtotalsMethodType.SubtotalsMethodTypeAverage);
        pt.setFields(pt.getPivotTableSetting(), XlPivotTableLayoutType.TabularForm,
                     Arrays.asList(rf), empty, empty, Arrays.asList(df));
        pt.refreshData();
        pt.refresh();
        System.out.println("[4] Changed subtotal method from Sum to Average");
        for (int r = 2; r <= 10; r++) {
            String label = ws.getRange("E" + r + ":E" + r).getValue().get("E" + r);
            String val   = ws.getRange("F" + r + ":F" + r).getValue().get("F" + r);
            if (label != null && !label.isEmpty()) {
                System.out.println("    " + label + " = " + val);
            }
        }

        // ---------- 5. Get field information ----------
        List<PivotFieldObjectWrapper> fields = pt.getFields();
        StringBuilder caps = new StringBuilder();
        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) caps.append(", ");
            caps.append(fields.get(i).getCaption());
        }
        System.out.println("[5] getFields: " + fields.size() + " fields → " + caps);
        System.out.println("    getLayoutType = " + pt.getLayoutType().getCode()
            + " (TabularForm=" + XlPivotTableLayoutType.TabularForm.getCode() + ")");

        // ---------- 6. Delete the pivot table ----------
        ws.getPivotTables().removePivotTable("SalesSummary");
        try {
            ws.getPivotTables().getPivotTable("SalesSummary");
            System.out.println("[6] Removal failed");
        } catch (Throwable t) {
            System.out.println("[6] removePivotTable → removal confirmed OK");
        }

        wb.save();
        wb.close();
    }
}

// pivot-sample.wasm.js — pivot tables: same verification as the Node.js sample (wasmosbxl / embind)
// Note (WebAssembly build): load the module first with createOsbxl(createModule).
// osbxl / dto / chart / enums used below are the four namespaces it returns
// (the equivalent of require("nodeosbxl") in the Node.js build).
const app = new osbxl.App();
const wb = app.createWorkBook("pivot-wasm.xlsx");
const ws = wb.openWorkSheet("Sheet1");

// ---------- 1. Source data ----------
// 10 rows of Region / Product / Quantity
const headers = ["Region", "Product", "Quantity"];
const srcData = [
  ["East", "Apple", 10], ["East", "Orange", 20], ["West", "Apple", 30],
  ["West", "Orange", 15], ["East", "Apple", 25], ["North", "Orange", 40],
  ["North", "Apple",  5], ["West", "Apple", 10], ["East", "Orange", 30],
  ["North", "Apple", 20],
];
headers.forEach((h, i) => {
  const col = String.fromCharCode(65 + i);
  ws.getRange(`${col}1:${col}1`).setValue(h);
});
srcData.forEach((row, ri) => {
  row.forEach((v, ci) => {
    const col = String.fromCharCode(65 + ci);
    const r = ri + 2;
    if (typeof v === "number") ws.getRange(`${col}${r}:${col}${r}`).setNumberValue(v);
    else ws.getRange(`${col}${r}:${col}${r}`).setValue(v);
  });
});
const srcTotal = srcData.reduce((s, r) => s + r[2], 0);
console.log(`[1] Source data: ${srcData.length} rows, quantity total = ${srcTotal}`);

// ---------- 2. Create the pivot table (rows=Region, values=Quantity sum) ----------
const pt = ws.getPivotTables().addPivotTable("SalesSummary", "A1:C11", "E1");

const rf = pt.getField("Region");
rf.setFieldType(enums.XlPivotFieldType.PivotFieldTypeRow);
const df = pt.getField("Quantity");
df.setFieldType(enums.XlPivotFieldType.PivotFieldTypeData);
df.setDataFieldSubTotalsMethod(enums.XlPivotFieldSubtotalsMethodType.SubtotalsMethodTypeSum);

pt.setFields(pt.getPivotTableSetting(), enums.XlPivotTableLayoutType.TabularForm,
             [rf], [], [], [df]);
pt.refreshData();
pt.refresh();
console.log("[2] Created PivotTable (rows=Region, values=Quantity sum, layout=tabular form)");

// ---------- 3. Read back the results and cross-check ----------
// Pivot output: E1=header, E2 onward=row labels, F2 onward=values, last row=grand total.
// The grand-total label ("総計") is written by the library itself, not by our data.
const pivotRows = {};
for (let r = 2; r <= 10; r++) {
  const label = ws.getRange(`E${r}:E${r}`).getValue()[`E${r}`];
  const val   = ws.getRange(`F${r}:F${r}`).getValue()[`F${r}`];
  if (label !== undefined && label !== "") {
    pivotRows[label] = Number(val);
    console.log(`    ${label} = ${val}`);
  }
}
const grandTotal = pivotRows["総計"];
console.log(`[3] Check: grand total ${grandTotal} === source total ${srcTotal} → ${grandTotal === srcTotal ? "OK" : "NG"}`);

// ---------- 4. Change the aggregation method to Average ----------
df.setDataFieldSubTotalsMethod(enums.XlPivotFieldSubtotalsMethodType.SubtotalsMethodTypeAverage);
pt.setFields(pt.getPivotTableSetting(), enums.XlPivotTableLayoutType.TabularForm,
             [rf], [], [], [df]);
pt.refreshData();
pt.refresh();
console.log("[4] Changed subtotal method from Sum to Average");
for (let r = 2; r <= 10; r++) {
  const label = ws.getRange(`E${r}:E${r}`).getValue()[`E${r}`];
  const val   = ws.getRange(`F${r}:F${r}`).getValue()[`F${r}`];
  if (label !== undefined && label !== "") {
    console.log(`    ${label} = ${val}`);
  }
}

// ---------- 5. Get field information ----------
const fields = pt.getFields();
console.log(`[5] getFields: ${fields.length} fields → ${fields.map(f => f.getCaption()).join(", ")}`);
console.log(`    getLayoutType = ${pt.getLayoutType()} (TabularForm=${enums.XlPivotTableLayoutType.TabularForm})`);

// ---------- 6. Delete the pivot table ----------
ws.getPivotTables().removePivotTable("SalesSummary");
try {
  ws.getPivotTables().getPivotTable("SalesSummary");
  console.log("[6] Removal failed");
} catch (e) {
  console.log("[6] removePivotTable → removal confirmed OK");
}

wb.save();
wb.close();