Hello, everyone!
Circumstance Of Excel With Ruby on Rails
When you guys handle Excel File, there are two operations.
- Read Excel File
- 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.
- Read Excel File
- Write Excel File
- 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!
Top comments (3)
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?
worksheet[y][x].change_contents(someValue) is not working, at the sametime p worksheet[y][x].value works
Oh... thank you!!!
I fixed it.