Convert Rails spreadsheet upload to Angular
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

Build a Rails / Angular app with a spreadsheet upload, based on an existing Rails app (code below).

Requirements

1. Allow for single file (only) select or drag onto area (as on the 'drop files here' section on http://angular-file-upload.appspot.com/)

2. Data from a text field on the page (batch.assembly) is also passed to the controller and updates the batches table

3. Spreadsheet rows are written to batch_details table (code and table below)

4. Filename is written to the batches table (in existing code)
5. Deliver as a working rails app on github

6. Test data below
7. Bonus for good error handling

Use the following technologies

Angular File Upload
https://github.com/danialfarid/angular-file-upload

Roo
https://github.com/Empact/roo

Sample File

I can't see how to attach a file, so create an Excel spreadsheet (.xlsx not csv), with the following test data

chrom       chrom_start     chrom_end
4               55593607    55593607
4               55593609    55593617
6              133105152    133105152

Existing code from a non-angular Rails app:

Upload Page

<div class="panel-group" id="accordion1">
  <div class="panel panel-default">
    <div class="panel-heading">
        <h4 class="panel-title">
          <a data-toggle="collapse" data-parent="#accordion1" href="#collapseOne">
            Spreadsheet Upload
          </a>
        </h4>
    </div>
    <div id="collapseOne" class="panel-collapse collapse">
      <div class="panel-body">
          <%= form_tag import_path, multipart: true do %>
            <%= file_field_tag :file, multiple: true %>
            <%= submit_tag "Import", :class => 'btn btn-small btn-info pull-right' %>
          <% end %>
      </div>
    </div>
  </div>
</div>

Controller

def import
    @batch = Batch.import(params[:file],current_user) 
end

app/models/batch.rb

def self.import(file,user)
    spreadsheet = open_spreadsheet(file)
    header = spreadsheet.row(1)
    batch = Batch.new
    batch.description = file.original_filename  <---- filename of uploaded xls .....
    batch.assembly =  <---- needs to come from angular .....
    batch.save!
    (2..spreadsheet.last_row).each do |i|
      batch_detail = BatchDetail.new
      row = Hash[[header, spreadsheet.row(i)].transpose]
      batch_detail.batch_id = batch.id
      batch_detail.chrom = row['chrom']
      batch_detail.chrom_start = row['chrom_start'].to_i
      batch_detail.chrom_end = row['chrom_end'].to_i
      batch_detail.save!
   end
  return batch
end


def self.open_spreadsheet(file)
  case File.extname(file.original_filename)
    when ".csv" then Roo::Csv.new(file.path, nil, :ignore)
    when ".xls" then Roo::Excel.new(file.path, nil, :ignore)
    when ".xlsx" then Roo::Excelx.new(file.path, nil, :ignore)
    else raise "Unknown file type: #{file.original_filename}"
  end
end

Database

create_table "batch_details", force: true do |t|
  t.integer  "batch_id"
  t.string   "chrom"
  t.integer  "chrom_start"
  t.integer  "chrom_end"
end

add_index "batch_details", ["batch_id"], name: "index_batch_details_on_batch_id", using: :btree

create_table "batches", force: true do |t|
  t.string   "status"
  t.datetime "created_at",         null: false
  t.datetime "updated_at",         null: false
  t.string   "description"
  t.string   "assembly"
end

add_index "batches", ["dataset_id"], name: "index_batches_on_dataset_id", using: :btree

Angular table row selection / select all code

updateSelected = (action, id) ->
  $scope.selected.push id  if action is "add" & $scope.selected.indexOf(id) is -1
  $scope.selected.splice $scope.selected.indexOf(id), 1  if action is "remove" and     $scope.selected.indexOf(id) isnt -1
  console.log("list of selections: #{$scope.selected}")

$scope.updateSelection = ($event, id) ->
  checkbox = $event.target
  action = ((if checkbox.checked then "add" else "remove"))
  updateSelected action, id


$scope.selectAll = ($event) ->
  checkbox = $event.target
  action = ((if checkbox.checked then "add" else "remove"))
  i = 0
  while i < $scope.assays.length
    entity = $scope.assays[i]
    updateSelected action, entity.id
    i++

$scope.getSelectedClass = (entity) ->
  (if $scope.isSelected(entity.id) then "selected" else "")

$scope.isSelected = (id) ->
  $scope.selected.indexOf(id) >= 0

In html view

        <input type="checkbox" name="selected" ng-checked="isSelected(assay.id)" ng-click="updateSelection($event, assay.id)"/>  
awarded to SonicHedgehog

Crowdsource coding tasks.

1 Solution


I have uploaded my code to GitHub: bountify-9a-spreadsheet-upload. Short instructions are included in the README.md. Note that it currently doesn’t give any kind of feedback after uploading – you can check the debug output or the SQLite database directly. I’ll try to get to that tomorrow. Let me know if you have any other suggestions.

Hi SonicHedgehog, thanks for the quick response. I am getting an error on both .xls and .xlsx. Also is it possible to direct rails to use a specific folder for the uploads instead of /var? ERROR MSG => use Roo::Excel.new to handle .xls spreadsheet files. This has Completed 500 Internal Server Error in 4ms TypeError (/var/folders/qb/gmv8nc159vnhjxq65bphfm0000gn/T/RackMultipart20140221-52927-15xk6zp is not an Excel file): app/controllers/batchescontroller.rb:20:in `new' app/controllers/batchescontroller.rb:20:in `create'
kusadasi almost 6 years ago
Would it be possible to remove all rails code from the new.html.erb template? ie: it should be html and angular only? Thanks!
kusadasi almost 6 years ago
Sorry for the problems with Excel spreadsheets, these should be fixed now. Furthermore, the form now resets on submit, shows a message after successfully uploading the file and presents a link to the (now implemented) show page. I also converted new.html.erb to HTML/Angular only. Regarding the uploads folder: You can change the temp directory by setting the environment variable TMPDIR. If you want to keep the original files, it might be better to just copy them elsewhere.
SonicHedgehog almost 6 years ago
No problem at all. Excellent work!!! Listen I will award the payment now but if you want to go for a $50 bonus, write the reverse now: user selects the rows in the table they want then clicks download, and the table data is downloaded into a xlsx spreadsheet. Let me know if you want to continue. I'll post the row selection code in the original spec.
kusadasi almost 6 years ago
Actually if I award the payment I can't easily award the bonus.... let me know if you want to work on the bonus ?
kusadasi almost 6 years ago
Definitely. Do you have any preference as to the Ruby gem to choose for writing the Excel files?
SonicHedgehog almost 6 years ago
Roo will be fine for now.
kusadasi almost 6 years ago
Roo only supports read access (write access is only available for Google spreadsheets).
SonicHedgehog almost 6 years ago
I see. How about this then - axlsx_rails
kusadasi almost 6 years ago
I’ve just updated my code. You can now select rows from the table and download a generated Excel spreadsheet. Let me know what you think.
SonicHedgehog almost 6 years ago
Great job. Just 2 things: (1) Is it possible to to structure the angular code so it is not all in one file ... ie the directive in it's own file, the controller in another and the app in one? I am learning angular so it is clearer for me that way. (2) Can you explain this code: $(document).on('ready page:load', function() { angular.bootstrap(document.body, ['spreadsheetUpload']) }) (3) I've put through the payment. Great work!
kusadasi almost 6 years ago
Thanks a lot! I have split the app up into separate files. I have added a comment explaining the manual Angular bootstrapping. It ensures that the app is properly initialized if a page is only partially loaded using Rails’ Turbolinks feature. See http://stackoverflow.com/a/15488920 for more on that. Alternatively, you could just disable Turbolinks.
SonicHedgehog almost 6 years ago
Thanks perfect .... thank you!
kusadasi almost 6 years ago
Hi SonicHedgehog... can you email me on sfcarroll@gmail.com ?? I may be able to give you some more substantial coding work.
kusadasi almost 6 years ago