class Scio::Excel::SimpleWorkbook

SimpleWorkbook

This is a one-sheet workbook. Really simple. It allows you to create an excel workbook from an array of hashes

Example 1

# first, create the styles (You should check the default styles)
st1 = Scio::Excel::SimpleStyle.new(:text => {:valign => "Top"},
                                   :borders => Scio::Excel::BORDER_ALL)
st2 = Scio::Excel::SimpleStyle.new(:text => {:valign => "Top", :wrap => true},
                                   :borders => Scio::Excel::BORDER_ALL)
st3 = Scio::Excel::SimpleStyle.new(:borders => Scio::Excel::BORDER_ALL,
                                   :bgcolor => "#666699",
                                   :font => {:bold => true, :color => "#ffffff"})

# now, create the columns. The order of appearance is the order in which you push them
# to the array.
columns = []
columns << Scio::Excel::Column.new("Birthday", :width => 76.5, :cell_style => st1, :header_style => st3)
columns << Scio::Excel::Column.new("Name", :width => 276.75, :cell_style => st1, :header_style => st3)

# next, create the data array
rows = User.all.collect {|u| {"Birthday" => u.bday.strftime("%d/%m/%Y"), "Name" => u.name}}

# create the workbook
wb = Scio::Excel::SimpleWorkbook.new("User's birthday List")
wb.columns = columns
wb.rows = rows

# finally, send the workbook to the browser
headers['Content-type'] = "application/vnd.ms-excel"
render_text(e.create)

Example 2 (Default Styles & PDF Output)

(needs fpdf for the PDF output)

wb = Scio::Excel::SimpleWorkbook.new("test de excel")

# create styles
sth = Scio::Excel::SimpleStyle.new
sth.text[:halign] = "Center"
sth.font[:bold] = true
sth.bgcolor = "#CCFFCC"
sth.borders = Scio::Excel::BORDER_TOP | Scio::Excel::BORDER_BOTTOM
stc = wb.default_cell_style
stc.font[:italic] = true
stc.text[:halign] = "Left"

# create the columns
columns = Array.new
columns << Scio::Excel::Column.new("Nombre Cliente", :width => 150, :header_style => sth)
columns << Scio::Excel::Column.new("R.U.T", :width => 40, :header_style => sth)

# crear the data
rows = Cliente.order('razon_social').collect {|c|
  {"Nombre Cliente" => c.razon_social, "R.U.T" => c.rut}
}

# set columns and rows
wb.columns = columns
wb.rows = rows
send_data wb.create_pdf, :filename => "something.pdf", :type => "application/pdf"

Attributes

columns

array of Column

rows

array of Hashes, each one representing a row. The key for the Hash must be the name of the corresponding column.

name

name of the workbook. This will be the name of the single worksheet.

row_height

set this to the height of the data rows.

Inspiration

This library was inspired by the Excel Export Plugin:

www.napcsweb.com/blog/2006/02/10/excel-plugin-10

Attributes

columns[RW]
name[RW]
row_height[RW]
rows[RW]

Public Class Methods

new(name) click to toggle source
# File lib/dm_core/scio_excel.rb, line 123
def initialize(name)
  @name       = name
  @columns    = []
  @rows       = []
  @row_height = nil
  @style_id   = 0
end

Public Instance Methods

create() click to toggle source

Creates the xml text of the Workbook. You can send it to the browser using render_text. You might need to set the HTTP headers before.

Example

wb = Scio::Excel::Workbook.new("list")
...
render_text(wb.create)
# File lib/dm_core/scio_excel.rb, line 140
def create
  buffer = ""
  xml = Builder::XmlMarkup.new(:target => buffer, :indent => 2)
  xml.instruct! :xml, :version => "1.0", :encoding => "UTF-8"
  xml.Workbook({
    'xmlns'      => "urn:schemas-microsoft-com:office:spreadsheet", 
    'xmlns:o'    => "urn:schemas-microsoft-com:office:office",
    'xmlns:x'    => "urn:schemas-microsoft-com:office:excel",    
    'xmlns:html' => "http://www.w3.org/TR/REC-html40",
    'xmlns:ss'   => "urn:schemas-microsoft-com:office:spreadsheet" 
  }) do
    # add styles to the workbook
    styles = []
    @columns.each do |c|
      # use the default style if none set
      hstyle = (c.header_style.nil?) ? default_header_style : c.header_style
      cstyle = (c.cell_style.nil?) ? default_cell_style : c.cell_style
      # set the style, in case it's the default one
      c.header_style = hstyle
      c.cell_style = cstyle
      styles << hstyle unless styles.include?(hstyle)
      styles << cstyle unless styles.include?(cstyle)
    end
    xml.Styles do
      xml.Style 'ss:ID' => 'Default', 'ss:Name' => 'Normal' do
        xml.Alignment 'ss:Vertical' => 'Bottom'
        xml.Borders
        xml.Font 'ss:FontName' => 'Arial'
        xml.Interior
        xml.NumberFormat
        xml.Protection
      end
      styles.each do |s|
        xml << s.create(self)
      end
    end
    xml << createWorksheet(@name)
    # reset the styles-id
    @style_id = 0
  end # Workbook
end
create_pdf(orientation = "L") click to toggle source

attempt to create a pdf for the table.

Example

wb = Scio::Excel::Workbook.new("customers")
...
send_data wb.create_pdf, :filename => "something.pdf", :type => "application/pdf"

One thing to note is that you must specify a width for each column, and that the units for the columns are different than those for excel (i.e: specify smaller numbers).

This requires fpdf (zeropluszero.com/software/fpdf/)

# File lib/dm_core/scio_excel.rb, line 195
def create_pdf(orientation = "L")
  require 'fpdf'
  raise "Invalid orientation" if !["L","P"].include?(orientation)
  pdf = FPDF.new
  # default font
  pdf.SetFont('Arial', '', 14)
  pdf.AddPage(orientation)
  # first, create the headers
  @columns.each do |c|
    # set the style
    hstyle = (c.header_style.nil?) ? default_header_style : c.header_style
    rgb = hstyle.bgcolor.to_rgb rescue nil
    fill = 0
    unless rgb.nil?
      pdf.SetFillColor(rgb[:red], rgb[:green], rgb[:blue])
      fill = 1
    end
    rgb = hstyle.font[:color].to_rgb rescue nil
    pdf.SetTextColor(rgb[:red], rgb[:green], rgb[:blue]) unless rgb.nil?
    fstyle = String.new
    fstyle << "B" if hstyle.font[:bold]
    fstyle << "I" if hstyle.font[:italic]
    pdf.SetFont('',fstyle)
    border = 0
    if hstyle.borders > 0
      if hstyle.borders == BORDER_ALL
        border = 1
      else
        border = String.new
        border << "T" if hstyle.borders & BORDER_TOP > 0
        border << "B" if hstyle.borders & BORDER_BOTTOM > 0
        border << "L" if hstyle.borders & BORDER_LEFT > 0
        border << "R" if hstyle.borders & BORDER_RIGHT > 0
      end
    end
    align = "C"
    unless hstyle.text[:align].nil?
      align = "L" if hstyle.text[:halign] == "Left"
      align = "R" if hstyle.text[:halign] == "Right"
    end
    # draw the cells
    pdf.Cell(c.width, 7, c.name, border, 0, align, fill)
  end # headers
  pdf.Ln

  # reset the styles
  pdf.SetTextColor(0,0,0)
  pdf.SetFont('','')

  # draw the rows
  @rows.each do |r|
    # set the style
    @columns.each do |c|
      cstyle = (c.cell_style.nil?) ? default_cell_style : c.cell_style
      rgb = cstyle.bgcolor.to_rgb rescue nil
      fill = 0
      unless rgb.nil?
        pdf.SetFillColor(rgb[:red], rgb[:green], rgb[:blue])
        fill = 1
      end
      rgb = cstyle.font[:color].to_rgb rescue nil
      pdf.SetTextColor(rgb[:red], rgb[:green], rgb[:blue]) unless rgb.nil?
      fstyle = String.new
      fstyle << "B" if cstyle.font[:bold]
      fstyle << "I" if cstyle.font[:italic]
      pdf.SetFont('',fstyle)
      border = 0
      if cstyle.borders > 0
        if cstyle.borders == BORDER_ALL
          border = 1
        else
          border = String.new
          border << "T" if cstyle.borders & BORDER_TOP > 0
          border << "B" if cstyle.borders & BORDER_BOTTOM > 0
          border << "L" if cstyle.borders & BORDER_LEFT > 0
          border << "R" if cstyle.borders & BORDER_RIGHT > 0
        end
      end
      align = "C"
      unless cstyle.text[:align].nil?
        align = "L" if cstyle.text[:halign] == "Left"
        align = "R" if cstyle.text[:halign] == "Right"
      end
      pdf.Cell(c.width, 7, r[c.name], border, 0, align, fill)
    end # columns
    pdf.Ln
  end # rows

  # salida
  pdf.Output
end
default_cell_style() click to toggle source

creates a default style for the cell. See default_header_style for documentation.

# File lib/dm_core/scio_excel.rb, line 323
def default_cell_style
  if @default_cell_style.nil?
    st = SimpleStyle.new
    st.borders = BORDER_ALL
    st.text[:valign] = "Center"
    @default_cell_style = st
  end
  @default_cell_style
end
default_header_style() click to toggle source

creates a default style for the header. This is used in case you don't set a style for the column. If you really want a “plain” style, create a column with an empty one:

nst = Scio::Excel::SimpleStyle.new
col = Scio::Excel::Column.new("name", :header_style => nst)

Since the default style is an instance variable, you don't need to set the style for a column if you modify it.

Example

wb = Scio::Excel::SimpleWorkbook.new("user list")
sth = wb.default_header_style
sth.bgcolor = "#CCFFCC"
columns = Array.new
# when rendering, the columns will use the default header style, but with a
# bgcolor = #CCFFCC.
columns << Scio::Excel::Column.new("User Name", :width => 276.75)
columns << Scio::Excel::Column.new("Birthday", :width => 76.5)

The same applies for the default_cell_style.

# File lib/dm_core/scio_excel.rb, line 309
def default_header_style
  if @default_header_style.nil?
    st = SimpleStyle.new
    st.font[:bold] = true
    st.font[:color] = "#FFFFFF"
    st.borders = BORDER_ALL
    st.text[:halign] = "Center"
    st.bgcolor = "#666699"
    @default_header_style = st
  end
  @default_header_style
end