• Stars
    star
    314
  • Rank 133,353 (Top 3 %)
  • Language
    C
  • License
    MIT License
  • Created almost 8 years ago
  • Updated about 1 year ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Ultra Fast Excel Writer for Ruby

Ultra Fast Excel Writer for Ruby

require 'fast_excel'

workbook = FastExcel.open("hello_world.xlsx", constant_memory: true)
workbook.default_format.set(
  font_size: 0, # user's default
  font_family: "Arial"
)

worksheet = workbook.add_worksheet("Example Report")

bold = workbook.bold_format
worksheet.set_column(0, 0, FastExcel::DEF_COL_WIDTH, bold)

price = workbook.number_format("#,##0.00")
worksheet.set_column(1, 1, 20, price)

date_format = workbook.number_format("[$-409]m/d/yy h:mm AM/PM;@")
worksheet.set_column(2, 2, 20, date_format)

worksheet.append_row(["message", "price", "date"], bold)

for i in 1..1000
  worksheet.append_row(["Hello", (rand * 10_000_000).round(2), Time.now])
end

worksheet.append_row(["Sum", FastExcel::Formula.new("SUM(B2:B1001)")], bold)

workbook.close

See more examples

This repository and gem contain sources of libxlsxwriter

Install

# Gemfile
gem 'fast_excel'

Or

gem install fast_excel

Create Document

workbook = FastExcel.open # creates tmp file
# ...
send_data(workbook.read_string, filename: "table.xlsx") # read tmp file and delete it

Also can use workbook.remove_tmp_folder to delete tmp file manually

Constant memory mode: saves each row to disk, good for really big files but can not change previous lines that already saved

workbook = FastExcel.open(constant_memory: true)

Save to file

workbook = FastExcel.open("my_dinner.xlsx")

Write Data

FastExcel will automatically detect data type and one of write_number or write_datetime or write_formula or write_string or write_url

workbook = FastExcel.open
worksheet = workbook.add_worksheet

# write specific type value value
worksheet.write_number(row = 0, col = 5, 1_234_567, format = nil)

# write value with type detection
worksheet.write_value(row = 0, col = 5, 1_234_567, format = nil)

# write row of values. format argument can be format object or array of format objects
worksheet.write_row(row = 1, ["strong", 123_456, Time.now], format = nil)

# write row to the bottom
worksheet.append_row(["strong", 123_456, Time.now], )

# shortcut for append_row()
worksheet << ["strong", 123_456, Time.now]

Saving dates: excel store dates as number of days since 1st January 1900, and FastExcel will make it for you.

To make saving of dates slightly faster can use FastExcel.date_num helper:

date_format = workbook.number_format("[$-409]m/d/yy hh:mm;@")
worksheet.write_number(0, 0, FastExcel.date_num(Time.now, Time.zone.utc_offset), date_format)

Formulas: special type of value in excel

worksheet << [1, 2, 3, 4]
worksheet << [FastExcel::Formula.new("SUM(A1:D1)")] # A2 will be shown as 10

URL: Link to website or something else

url_format = workbook.add_format(underline: :underline_single, font_color: :blue) # format is optional
worksheet.append_row([
  FastExcel::URL.new("https://github.com/Paxa/fast_excel"),
  FastExcel::URL.new("postgres://localhost")
], url_format)
# or
worksheet.write_url(0, 2, "https://github.com/Paxa/fast_excel", url_format)

Data Formatting

format = workbook.add_format(
  bold: true,
  italic: true,
  font_outline: true,
  font_shadow: true,
  text_wrap: true,
  font_strikeout: true,
  shrink: true,
  text_justlast: true,
  font_size: 13, # default is 11, use 0 for user's default
  font_name: "Arial", # default is Calibri, also accessible via font_family
  font_color: :orange, # can use RGB hex as "#FF0000" or 0x00FF00 or color name as symbol or string
  font_script: :font_subscript,
  rotation: 10,
  underline: :underline_single, # or :underline_double or :underline_single_accounting or :underline_double_accounting
  indent: 1,
  # border styles
  border: :border_thin,
  left: :medium,
  top: :dashed,
  right: :double,
  bottom: :hair,
  bottom_color: :alice_blue,
  top_color: "#11ABCD",
  # Align
  align: {h: :align_center, v: :align_vertical_center},
  num_format: "#,##0.00"
)

Shortcuts:

workbook.bold_format # bold text
workbook.number_format("[$-409]m/d/yy h:mm AM/PM;@") # format for date

Set Column Width

worksheet.set_column(start_col, end_col, width = nil, format = nil)
# or
worksheet.set_column_width(col, width = 60)
# or
worksheet.set_columns_width(start_col, end_col, width = 60)

Set Row Height

worksheet.set_row(row_num = 0, height = 30, format = nil)

Column Auto Width

Column authwidth only works for string values, because numbers may have custom formatting

Enabling column auto widths will slow down writing string values for about 15-25%

require 'fast_excel'

workbook = FastExcel.open(constant_memory: true)

worksheet = workbook.add_worksheet
worksheet.auto_width = true

worksheet.append_row(["some text", "some longer text for example"])

content = workbook.read_string
File.open('./some_file.xlsx', 'wb') {|f| f.write(content) }

fast_excel_auto_width

API

This gem is FFI binding for libxlsxwriter C library with some syntax sugar. All original functions is avaliable, for example:

Libxlsxwriter.worksheet_activate(worksheet) # => will call void worksheet_activate(lxw_worksheet *worksheet)
# or shorter:
worksheet.activate

Full libxlsxwriter documentation: http://libxlsxwriter.github.io/

Generated rdoc: rubydoc.info/github/Paxa/fast_excel

Benchmarks

1000 rows:

Comparison:
           FastExcel:       31.7 i/s
               Axlsx:        8.0 i/s - 3.98x  slower
          write_xlsx:        6.9 i/s - 4.62x  slower

20000 rows:

Comparison:
           FastExcel:        1.4 i/s
               Axlsx:        0.4 i/s - 3.46x  slower
          write_xlsx:        0.1 i/s - 17.04x  slower

Max memory usage, generating 100k rows:

FastExcel   - 20 MB
Axlsx       - 60 MB
write_xlsx - 100 MB

More Repositories

1

postbird

Open source PostgreSQL GUI client for macOS, Linux and Windows
JavaScript
1,537
star
2

light_record

ActiveRecord extension to kick the speed of allocating ActiveRecord object
Ruby
162
star
3

green_monkey

Rails 4 & 5 microdata helpers
Ruby
54
star
4

russian_mutators

Гем для автоматического построения формы множественного числа для существительных
Ruby
22
star
5

kt

Docker image for "kt" - kafka command line tool
Shell
18
star
6

mysql.d

mysql library binding for D programming language
D
17
star
7

waithook

Rust
9
star
8

stimulus-rails

JavaScript
8
star
9

mida_vocabulary

Ruby
4
star
10

slimerjs-docker

Docker image for slimerjs + firefox, runs on alpine linux
4
star
11

semantic_data

Microdata online viewer
Ruby
3
star
12

can_write

Set permissions for folders and files
2
star
13

sass-classic-bundle

sass textmate bundle with useful shortcuts
1
star
14

vertexdb_site

Ruby
1
star
15

sunraise_test_app

simple rails (2) application for sunraise testing
Ruby
1
star
16

Boo

Boo
Ruby
1
star
17

sunraise

Super-fast and simple rails deployment
Ruby
1
star
18

HUI_name

generates form elements for accepts_nested_attributes_for in Rails
JavaScript
1
star
19

jsus

Javascript packager & dependency resolver, and G-d knows what's more
Ruby
1
star
20

sass_component

Ruby
1
star
21

server-compare

Compare remote servers configuration and save it to the git
Ruby
1
star
22

process_memory

Get process current memory usage for ruby
Ruby
1
star
23

lsd_rails

Ruby
1
star
24

httpit

HTTP sever for directory index with extra features
Ruby
1
star
25

docker-cake

Command line program to inspect docker images size
Ruby
1
star
26

pg_cluster

vagrant script to setup postgresql cluster
Shell
1
star
27

forefeed

foreigin feed reader
JavaScript
1
star
28

ruby-vertexdb

vertexdb ruby interface
Ruby
1
star