Reading Excel Sheets using "Roo" gem in ruby on rails

By
This gem allows you to read the contents of
  • Open-office spreadsheets (.ods)
  • Excel spreadsheets (.xls)
  • Google (online) spreadsheets
  • Excel's new file format .xlsx

How to use this gem :
1) Install 

          [sudo] gem install roo

2) Create an instance of the excel sheet file        
  
       require 'rubygems'
       require 'roo'       
       s = Roo::Openoffice.new("myspreadsheet.ods")
       s = Roo::Excel.new("myspreadsheet.xls")
       s = Roo::Google.new("myspreadsheet_at_google")
       s = Roo::Excelx.new("myspreadsheet.xlsx")
    
3)  Reading from a particular sheet of the spreadsheet:
     Lets say your spreadsheet has 4 sheets and you want to read the 2nd sheet
      s.default_sheet = s. sheets[2]

   *  Sheets is an array containing the names of all the sheets inside the spread sheet. 
   *  If we do not set the default sheet option, the first sheet of the spreadsheet is taken as the         default sheet.

4) Reading each cell of the sheet
       s.cell(1,1)                                   # returns the first column of the first row
      s.cell(A,1)                                   # returns the same cell
      s.cell(1,A)                                   # returns the same cell
      s.cell(1, A, s.sheets[1])           # returns the same cell of the second sheet in the                                                                                        spreadsheet.

  * Almost all methods have an optional argument 'sheet'.  If we do not specify this parameter,     the default sheet is used.

 5) Other Important Methods

s.first_row                                           # returns the number of first un-empty row

 s.last_row                                            # returns the number of last un-empty row

 s.first_column                                   # returns the number of first un-empty column

 s.last_column                                    # returns the number of last un-empty column

 s.celltype(1,1)                                    # returns the cell type of the specified cell. It may be                                                                                 string, date,float,datetime, percentage,formula.
Experienced Ruby on Rails and Web Developer having 11+ years experience in Ruby & Ruby on Rails framework, TTD, Rspec. Expert on Git, SVN , Git Hook, Bitbucket. I use MySQL, MongoDB , PostgreSQL & Sqlite for Database Management. In front-end development I use HTML5 / HAML, CSS3 / SCSS, javascript / coffeescript. I also know about the deploy the applications on VPS Server i.e AWS EC2, Heroku, Ditital Ocean, Hostgator, Site5, BlueHost, AWS Load Balancing, Elastic Beanstalk, AWS S3 e.t.c ☆ Frameworks ☆ Ruby on Rails, Sinatra ☆ Database ☆ - PostgreSQL, MySQL, SQLite, - MongoDB, Amazon DynamoDB,AWS RDS. ☆ Front end Techniques ☆ - ReactJS, Angular js, Node js, Javascript, JQuery, HTML, CSS - Twitter Bootstrap, Coffeescript. Why hire me? * Master degree in Computer Science * 6 years professional experiences and 10+ projects completed * Available all day long, 7 days/24 hours * Good communication skills with fluent English * Self-motivated, fast-learner and "I can do" mind * Quality insurance and fast delivery

Comments

  1. sneha19 August 2020 at 15:07

    Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    ruby on rails training India
    ruby on rails training Hyderabad

    ReplyDelete

Post a Comment

Popular Posts

How to pass hash in Postman

By
By the looks of what you are trying to send, you need to change the Grape restriction, because destinations is an Array , not a Hash : requires : destinations , type : Array   You don't need the "destination" hash when sending the request: { destinations => [ { name => 'dest1' , other_attribute : 'value' , etc ... }, { name => 'dest2' , other_attribute : 'value' , etc ... }, { name => 'dest3' , other_attribute : 'value' , etc ... } ]}   This creates an Array of hashes. In order to send this through POSTMAN, you'll need to modify that destinations param your sending and add multiple lines in POSTMAN. Something like: destinations [][ name ] 'dest1' destinations [][ other_attribute ] 'value1' destinations [][ name ] 'dest2' destinations [][ other_attribute ] 'value2'
Read more

nginx: unrecognized service

By
The nginx: unrecognized service error means the startup scripts need to be created. Fortunately the startup scripts have already been written. We can fetch them with wget and set them up following these steps: # Download nginx startup script wget -O init-deb.sh https://www.linode.com/docs/assets/660-init-deb.sh # Move the script to the init.d directory & make executable sudo mv init-deb.sh /etc/init.d/nginx sudo chmod +x /etc/init.d/nginx # Add nginx to the system startup sudo /usr/sbin/update-rc.d -f nginx defaults Now we can control nginx using: sudo service nginx stop sudo service nginx start sudo service nginx restart sudo service nginx reload
Read more

Bootstrap Select Picker append add new item if search not exist

By
  <select class = "selectpicker" data-live-search= "true" id= "branch" multiple> < option > B.Tech </ option > < option > M.Tech </ option > < option > Ph.D </ option > </select> jQuery script $( '.selectpicker' ). selectpicker ({ noneResultsText : 'No result found <button class="btn btn-dark" onclick=(add_opt(this))>Add new option</button>' }); function add_opt ( event ){ var value = $(event). parents ( 'div' ). siblings ( '.bs-searchbox' ). find ( 'input' ). val (); $(event). parents ( 'div' ). siblings ( '.selectpicker' ). append ($( "<option></option>" ). text (value)). val (value); $( '.selectpicker' ). selectpicker ( 'refresh' ); }
Read more

Add CORS to Nginx on AWS Elastic Beanstalk

By
Image
Apache Add the following block to your  .htaccess  file: <FilesMatch ".(eot|otf|ttf|woff|woff2)"> Header always set Access-Control-Allow-Origin "*" </FilesMatch> Nginx Add the following location block to your virtual host file (usually within the server directive) and reload Nginx: location ~* \.(eot|otf|ttf|woff|woff2)$ { add_header Access-Control-Allow-Origin *; } Invalidate CloudFront Distribution Once the changes have been made you will need to  invalidate the CloudFront cache  with a path of “/*”. Integrating to CloudFront service It’s simple to config and use CloudFront (a CDN service) for Rails app.  Just one thing from my experience, regarding to CORS issue on EB when your CSS want get loading font files or font-awesome. Because EB run your app with Nginx server serve public static files, then work around would be overwriting web server config in Nginx with  add_header Access-Control-Allow-Origin ‘*’  by crea
Read more

Enable gzip compression on Elastic Beanstalk with nginx

By
Create nginx_gzip_configuration.config file into .ebextensions directory and then put the following script. files:   "/etc/nginx/conf.d/gzip.conf":       mode: "644"       owner: "root"       group: "root"       content: |         # enable gzip compression         gzip on;         gzip_min_length  1100;         gzip_buffers  4 32k;         gzip_types    text/plain text/html text/xml text/css application/javascript application/json;         gzip_vary on;         # end gzip configuration container_commands:   02_restart_nginx:     command: service nginx restart
Read more

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

By
If you are trying to connect mysql using password or without password like mysql -u USERNAME or mysql -u USERNAME -p PASSWORD and getting error:-   Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) find first socket location sudo find / -type s my be location would be /tmp/mysql.sock just go in the mysql file my.cnf sudo nano  /etc/mysql/my.cnf and do changes in scocket port            = 3306 socket          = /tmp/mysql.sock [mysqld_safe] socket          = /tmp/mysql.sock nice            = 0 Restart mysql, sudo /etc/init.d/mysql restart And try login again mysql -u USERNAME Hope it will work. Command to know all the mysql variable sudo mysqladmin variables To know mysql socket path mysqld --verbose --help | grep ^socket
Read more

Get video duration by URL in Ruby on Rails

By
First install ffmpeg on your system  http://ffmpeg.org/download.html OR sudo add-apt-repository ppa:mc3man/trusty-media And confirm the following message by pressing <enter>: Also note that with apt-get a sudo apt-get dist-upgrade is needed for initial setup & with some package upgrades More info: https://launchpad.net/~mc3man/+archive/ubuntu/trusty-media Press [ENTER] to continue or ctrl-c to cancel adding it Update the package list. sudo apt-get update sudo apt-get dist-upgrade Now FFmpeg is available to be installed with apt: sudo apt-get install ffmpeg In Controller: your_video_path =  https://s3-us-west-2.amazonaws.com/its-get-brighter-staging/uploads/video/video/2/Planking.mp4 result = `ffmpeg -i #{your_video_path} 2>&1` videotime = result.match("Duration: ([0-9]+):([0-9]+):([0-9]+).([0-9]+)")[0].split("Duration: ").last.split(".").first
Read more

site-enables nginx setting in ruby in rails

By
/etc/nginx/sites-available/ default # You may add here your # server { # ... # } # statements for each of your virtual hosts to this file ## # You should look at the following URL's in order to grasp a solid understanding # of Nginx configuration files in order to fully unleash the power of Nginx. # http://wiki.nginx.org/Pitfalls # http://wiki.nginx.org/ QuickStart # http://wiki.nginx.org/ Configuration # # Generally, you will want to move this file somewhere, and start with a clean # file but keep this around for reference. Or just disable in sites-enabled. # # Please see /usr/share/doc/nginx-doc/ examples/ for more detailed examples. ## server {       listen 80;       server_name localhost;       root /home/yuva/YuvaSoft/new_ pollutionapi/public;   # <--- be sure to point to 'public'!       passenger_enabled on; # Make site accessible from http://localhost/ # passenger_enabled on; location / { # Fir
Read more