如何用RubyXL获取XLSX文件最后行列信息并追加内容？

Hey there! Let's walk through how to get row/column details from an existing XLSX file using RubyXL, including targeting the last row/column to append new content. We'll also cover alternative gems if RubyXL doesn't fit your needs.

1. Getting Basic Row & Column Information with RubyXL #

First, make sure you have the rubyXL gem installed (run gem install rubyXL if you haven't). Here's how to pull out core row/column data:

require 'rubyXL'

# Load the workbook and target your worksheet
workbook = RubyXL::Parser.parse('your_spreadsheet.xlsx')
worksheet = workbook[0] # Grab the first sheet, or use worksheet_by_name('Sheet1') for a named sheet

# Get total number of rows (including empty ones)
total_rows = worksheet.sheet_data.rows.size

# Get total number of columns (the highest column index used in the sheet)
total_columns = worksheet.max_column

# If you want to count only rows with actual content:
rows_with_content = worksheet.sheet_data.rows.reject { |row| row.cells.compact.empty? }
total_non_empty_rows = rows_with_content.size

puts "Total rows: #{total_rows}, Non-empty rows: #{total_non_empty_rows}"
puts "Total columns used: #{total_columns}"

2. Finding Last Row/Column & Appending New Content #

To append content right after the last filled row, you need to first identify the last row with actual data (ignoring empty trailing rows). Here's a step-by-step example:

require 'rubyXL'

workbook = RubyXL::Parser.parse('your_spreadsheet.xlsx')
worksheet = workbook[0]

# Find the last non-empty row
last_filled_row = worksheet.sheet_data.rows.reject { |row| row.cells.compact.empty? }.last

# Calculate the index for the new row (RubyXL uses 0-based indexing for rows)
new_row_index = last_filled_row ? last_filled_row.index + 1 : 0

# Get the last used column (from the last filled row)
last_filled_column = last_filled_row ? last_filled_row.cells.compact.size : 0

# Example: Append a new row with data
new_data = ["Q3 Sales", "$45,000", "West Region"]
new_row = worksheet.add_row(new_row_index)

# Populate the new row with your data
new_data.each_with_index do |value, col_idx|
  new_row.add_cell(col_idx, value)
end

# Save the updated workbook
workbook.write('updated_spreadsheet.xlsx')

Note: If you don't care about empty trailing rows and just want to add after the last row (even if it's empty), you can simplify new_row_index to worksheet.sheet_data.rows.size.

Alternative Gems If RubyXL Doesn't Meet Your Needs #

RubyXL works great for most basic to intermediate tasks, but if you run into limitations (like handling extremely large files or complex formatting), these gems are solid alternatives:

• Roo: A versatile gem that supports reading XLSX, XLS, CSV, and more. It has a clean, intuitive API and handles most common spreadsheet operations smoothly. Perfect for everyday use.
• Creek: Built for large XLSX files, Creek uses streaming to read data without loading the entire file into memory. Ideal if you're working with huge spreadsheets that would crash RubyXL due to memory constraints.
• Axlsx: While primarily focused on generating spreadsheets, Axlsx also has reading capabilities. It's a great choice if you need precise control over formatting (like styles, formulas, or charts) when appending content.

内容的提问来源于stack exchange，提问作者Jackson Gong