Ruby: работа с Excel (xls) и CSV с гемами spreadsheet, axlsx

В целом про csv/excel тут.

Для работы с CSV достаточно системной библиотеки CSV.

 

CSV

Работа с CSV

# открываем файл, каждая строка является массивом, каждая ячейкам – элементом массива

csv.foreach("csv.csv") do |line|
puts line.inspect
end

# если нам нужно сохранить весь файл в двумерный массив (array, в нем строки в виде отдельных array) – просто вызываем read. В последующем с ним можно работать как с обычным array – делать поиск в нем/модифицировать и т.д.

arr = CSV.read("csv.csv")

# Добавляем данные в конец array.

CSV.open("csv.csv", "a"| do |arr|
add = [ "1", "2" ]
arr.add_row(add)
end

Функция по генерации CSV

def create_csv_from_simple_arr(filename,arr)
	CSV.open("#{filename}", "w", {:col_sep => ";"}) do |csv|
		arr.each do |row|
			csv << row
		end
	end
end

Для работы с xls/xlsx первоначально использовал gem Spreadsheet. Гем самый популярный, но не сказать что функциональный – нет поддержки xlsx, нельзя включить autofilter поля, нет создания caviat table (сводные отчеты). Первые два есть в PHPExcel, поэтому гему “не зачет”. Если все это (и другое) необходимо – есть gem Axlsx, который явно больше прокачан по функционалу. Его и детально рассмотрим.

 

Axlsx

Дока

Примеры на git

Мои примеры

p = Axlsx::Package.new
p.workbook.add_worksheet(:name => 'result') do |sheet|
sheet.add_row ['row1']
sheet.add_row ['row2']
sheet.add_hyperlink :location => 'https://hlpdesk.fttb.corbina.net/shedule_crash_tt.pl?area=123', :ref => sheet.rows.first.cells.first
#sheet['A1'].color = "0000FF"
end
p.serialize('Test Excel.xlsx')

arr_data = [["link", "1", "2"], ["link", "2", "8"], ["link", "3", "9"]]
p = Axlsx::Package.new
ws = p.workbook.add_worksheet(:name => 'result') 
title = ws.styles.add_style(:bg_color => "FFE8E5E5", :sz=>14, :border=> {:style => :thin, :color => "000000"})
blue_link = ws.styles.add_style(:fg_color => '0000FF')
# header
ws.auto_filter = "A1:C1"
ws.sheet_view.pane do |pane|
pane.top_left_cell = "A2"
pane.state = :frozen_split
pane.y_split = 1
pane.x_split = 0
pane.active_pane = :bottom_right
end
ws.add_row [ "", "row1", "row2"]
# data
i=1
arr_data.each do |row|
i+=1
ws.add_row row
ws.add_hyperlink :location => "https://url.ru/#{row[1]}", :ref => "A#{i}"
ws["A#{i}"].style = blue_link
end
p.serialize('Test Excel.xlsx')

Разбор

p = Axlsx::Package.new # instance
ws = p.workbook.add_worksheet(:name => 'result')  # книги и листа одновременно (можно разделять)

ws.add_row [ "", "row1", "row2"]
ws.add_row [ "", "row1", "row2"] + arr + [some_calc_value]
sheet.add_image(:image_src => image_path)  # картинки

alg_left = wb.styles.add_style({:alignment => {:horizontal => :left}})
title = ws.styles.add_style(:bg_color => "FFE8E5E5", :sz=>14, :border=> {:style => :thin, :color => "000000"}) # header
blue_link = ws.styles.add_style(:fg_color => '0000FF') # hyperlink
ws["A#{i}"].style = blue_link # применение стиля на ячейку
sheet.add_row arr, :style => header # применение стиля на строку ячеек

ws.add_hyperlink :location => 'https://hlpdesk.fttb.corbina.net/shedule_crash_tt.pl?area=123', :ref => sheet.rows.first.cells.first
ws.add_hyperlink :location => "https://hlpdesk.fttb.corbina.net/shedule_crash_tt.pl?area=#{row[1]}", :ref => "A#{i}"

# header
ws.auto_filter = "A1:C1"
ws.sheet_view.pane do |pane|
pane.top_left_cell = "A2"
pane.state = :frozen_split
pane.y_split = 1
pane.x_split = 0
pane.active_pane = :bottom_right
end

p.serialize('Test Excel.xlsx')

 

Spreadsheet

Установка

sudo gem install spreadsheet

Код конвертации с CSV в XLS

Сделал консольный скрипт csv_to_xls.rb по конвертации CSV в XLS с настройками форматирования столбцов.

Usage:

csv_to_xls.rb test.csv test.xls

Код, основа отсюда:

#!/usr/bin/env ruby 
#coding: utf-8

require 'spreadsheet' 
require 'csv' # Встроенная библиотека, установка не обязательна

csv_path = ARGV[0] 
xls_path = ARGV[1] 
book = Spreadsheet::Workbook.new 
sheet1 = book.create_worksheet(:name => 'Sheet1') # Можно имя и не указывать

# Построчно ковертируем CSV в XLS 
CSV.open('/home/user/test.csv', 'r', {:col_sep => ";"}) do |csv| 
 csv.each_with_index do |row, i| 
  sheet1.row(i).replace(row) 
 end 
end

# sheet2[1,4] = Spreadsheet::Link.new 'www.google.com', '2017-03-13' # <чтобы не потерять> Можно сделать линк, указав [строку, ряд] ячейки

# Делаем шаблон для форматирования header
header_format = Spreadsheet::Format.new( 
 :weight => :bold, 
 :horizontal_align => :center, 
 :locked => true, 
 :text_wrap => true # Перенос по словам
 :pattern_fg_color => :silver, :pattern => 1 # Серый цвет
 #:number_format => 'YYYY.MM.DD' # Формат даты
)

# Для форматирования остальных столбцов
other_format = Spreadsheet::Format.new( 
 :text_wrap => true # perenos po slovam!
)

# Применяем формат к столбцам
sheet1.row(0).default_format = header_format 
sheet1.column(0).default_format = other_format 
sheet1.column(1).default_format = other_format 
sheet1.column(2).default_format = other_format 
sheet1.column(3).default_format = other_format 
sheet1.column(4).default_format = other_format 
sheet1.column(5).default_format = other_format 
sheet1.column(6).default_format = other_format 
sheet1.column(7).default_format = other_format 
sheet1.column(8).default_format = other_format

# Выставляем ширину столбцов
 sheet1.column(0).width = 20 
 sheet1.column(1).width = 40 
 sheet1.column(2).width = 15 
 sheet1.column(3).width = 5 
 sheet1.column(4).width = 10 
 sheet1.column(5).width = 10 
 sheet1.column(6).width = 40 

# Создаем файл
book.write('/home/user/test.xls')