Class: Scio::Excel::SimpleWorkbook
- Inherits:
-
Object
- Object
- Scio::Excel::SimpleWorkbook
- Defined in:
- lib/dm_core/scio_excel.rb
Overview
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., "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:
Instance Attribute Summary collapse
-
#columns ⇒ Object
Returns the value of attribute columns.
-
#name ⇒ Object
Returns the value of attribute name.
-
#row_height ⇒ Object
Returns the value of attribute row_height.
-
#rows ⇒ Object
Returns the value of attribute rows.
Instance Method Summary collapse
-
#create ⇒ Object
Creates the xml text of the Workbook.
-
#create_pdf(orientation = "L") ⇒ Object
attempt to create a pdf for the table.
-
#default_cell_style ⇒ Object
creates a default style for the cell.
-
#default_header_style ⇒ Object
creates a default style for the header.
-
#initialize(name) ⇒ SimpleWorkbook
constructor
A new instance of SimpleWorkbook.
-
#next_style_id ⇒ Object
:nodoc:.
Constructor Details
#initialize(name) ⇒ SimpleWorkbook
Returns a new instance of SimpleWorkbook.
123 124 125 126 127 128 129 |
# File 'lib/dm_core/scio_excel.rb', line 123 def initialize(name) @name = name @columns = [] @rows = [] @row_height = nil @style_id = 0 end |
Instance Attribute Details
#columns ⇒ Object
Returns the value of attribute columns.
118 119 120 |
# File 'lib/dm_core/scio_excel.rb', line 118 def columns @columns end |
#name ⇒ Object
Returns the value of attribute name.
118 119 120 |
# File 'lib/dm_core/scio_excel.rb', line 118 def name @name end |
#row_height ⇒ Object
Returns the value of attribute row_height.
118 119 120 |
# File 'lib/dm_core/scio_excel.rb', line 118 def row_height @row_height end |
#rows ⇒ Object
Returns the value of attribute rows.
118 119 120 |
# File 'lib/dm_core/scio_excel.rb', line 118 def rows @rows end |
Instance Method Details
#create ⇒ Object
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 |
# 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") ⇒ Object
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/)
195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 |
# 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 ⇒ Object
creates a default style for the cell. See default_header_style for documentation.
323 324 325 326 327 328 329 330 331 |
# 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 ⇒ Object
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.
309 310 311 312 313 314 315 316 317 318 319 320 |
# 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 |
#next_style_id ⇒ Object
:nodoc:
333 334 335 336 |
# File 'lib/dm_core/scio_excel.rb', line 333 def next_style_id #:nodoc: @style_id += 1 "s#{@style_id}" end |