ruby でExcel を読み書き

こちらでも記載しています。

---

●Excelから読み込む場合のサンプル１

require 'rubygems'
require 'spreadsheet'

xls = Spreadsheet.open('tests.xls', 'rb') #Excelファイルの読み込み
sheet = xls.worksheet(0) #0番目つまり1番始めのsheetを読み込む

#読み込みの開始が'０'から始まる。
print sheet[0,1] #実際のExcelでは、1行目の2列目の値を読み込み
print "\n"

#行単位で読み込む事も出来ます。
row = sheet.row(2) #3行目のデータを読み込み
print row[2] #3行目の3列目のデータを読み込み
print "\n"

---

●Excelから読み込む場合のサンプル２

require 'rubygems'
require 'parseexcel'
#Open the excel file passed in from the commandline
workbook = Spreadsheet::ParseExcel.parse(ARGV[0])

#Get the first worksheet
worksheet = workbook.worksheet(0)

#cycle over every row
  j=0
worksheet.each { |row|
# j=0
  i=0
  if row != nil
  #cycle over each cell in this row if it's not an empty row
  row.each { |cell|
    if cell != nil
      #Get the contents of the cell as a string
      #contents = cell.to_s('latin1')
      contents = cell.to_s('utf-8')
      puts "Row: #{j} Cell: #{i}> #{contents}"
    end
    i = i+1
  }
  end
  j = j+1
}

---

●Excelへ書き込む場合のサンプル１

$KCODE = 'utf-8'

require 'rubygems'
require 'spreadsheet'

Spreadsheet.client_encoding = 'UTF-8'

workbook = Spreadsheet::Workbook.new
worksheet = workbook.create_worksheet(:name => 'test1')

# worksheet[行番号,列番号] で書き込める
worksheet[0, 0] = "hello"
# 行を纏めて書き込むには，worksheet.row(行番号).replaceにArrayを叩き込む
worksheet.row(1).replace ["Matz", "Guido", "Larry"]
# セル内改行もできます
worksheet[2, 0] = "日本語\nです"

workbook.write('tests.xls')

---

●Excelへ書き込む場合のサンプル２

require 'rubygems'
 require 'axlsx'
#// A Simple Workbook

  p = Axlsx::Package.new
  p.workbook.add_worksheet do |sheet|
    sheet.add_row ["First", "Second", "Third"]
    sheet.add_row [1, 2, 3]
  end
  p.serialize("example1.xlsx")

---

●Excelへ書き込む場合のサンプル３

require 'rubygems'
 require 'axlsx'
p = Axlsx::Package.new
 wb = p.workbook
 black_cell = wb.styles.add_style :bg_color => "FF000000", :fg_color => "FFFFFFFF", :sz=>14, :alignment => { :horizontal=> :center }
 blue_cell = wb.styles.add_style  :bg_color => "FF0000FF", :fg_color => "FFFFFFFF", :sz=>14, :alignment => { :horizontal=> :center }
 wb.add_worksheet do |sheet|
   sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell]
   sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER
 end
 p.serialize("example4.xlsx")