xlsx_writer
Writes (doesn't read or modify) XLSX files.
Imposes (non-customizable) a certain style: Arial 10pt, left-aligned text and dates, right-aligned numbers and currency
Optionally adds an autofilter.
Credit
Based on the original simple_xlsx_writer gem and patches by mumboe
Changes from simple_xlsx_writer (caution opinionated)
Then I tore it down and rebuilt it:
- no longer constructs everything in a single zipstream... instead writes the individual files to /tmp and then zips them together
- absolute minimum XML - went through every line, testing to see if I could remove it
- no more block format - this was more appropriate when it was constructed as a zipstream
Features not present in simple_xlsx_writer:
- opinionated, non-customizable styles - Arial 10pt, left-aligned text and dates, right-aligned numbers and currency
- autofilter based on a cell range
- header and footer, with support for images (.emf only) and page numbers
- fits columns to text
Wishlist
- real documentation
- real tests
Corporate support
We use xlsx_writer for marketing analytics at Faraday.
Example
require 'xlsx_writer'
doc = XlsxWriter.new
# show TRUE for true but a blank cell instead of FALSE
doc.quiet_booleans!
sheet1 = doc.add_sheet("People")
# freeze pane underneath the first (header) row
sheet1.freeze_top_left = 'A2'
# DATA
sheet1.add_row([
"DoB",
"Name",
"Occupation",
"Salary",
"Citations",
"Average citations per paper"
])
sheet1.add_row([
Date.parse("July 31, 1912"),
"Milton Friedman",
"Economist / Statistician",
{:type => :Currency, :value => 10_000},
500_000,
0.31
])
sheet1.add_autofilter 'A1:E1'
# FORMATTING
doc.page_setup.top = 1.5
doc.header.right.contents = 'Corporate Reporting'
doc.footer.left.contents = 'Confidential'
doc.footer.right.contents = :page_x_of_y
# if you really need images in header/footer: do it in Excel, save, unzip the xlsx... get the .emf files, "cropleft" (if necessary), etc. from there
left_header_image = doc.add_image('image1.emf', 118, 107)
left_header_image.croptop = '11025f'
left_header_image.cropleft = '9997f'
center_footer_image = doc.add_image('image2.emf', 116, 36)
doc.header.left.contents = left_header_image
doc.footer.center.contents = [ 'Powered by ', center_footer_image ]
doc.page_setup.header = 0
doc.page_setup.footer = 0
# OUTPUT
# You should move the file to where you want it
require 'fileutils'
::FileUtils.mv doc.path, 'myfile.xlsx'
# don't forget
doc.cleanup
Copyright
Copyright (c) 2014 Dee Zsombor, Justin Beck, Seamus Abshere. See LICENSE for details.