DOCSEARCHbyAlgolia
Submit Docs

Creek Ruby Gem

repository·master·Indexed 16 days ago

https://github.com/pythonicrubyist/creek

A high-performance Ruby gem for streaming the parsing of large Excel (.xlsx and .xlsm) files. Optimized for memory efficiency, Creek provides various row enumerators (rows, simple_rows, rows_with_meta_data, and simple_rows_with_meta_data) to process spreadsheets. It supports remote file parsing, header mapping, and image extraction via the Creek::Drawing and Creek::Sheet classes.

Tokens
2.7K
Snippets
14
Records
16
Agent score
64%

What's inside Creek

  1. Parse images in Excel sheets

    master

    Creek does not parse images by default to maintain performance. To include image information, you must call the with_images method on the sheet before iterating over rows.

    When enabled, cells containing images will return an array of Pathname objects. If an image spans multiple cells, the same Pathname object is returned for each.

    If a row contains only images and no text, the standard row iterators might return nil for those cells. In such cases, use the images_at(cell_name) method to retrieve the images directly.

    # Preload images
sheet.with_images.rows.each do |row|
  puts row # => {"A1"=>[#<Pathname:/path/to/image.jpeg>], "B2"=>"Fluffy"}
end

# Get images for a specific cell
puts sheet.images_at('A1') # => [#<Pathname:/path/to/image.jpeg>]
puts sheet.images_at('C1') # => nil

  2. Basic Usage of Creek to parse Excel files

    master

    To parse an Excel file, initialize a Creek::Book object with the file path. You can then access specific sheets and iterate through rows using several different enumerators depending on the data format you need.

    Available row enumerators:

    • rows: Returns a hash mapping cell coordinates (e.g., "A1") to values.
    • simple_rows: Returns a hash mapping column letters (e.g., "A") to values.
    • rows_with_meta_data: Returns a hash containing cell values and metadata (e.g., "collapsed", "hidden", "r" for row index).
    • simple_rows_with_meta_data: Returns a hash mapping column letters to values, including metadata.

    Note: with_headers: true can be used during Creek::Book.new to map cells to header names, but this only works with the simple_rows method.

    require 'creek'
creek = Creek::Book.new 'spec/fixtures/sample.xlsx'
sheet = creek.sheets[0]

sheet.rows.each do |row|
  puts row # => {"A1"=>"Content 1", "B1"=>nil, "C1"=>nil, "D1"=>"Content 3"}
end

  4. How Creek::Sheet row formats differ

    master

    When iterating through a Creek::Sheet, you can control the shape of the yielded data using different methods. This allows you to switch between accessing data by specific cell coordinates or by column headers.

    MethodKey TypeMetadata Included?
    rowsCell ID (e.g., A1)No
    simple_rowsColumn ID (e.g., A)No
    rows_with_meta_dataCell ID (e.g., A1)Yes (via 'cells' key)
    simple_rows_with_meta_dataColumn ID (e.g., A)Yes (via 'cells' key)

    If with_headers is set to true on the sheet, simple_rows and simple_rows_with_meta_data will use the values from the first row as the keys instead of the column letters.

  5. Use Creek in a Rails controller for file uploads

    master

    Creek can parse uploaded files from Rails params directly without needing external file upload gems. Since Rails uploads to a temporary location, you can pass the file path and disable the extension check to handle the StringIO object correctly.

    def import
  file = params[:file]
  Creek::Book.new file.path, check_file_extension: false
end

  6. Map cells with header names

    master

    To access cell values using the header names (the first string of the sheet) instead of column letters, initialize the book with with_headers: true.

    Important: This mode is only compatible with the simple_rows method.

    creek = Creek::Book.new file.path, with_headers: true

  7. Configure Creek file extension validation

    master

    By default, Creek validates that the file extension is either *.xlsx or *.xlsm. You can bypass this check by passing :check_file_extension => false to Creek::Book.new.

    path = 'sample-as-zip.zip'
Creek::Book.new path, :check_file_extension => false

  9. Extract images from a Creek::Sheet

    master

    To access images embedded in an Excel sheet, you must first preload the image information. If you do not call with_images before iterating through rows, image data will not be available.

    1. Call with_images on the sheet instance. This returns self to allow chaining.
    2. Use images_at(cell) to retrieve images for a specific cell. This returns an array of Pathname objects pointing to the extracted images in a temporary folder, or nil if no images are found or if with_images was not called.
    # Preload images and then iterate
sheet.with_images.rows.each do |row|
  # row is a hash of cell_id => value
  row.each do |cell_id, value|
    images = sheet.images_at(cell_id)
    next if images.nil?
    
    images.each { |img_path| puts "Found image at: #{img_path}" }
  end
end

  10. Iterate through rows in a Creek::Sheet

    master

    The Creek::Sheet class provides several methods to iterate over rows using Enumerators. Depending on your needs, you can choose between different data formats for the rows:

    1. Standard Rows (rows): Returns an Enumerator where each yielded value is a hash. The key is the Cell ID (e.g., A1, B2) and the value is the cell content.
    2. Simple Rows (simple_rows): Returns an Enumerator where each yielded value is a hash. The key is the Column ID (e.g., A, B) and the value is the cell content.
    3. Rows with Metadata (rows_with_meta_data): Returns an Enumerator where each yielded value is a hash containing row metadata and an embedded 'cells' hash containing the cell contents.
    4. Simple Rows with Metadata (simple_rows_with_meta_data): Similar to rows_with_meta_data, but uses Column IDs for the cell keys within the 'cells' hash.
    # Example: Using simple_rows to get column-based access
sheet.simple_rows.each do |row|
  puts row['A'] # Accesses value in column A
end

# Example: Using rows to get cell-id based access
sheet.rows.each do |row|
  puts row['A1'] # Accesses value in cell A1
end

  11. Initialize a Creek::Book to load Excel files

    master

    Use Creek::Book.new to open an Excel file (.xlsx or .xlsm).

    Options

    • path: The local file path or a remote URL.
    • remote: (Boolean) If true, treats the path as a URL and downloads it.
    • check_file_extension: (Boolean) If true (default), validates that the file extension is .xlsx or .xlsm. If you are providing a remote URL, you may need to pass the :original_filename to ensure validation passes.
    • original_filename: (String) Used for extension validation when check_file_extension is enabled.
    • with_headers: (Boolean) If true, sets the with_headers attribute on all loaded sheets. This is useful when you want to treat the first row of a sheet as header names.
    # Local file
book = Creek::Book.new('path/to/file.xlsx')

# Remote file with header support
book = Creek::Book.new('https://example.com/data.xlsx', remote: true, with_headers: true)

  12. Extract images from specific cells using Creek::Drawing#images_at

    master

    The Creek::Drawing class allows you to retrieve images associated with specific Excel cells. You can use the images_at method to get an array of Pathname objects pointing to the extracted image files in a temporary directory.

    Note that:

    • If no images are found at the specified cell, it returns nil.
    • Multiple images can exist in a single cell; in such cases, an array containing all relevant Pathname objects is returned.
    • If an image spans multiple cells (via a range), the same image path will be returned for each cell in that range.
    # Assuming 'drawing' is an instance of Creek::Drawing
images = drawing.images_at('A1')

if images
  images.each do |image_path|
    puts "Found image at: #{image_path}"
    # image_path is a Pathname object
  end
else
  puts "No images found in cell A1"
end