Export Rails schema to xlsx

export_schema.rake

# frozen_string_literal: true

if Rails.env.development?
  require 'export_schema'

  namespace :export_schema do
    desc 'Export schema'
    task to_excel: :environment do
      ExportSchema.to_excel
    end
  end

  namespace :db do
    task :migrate do
      Rake::Task['export_schema:to_excel'].invoke
    end

    namespace :migrate do
      [:change, :up, :down, :reset, :redo].each do |t|
        task t do
          Rake::Task['export_schema:to_excel'].invoke
        end
      end
    end
  end
end

export_schema.rb

# frozen_string_literal: true

require 'caxlsx'

class ExportSchema
  def self.to_excel
    Axlsx::Package.new do |p|
      p.workbook do |wb|
        header_style = wb.styles.add_style(
          font_name: '游ゴシック',
          b: true,
          sz: 12,
          alignment: { horizontal: :center },
          bg_color: '00B0F0',
          fg_color: 'FFFFFF'
        )
        odd_row_format = wb.styles.add_style(
          font_name: '游ゴシック',
          sz: 12,
          alignment: { horizontal: :left }
        )
        even_row_format = wb.styles.add_style(
          font_name: '游ゴシック',
          sz: 12,
          alignment: { horizontal: :left },
          bg_color: 'F2F2F2'
        )

        columns = [
          { label: 'カラム名', width: 30 },
          { label: 'データ型', width: 15 },
          { label: 'NULL許可', width: 10 },
          { label: 'デフォルト値', width: 30 },
          { label: 'PK', width: 15 },
          { label: 'FK', width: 15 },
          { label: 'コメント', width: 100 }
        ]

        ActiveRecord::Base.connection.tables.each do |table_name|
          next if %w[schema_migrations ar_internal_metadata].include?(table_name)

          wb.add_worksheet(name: table_name) do |sheet|
            header = columns.map { |column| column[:label] }
            sheet.add_row header, style: header_style
            columns.each_with_index do |column, index|
              sheet.column_info[index].width = column[:width]
            end

            primary_key = ActiveRecord::Base.connection.primary_key(table_name)
            foreign_keys = ActiveRecord::Base.connection.foreign_keys(table_name).map(&:column)

            ActiveRecord::Base.connection.columns(table_name).each_with_index do |column, index|
              row_style = index.even? ? even_row_format : odd_row_format
              sheet.add_row [
                column.name,
                column.type.to_s,
                column.null,
                column.default,
                column.name == primary_key,
                foreign_keys.include?(column.name),
                column.comment
              ], style: row_style
            end
          end
        end
      end

      p.serialize('db/schema.xlsx')
    end
  end
end

Gemfile

# frozen_string_literal: true

source 'https://rubygems.org'

git_source(:github) { |repo| "https://github.com/#{repo}.git" }

ruby '3.1.2'

group :development do
  gem 'caxlsx'
end