読者です 読者をやめる 読者になる 読者になる

不器用(TOT) エンジニアの気ままにプログラミング

~考え、作って、また考える~

RubyでExcel操作メモ_010_ファイルの書き換え

RubyでExcel操作

ファイルの一部の文字列をExcelファイルから取得した

値で置き換える処理をメモしておく。 

 

<前提条件>

チェック処理等を省いているので、

・ファイルが用意され、開かれていないこと。

Excelファイル内にA〜K列まで1行目から入力値があること

を前提条件としています。

 

<主な処理の流れ>

処理の概要としては

==============================

・置き換えファイルの準備

 (copy.txtを元にpaste.txtを作成)

  ↓

Excelファイルから値を取得

  ↓

・置き換えファイルの更新

 (paste.txtを更新)

==============================

となります。

 

#! ruby -Ks
require 'win32ole'   #Excelデータの取得に使用
require 'fileutils'  #ファイルのコピーに使用
require 'kconv'      #UTF-8への変換に使用

#########################################################
#
# ファイル編集用クラス
#
#########################################################
class FileCls

    #----------------------------------------
    # クラス定数
    #----------------------------------------
    COPYFILENAME       = 'C:\\rb\\copy.txt'  #コピー元のファイル名
    PASTEFILENAME      = 'C:\\rb\\past.txt'  #コピー先のファイル名
    CHKFILENAME        = 'C:\\rb\\list.xlsx' #データ取得するExcelファイル名
    CHKSHTNAME         = 'TESTSHEET'         #データ取得するExcelシート名
   
    #----------------------------------------
    # クラス変数
    #----------------------------------------
    @@aryData     =
    @@intRow      = 1
   
    #----------------------------------------
    #  ExcelのセルNo取得用
    #----------------------------------------
    module Col
        COLA  = 1
        COLB  = 2
        COLC  = 3
        COLD  = 4
        COLE  = 5
        COLF  = 6
        COLG  = 7
        COLH  = 8
        COLI  = 9
        COLJ  = 10
        COLK  = 11
    end
   
    #----------------------------------------
    # 配列のインデックス取得
    # [Param]
    #   col:module「Col」の値を想定
    # [Return]
    #   デクリメントした値
    #----------------------------------------
    def self.getidx(col)
        retval = col - 1
        return retval
    end
   
    #----------------------------------------
    # Excelのセルデータ取得
    # [Param]
    #   book:取得元のExcelファイル
    # [Return]
    #   配列化したExcelデータ
    #----------------------------------------
    def self.getary(book)
        while book.sheets(CHKSHTNAME).Cells(@@intRow,1).Value != nil  do
            ary =

            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLA ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLB ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLC ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLD ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLE ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLF ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLG ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLH ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLI ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLJ ).Value
            ary << book.sheets(CHKSHTNAME).Cells(@@intRow,Col::COLK ).Value
            @@aryData << ary
            @@intRow += 1
        end
        return @@aryData
    end
   
    #----------------------------------------
    # ファイルのコピー
    #----------------------------------------
    def self.cpfile
        FileUtils.copy(COPYFILENAME,PASTEFILENAME)
    end
   
    #----------------------------------------
    # UTF-8変換
    # [Param]
    #   val:変換対象の文字列
    # [Return]
    #   UTF-8へ変換した文字列
    #----------------------------------------
    def self.chkNil(val)
        retval = ''
        if val != nil then
            retval = val.toutf8
        end
       
        return retval
    end
end

#########################################################
#
# 実行処理
#
#########################################################
#実行処理
begin

    #----------------------------------------
    # ファイルのコピー(copy.txt⇒past.txt)
    #----------------------------------------
    FileCls::cpfile
   
    #----------------------------------------
    # Excelファイルを開き、値を配列に保持
    #----------------------------------------
    app     = WIN32OLE.new('Excel.Application')
    inbook  = app.Workbooks.Open( FileCls::CHKFILENAME)
    ary =  FileCls::getary(inbook)

    #----------------------------------------
    # 取得したデータ分繰り返す
    #----------------------------------------
    ary.each do |aryRow|
       
        #------------------------------
        # 変換文字列の設定
        #------------------------------
        settxt = ''
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLA) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLB) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLC) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLD) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLE) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLF) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLG) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLH) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLI) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLJ) ])  + "\n".toutf8)
        settxt = settxt.concat(FileCls::chkNil(aryRow[FileCls.getidx(FileCls::Col::COLK) ])  + "\n".toutf8)
       
        #------------------------------
        # 再変換用の文字を末尾に追加
        # 最後は変換用の文字は不要
        #------------------------------
        if aryRow != ary.last
            settxt = settxt.concat('RepArea'.toutf8)
        end
       
        #------------------------------
        # 更新対象ファイルのデータ取得
        #------------------------------
        file = File.open(FileCls::PASTEFILENAME,"r")
        data = file.read
        file.close
       
        #------------------------------
        # テキスト内容を変換
        #------------------------------
        new_contents = ''
        new_contents = data.toutf8
        new_contents = new_contents.gsub("RepArea", settxt.toutf8)
       
        #------------------------------
        # ファイルの更新
        #------------------------------
        File.open(FileCls::PASTEFILENAME, "w") do |f|
            f.write(new_contents)
        end
    end

#例外処理
rescue Exception => e
    puts'rescue'
    puts e.message
    puts e.backtrace.inspect

#共通処理
ensure
    inbook.save
    inbook.close(true)
    app.quit
end

 

以下、処理メモ

 

文字コードの変換>

文字コードを「Shift-JIS」ではなく、「UTF-8」で保存したかったため、

require 'kconv'

の「kconv」で「〜.toutf8」を使用して変換処理を行っている。

また、空文字「''」に対して「〜.toutf8」を行なうとエラーが発生するため、

空文字以外に対してのみ、UTF-8への変換処理を行っている

 

Rubyの例外処理>

Rubyでは

begin

 ここには通常処理を記載

rescue 例外の型=>変数

 ここには例外発生処理を記載

ensure

 ここは必須処理を記載

end

という書き方をする。

 

<ファイルの更新タイミング>

本来であれば、ファイルへの更新は最後に1度だけにしたかったが、

Excelファイルを纏めている処理を長く繰り返すと、

メモリエラーが発生したため、

Excelの行ごとにファイルへの更新処理を行っている。

 

<書き込みファイルのオープン、クローズ>

更新対象ファイルのデータ取得として

#------------------------------
# 更新対象ファイルのデータ取得
#------------------------------
file = File.open(FileCls::PASTEFILENAME,"r")
data = file.read
file.close

という処理と

#------------------------------
# ファイルの更新
#------------------------------
File.open(FileCls::PASTEFILENAME, "w") do |f|
f.write(new_contents)
end

をわけ、2度にわたり開いたり、閉じたりを行っているが、

書き込みモードでファイルをオープンした際のファイル内の

値の取得方法がわからなかったため、

「読み込みモード」と「書き込みモード」でそれぞれの

処理を分けている。

※1度で実施する方法がわかれば、別途、追記等を行う