We can Use the roo Gem For to read and Write in xlsx

Follow Below Instruction:

Install The Gem

$ gem install roo

Or add it to your Gemfile

gem "roo", "~> 2.8.0"

Usage
Opening a spreadsheet

 

require 'roo'
xlsx = Roo::Spreadsheet.open('./sample.xlsx')
xlsx = Roo::Excelx.new("./sample.xlsx")
# Use the extension option if the extension is ambiguous.
xlsx = Roo::Spreadsheet.open('./rails_temp_upload', extension: :xlsx)
xlsx.info
# => Returns basic info about the spreadsheet file
Roo::Spreadsheet.open can accept both paths and File instances.

Working with XLSX sheets In Ruby On Rails

 

ods.sheets
# => ['Info', 'Sheet 2', 'Sheet 3']   # an Array of sheet names in the workbook
ods.sheet('Info').row(1)
ods.sheet(0).row(1)
# Set the last sheet as the default sheet.
ods.default_sheet = ods.sheets.last
ods.default_sheet = ods.sheets[2]
ods.default_sheet = 'Sheet 3'
# Iterate through each sheet
ods.each_with_pagename do |name, sheet|
  p sheet.row(1)
end

Accessing rows and columns In Ruby On Rails using Roo Gem

Roo Gem uses Excel's numbering for rows, columns and cells, so 1 is the first index, not 0 as it is in an Array

 

sheet.row(1)
# returns the first row of the spreadsheet.
sheet.column(1)
# returns the first column of the spreadsheet.

Almost all methods have an optional argument sheet. If this parameter is omitted, the default_sheet will be used.

 

sheet.first_row(sheet.sheets[0])
# => 1             # the number of the first row
sheet.last_row
# => 42            # the number of the last row
sheet.first_column
# => 1             # the number of the first column
sheet.last_column
# => 10            # the number of the last colum 

How To Access cells In ruby on rails

You can access the top-left cell in the following ways

sheet.cell(1,1)
sheet.cell('A',1)
sheet.cell(1,'A')
sheet.a1
# Access the second sheet's top-left cell.
sheet.cell(1,'A',sheet.sheets[1]

ROR using roo Gem Querying a spreadsheet
Use each to iterate over each row.

If each is given a hash with the names of some columns, then each will generate a hash with the columns supplied for each row.

sheet.each(id: 'ID', name: 'FULL_NAME') do |hash|
  puts hash.inspect
  # => { id: 1, name: 'John Smith' }
end

Use sheet.parse to return an array of rows. Column names can be a String or a Regexp.

sheet.parse(id: /UPC|SKU/, qty: /ATS*\sATP\s*QTY\z/)
# => [{:id => 727880013358, :qty => 12}, ...]

Use the :headers option to include the header row in the parsed content.

sheet.parse(headers: true)

Use the :header_search option to locate the header row and assign the header names.

sheet.parse(header_search: [/UPC*SKU/,/ATS*\sATP\s*QTY\z/])

Use the :clean option to strip out control characters and surrounding white space.

sheet.parse(clean: true)

Options
When opening the file you can add a hash of options.

expand_merged_ranges
If you open a document with merged cells and do not want to end up with nil values for the rows after the first one.

xlsx = Roo::Excelx.new('./roo_error.xlsx', {:expand_merged_ranges => true})

Exporting spreadsheets In Ruby On Rails Using Roo Gem

Roo has the ability to export sheets using the following formats. It will only export the default_sheet.

Using Roo Gem We can convert to csv,to_xml etc

sheet.to_csv
sheet.to_matrix
sheet.to_xml
sheet.to_yaml

Excel (xlsx and xlsm) Support
Stream rows from an Excelx spreadsheet.

xlsx = Roo::Excelx.new("./test_data/test_small.xlsx")
xlsx.each_row_streaming do |row|
  puts row.inspect # Array of Excelx::Cell objects
end

By default blank cells will be excluded from the array. To keep them, use the option pad_cells = true. (They will be set to nil in the array)

xlsx.each_row_streaming(pad_cells: true) do |row|
  puts row.inspect # Array of Excelx::Cell objects
end

To stream only some of the rows, you can use the max_rows and offset options.

xlsx.each_row_streaming(offset: 1) do |row| # Will exclude first (inevitably header) row
  puts row.inspect # Array of Excelx::Cell objects
end
xlsx.each_row_streaming(max_rows: 3) do |row| # Will yield 4 rows (it's automatically incremented by 1) after the supplied offset.
  puts row.inspect # Array of Excelx::Cell objects
end
Iterate over each row
xlsx.each_row do |row|
  ...
end

 

Roo::Excelx also provides these helpful methods To add Comment and update Font in xlsx Sheet In ROR.

xlsx.excelx_type(3, 'C')
# => :numeric_or_formula
xlsx.cell(3, 'C')
# => 600000383.0
xlsx.excelx_value(row,col)
# => '600000383'
xlsx.formatted_value(row,col)
# => '0600000383'

Roo::Excelx can access celltype, comments, font information, formulas, hyperlinks and labels.

xlsx.comment(1,1, ods.sheets[-1])
xlsx.font(1,1).bold?
xlsx.formula('A', 2)