View on GitHub

Excel-data

a ruby gem for generating iqy files and parsing excel dates

Download this project as a .zip file Download this project as a tar.gz file

Introduction

Excel uses .iqy files to format external data queries. You add an iqy file to your project by going to data > get external data > run saved query. You can specify parameters to append to the url when the query is run. This gem provides methods for parsing paramaters from excel and generating responses that excel can understand.

1. Install excel-data via ruby gems

   $ gem install excel-data
   # Gemfile
   require 'excel-data'

2. Create a new Iqy object with the root url of your site

    # config/intializers/excel_data.rb
    $iqy = Iqy.new("http://localhost:3000")

3. Create an iqy file

To create a new iqy file, call method iqy_file on your Iqy instance. iqy_file takes two arguments - parameters and the path to the end point you want the web query to hit. For now, we'll keep it simple and pass a blank parameter hash. We'll then use send_data in our controller to send the iqy file when someone hits the end point

    #app/controllers/pages_controller.rb
    def excel_query
      iqy_file = $iqy.iqy_file({},'/pages/excel_endpoint')
      send_data iqy_file,  :filename => "example_query.iqy"
    end

4. Create the endpoint for excel to hit

If you open example_query.iqy with data > get external data > run saved query, your excel spreadsheet will update itself with the data from localhost:3000/pages/excel_endpoint whenever you refresh the data. Right now, iqy files created with excel-data can only parse simple html tables, so put your data in that format. It might look something like this. Note the helper methods .format_excel and .to_excel_currency in the view, these format your strings so they're picked up as the correct data type by excel.

    #app/controllers/pages_controller.rb
    def excel_endpoint
      @sales = Sale.all
      respond_to do |format|
        format.html {render layout: false }  
      end
    end
    // app/views/excel_data.html.erb
<table>
  <thead>
    <tr>
      <th>Date</th>
      <th>Gross</th>
    </tr>
  </thead>   
  <tbody>
    <% @sales.each do |s| %>
      <tr>
        <td><%= s.date.format_excel %></td>
        <td><%= s.gross.to_excel_currency %></td>
      </tr> 
    <% end %>
  </tbody>
</table>

5. Adding parameters

What if you want to be able to pick the date range to get sales from in excel? And just an unprotected endpoint, that seems insecure, so lets require an api key as well. No problem, just setup a parameters hash like so:

    #app/controllers/pages_controller.rb
    def excel_query

    excel_params = {
      api_key: 'supersecretapikey',
      start_date: ["Start Date","first date you want sales returned"],
      end_date: ["End Date","last date you want sales returned"],
    }
      iqy_file = $iqy.iqy_file(excel_params,'/pages/excel_endpoint')
      send_data iqy_file,  :filename => "example_query.iqy"
    end

values that are arrays get converted into excel prompts, with array[0] becoming the title, and array[1] becoming the description. All other values get hard coded into the iqy as GET parameters.

6. Parsing excel parameters

Parsing the parameters excel posts to your endpoint. Ruby will handle most excel datatypes natively, with the exception of dates and datetimes, which are formatted as follows:

Dates are given as an integer of days since 01/01/1900 minus two days because microsoft sucks at leap years. DateTimes are given as a float, with the integer portion representing days since 1900, and the decimal representing the porportion of the day completed. So January 2nd 1900 at noon would be 2.5, and so forth. excel-data adds the .parse_excel_date helper method, which will convert excel's dates to ruby's.

So your updated endpoint controller from step #4 might look something like this:

      def excel_endpoint
      @start_date = params[:start_date].parse_excel_date
      @end_date = params[:end_date].parse_excel_date
      @sales = Sale.where("created_at > :start_date and created_at < :end_date",
        start_date: @start_date,
        end_date:@end_date)
      respond_to do |format|
        format.html {render layout: false }  
      end
    end

Contact

Got questions, comments, feedback? Create an issue or hit me up at [email protected] and I'll do my best to help you out.