Handling Excel File with Ruby on Rails

Hello, everyone!

Circumstance Of Excel With Ruby on Rails

When you guys handle Excel File, there are two operations.

1. Read Excel File
2. Write Excel FIle

Today, there are several gems to handle Excel File in Ruby on Rails ,e.g roo, axlsx, rubyXL. But, it does not always mean all of them are useful.
That is some of the above gems is not equipped with all operations.

For example, 'axlsx' gem can't read Excel file and 'roo' gem can't write. Therefore, I was looking for the gems that have all operations and usefulness. And then I discoverd 'rubyXL' gem which is so useful and easier to use than other gems. In the following, I will introduce the standard method to use rubyXL.

What Is Implemented With rubyXL

On condintion that you have format file, I implement the following.

1. Read Excel File
2. Write Excel File
3. Export Excel File

Preparing To Implement

Install rubyXL

add the following code into Gemfile.

gem 'rubyXL'

and then you should do 'bundle install'

bundle install

How To Implement With rubyXL

Read Excel File

workbook = RubyXL::Parser.parse('ExcelFilePath')

You should replace 'ExcelFilePath' with the path in which you want read file.

Acquire WorkSheet

worksheet = workbook['WorkSheetName']

As the following, you can specify worksheet by index.

worksheet = workbook[０]
worksheet = workbook.first

Acquire CellValue

cell_value = worksheet[y][x].value

You should be careful with the location of 'x' and 'y'.

Add Cell

worksheet.add_cell(y, x ,value)

Similarly, You should be careful with the location of 'x' and 'y'.

cell_value = worksheet.add_cell(y, x ,value)

As the above, you can also acquire the value of added cell.

Write Excel File

workbook.write('ExcelFilePath')

you should replace 'ExcelFilePath' with the path in which you want put written file.

Export Excel File

send_data(
      workbook.stream.read,　
      :disposition => 'attachment',
      :type => 'application/excel',
      :filename => "FileName.xlsx"
    )

If you want binary data, you can get the data in the following.

workbook.stream.read

For Copy & Paste

#Read Excel File
workbook = RubyXL::Parser.parse('ExcelFilePath')

#Acquire WorkSheet
worksheet = workbook['SheetName']

#Acquire Cell Value
cell_value = worksheet[y][x].value

#Write Excel File
workbook.write('ExcelFilePath')

#Export File
send_data(
      workbook.stream.read,　
      :disposition => 'attachment',
      :type => 'application/excel',
      :filename => "FileName.xlsx"
    )

Thank You For Reading

In this time, I show implemention of rubyXL on condintion that there is format file. If you want to change width of cell and color of text and anything, you should read Document of rubyXL.

Thank you for much.
Have a good ruby life!

Comments

[beauXjames]

Being rather new to Ruby I have been trying to leverage RubyXL::Parser.parse_buffer. I have been attempting to use the open-uri library within the core however all of the permutations I have attempted result in some sort of failed attempt. I have no problems opening a file locally, as you have shown above. Have you had any luck with loading a file into a buffer and using this method?

[tejaravi89]

worksheet[y][x].change_contents(someValue) is not working, at the sametime p worksheet[y][x].value works

[Ryo]

Oh... thank you!!!
I fixed it.