rubyXL ↑

To Install: ↑

gem install rubyXL

To Use: ↑

require 'rubyXL' # Assuming rubygems is already required

Parsing an existing workbook ↑

workbook = RubyXL::Parser.parse("path/to/Excel/file.xlsx")

Creating a new Workbook ↑

workbook = RubyXL::Workbook.new

Accessing ↑

Accessing a Worksheet ↑

workbook.worksheets[0] # Returns first worksheet
workbook[0]            # Returns first worksheet
workbook['Sheet1']     # Finds and returns worksheet titled "Sheet1"

Accessing just the values ↑

worksheet = workbook[0]
worksheet.extract_data  # Produces a simple rectangular array that consists only of cell values (rather than the Cell objects)

Accessing a Row (Array of Cells) ↑

worksheet = workbook[0]
worksheet.sheet_data[0] # Returns first row of the worksheet
worksheet[0]            # Returns first row of the worksheet

Accessing a Cell object ↑

worksheet = workbook[0]
worksheet.sheet_data[0][0] # Returns cell A1 in the worksheet
worksheet[0][0]            # Returns cell A1 in the worksheet

Wrappers for accessing Cell properties ↑

cell = workbook[0][0][0]
cell.is_struckthrough  # Returns +true+ if the cell is struckthrough, other boolean properties have same syntax
cell.font_name
cell.font_size
cell.font_color
cell.fill_color
cell.horizontal_alignment
cell.vertical_alignment
cell.border_top

Wrappers for accessing Row properties ↑

Please note: these methods are being phased out in favor of the OOXML object model.

worksheet = workbook[0]
worksheet.get_row_fill(0)
worksheet.get_row_font_name(0)
worksheet.get_row_font_size(0)
worksheet.get_row_font_color(0)
worksheet.is_row_underlined(0)
worksheet.get_row_height(0)
worksheet.get_row_horizontal_alignment(0)
worksheet.get_row_vertical_alignment(0)
worksheet.get_row_border_right(0)

Accessing column properties ↑

Please note: these methods are being phased out in favor of the OOXML object model.

worksheet = workbook[0]
worksheet.get_column_fill(0)
worksheet.get_column_font_name(0)
worksheet.get_column_font_size(0)
worksheet.get_column_font_color(0)
worksheet.is_column_underlined(0)
worksheet.get_column_height(0)
worksheet.get_column_horizontal_alignment(0)
worksheet.get_column_vertical_alignment(0)
worksheet.get_column_border_right(0)

Table identification ↑

worksheet = workbook[0]
worksheet.get_table(["NAME", "AGE", "HEIGHT"]) # Returns hash of a table in the first worksheet, with the specified strings as headers, accessible by row and column
#it returns the following structure
{
 :Name=>["John", "Jane", "Joe"], 
 :Height=>[70, 65, 68], 
 :Age=>[30, 25, 35]
 :table=>[
  {:Name=>"John", :Height=>70, :Age=>30},
  {:Name=>"Jane", :Height=>65, :Age=>25}, 
  {:Name=>"Joe", :Height=>68, :Age=>35}
 ]
}

Modifying ↑

Adding Worksheets ↑

worlsheet = workbook.add_worksheet('Sheet2')

Adding Cells ↑

worksheet.add_cell(0, 0, 'A1')      # Sets cell A1 to string "A1"
worksheet.add_cell(0, 1, '', 'A1')  # Sets formula in the cell B1 to '=A1'

Changing Cells ↑

worksheet[0][0].change_contents("", worksheet[0][0].formula) # Sets value of cell A1 to empty string, preserves formula

Changing Fonts ↑

worksheet.sheet_data[0][0].change_font_bold(true) # Makes A1 bold
worksheet.change_row_italics(0,true)              # Makes first row italicized
worksheet.change_column_font_name(0, 'Courier')   # Makes first column have font Courier

Changing Fills ↑

worksheet.sheet_data[0][0].change_fill('0ba53d')  # Sets A1 to have fill #0ba53d
worksheet.change_row_fill(0, '0ba53d')            # Sets first row to have fill #0ba53d
worksheet.change_column_fill(0, '0ba53d')         # Sets first column to have fill #0ba53d

Changing Borders ↑

# Possible weights: hairline, thin, medium, thick
# Possible "directions": top, bottom, left, right, diagonal
worksheet.sheet_data[0][0].change_border_top('thin') # Sets A1 to have a top, thin border
worksheet.change_row_border_left(0, 'hairline')      # Sets first row to have a left, hairline border
worksheet.change_column_border_diagonal(0, 'medium') # Sets first column to have diagonal, medium border

Changing Alignment ↑

Horizontal ↑

center, distributed, justify, left, right

worksheet.sheet_data[0][0].change_horizontal_alignment('center') # Sets A1 to be centered
worksheet.change_row_horizontal_alignment(0, 'justify')          # Sets first row to be justified
worksheet.change_column_horizontal_alignment(0, 'right')         # Sets first column to be right-aligned
Vertical ↑

bottom, center, distributed, top

worksheet.sheet_data[0][0].change_vertical_alignment('bottom')  # Sets A1 to be bottom aligned
worksheet.change_row_vertical_alignment(0, 'distributed')       # Sets first row to be distributed vertically
worksheet.change_column_vertical_alignment(0, 'top')            # Sets first column to be top aligned

Changing Row Height ↑

worksheet.change_row_height(0, 30)  # Sets first row height to 30

Changing Column Width ↑

worksheet.change_column_width(0, 30)  # Sets first column width to 30

Merging Cells ↑

worksheet.merge_cells(0, 0, 1, 1)  # Merges A1:B2

Insert Row ↑

This method will insert a row at specified index, pushing all rows below it down. It also copies styles from row above.

WARNING: Use of this method WILL break formulas referencing cells which have been moved, as the formulas do not adapt to the shifted rows

worksheet.insert_row(1)

Insert Column ↑

This method will insert a column at specified index, pushing all columns to the right of it one to the right. It also copies styles from column to the left

WARNING: Use of this method WILL break formulas referencing cells which have been moved, as the formulas do not adapt to the shifted columns

worksheet.insert_column(1)

Delete Row ↑

This method will delete a row at specified index, pushing all rows below it up.

WARNING: Use of this method WILL break formulas referencing cells which have been moved, as the formulas do not adapt to the shifted rows

worksheet.delete_row(1)

Delete Column ↑

This method will delete a column at specified index, pushing all columns to the right of it left.

WARNING: Use of this method WILL break formulas referencing cells which have been moved, as the formulas do not adapt to the shifted columns

worksheet.delete_column(1)

Insert Cell ↑

This method will insert a cell at specified position. It takes a :right or :down option, to shift cells either left or down upon inserting (nil means replacing the cell)

WARNING: Use of this method WILL break formulas referencing cells which have been moved, as the formulas do not adapt to the shifted cells

worksheet.insert_cell(0, 0, "blah", formula = nil, :right)  # Inserts cell at A1, shifts cells in first row right
worksheet.insert_cell(0, 0, "blah", formula = nil, :down)   # Inserts cell at A1, shifts cells in first column down
worksheet.insert_cell(0, 0, "blah")                         # Inserts cell at A1, shifts nothing

Delete Cell ↑

This method will delete a cell at specified position. It takes a :left or :up option, to shift cells either up or left upon deletion (nil means simply deleting the cell contents)

WARNING: Use of this method WILL break formulas referencing cells which have been moved, as the formulas do not adapt to the shifted cells

worksheet.delete_cell(0, 0, :left)  # Deletes A1, shifts contents of first row left
worksheet.delete_cell(0, 0, :up)    # Deletes A1, shifts contents of first column up
worksheet.delete_cell(0, 0)         # Deletes A1, does not shift cells

Writing ↑

workbook.write("path/to/desired/Excel/file.xlsx")

Miscellaneous ↑

Reference.ind2ref(0,0) == 'A1'    # Converts row and column index to Excel-style cell reference
Reference.ref2ind('A1') == [0, 0] # Converts Excel-style cell reference to row and column index

For more information ↑

Take a look at the files in spec/lib/ for rspecs on most methods

Contributing to rubyXL ↑

Copyright © 2011 Vivek Bhagwat, 2013-2014 Wesha. See LICENSE.txt for further details.