Sample to create PDF from Excel with Ruby

background

Nowadays, it is common to create a program that extracts and organizes list-format information written in EXCEL and outputs it as PDF in form format. Before I forget, I want to describe the essence of the program.

Program overview

Read the list listed in EXCEL Create the following PDF files for the number of people in the list at once.

Click here for GitHub, a set of execution environment

Development environment

• macOS Big Sur
• Ruby 2.6.5

procedure

1. EXCEL list
2. Background image
3. Japanese font
4. Create a project
5. Program code creation

1. EXCEL list

Create the following EXCEL file

--Filename and bus ./infiles/members.xlsx

--Sheet name 2021 message

Sample name created with gem gimei

2. Background image

Prepare an image file with the following name ./infiles/background.png

サンプル(筆者撮影)

3. Japanese font

Download Japanese fonts for PDF output Put it in ./infiles/ipam.ttf

4. Project creation

Use bundler (fixed version, not required)

Gemfile

% bundle init

Add the following to the generated Gemfile

Gemfile

gem "roo", "~> 2.8.0"
gem "prawn", "~> 2.3.0"

Install below

% bundle install

Gemfile.lock is created

5. Program code creation

cows.rb

#Collect gems in Gemfile
require 'bundler'
Bundler.require

#OPEN EXCEL file
excel_path = './infiles/members.xlsx'
book = Roo::Spreadsheet.open(excel_path)

#Specify the sheet name(Not required)
sheet = book.sheet('2021 message')

#Confirmation of the last column and last row that exist
puts "Last column number: #{sheet.last_column}Last line number: #{sheet.last_row}"
# 'B3'Sheet to refer to a cell.cell(3, 2) or sheet.cell(3, 'B')

#Loop in the number of rows of the sheet. First line skipped as header
(2..sheet.last_row).each do |idx|

  #PDF initialization
  # A6(Postcard size)Sideways, no border
  pdf = Prawn::Document.new(
    page_size: "A6", page_layout: :landscape,
    top_margin: 0, bottom_margin: 0, left_margin: 0, right_margin: 0)

  #Loading Japanese fonts
  pdf.font "./infiles/ipam.ttf"

  #Background image Placed without borders
  pdf.image("./infiles/background.png ", at: [0,pdf.cursor])

  #Define drawing area
  pdf.bounding_box([10, pdf.cursor-10], width: 500) do
    # 'B3'Sheet to refer to a cell.cell(3, 2) or sheet.cell(3, 'B')

    #name
    pdf.font_size(20)
    sei = sheet.cell(idx, "A")
    mei = sheet.cell(idx, "B")
    pdf.text "#{sei} #{mei}Mr.", color: "ffffff"

    #Akeome
    pdf.move_down 15
    pdf.font_size(24)
    greet = "happy New Year"
    pdf.text greet, color: "ffffff"

    #message
    pdf.move_down 15
    pdf.font_size(14)
    message = sheet.cell(idx, "E")
    pdf.text message, color: "c9dfed"

    #check
    puts  " -- [#{idx}] #{sei} #{mei}Mr. msg: #{message}"
  end

  #Cow
  pdf.bounding_box([165, pdf.cursor-80], width: 500) do
    pdf.font_size(80)
    pdf.text "Year of ox",  color: "ff75b1"
  end

  #PDF save file name"outfiles/last_first.pdf"
  file_name = "./outfiles/#{sheet.cell(idx,"C")}_#{sheet.cell(idx,"D")}.pdf"
  pdf.render_file(file_name)

end

Folder structure

├── Gemfile
├── Gemfile.lock
├── cows.rb (Program code)
├── infiles
│   ├── background.png (Background image file)
│   ├── ipam.ttf (Japanese font)
│   └── members.xlsx (List file)
└── outfiles
    ├── hara_suzuka.pdf (Created PDF)
    ├── .....
    └── .....

When ruby ​​is executed below, pdf is created under ./outfies/

% ruby cows.rb

Reference site

https://github.com/roo-rb/roo https://github.com/prawnpdf/prawn https://prawnpdf.org/manual.pdf https://moji.or.jp/ipafont/ipaex00401/ https://github.com/willnet/gimei