DOCSEARCHbyAlgolia
Submit Docs

caxlsx

repository·master·Indexed 20 days ago

https://github.com/caxlsx/caxlsx

A Ruby library for generating complex Excel (XLSX) files. It supports advanced features including charts (Bar, Line, Pie, Scatter), conditional formatting, pivot tables, rich text styling, and comprehensive print configuration. The library provides tools for cell merging, data type detection, formula creation, and support for Asian languages.

Tokens
40.4K
Snippets
117
Records
131
Agent score
70%

What's inside caxlsx

  1. Configure Excel print settings

    master

    The library supports comprehensive print configuration to control how spreadsheets appear when printed:

    • Fit to Page: Adjust settings to ensure content fits within page boundaries.
    • Print Area: Define specific ranges to be included in the print job.
    • Headers & Footers: Customize information displayed at the top and bottom of pages.
    • Page Breaks: Manually insert page breaks.
    • Repeated Headers: Configure rows or columns to repeat on every printed page.
    • General Settings: Access broader print configuration options.

  2. Explore caxlsx features and recipes

    master

    The examples directory provides categorized code samples for various Excel features. Use these as templates for your own implementation:

    Core Spreadsheet Features

    • Basic: Custom styles, style overrides, Asian language support, shared strings, and streaming serialization.
    • Cells: Merging cells, automatic data type detection, and manual data type overrides.
    • Rows & Columns: Custom row/column styles, row heights, column widths, outlines, and autowidth tuning.
    • Sheet Management: Setting active tabs, hiding gridlines/sheets, sheet protection, and tab colors.

    Styling & Formatting

    • Text Decorations: Font customization, format codes, number/currency formats, rich text, and text wrapping.
    • Borders: Custom and surrounding cell borders.
    • Conditional Formatting: Rules for greater than, between, color scales, data bars, icon sets, and text equality.

    Advanced Data & Analysis

    • Formulas: Basic formulas, cached formulas, escaping formulas, and defined names.
    • Filters: Auto filters and filtered tables.
    • Validations: List, range, and text length validations.
    • Pivot Tables: Creating and configuring pivot tables.
    • Comments: Adding comments to cells.

    Visuals & Media

    • Charts: Bar, Line (2D/3D), Pie (2D/3D), Scatter, and Stacked Bar charts. Includes customizations for axis labels, tick marks, colors, gridlines, and series colors.
    • Media: Inserting images (locked or hyperlinked) and standard hyperlinks.

  3. Run the caxlsx examples suite

    master

    The examples directory contains a suite of demonstration scripts. You can run all examples at once or target specific files using the generate.rb script.

    To run all examples:

    1. Change directory to examples.
    2. Execute ruby generate.rb or ./generate.rb.

    To run specific examples: Execute ruby generate.rb followed by the filenames (e.g., file1.md file2.md).

    # Run all examples
cd examples
ruby generate.rb

# Run specific examples
ruby generate.rb basic_example.md custom_styles_example.md

  4. Format cell values using `format_code` and `num_fmt`

    master

    To control how numbers, dates, and currencies are displayed in a cell, use either format_code or num_fmt.

    Using format_code (Recommended)

    It is generally preferable to write out the format_code string manually for maximum control.

    • Currency Example: To show a dollar sign, thousands separators, and two decimal places, use: format_code: '\$#,##0.00'
    • Date/Time Example: To show a formatted date/time, use: format_code: 'm/d/yyyy h:mm:ss AM/PM'

    Using num_fmt

    num_fmt uses integer IDs to map to predefined format strings. While convenient, manual format_code is often more flexible.

  5. Prevent Formula Injection by escaping formulas

    master

    To prevent Formula Injection vulnerabilities, you can escape formulas at multiple levels of granularity within caxlsx. Escaping ensures that strings starting with characters like = are treated as text rather than executable formulas.

    You can set the escape_formulas property at the following scopes:

    ScopeImplementation ExampleNotes
    GlobalAxlsx.escape_formulas = trueAffects worksheets created after setting. Does not affect existing worksheets.
    Workbookworkbook.escape_formulas = trueAffects child worksheets added after setting. Does not affect existing child worksheets.
    Worksheetworkbook.add_worksheet(name: 'Name', escape_formulas: true)Sets escaping for the specific worksheet.
    Worksheetworksheet.escape_formulas = trueAffects child rows/cells added after setting. Does not affect existing child rows/cells.
    Rowworksheet.add_row([...], escape_formulas: [true, false])Can be a Boolean (applies to all cells in row) or an Array (one value per cell).
    Rowrow.escape_formulas = [true, false]Changes the escape_formulas value on existing cells. Can be a Boolean or an Array.
    Cellcell.escape_formulas = trueSets escaping for a specific cell.
    require 'axlsx'

# Global setting
Axlsx.escape_formulas = true

p = Axlsx::Package.new
bw = p.workbook

bw.add_worksheet(name: 'Escaping Formulas') do |sheet|
  # Apply to all cells in the row
  sheet.add_row [1, 2, 3, '=SUM(A2:C2)'], escape_formulas: true
  
  # Apply per-cell using an array
  sheet.add_row [
    '=IF(2+2=4,4,5)',
    '=IF(13+13=4,4,5)',
    '=IF(99+99=4,4,5)'
  ], escape_formulas: [true, false, true]

  # Modify an existing cell
  sheet.rows.first.cells.first.escape_formulas = false
end

p.serialize 'escape_formula_example.xlsx'

  6. Enable shared strings for Apple Numbers compatibility

    master

    When generating XLSX files intended to be opened with Apple Numbers, you must enable shared strings to ensure compatibility. The most effective way to do this is to set use_shared_strings = true on the Axlsx::Package instance immediately before calling serialize.

    require 'axlsx'

p = Axlsx::Package.new
wb = p.workbook

wb.add_worksheet(name: 'Basic Worksheet') do |sheet|
  sheet.add_row ['First', 'Second', 'Third']
  sheet.add_row [1, 2, 3]
end

# Enable shared strings for Apple Numbers compatibility
p.use_shared_strings = true
p.serialize 'shared_strings_example.xlsx'

  7. Configure a Pic object

    master

    A Pic object represents an image in your worksheet. When creating or configuring a picture, you can use the following options:

    • image_src: The path to the image file (local) or a URI (if remote: true). Supported MIME types are image/jpeg, image/png, and image/gif.
    • remote: A boolean indicating if image_src is a remote URI.
    • name: The name of the picture.
    • descr: A description of the picture.
    • start_at: An array [column, row] defining the starting position.
    • width: The width of the image (requires a OneCellAnchor).
    • height: The height of the image (requires a OneCellAnchor).
    • opacity: A float between 0.0 and 1.0 to set picture transparency.
    # Example of configuring a Pic object via options
worksheet.add_image(image_src: 'path/to/image.png', start_at: [0, 0], opacity: 0.5)

  8. How DateGroupItem works in AutoFilters

    master

    A DateGroupItem is used to express a group of dates or times (e.g., all dates in a specific year or month) within an AutoFilter.

    Note: While you can specify these in your workbook, they may not be applied to the date values in your workbook at this time.

    To create a DateGroupItem, you must provide a date_time_grouping and a year.

    Required and available options:

    • date_time_grouping (String): Must be one of year, month, day, hour, minute, or second.
    • year (Integer|String): A four-digit year.
    • month (Integer): 1 to 12.
    • day (Integer): 1 to 31.
    • hour (Integer): 0 to 23.
    • minute (Integer): 0 to 59.
    • second (Integer): 0 to 59.
    # Example of the hash structure used for date_group_items in add_column:
# ws.auto_filter.add_column(0, :filters, :date_group_items => [{ :date_time_grouping => 'year', :year => 2023 }])

  9. Configure worksheet auto-filters

    master

    To apply filters to specific columns in a worksheet, use the AutoFilter#add_column method. This is the recommended way to interact with filter objects rather than instantiating Axlsx::Filters directly.

    Example of adding a filter to the first column (index 0) that includes specific values, a blank filter flag, and a specific calendar type:

    ws.auto_filter.add_column(0, :filters, :blank => true, :calendar_type => 'japan', :filter_items => [100, 'a'])

  10. Add images to a worksheet using Worksheet#add_image

    master
    The recommended way to manage images in your sheets is to use the Worksheet#add_image method. While the Pic class represents the image object, you should interact with it through the worksheet's high-level API to ensure proper anchor and drawing management.

  11. Create charts in worksheets

    master

    To create a chart in a worksheet, use the Worksheet#add_chart method. The Axlsx::Chart class serves as the superclass for all specific chart types. When initializing a chart, you can define its position, title, and legend settings via an options hash or a block.

    Common initialization options include:

    • title: A String or Cell for the chart title.
    • show_legend: Boolean to toggle the legend.
    • legend_position: Symbol to set the legend location.
    • start_at: Array, String, or Cell defining the top-left corner.
    • end_at: Array, String, or Cell defining the bottom-right corner.
    • plot_visible_only: Boolean (defaults to true) to only plot data from visible cells.
    • rounded_corners: Boolean (defaults to true) to enable rounded corners on the chart area.
    # Example of adding a chart (conceptual usage based on documentation)
worksheet.add_chart(Axlsx::Pie3DChart, title: 'My Chart', start_at: 'A1', end_at: 'H20') do |chart|
  chart.add_series data: [1, 2, 3], labels: ['A', 'B', 'C']
end

  12. Split or freeze sheet panes in Axlsx

    master

    You can divide a worksheet into panes to freeze rows/columns or create split views using the sheet_view.pane configuration.

    Set the pane.state to one of the following three symbols:

    • :split: Creates a split view.
    • :frozen: Freezes rows and/or columns.
    • :frozen_split: Combines both freezing and splitting.

    Key attributes for configuring the pane include:

    • top_left_cell: The cell that becomes the top-left corner of the scrolling area (e.g., 'B2').
    • y_split: The number of rows to split or freeze.
    • x_split: The number of columns to split or freeze.
    • active_pane: Determines which pane is currently selected (e.g., :top_left, :top_right, :bottom_left, or :bottom_right).
    require 'axlsx'

p = Axlsx::Package.new
wb = p.workbook

wb.add_worksheet(name: 'Panes') do |sheet|
  # Add data
  sheet.add_row [''] + (0..99).map { |i| "column header #{i}" }
  100.times { |index| sheet.add_row ['row header'] + (0..index).to_a }

  # Configure panes
  sheet.sheet_view.pane do |pane|
    pane.top_left_cell = 'B2'
    pane.state = :frozen_split
    pane.y_split = 1
    pane.x_split = 1
    pane.active_pane = :bottom_right
  end
end

p.serialize 'panes_example.xlsx'