UmyaSpreadsheet (umya_spreadsheet_ex v0.7.0)
View SourceUmyaSpreadsheet is an Elixir wrapper for the Rust umya-spreadsheet library,
which provides Excel (.xlsx, .xlsm) file manipulation capabilities with the
performance benefits of Rust.
Overview
This library allows you to:
- Create new spreadsheets from scratch
- Read/write Excel files with full formatting support
- Manipulate cell values, formulas, and styles
- Move and organize ranges of data
- Add and manage multiple worksheets
- Create charts, images, and visual elements
- Embed OLE objects (Word documents, PowerPoint presentations, etc.)
- Add data validation and conditional formatting
- Export to CSV with performance optimizations
- Apply advanced formatting and styling options
Architecture
UmyaSpreadsheet follows a modular architecture with specialized function modules:
Core Architecture
UmyaSpreadsheet (Main Module)
├── UmyaNative (Rust NIF Interface)
│ ├── Spreadsheet Operations (new, read, write)
│ ├── Cell Operations (get/set values, formatting)
│ ├── Sheet Management (add, remove, rename)
│ └── Native Rust Library (umya-spreadsheet)
│
└── Specialized Function Modules:
├── AdvancedFillFunctions - Advanced gradient and pattern fills
├── AutoFilterFunctions - Data filtering and sorting
├── BackgroundFunctions - Cell background colors and patterns
├── BorderFunctions - Cell border styling and formatting
├── CellFunctions - Cell value manipulation and retrieval
├── ChartFunctions - Chart creation and customization
├── CommentFunctions - Cell comment management
├── ConditionalFormatting - Advanced conditional formatting rules
├── CSVFunctions - CSV export and import operations
├── DataValidation - Input validation and dropdown lists
├── Drawing - Shapes, connectors, and drawing objects
├── FileFormatOptions - File format and compression options
├── FontFunctions - Font styling and text formatting
├── FormulaFunctions - Formula creation and named ranges
├── Hyperlink - Hyperlink management and navigation
├── ImageFunctions - Image insertion and positioning
├── OleObjects - Object Linking and Embedding (OLE) objects
├── PerformanceFunctions - Memory-optimized operations
├── PivotTable - Pivot table creation and management
├── RichText - Formatted text within cells with styling
├── Table - Excel table creation and management
├── PrintSettings - Page setup and print configuration
├── ProtectionFunctions - Security and access control
├── RowColumnFunctions - Row and column operations
├── SheetFunctions - Worksheet management and properties
├── StyleFunctions - Cell styling and number formatting
└── WindowFunctions - View settings and window managementData Flow
- NIF Layer: All operations go through the native interface which interfaces with Rust
- Spreadsheet Reference: Operations work on a Rust-managed spreadsheet reference
- Function Modules: Specialized modules provide domain-specific functionality
- Error Handling: Comprehensive error handling with descriptive messages
- Memory Management: Rust handles memory management for performance
Thread Safety
The library is designed for concurrent operations:
- Each spreadsheet maintains its own Rust reference
- Multiple spreadsheets can be operated on simultaneously
- Thread-safe patterns are documented in the guides
Performance Characteristics
- Memory Efficient: Rust memory management with minimal Elixir overhead
- Fast I/O: Native Rust file operations for reading/writing
- Lazy Loading: Optional lazy reading for large files
- Light Writers: Memory-optimized writers for simple operations
- Concurrent Safe: Multiple processes can work with different spreadsheets
Compatibility
- Excel Versions: Full support for Excel 2007+ (.xlsx, .xlsm formats)
- Elixir Versions: Compatible with Elixir 1.12+
- OTP Versions: Compatible with OTP 24+
- Platforms: Linux, macOS, Windows (via precompiled NIFs)
Guides
For detailed guides on specific features, see:
- Charts - Creating and customizing various chart types
- CSV Export & Performance - CSV export and optimized writers
- Image Handling - Working with images in spreadsheets
- Sheet Operations - Managing worksheets effectively
- Styling and Formatting - Making your spreadsheets look professional
See the Guide Index for a complete list of available guides.
Quick Start
# Create a new spreadsheet
{:ok, spreadsheet} = UmyaSpreadsheet.new()
# Set some cell values
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Hello")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B1", "World")
# Apply formatting
UmyaSpreadsheet.set_font_bold(spreadsheet, "Sheet1", "A1", true)
UmyaSpreadsheet.set_background_color(spreadsheet, "Sheet1", "A1", "#FF0000")
# Write to file
UmyaSpreadsheet.write(spreadsheet, "example.xlsx")Error Handling
Most functions return either :ok or {:error, reason} tuples. Common error patterns:
case UmyaSpreadsheet.set_cell_value(spreadsheet, "NonExistent", "A1", "value") do
:ok -> IO.puts("Success!")
{:error, reason} -> IO.puts("Error: #{inspect(reason)}")
endSummary
Functions
Adds a comment to a cell.
Adds formatted text directly to a RichText object.
Adds a hyperlink to a cell.
Adds an OLE object to a collection.
Adds a TextElement to a RichText object.
Creates a defined name in the spreadsheet with an associated formula.
Creates a named range in the spreadsheet.
Creates a new empty RichText object for building formatted text.
Creates a RichText object from an HTML string with formatting.
Creates a TextElement with text and optional font properties.
Gets all hyperlinks from a worksheet.
Gets the range of an auto filter in a worksheet.
Gets the bx property of a formula.
Gets rich text from a specific cell.
Gets the comment text and author from a cell.
Gets the number of comments in a sheet.
Gets the data table 2D property of a formula.
Gets the data table row property of a formula.
Gets all defined names in the spreadsheet.
Gets the ProgID from embedded object properties.
Gets the shape ID from embedded object properties.
Gets the formula text from a cell.
Gets the complete formula object from a cell, returning detailed information.
Gets the shared index of a formula in a cell.
Gets the formula type of a cell.
Gets hyperlink information from a cell.
Gets all hyperlinks from a worksheet (alias for get_all_hyperlinks).
Gets the input 1 deleted property of a formula.
Gets the input 2 deleted property of a formula.
Gets the binary data from an OLE object.
Gets the file extension from an OLE object.
Gets the ProgID from an OLE object.
Gets the properties of an OLE object.
Gets the 'requires' attribute from an OLE object.
Gets the count of OLE objects in a collection.
Gets the OLE objects collection from a worksheet.
Gets the R1 property of a formula.
Gets the R2 property of a formula.
Gets the reference of a formula.
Gets all text elements from a RichText object.
Gets plain text from a RichText object without formatting.
Gets the shared index of a formula.
Gets the text content of a formula in a cell.
Gets font properties from a TextElement as a map.
Gets text content from a TextElement.
Checks if a worksheet has an auto filter.
Checks if a sheet has any comments.
Checks if a specific cell has a hyperlink (alias for has_hyperlink?).
Checks if a specific cell has a hyperlink.
Checks if a worksheet contains any hyperlinks (alias for has_hyperlinks?).
Checks if a worksheet contains any hyperlinks.
Checks if an OLE objects collection has any objects.
Checks if a cell contains a formula.
Checks if an OLE object is in binary format.
Checks if an OLE object is in Excel format.
Reads an Excel (.xlsx, .xlsm) file from the given path using lazy loading. Worksheet contents are only loaded when accessed, which can improve performance for large files.
Lists all OLE objects in a collection.
Loads an OLE object from a file.
Creates a new empty spreadsheet with a default sheet.
Creates new embedded object properties.
Creates a new empty spreadsheet without any default sheets.
Creates a new OLE object.
Creates a new OLE object and loads data from a file.
Creates a new OLE object with binary data.
Creates a new OLE objects collection.
Reads an Excel (.xlsx, .xlsm) file from the given path.
Removes an auto filter from a worksheet.
Removes a comment from a cell.
Removes a hyperlink from a cell.
Converts RichText to HTML representation.
Saves an OLE object to a file.
Sets an array formula for a range of cells. Array formulas can return multiple values across a range of cells.
Sets an auto filter for a range of cells in a worksheet.
Sets rich text to a specific cell.
Sets the ProgID for embedded object properties.
Sets the shape ID for embedded object properties.
Sets a regular formula in a cell.
Sets the binary data for an OLE object.
Sets the file extension for an OLE object.
Sets the ProgID for an OLE object.
Sets the properties for an OLE object.
Sets the 'requires' attribute for an OLE object.
Sets the OLE objects collection for a worksheet.
Converts a spreadsheet to binary XLSX format without writing to disk.
Unwraps the reference from a Spreadsheet struct. This is an internal function used by other modules.
Updates an existing comment in a cell.
Updates an existing hyperlink in a cell.
Writes a spreadsheet to the specified path.
Writes a spreadsheet to disk with a specified compression level.
Writes a spreadsheet to disk with enhanced encryption options.
Writes a spreadsheet to the specified path with password protection.
Functions
See UmyaSpreadsheet.ConditionalFormatting.add_above_below_average_rule/6.
See UmyaSpreadsheet.ConditionalFormatting.add_cell_is_rule/7.
See UmyaSpreadsheet.ConditionalFormatting.add_cell_value_rule/7.
See UmyaSpreadsheet.ChartFunctions.add_chart_with_options/15.
See UmyaSpreadsheet.ChartFunctions.add_chart_with_options/16.
See UmyaSpreadsheet.ConditionalFormatting.add_color_scale/9.
See UmyaSpreadsheet.ConditionalFormatting.add_color_scale/12.
Adds a comment to a cell.
Adds formatted text directly to a RichText object.
Examples
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> UmyaSpreadsheet.add_formatted_text_to_rich_text(rich_text, "Bold text", %{bold: true})
:okAdds a hyperlink to a cell.
See UmyaSpreadsheet.DataValidation.add_number_validation/11.
Adds an OLE object to a collection.
Examples
iex> {:ok, ole_objects} = UmyaSpreadsheet.new_ole_objects()
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> UmyaSpreadsheet.add_ole_object(ole_objects, ole_object)
:okAdds a TextElement to a RichText object.
Examples
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> element = UmyaSpreadsheet.create_text_element("Test", %{bold: true})
iex> UmyaSpreadsheet.add_text_element_to_rich_text(rich_text, element)
:okSee UmyaSpreadsheet.DataValidation.add_text_length_validation/5.
See UmyaSpreadsheet.DataValidation.add_text_length_validation/6.
See UmyaSpreadsheet.DataValidation.add_text_length_validation/7.
See UmyaSpreadsheet.DataValidation.add_text_length_validation/11.
See UmyaSpreadsheet.ConditionalFormatting.add_top_bottom_rule/7.
Creates a defined name in the spreadsheet with an associated formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.create_defined_name(spreadsheet, "TaxRate", "0.15")
:ok
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.create_defined_name(spreadsheet, "Department", "Sales", "Sheet1")
:okCreates a named range in the spreadsheet.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.create_named_range(spreadsheet, "MyRange", "Sheet1", "A1:B10")
:okCreates a new empty RichText object for building formatted text.
Examples
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> is_reference(rich_text)
trueCreates a RichText object from an HTML string with formatting.
Examples
iex> html = "<b>Bold</b> and <i>italic</i> text"
iex> rich_text = UmyaSpreadsheet.create_rich_text_from_html(html)
iex> is_reference(rich_text)
trueCreates a TextElement with text and optional font properties.
Examples
iex> element = UmyaSpreadsheet.create_text_element("Bold text", %{bold: true, color: "#FF0000"})
iex> is_reference(element)
trueGets all hyperlinks from a worksheet.
Gets the range of an auto filter in a worksheet.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_auto_filter(spreadsheet, "Sheet1", "A1:E10")
iex> UmyaSpreadsheet.get_auto_filter_range(spreadsheet, "Sheet1")
{:ok, "A1:E10"}Gets the bx property of a formula.
The bx property indicates whether the formula calculation should be done on exit from the cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_bx(spreadsheet, "Sheet1", "A1")
nilSee UmyaSpreadsheet.BackgroundFunctions.get_cell_background_color/3.
See UmyaSpreadsheet.BackgroundFunctions.get_cell_foreground_color/3.
See UmyaSpreadsheet.CellFunctions.get_cell_horizontal_alignment/3.
See UmyaSpreadsheet.CellFunctions.get_cell_number_format_id/3.
See UmyaSpreadsheet.BackgroundFunctions.get_cell_pattern_type/3.
Gets rich text from a specific cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> rich_text = UmyaSpreadsheet.get_cell_rich_text(spreadsheet, "Sheet1", "A1")
iex> is_reference(rich_text)
trueSee UmyaSpreadsheet.CellFunctions.get_cell_vertical_alignment/3.
See UmyaSpreadsheet.ConditionalFormatting.get_color_scales/3.
See UmyaSpreadsheet.RowColumnFunctions.get_column_auto_width/3.
Gets the comment text and author from a cell.
Gets the number of comments in a sheet.
Gets the data table 2D property of a formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_data_table_2d(spreadsheet, "Sheet1", "A1")
nilGets the data table row property of a formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_data_table_row(spreadsheet, "Sheet1", "A1")
nilGets all defined names in the spreadsheet.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.create_named_range(spreadsheet, "MyRange", "Sheet1", "A1:B10")
iex> UmyaSpreadsheet.create_defined_name(spreadsheet, "TaxRate", "0.15")
iex> defined_names = UmyaSpreadsheet.get_defined_names(spreadsheet)
iex> is_list(defined_names)
trueGets the ProgID from embedded object properties.
Examples
iex> {:ok, properties} = UmyaSpreadsheet.new_embedded_object_properties()
iex> :ok = UmyaSpreadsheet.set_embedded_object_prog_id(properties, "Word.Document.12")
iex> {:ok, "Word.Document.12"} = UmyaSpreadsheet.get_embedded_object_prog_id(properties)Gets the shape ID from embedded object properties.
Examples
iex> {:ok, properties} = UmyaSpreadsheet.new_embedded_object_properties()
iex> :ok = UmyaSpreadsheet.set_embedded_object_shape_id(properties, 123)
iex> {:ok, 123} = UmyaSpreadsheet.get_embedded_object_shape_id(properties)Gets the formula text from a cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_formula(spreadsheet, "Sheet1", "A1")
"=SUM(B1:B10)"Gets the complete formula object from a cell, returning detailed information.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> {text, type, _shared_index, _reference} = UmyaSpreadsheet.get_formula_obj(spreadsheet, "Sheet1", "A1")
iex> text
"=SUM(B1:B10)"
iex> type
"Normal"Gets the formula type of a cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_formula_type(spreadsheet, "Sheet1", "A1")
"Normal"See UmyaSpreadsheet.AdvancedFillFunctions.get_gradient_fill/3.
Gets hyperlink information from a cell.
Gets all hyperlinks from a worksheet (alias for get_all_hyperlinks).
Gets the input 1 deleted property of a formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_input_1deleted(spreadsheet, "Sheet1", "A1")
nilGets the input 2 deleted property of a formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_input_2deleted(spreadsheet, "Sheet1", "A1")
nilGets the binary data from an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:ok, _data} = UmyaSpreadsheet.get_ole_object_data(ole_object)Gets the file extension from an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> :ok = UmyaSpreadsheet.set_ole_object_extension(ole_object, "xlsx")
iex> {:ok, "xlsx"} = UmyaSpreadsheet.get_ole_object_extension(ole_object)Gets the ProgID from an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> :ok = UmyaSpreadsheet.set_ole_object_prog_id(ole_object, "Excel.Sheet.12")
iex> {:ok, "Excel.Sheet.12"} = UmyaSpreadsheet.get_ole_object_prog_id(ole_object)Gets the properties of an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:ok, _properties} = UmyaSpreadsheet.get_ole_object_properties(ole_object)Gets the 'requires' attribute from an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> :ok = UmyaSpreadsheet.set_ole_object_requires(ole_object, "xl")
iex> {:ok, "xl"} = UmyaSpreadsheet.get_ole_object_requires(ole_object)Gets the count of OLE objects in a collection.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> {:ok, ole_objects} = UmyaSpreadsheet.get_ole_objects_from_worksheet(spreadsheet, "Sheet1")
iex> {:ok, _count} = UmyaSpreadsheet.get_ole_objects_count(ole_objects)Gets the OLE objects collection from a worksheet.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> {:ok, _ole_objects} = UmyaSpreadsheet.get_ole_objects_from_worksheet(spreadsheet, "Sheet1")See UmyaSpreadsheet.AdvancedFillFunctions.get_pattern_fill/3.
See UmyaSpreadsheet.PivotTable.get_pivot_table_source_range/3.
See UmyaSpreadsheet.PivotTable.get_pivot_table_target_cell/3.
Gets the R1 property of a formula.
The R1 property is used in data table formulas to specify the first input cell reference.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_r1(spreadsheet, "Sheet1", "A1")
nilGets the R2 property of a formula.
The R2 property is used in data table formulas to specify the second input cell reference.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_r2(spreadsheet, "Sheet1", "A1")
nilGets the reference of a formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_array_formula(spreadsheet, "Sheet1", "A1:A3", "ROW(1:3)")
iex> UmyaSpreadsheet.get_reference(spreadsheet, "Sheet1", "A1")
nilGets all text elements from a RichText object.
Examples
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> UmyaSpreadsheet.add_formatted_text_to_rich_text(rich_text, "Test", %{bold: true})
iex> elements = UmyaSpreadsheet.get_rich_text_elements(rich_text)
iex> is_list(elements)
trueGets plain text from a RichText object without formatting.
Examples
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> UmyaSpreadsheet.add_formatted_text_to_rich_text(rich_text, "Test", %{bold: true})
iex> UmyaSpreadsheet.get_rich_text_plain_text(rich_text)
"Test"See UmyaSpreadsheet.SheetViewFunctions.get_show_grid_lines/2.
Gets the text content of a formula in a cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.get_text(spreadsheet, "Sheet1", "A1")
"=SUM(B1:B10)"Gets font properties from a TextElement as a map.
Examples
iex> element = UmyaSpreadsheet.create_text_element("Test", %{bold: true})
iex> {:ok, props} = UmyaSpreadsheet.get_text_element_font_properties(element)
iex> props[:bold]
"true"Gets text content from a TextElement.
Examples
iex> element = UmyaSpreadsheet.create_text_element("Test", %{})
iex> UmyaSpreadsheet.get_text_element_text(element)
"Test"See UmyaSpreadsheet.WorkbookProtectionFunctions.get_workbook_protection_details/1.
See UmyaSpreadsheet.WorkbookViewFunctions.get_workbook_window_position/1.
Checks if a worksheet has an auto filter.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_auto_filter(spreadsheet, "Sheet1", "A1:E10")
iex> UmyaSpreadsheet.has_auto_filter(spreadsheet, "Sheet1")
{:ok, true}Checks if a sheet has any comments.
Checks if a specific cell has a hyperlink (alias for has_hyperlink?).
Checks if a specific cell has a hyperlink.
Checks if a worksheet contains any hyperlinks (alias for has_hyperlinks?).
Checks if a worksheet contains any hyperlinks.
Checks if an OLE objects collection has any objects.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> {:ok, ole_objects} = UmyaSpreadsheet.get_ole_objects_from_worksheet(spreadsheet, "Sheet1")
iex> {:ok, _has_objects} = UmyaSpreadsheet.has_ole_objects(ole_objects)See UmyaSpreadsheet.SheetFunctions.insert_new_column_by_index/4.
Checks if a cell contains a formula.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "=SUM(B1:B10)")
iex> UmyaSpreadsheet.is_formula(spreadsheet, "Sheet1", "A1")
true
iex> UmyaSpreadsheet.is_formula(spreadsheet, "Sheet1", "B1")
falseChecks if an OLE object is in binary format.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:ok, _is_binary} = UmyaSpreadsheet.is_ole_object_binary_format(ole_object)Checks if an OLE object is in Excel format.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:ok, _is_excel} = UmyaSpreadsheet.is_ole_object_excel_format(ole_object)See UmyaSpreadsheet.WorkbookProtectionFunctions.is_workbook_protected/1.
Reads an Excel (.xlsx, .xlsm) file from the given path using lazy loading. Worksheet contents are only loaded when accessed, which can improve performance for large files.
Parameters
path- Path to the Excel file
Examples
iex> result = UmyaSpreadsheet.lazy_read("path/to/file.xlsx")
iex> is_tuple(result)
trueLists all OLE objects in a collection.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> {:ok, ole_objects} = UmyaSpreadsheet.get_ole_objects_from_worksheet(spreadsheet, "Sheet1")
iex> {:ok, _objects_list} = UmyaSpreadsheet.list_ole_objects(ole_objects)Loads an OLE object from a file.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:error, "File not found"} = UmyaSpreadsheet.load_ole_object_from_file(ole_object, "nonexistent.docx")Creates a new empty spreadsheet with a default sheet.
Examples
iex> spreadsheet = UmyaSpreadsheet.new()
iex> match?({:ok, %UmyaSpreadsheet.Spreadsheet{}}, spreadsheet)
trueCreates new embedded object properties.
Examples
iex> {:ok, _properties} = UmyaSpreadsheet.new_embedded_object_properties()Creates a new empty spreadsheet without any default sheets.
Examples
iex> spreadsheet = UmyaSpreadsheet.new_empty()
iex> match?({:ok, %UmyaSpreadsheet.Spreadsheet{}}, spreadsheet)
trueCreates a new OLE object.
Examples
iex> {:ok, _ole_object} = UmyaSpreadsheet.new_ole_object()Creates a new OLE object and loads data from a file.
Examples
iex> {:ok, _ole_object} = UmyaSpreadsheet.new_ole_object()Creates a new OLE object with binary data.
Examples
iex> _data = "test data"
iex> {:ok, _ole_object} = UmyaSpreadsheet.new_ole_object()Creates a new OLE objects collection.
Examples
iex> {:ok, _ole_objects} = UmyaSpreadsheet.new_ole_objects()Reads an Excel (.xlsx, .xlsm) file from the given path.
Parameters
path- Path to the Excel file
Examples
iex> result = UmyaSpreadsheet.read("path/to/file.xlsx")
iex> is_tuple(result)
trueRemoves an auto filter from a worksheet.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_auto_filter(spreadsheet, "Sheet1", "A1:E10")
iex> UmyaSpreadsheet.remove_auto_filter(spreadsheet, "Sheet1")
:okSee UmyaSpreadsheet.SheetFunctions.remove_column_by_index/4.
Removes a comment from a cell.
See UmyaSpreadsheet.DataValidation.remove_data_validation/3.
Removes a hyperlink from a cell.
Converts RichText to HTML representation.
Examples
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> UmyaSpreadsheet.add_formatted_text_to_rich_text(rich_text, "Bold", %{bold: true})
iex> UmyaSpreadsheet.rich_text_to_html(rich_text)
"<b>Bold</b>"Saves an OLE object to a file.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:error, "No object data to save"} = UmyaSpreadsheet.save_ole_object_to_file(ole_object, "output.docx")Sets an array formula for a range of cells. Array formulas can return multiple values across a range of cells.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_array_formula(spreadsheet, "Sheet1", "A1:A3", "ROW(1:3)")
:okSets an auto filter for a range of cells in a worksheet.
Auto filters allow users to filter data in Excel by adding dropdown menus to column headers.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_auto_filter(spreadsheet, "Sheet1", "A1:E10")
:okSee UmyaSpreadsheet.BackgroundFunctions.set_background_color/4.
Sets rich text to a specific cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> rich_text = UmyaSpreadsheet.create_rich_text()
iex> UmyaSpreadsheet.add_formatted_text_to_rich_text(rich_text, "Test", %{bold: true})
iex> UmyaSpreadsheet.set_cell_rich_text(spreadsheet, "Sheet1", "A1", rich_text)
:okSee UmyaSpreadsheet.ChartFunctions.set_chart_legend_position/5.
See UmyaSpreadsheet.RowColumnFunctions.set_column_auto_width/4.
See UmyaSpreadsheet.AdvancedFillFunctions.set_custom_gradient_fill/5.
See UmyaSpreadsheet.AdvancedFillFunctions.set_custom_gradient_fill/6.
Sets the ProgID for embedded object properties.
Examples
iex> {:ok, properties} = UmyaSpreadsheet.new_embedded_object_properties()
iex> UmyaSpreadsheet.set_embedded_object_prog_id(properties, "Word.Document.12")
:okSets the shape ID for embedded object properties.
Examples
iex> {:ok, properties} = UmyaSpreadsheet.new_embedded_object_properties()
iex> UmyaSpreadsheet.set_embedded_object_shape_id(properties, 123)
:okSets a regular formula in a cell.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> UmyaSpreadsheet.set_formula(spreadsheet, "Sheet1", "A1", "SUM(B1:B10)")
:okSee UmyaSpreadsheet.AdvancedFillFunctions.set_gradient_fill/5.
See UmyaSpreadsheet.AdvancedFillFunctions.set_linear_gradient_fill/5.
See UmyaSpreadsheet.AdvancedFillFunctions.set_linear_gradient_fill/6.
Sets the binary data for an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> :ok = UmyaSpreadsheet.set_ole_object_data(ole_object, "test data")Sets the file extension for an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> UmyaSpreadsheet.set_ole_object_extension(ole_object, "xlsx")
:okSets the ProgID for an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> UmyaSpreadsheet.set_ole_object_prog_id(ole_object, "Excel.Sheet.12")
:okSets the properties for an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> {:ok, properties} = UmyaSpreadsheet.new_embedded_object_properties()
iex> UmyaSpreadsheet.set_ole_object_properties(ole_object, properties)
:okSets the 'requires' attribute for an OLE object.
Examples
iex> {:ok, ole_object} = UmyaSpreadsheet.new_ole_object()
iex> UmyaSpreadsheet.set_ole_object_requires(ole_object, "xl")
:okSets the OLE objects collection for a worksheet.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> {:ok, ole_objects} = UmyaSpreadsheet.new_ole_objects()
iex> UmyaSpreadsheet.set_ole_objects_to_worksheet(spreadsheet, "Sheet1", ole_objects)
:okSee UmyaSpreadsheet.AdvancedFillFunctions.set_pattern_fill/5.
See UmyaSpreadsheet.AdvancedFillFunctions.set_pattern_fill/6.
See UmyaSpreadsheet.AdvancedFillFunctions.set_radial_gradient_fill/5.
See UmyaSpreadsheet.SheetViewFunctions.set_show_grid_lines/3.
See UmyaSpreadsheet.AdvancedFillFunctions.set_three_color_gradient_fill/6.
See UmyaSpreadsheet.AdvancedFillFunctions.set_three_color_gradient_fill/7.
See UmyaSpreadsheet.WorkbookFunctions.set_workbook_protection/2.
See UmyaSpreadsheet.WorkbookViewFunctions.set_workbook_window_position/5.
See UmyaSpreadsheet.SheetViewFunctions.set_zoom_scale_normal/3.
See UmyaSpreadsheet.SheetViewFunctions.set_zoom_scale_page_break/3.
See UmyaSpreadsheet.SheetViewFunctions.set_zoom_scale_page_layout/3.
Converts a spreadsheet to binary XLSX format without writing to disk.
This is useful for serving Excel files directly in web applications.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> binary = UmyaSpreadsheet.to_binary_xlsx(spreadsheet)
iex> is_binary(binary)
true@spec unwrap_ref(UmyaSpreadsheet.Spreadsheet.t() | reference()) :: reference()
Unwraps the reference from a Spreadsheet struct. This is an internal function used by other modules.
Updates an existing comment in a cell.
Updates an existing hyperlink in a cell.
Writes a spreadsheet to the specified path.
Parameters
spreadsheet- A spreadsheet structpath- The path where to save the Excel file
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> result = UmyaSpreadsheet.write(spreadsheet, "path/to/output.xlsx")
iex> is_atom(result) or is_tuple(result)
trueWrites a spreadsheet to disk with a specified compression level.
Compression levels range from 0 (no compression) to 9 (maximum compression). Higher compression levels result in smaller files but take longer to create.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> unique_id = :crypto.strong_rand_bytes(4) |> Base.encode16() |> String.downcase()
iex> file_path = "test/result_files/high_compression_" <> unique_id <> ".xlsx"
iex> File.mkdir_p!(Path.dirname(file_path))
iex> if File.exists?(file_path), do: File.rm!(file_path)
iex> UmyaSpreadsheet.write_with_compression(spreadsheet, file_path, 9)
:ok
iex> File.exists?(file_path)
true
iex> File.rm!(file_path)
:okWrites a spreadsheet to disk with enhanced encryption options.
This function provides more control over the encryption process than the standard
write_with_password function, allowing you to specify encryption algorithm,
salt values, and spin counts for enhanced security.
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> unique_id = :crypto.strong_rand_bytes(4) |> Base.encode16() |> String.downcase()
iex> file_path = "test/result_files/secure_options_" <> unique_id <> ".xlsx"
iex> File.mkdir_p!(Path.dirname(file_path))
iex> if File.exists?(file_path), do: File.rm!(file_path)
iex> UmyaSpreadsheet.write_with_encryption_options(spreadsheet, file_path, "secret", "AES256")
:ok
iex> File.exists?(file_path)
true
iex> File.rm!(file_path)
:okWrites a spreadsheet to the specified path with password protection.
Parameters
spreadsheet- A spreadsheet structpath- The path where to save the Excel filepassword- Password for the Excel file
Examples
iex> {:ok, spreadsheet} = UmyaSpreadsheet.new()
iex> unique_id = :crypto.strong_rand_bytes(4) |> Base.encode16() |> String.downcase()
iex> file_path = "test/result_files/secure_password_" <> unique_id <> ".xlsx"
iex> File.mkdir_p!(Path.dirname(file_path))
iex> if File.exists?(file_path), do: File.rm!(file_path)
iex> UmyaSpreadsheet.write_with_password(spreadsheet, file_path, "password123")
:ok
iex> File.exists?(file_path)
true
iex> File.rm!(file_path)
:okSee UmyaSpreadsheet.PerformanceFunctions.write_with_password_light/3.