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:
Attributes
Public Class Methods
# 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
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
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
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
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