Using Doc Raptor to create Excel Spreadsheets

Remember back many moons ago when we posted a previous tutorial about Excel document creation? Specifically, this one. It worked, but if you take a look at it again, that code was painful. And ugly. And required hooking up ruby with a Java library called JExcelAPI using RJB – which, really, is not something anyone actually wants to do. So today we are going to take a look at something else (that I think) is a lot nicer/cleaner for Excel document generation: a new web service specifically for document creation called Doc Raptor.

As a disclaimer, I am one of the people who wrote Doc Raptor – so I apologize if I come across as too excited about it. In all honesty, I think I’d be just as excited if someone else had created the service – because I really hated integrating and using that Java library in every app that needed to produce Excel documents.

Enough talk about that, let us dive in and see how Doc Raptor works. The example here today is going to be in Ruby (because that is what I write most of the time now), but since Doc Raptor is just a RESTful webservice, you can easily make it work with the language of your choice. The high level overview is that you create html tables, send them as a POST request to Doc Raptor, and get back an Excel document. Below, you can see the Ruby code necessary for such a request:

class Doc
  API_KEY = "YOUR_API_KEY_HERE"

  include HTTParty
  base_uri "https://docraptor.com"
 
  # :document_content => content, :name => "a doc",
  # :document_type => "xls", :test => false
  #
  # returns a string that is the document data
  def self.create(document_information)
    post("/docs", :body => {:doc => document_information},
                  :basic_auth => {:username => API_KEY})
  end
end

First off, you need to get a Doc Raptor API Key – which is easy to do, you can just go sign up for the free plan, and it will be right there on the dashboard:

Below that you see that we are including the module HTTParty. HTTParty is a ruby gem that makes dealing with http connections absurdly easy. Including the module makes it so that this Doc class can act as a nice encapsulation of communication with Doc Raptor – giving us handy methods like post. Right below the include line, you can see the call to base_uri. That is a method that we get by including HTTParty, and allows us to set what the base url we will be hitting against will be – in this case, it is “https://docraptor.com”.

Then we get to the meat of this class (although there really isn’t much to it) – the create method. This method takes the document information passed in (we will go over what is passed in in a moment), and does a POST request of that data plus the api key against “/docs” (which is really “https://docraptor.com/docs” – because of the setting of the base uri earlier). What is returned is just the response from Doc Raptor, which, if all went well, is the created document.

So what is passed into create? A hash of a couple parameters to send to Doc Raptor – :document_content, :document_type, :name, and :test. You can read about these parameters in detail in the Doc Raptor documentation, but I will go over them briefly here. :document_content is the most important – this is the string of html that Doc Raptor will use to create the document, and is what we will spend most of the rest of the tutorial creating. :document_type is used to tell Doc Raptor what type of document to create – it supports creating both Excel and PDF files – “xls” and “pdf”, respectively. In this tutorial, we are just creating Excel files, so this will be set to “xls”. :name is just a name for the document for your reference (shown on the Doc Raptor account dashboard with the date/time the document was created). And finally, :test sets if Doc Raptor creates this document in test mode – test mode allows you to create documents that don’t count against your monthly limit, but are watermarked, so it is great when figuring out how you want a document to look.

Ok, so that is a bunch of text covering how to use that create method, but who reads text? You want to see some code! So let us get to generating some content to create an Excel file from.

Since Doc Raptor takes html, we might as well use all the tools at our disposal in ruby that make creating html easy. I’m talking about haml. If you haven’t heard of haml, you should go take a look – it is a very clean and easy to use markup language that can be used to produce html. We are going to take some data and render it with a haml template, producing html that we can give to Doc Raptor. Below you can see the ruby code that does the rendering:

some_data = [2,4,1,6,3,5,18,22]

template = File.read(‘my_excel_template.haml’)
haml_engine = Haml::Engine.new(template)
rendered_template = haml_engine.render(Object.new, { :supplied_data => some_data })

We have an array of, literally, some data that we will be passing into the template. First, we open and read in the haml template (from a file called my_excel_template.haml (we will look at the contents of that file in a moment). Then we create a new haml renderer around that template. Finally, we render the template, and make the some_data variable available to be used inside the template as the variable supplied_data. The generated html is stored in the variable rendered_template.

Ok, so now we can take a look at this haml template. Haml is pretty easy to read even if you have never seen it before, but the folks at the haml website do a much better job explaining it than I ever could, so you should read through their tutorial if the markup below makes no sense.

%table{ :name => ‘My First Sheet’ }
  %tr
  %tr
    %td{ :colspan => 3,
         :style => "text-align:center;font-size:15pt;background-color:orange;" }
      Woo A Header Cell
  %tr
  %tr
    %td{ :colspan => 3, :style => "text-align:center;" }
      some subheader action
  %tr
  %tr{ :style => "background-color:#BBBBBB;border-top-style:thin;font-weight:bold;" }
    %td{ :style => "border-left-style:thin;" }
      My col data 1
    %td{ :style => "text-decoration:underline;" }
      My col data 2
    %td{ :style => "border-right-style:thin;" }
      My col data 3
  supplied_data.each do |value|
    %tr
      %td{ :style => "border-left-style:thin;" }
        = "Some text: #{value}"
      %td
        = value*value
      %td{ :style => "border-right-style:thin;" }
        Wheeee
  %tr{ :style => "border-top-style:thin;height:30;" }
    %td{ :style => "text-align:right;"}
      Sum:
    %td{ :style => "color:green;font-weight:bold;" }
      = "=SUM(B7:B#{supplied_data.count+6})"
    %td

Essentially, the code above is just describing an html table with some headers and then some rows of data generated by the data we passed in when rendering the template. So when this is rendered with that data, we get the html shown below:

<table name=‘My First Sheet’>
  <tr></tr>
  <tr>
    <td colspan=‘3’
        style=‘text-align:center;font-size:15pt;background-color:orange;’>
      Woo A Header Cell
    </td>
  </tr>
  <tr></tr>
  <tr>
    <td colspan=‘3’ style=‘text-align:center;’>
      some sub-header action
    </td>
  </tr>
  <tr></tr>
  <tr style=‘background-color:#BBBBBB;border-top-style:thin;font-weight:bold;’>
    <td style=‘border-left-style:thin;’>
      My col data 1
    </td>
    <td style=‘text-decoration:underline;’>
      My col data 2
    </td>
    <td style=‘border-right-style:thin;’>
      My col data 3
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 2
    </td>
    <td>
      4
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 4
    </td>
    <td>
      16
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 1
    </td>
    <td>
      1
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 6
    </td>
    <td>
      36
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 3
    </td>
    <td>
      9
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 5
    </td>
    <td>
      25
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 18
    </td>
    <td>
      324
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr>
    <td style=‘border-left-style:thin;’>
      Some text: 22
    </td>
    <td>
      484
    </td>
    <td style=‘border-right-style:thin;’>
      Wheeee
    </td>
  </tr>
  <tr style=‘border-top-style:thin;height:30;’>
    <td style=‘text-align:right;’>
      Sum:
    </td>
    <td style=‘color:green;font-weight:bold;’>
      =SUM(B7:B14)
    </td>
    <td></td>
  </tr>
</table>

So this is some pretty standard html, and will in fact render as a table if you throw it in a browser. There are a couple things to point out, though. The name property on the table is used by Doc Raptor to set the name of the sheet inside Excel – so in this case we will get a sheet named “My First Sheet”. Doc Raptor will also pay attention to the any style attributes to the best of its abilities – you can read the full set of supported styles in their documentation.

Ok, time to take this html and send it off:

File.open("my_excel_file.xls", "w+") do |f|
  f.write Doc.create(:document_content => rendered_template,
                     :name             => "my_excel_file.xls",
                     :document_type    => "xls",
                     :test             => true)
end

We just set those parameters like we talked about earlier, call create, and write the response to a file. Nice and simple! The resulting spreadsheet for this example looks like the following:

Pretty sweet for just making html – and a lot nicer than trying to deal with awkward java libraries. Below is the ruby code all together:

require ‘rubygems’
require ‘httparty’
require ‘haml’

class Doc
  API_KEY = "YOUR_API_KEY_HERE"

  include HTTParty
  base_uri "https://docraptor.com"
 
  # :document_content => content, :name => "a doc",
  # :document_type => "xls", :test => false
  #
  # returns a string that is the document data
  def self.create(document_information)
    post("/docs", :body => {:doc => document_information},
                  :basic_auth => {:username => API_KEY})
  end
end

some_data = [2,4,1,6,3,5,18,22]

template = File.read(‘my_excel_template.haml’)
haml_engine = Haml::Engine.new(template)
rendered_template = haml_engine.render(Object.new, { :supplied_data => some_data })

File.open("my_excel_file.xls", "w+") do |f|
  f.write Doc.create(:document_content => rendered_template,
                     :name             => "my_excel_file.xls",
                     :document_type    => "xls",
                     :test             => true)
end

And that wraps up this introduction to Doc Raptor! If ruby isn’t your thing, you can find other language examples on the examples page. If you have any questions or comments, feel free to leave them below, and I will do my best to answer them.

Leave a Reply

Your email address will not be published.