Cell Format Options

Some export formats supports adding format to the cell. The cell format options can be set into the column, in the controller, with export_options setter, or returned by the helper override. As different export formats are supported, but not all formats will support the same options for cell formatting, export_options must be a nested Hash, with export format as key, and cell format options Hash as value. The helper override must return the cell format options for the requested export format, as they have format argument.

CSV doesn't support cell formatting, so any format returned by helpers will be ignored, and formats set in export_options[:csv] too.

Header styles

The styles for header can be defined in the helper, overriding export_column_header_style, with gets two arguments: column and format. It returns {sz: 11, b: true, bg_color: "69B5EF", fg_color: "FF", alignment: {horizontal: :center}} for :xlsx format by default.

If the style for the header includes :width, then it will be used for the column's width instead of the width defined in export_options[format].

  def export_column_header_style(column, format)
    if format == :xlsx
      styles = super
      if column.name.in? %i[comments]
        styles.merge(alignment: {wrapText: 'true'})
      else
        styles
      end
    else
      super
    end
  end

If the export_column_header_style is overrided and calls super to extend the default options, it must use merge, don't use [:key] = setter or merge!, as the default header style is cached with an instance variable, so it's shared by all columns.

Options per format

XLSX

The format options supported are the ones supported by add_style method of Caxlsx gem. Also, :width is supported only in export_options of the column, and export_column_header_style helper, not in column override or export UI. If no width is defined for a column, :auto is used.

As different export formats are supported, but not all formats will support the same options for cell formatting, export_options must be a nested Hash, with export format as key, and cell format options Hash as value.

conf.columns['actions'].export_options = {
      xlsx: {
        width: 50,
        alignment: {wrapText: 'true'},
        fg_color: 'FF000000',
        bg_color: 'FFFFFFED'
      }
    }

After all data has been added to the worksheet, customize_xlsx controller method is called to allow any other customization, such as changing column widths, adding charts, and so on. The method receives one argument, the Axlsx::Package instance.

It's possible to customize the tab name in the worksheet, overriding worksheet_name method in the controller, it defaults to active_scaffold_config.label, replacing forbidden characters with '-', and truncating the name to 31 characters, the maximum allowed by MS Excel, using the default options for truncate rails method. It's possible to override these defaults overriding the method and calling super with options hash, the options hash will be used to call truncate, and :replace option will be used to replace forbidden characters.

def worksheet_name(options = {})
  super replace: '_', omission: '' # replace forbidden characters with _, don't add suffix when truncating
end