@spec add_pivot_table(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t(),
  String.t(),
  String.t(),
  String.t(),
  [integer()],
  [integer()],
  [{integer(), String.t(), String.t()}]
) :: :ok | {:error, atom()}

Adds a new pivot table to a spreadsheet.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet where the pivot table will be placed
• name - Name of the pivot table
• source_sheet - Name of the sheet containing the source data
• source_range - Range of cells containing the source data in A1 notation (e.g., "A1:D10")
• target_cell - Top-left cell for the pivot table placement
• row_fields - List of field indices (0-based) to use as row fields
• column_fields - List of field indices (0-based) to use as column fields
• data_fields - List of data field configs in the format [{field_index, "Function", "Custom Name"}] where function is one of "sum", "count", "average", "max", "min", "product", "count_nums", "stddev", "stddevp", "var", "varp"

Examples

# Create a simple pivot table from data in sheet "Data"
PivotTable.add_pivot_table(
  spreadsheet,
  "PivotSheet",
  "Sales Analysis",
  "Data",
  "A1:D100",
  "A3",
  [0], # Use first column (Region) as row field
  [1], # Use second column (Product) as column field
  [{2, "sum", "Total Sales"}] # Sum the third column (Sales) as data field
)

@spec add_pivot_table_data_field(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t(),
  String.t(),
  non_neg_integer(),
  integer() | nil,
  non_neg_integer() | nil
) :: :ok | {:error, atom()}

Adds a new data field to an existing pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table
• field_name - Name for the new data field
• field_id - Index of the source field (0-based)
• base_field_id - Optional base field ID for calculated fields
• base_item - Optional base item for calculated fields

Returns

• :ok - Field was added successfully
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Add a new data field for "Sum of Sales"
:ok = PivotTable.add_pivot_table_data_field(
  spreadsheet,
  "PivotSheet",
  "Sales Analysis",
  "Sum of Sales",
  2,  # Field index 2 (Sales column)
  nil,
  nil
)

@spec count_pivot_tables(UmyaSpreadsheet.Spreadsheet.t(), String.t()) :: integer()

Gets the number of pivot tables in a sheet.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet to check

Examples

# Get number of pivot tables
count = PivotTable.count_pivot_tables(spreadsheet, "Sheet1")

@spec get_pivot_table_cache_field(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t(),
  non_neg_integer()
) :: {:ok, {String.t(), non_neg_integer(), [String.t()]}} | {:error, atom()}

Gets detailed information about a specific cache field in a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table
• field_index - Index of the field to get (0-based)

Returns

• {:ok, {name, format_id, shared_items}} - Details for the requested field
  • name - Field name
  • format_id - Number format ID for the field
  • shared_items - List of unique values in this field
• {:error, atom()} - Error if sheet, pivot table, or field doesn't exist

Examples

# Get details for the first cache field
case PivotTable.get_pivot_table_cache_field(spreadsheet, "Sheet1", "Sales Analysis", 0) do
  {:ok, {name, _, items}} ->
    IO.puts("Field: " <> name)
    IO.puts("Unique values: " <> inspect(items))
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_cache_fields(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t()
) ::
  {:ok, [{String.t(), non_neg_integer(), boolean()}]} | {:error, atom()}

Gets all cache fields from a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, fields} - List of cache fields with their details
  • Each field is a tuple: {name, format_id, has_shared_items}
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get all cache fields for a pivot table
case PivotTable.get_pivot_table_cache_fields(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, fields} ->
    fields |> Enum.each(fn {name, _, has_items} ->
      IO.puts("Field: " <> name <> (if has_items, do: " (has items)", else: ""))
    end)
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_cache_source(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t()
) ::
  {:ok, {String.t(), {String.t(), String.t()} | nil}} | {:error, atom()}

Gets the cache source configuration for a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, {source_type, worksheet_source}} - Cache source details
  • source_type - String describing the source type (worksheet, external, etc.)
  • worksheet_source - Optional tuple of {sheet_name, range} or nil if not a worksheet source
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get the cache source for a pivot table
case PivotTable.get_pivot_table_cache_source(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, {"worksheet", {sheet, range}}} ->
    IO.puts("Source data: " <> sheet <> "!" <> range)
  {:ok, {source_type, _}} ->
    IO.puts("Non-worksheet source: " <> source_type)
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_data_fields(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t()
) ::
  {:ok, [{String.t(), non_neg_integer(), integer(), non_neg_integer()}]}
  | {:error, atom()}

Gets all data fields from a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, fields} - List of data fields with their details
  • Each field is a tuple: {name, field_id, base_field_id, base_item}
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get all data fields for a pivot table
case PivotTable.get_pivot_table_data_fields(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, fields} ->
    fields |> Enum.each(fn {name, field_id, _, _} ->
      IO.puts("Data field: " <> name <> " (field: " <> to_string(field_id) <> ")")
    end)
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_fields(UmyaSpreadsheet.Spreadsheet.t(), String.t(), String.t()) ::
  {:ok, {[integer()], [integer()], [{integer(), String.t(), String.t()}]}}
  | {:error, atom()}

Gets the field configuration for a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, {row_fields, column_fields, data_fields}} - Tuple containing field configurations
  • row_fields - List of field indices used as row fields
  • column_fields - List of field indices used as column fields
  • data_fields - List of data field configs in format [{field_index, function, custom_name}]
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get field configuration for a pivot table
case PivotTable.get_pivot_table_fields(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, {row_fields, column_fields, data_fields}} ->
    IO.puts("Row fields: " <> inspect(row_fields))
    IO.puts("Column fields: " <> inspect(column_fields))
    IO.puts("Data fields: " <> inspect(data_fields))
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_info(UmyaSpreadsheet.Spreadsheet.t(), String.t(), String.t()) ::
  {:ok, {String.t(), String.t(), String.t(), String.t()}} | {:error, atom()}

Gets detailed information about a specific pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, {name, location, source_range, cache_id}} - Tuple containing pivot table details
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get detailed info about a pivot table
case PivotTable.get_pivot_table_info(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, {name, location, source_range, cache_id}} ->
    IO.puts("Name: " <> name <> ", Location: " <> location)
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_names(UmyaSpreadsheet.Spreadsheet.t(), String.t()) ::
  {:ok, [String.t()]} | {:error, atom()}

Gets a list of all pivot table names in a sheet.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet to check

Returns

• {:ok, [String.t()]} - List of pivot table names
• {:error, atom()} - Error if sheet doesn't exist

Examples

# Get all pivot table names in a sheet
case PivotTable.get_pivot_table_names(spreadsheet, "Sheet1") do
  {:ok, names} -> IO.inspect(names)
  {:error, reason} -> IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_source_range(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t()
) ::
  {:ok, {String.t(), String.t()}} | {:error, atom()}

Gets the source data range for a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, {source_sheet, source_range}} - Tuple containing source sheet name and range
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get source range for a pivot table
case PivotTable.get_pivot_table_source_range(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, {source_sheet, source_range}} ->
    IO.puts("Source: " <> source_sheet <> "!" <> source_range)
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec get_pivot_table_target_cell(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t()
) ::
  {:ok, String.t()} | {:error, atom()}

Gets the target cell (top-left placement) for a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table

Returns

• {:ok, String.t()} - Target cell reference (e.g., "A3")
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Get target cell for a pivot table
case PivotTable.get_pivot_table_target_cell(spreadsheet, "Sheet1", "Sales Analysis") do
  {:ok, target_cell} ->
    IO.puts("Pivot table starts at: " <> target_cell)
  {:error, reason} ->
    IO.puts("Error: " <> inspect(reason))
end

@spec has_pivot_tables?(UmyaSpreadsheet.Spreadsheet.t(), String.t()) :: boolean()

Checks if a sheet contains any pivot tables.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet to check

Examples

# Check if a sheet has pivot tables
if PivotTable.has_pivot_tables?(spreadsheet, "Sheet1") do
  # Handle sheet with pivot tables
end

@spec refresh_all_pivot_tables(UmyaSpreadsheet.Spreadsheet.t()) ::
  :ok | {:error, atom()}

Refreshes all pivot tables in a spreadsheet.

Parameters

• spreadsheet - A spreadsheet struct

Examples

# Refresh all pivot tables
PivotTable.refresh_all_pivot_tables(spreadsheet)

@spec remove_pivot_table(UmyaSpreadsheet.Spreadsheet.t(), String.t(), String.t()) ::
  :ok | {:error, atom()}

Removes a pivot table from a sheet by name.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table to remove

Examples

# Remove a specific pivot table
PivotTable.remove_pivot_table(spreadsheet, "Sheet1", "Sales Analysis")

@spec update_pivot_table_cache(
  UmyaSpreadsheet.Spreadsheet.t(),
  String.t(),
  String.t(),
  String.t(),
  String.t()
) :: :ok | {:error, atom()}

Updates the cache source configuration for a pivot table.

Parameters

• spreadsheet - A spreadsheet struct
• sheet_name - Name of the sheet containing the pivot table
• pivot_table_name - Name of the pivot table
• source_sheet - Name of the sheet containing the source data
• source_range - Range of cells containing the source data (e.g., "A1:D10")

Returns

• :ok - Cache was updated successfully
• {:error, atom()} - Error if sheet or pivot table doesn't exist

Examples

# Update the source range for a pivot table
:ok = PivotTable.update_pivot_table_cache(
  spreadsheet,
  "PivotSheet",
  "Sales Analysis",
  "DataSheet",
  "A1:D20"  # Extended range
)