FastXlsx

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

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

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.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)
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.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

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)