FastXlsx

require "date"
require "fast_xlsx"

# Sample data; in an app it comes from your database.
Sale = Struct.new(:region, :product, :units, :price, :sold_on)
sales = [
  Sale.new("North", "Widget", 120, 9.5, Date.new(2026, 7, 3)),
  Sale.new("North", "Gadget", 45, 24.0, Date.new(2026, 7, 9)),
  Sale.new("South", "Widget", 80, 9.5, Date.new(2026, 7, 14)),
  Sale.new("South", "Gizmo", 12, 120.0, Date.new(2026, 7, 21))
]

# 1. A workbook with one worksheet.
wb = FastXlsx::Workbook.new
ws = wb.add_worksheet("Sales")

# 2. A header row, bold on a light blue fill.
header = FastXlsx::Format.new(bold: true, bg_color: "#DDEBF7", border_bottom: :thin)
ws.append(["Region", "Product", "Units", "Price", "Sold on", "Revenue"], format: header)

# 3. One row per sale. Dates show as yyyy-mm-dd on their own; a formula is a
#    FastXlsx::Formula. In formulas rows count from 1, so the first sale is row 2.
money = FastXlsx::Format.new(num_format: "#,##0.00")
sales.each.with_index(2) do |sale, row|
  ws.append([sale.region, sale.product, sale.units, sale.price, sale.sold_on,
             FastXlsx::Formula.new("C#{row}*D#{row}")],
            format: [nil, nil, nil, money, nil, money])
end

# 4. A totals row: the header's look, with the money format added.
last = sales.size + 1
ws.append(["Total", nil, FastXlsx::Formula.new("SUM(C2:C#{last})"), nil, nil,
           FastXlsx::Formula.new("SUM(F2:F#{last})")],
          format: [header, header, header, header, header, header.merge(num_format: "#,##0.00")])

# 5. Easier to read: columns sized to fit (set by hand where autofit can't tell), the header kept in view while
#    scrolling, and filter buttons on the header (rows and columns count from 0).
ws.autofit
ws.column_width(3..5, 12) # money, dates, formulas: autofit measures raw values
ws.freeze_panes("A2")
ws.autofilter("A1:F#{last}")

# 6. Save it. (In Rails: send_data wb.to_xlsx, filename: "sales.xlsx", ...)
wb.save("sales.xlsx")

require "fast_xlsx"

wb = FastXlsx::Workbook.new                       # or memory: :constant / :low, see below
ws = wb.add_worksheet("Report")                  # later: wb.worksheet("Report"), wb.worksheets

ws << ["id", "name", "created_at"]                # append a row
ws.concat(records.map { |r| [r.id, r.name, r.created_at] })  # append many rows in one call
ws.write(0, 5, 42)                                # write a single cell (row, col, value)
ws.write("F1", 42)                                # or by its Excel reference
ws.write("F1", 42, { bold: true })                # a format positionally, or as format: like append
ws["F1"] = 42                                     # the same (ws[0, 5] = 42 too)

wb.properties(title: "Q3 report", author: "Zac", keywords: "Confidential") # File > Info in Excel
wb.save("report.xlsx")                            # or wb.to_xlsx => binary String

wb = FastXlsx::Workbook.new do |wb|
  wb.add_worksheet("Users") do |ws|
    ws.append(%w[id name email], format: { bold: true })
    User.find_each { |u| ws << u.attributes.values_at("id", "name", "email") } # a Hash's values in column order
  end
end

def export
  wb = FastXlsx::Workbook.new(memory: :constant) # large exports: memory stays flat
  ws = wb.add_worksheet("Orders")
  ws.append(["Order", "Customer", "Total", "Placed at"], format: { bold: true })
  Order.includes(:customer).find_each { |o| ws << [o.number, o.customer.name, o.total, o.created_at] }
  send_data wb.to_xlsx, filename: "orders.xlsx",
                        type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
end

FastXlsx::Workbook.new                          # memory: :standard (default): everything in memory, any write order
FastXlsx::Workbook.new(memory: :constant)       # rows on disk, strings stored inline in each cell
FastXlsx::Workbook.new(memory: :low)            # rows on disk, strings in Excel's shared string table

header = FastXlsx::Format.new(bold: true, bg_color: "#DDEBF7", border_bottom: :thin, align: :center)
date   = FastXlsx::Format.new(num_format: "yyyy-mm-dd")

ws.append(["id", "name", "created_at"], format: header)  # format every cell in the row
ws.append([1, "a", Time.now], format: [nil, nil, date]) # or one format (or nil) per cell
ws.write(1, 2, Date.today, date)                         # format one cell

ws.append(["Total", 1_234], format: { bold: true })
title = header.merge(font_size: 16)   # header's options plus font_size; header.to_h lists them

ws.column_width(0, 20)        # column A, width in characters
ws.column_width(1..3, 12)     # columns B–D
ws.column_format(4, FastXlsx::Format.new(num_format: "#,##0.00")) # default for cells in E written without a format
ws.autofit                        # size other columns to the data written so far; set widths are kept
ws.autofilter("A1:D101")          # filter buttons on A1:D101

ws.zoom(150)                # 10..400 percent
ws.tab_color("#C00000")
ws.hide_gridlines
ws.activate                 # Excel opens on this sheet (un-hides it if hidden)
ws.selection("B2:C3")       # the cells selected when it opens
ws.top_left_cell("A50")     # and the cell scrolled to the top left
ws.ignore_error("A2:A1000", :number_stored_as_text) # no green triangles (one kind per range; don't overlap ranges)
other.hide                  # the sheet Excel opens on can't be hidden: activate another one first

ws.page_setup(landscape: true, paper: :a4,  # or :letter, :legal, :tabloid, :a3, :a5, Excel's paper number, 0 = printer default
              fit_width: 1,                 # 1 page wide, as many pages tall as needed
              repeat_rows: 0,               # print the header row on every page (an index or a Range)
              print_area: "A1:D100",        # any cell range style
              gridlines: true)              # print the gridlines

ws.autofilter(0, 0, 100, 3)   # four 0-based numbers: first_row, first_col, last_row, last_col
ws.autofilter("A1:D101")      # an Excel reference; "$A$1:$D$101" and a single "B2" work too
ws.autofilter(0..100, 0..3)   # rows and columns, each an Integer or a Range
ws.merge_range(0, 0..3, "Q3 report", title) # row 1, columns A–D

ws.freeze_panes(1, 0)                          # keep the first row visible while scrolling
ws.row_height(0, 30)                       # row 1, height in points
ws.default_row_height(18)                  # every other row; call it before row_format, hide_rows, group_rows
ws.row_format(0, header)                   # default for cells in row 1 written without a format
ws.hide_rows(10..20)                       # also hide_columns(3), an index or a Range
ws.merge_range(0, 0, 0, 3, "Q3 report", title) # merge A1:D1; the value can be any cell type
ws.page_breaks([50, 100])                  # print a new page before rows 51 and 101
ws.vertical_page_breaks([8])               # and before column I
ws.page_header("&CPage &P of &N")               # printed header, Excel header/footer codes
ws.page_footer("&L&A", margin: 0.2)             # sheet name on the left; margin in inches
ws.margins(left: 0.5, top: 1)              # other margins keep Excel's defaults

ws.group_rows(1..10)                     # rows 2–11 get an expand/collapse button
ws.group_rows(1..4)                      # grouping again nests them (up to 7 levels)
ws.group_columns(2..3, collapsed: true)  # columns C–D, collapsed until expanded

wb.define_name("Rate", "=0.96")                      # workbook-wide; use as =A1*Rate
wb.define_name("Report!Sales", "=Report!$B$2:$B$13") # only on the Report sheet

input = FastXlsx::Format.new(locked: false)
ws.write(1, 1, 0, input)                                  # B2 stays editable
ws.protect                                                # lock everything else
ws.protect(password: "secret", allow: %i[sort use_autofilter]) # or with a password and allowed actions
ws.unprotect_range("B2:D10", name: "Inputs")               # a range users can still edit (optional password:)

red = FastXlsx::Format.new(font_color: "#9C0006", bg_color: "#FFC7CE")

# rows 1–100 of column B (first_row, first_col, last_row, last_col)
ws.conditional_format(0, 1, 99, 1, type: :cell, criteria: :<, value: 0, format: red)
ws.conditional_format(0, 1, 99, 1, type: :cell, criteria: :between, value: [1, 10], format: red)
ws.conditional_format(0, 0, 99, 0, type: :text, criteria: :contains, value: "error", format: red)
ws.conditional_format(0, 0, 99, 3, type: :formula, value: "=$D1>100", format: red)
ws.conditional_format(0, 2, 99, 2, type: :data_bar)
ws.conditional_format(0, 2, 99, 2, type: :color_scale)            # 3-color; colors: 2 for 2-color

ws.data_validation(1, 2, 100, 2, type: :list, value: %w[Open Closed])     # dropdown in C2:C101
ws.data_validation(1, 2, 100, 2, type: :list, value: "=$Z$1:$Z$10")       # dropdown from a range
ws.data_validation(1, 3, 100, 3, type: :whole_number, criteria: :between, value: [1, 10],
                   input_title: "Quantity", input_message: "1 to 10",
                   error_title: "Invalid", error_message: "Enter a whole number from 1 to 10")

ws.write_comment(0, 0, "Checked by finance", author: "Zac")   # Excel shows it as a note on A1

ws.insert_image(0, 0, "logo.png")                                  # top-left corner in A1
ws.insert_image(0, 5, StringIO.new(blob.download), scale: 0.5, x_offset: 10, y_offset: 4, alt_text: "Logo")
ws.insert_image(10, 0, "chart.png", width: 320, height: 180)       # pixel size; one of them keeps the aspect ratio

ws.concat([%w[Region Rep Sales], *sales])            # header row, then the data
ws.add_table(0, 0, sales.size + 1, 2, total_row: true, style: :medium2, # +1 row for the totals
             columns: [{ header: "Region", total_label: "Total" }, "Rep", { header: "Sales", total: :sum }])

ws.concat([%w[Month Sales Costs], ["Jan", 10, 7], ["Feb", 25, 12], ["Mar", 18, 11]])

ws.insert_chart(1, 4, type: :column,
                series: [
                  { name: "Sales", categories: "Sheet1!$A$2:$A$4", values: "Sheet1!$B$2:$B$4" },
                  { name: "Costs", categories: "Sheet1!$A$2:$A$4", values: "Sheet1!$C$2:$C$4" }
                ],
                title: "Q1", x_axis: "Month", y_axis: "Amount", width: 600, height: 360)