write_xlsx Ruby Gem

Instead of using Excel-style range strings, you can define chart series ranges using an array reference. The format is: [ sheetname, row_start, row_end, col_start, col_end ].

Note that when using this array format, the row and column indices are 0-based.

chart.add_series(
    name:       '=Sheet1!$C$1',
    categories: [ 'Sheet1', 1, 6, 0, 0 ],
    values:     [ 'Sheet1', 1, 6, 2, 2 ]
)

In Excel, a date or time is not a string; it is a real number combined with an Excel number format.

• The integer part of the number represents the number of days since the epoch.
• The fractional part represents the percentage of the day (time).

Warning: If you use the standard write() method with a date string (e.g., worksheet.write('A1', '02/03/04')), WriteXLSX will write it as a literal string, not a functional Excel date. To ensure Excel treats the data as a date, you must write the numeric representation and apply a num_format via a format object.

# This writes a string, NOT a date
worksheet.write('A1', '02/03/04') 

# This writes a functional date (number + format)
number = 39506.5
format = workbook.add_format(num_format: 'dd/mm/yy')
worksheet.write('A2', number, format)

In addition to standard Excel range strings (e.g., '=Sheet1!$B$2:$B$7'), add_series supports an alternative array reference format for defining ranges. This is useful for programmatic range construction.

An array reference follows this pattern: [ sheetname, row_start, row_end, col_start, col_end ].

Note: Indices are 0-based.

# Example using array references for categories and values
chart.add_series(
    name:       '=Sheet1!$C$1',
    categories: [ 'Sheet1', 1, 6, 0, 0 ],
    values:     [ 'Sheet1', 1, 6, 2, 2 ]
)

The :rotation property allows you to rotate text within a chart, which is useful for displaying large datasets (like dates) compactly.

Supported values:

• Integer range -90 to 90.
• 270: Stacked text (runs from top to bottom).
• 271: A special variant of stacked text for East Asian fonts.

chart.set_x_axis( num_font: { rotation: 45 } )

When calling add_series(), you can define data ranges using an array reference instead of Excel string notation. The format is: [ sheetname, row_start, row_end, col_start, col_end ].

Note: Row and column indices are 0-indexed in this array format.

chart.add_series(
    name:       'Doughnut sales data',
    categories: [ 'Sheet1', 1, 3, 0, 0 ], # Sheet1, rows 1-3, col 0
    values:     [ 'Sheet1', 1, 3, 1, 1 ]  # Sheet1, rows 1-3, col 1
)

WriteXLSX supports two ways to specify cell positions:

1. Row-Column notation: Uses a zero-based index for both row and column (e.g., (0, 0) for the top-left cell).
2. A1 notation: Uses standard Excel alphanumeric sequences (e.g., 'A1' for the top-left cell).

Row-column notation is ideal for programmatic loops, while A1 notation is useful for manual setup and formulas.

Note: R1C1 notation is not supported.

# Row-column notation (zero-based)
(0..9).each do |i|
  worksheet.write(i, 0, 'Hello') # Cells A1 to A10
end

# A1 notation
worksheet.write('H1', 200)
worksheet.write('H2', '=H1+1')

# Column notation in formulas
worksheet.write('A1', '=SUM(B:B)')

The maximum length for a hyperlink URL depends on the Excel version being used:

• Excel versions prior to 2015: Limited to 255 characters for both links and anchor/locations.
• Excel versions 2015 and later: Support URLs up to 2,079 characters.

Excel::Writer::XLSX versions >= 1.0.2 support the longer 2,079 character limit by default.

The write_xlsx library supports several ways to define colors for formats via the fg_color option in workbook.add_format():

1. Named Colors: Use standard strings like 'black', 'blue', 'red', 'white', etc.
2. Excel Color Palette: Use integer indices (e.g., 0x08, 0x0C) representing the built-in Excel color palette.
3. HTML Colors: Use hex strings (e.g., '#FF0000', '#FFFFFF').

# Using a named color
format_named = workbook.add_format(fg_color: 'red')

# Using an Excel palette index
format_palette = workbook.add_format(fg_color: 0x0A)

# Using an HTML hex color
format_html = workbook.add_format(fg_color: '#FF0000')