LoginSignup
0
0

More than 5 years have passed since last update.

@jinuse14142236(じぬせ)

IronRubyでExcelの文字列をサニタイズする

Posted at

概要

IronRubyを使い、起動中のExcelのプロセスから文字列を取得して加工する場合の注意点です。

RubyとExcelでは改行コードが共に"\n"なので改行コードの変換は不要ですが、Excelから取り込んだ文字列をRubyの正規表現等で加工するような場合は、念のためto_sメソッドを呼んだ方がよさそうです。

ここでは、このことをサンプルコードで確認します。

本トピックの最後では、セルの中に改行を含む長大な文字列が入力されている際に便利かもしれないスクリプトを紹介します。

なお、本トピックの内容を表にまとめると次のようになります。

システム 改行コード 文字列クラス
Excel \n .netのStringクラス
Ruby \n RubyのStringクラス
WPF \r\n .netのStringクラス

前提

RubyとExcelのマクロについてある程度知っている方向けです。
本記事のテーマはRubyやExcelマクロの文法やコーディング作法ではなく、Ruby(IronRuby)とExcelを連携させるときのテクニックです。

環境

Windows 8.1（64ビット）
IronRuby 1.1
Excel 2010

文字列の性質の違いの調査

最初にいくつかの実験をして文字列の性質の違いを確認します。

コード

サンプルコードは次の通りです。

excel_sanitize_print_type.rb
# coding: utf-8


# 起動中のExcelのプロセスにアタッチする
begin
    excel = System::Runtime::InteropServices::Marshal::GetActiveObject("Excel.Application")
rescue
    abort "No Excel process."
end


puts "[1]"

# GetTypeは.netのメソッドで、クラスのタイプを取得できる
# "System.String"と表示される => .netのクラスである
# Rubyの文字列クラスではないので、Rubyの文字列加工機能を使うと思わぬ不具合が出るかもしれない
puts excel.ActiveCell.Item(1).Text.GetType
puts


puts "[2]"

# classはRubyのメソッドで、クラスのタイプを取得できる
# "System::String"と表示される => .netのクラスである
# Rubyの文字列クラスではないので、Rubyの文字列加工機能を使うと思わぬ不具合が出るかもしれない
puts excel.ActiveCell.Item(1).Text.class
puts

実行方法

[1] Excelを起動します

[2] 適当なセルに適当な文字列を入力します（次の図を参照）。改行を含むテキストがよいです

excel_sanitize_big_cell.PNG

[3] 文字列を入力したセルを選択します

[4] DOS窓を開き、excel_sanitize_print_type.rbを実行します

> "C:\Program Files (x86)\IronRuby 1.1\bin\ir.exe" excel_sanitize_print_type.rb

[5] 文字が出ます

[1]
System.String

[2]
System::String

解説

[1]で使用したGetTypeは.netのメソッドで、クラスのタイプを取得できます。

System.Stringなので、これのことです。

[2]で使用したclassはRubyのメソッドで、これもクラスのタイプを取得できます。

名前空間の表現方法がRuby風に変換されており、System::Stringとなっています。

つまり、Excelからとってきたばかりの文字列はRubyのStringではなく、.netのStringであるということです。

これをRubyのStringに変換するにはto_sを使います。

上のコードに次のコード断片を追加して実行してみてください。

puts "[3]"

# to_sメソッドを呼んだ後にGetTypeとclassを実行する同じことをする
# "String" => Rubyのクラスである
# Rubyの文字列クラスになったので、Rubyの文字列加工機能を自由に使うことができる
puts excel.ActiveCell.Item(1).Text.to_s.class
puts

このように表示され、RubyのStringに変換されたことがわかります。

[3]
String

次に、Excelからとってきた文字列に対して、RubyのStringクラスに固有のメソッドをコールしてみます。

上のコードに次のコード断片を追加して実行してみてください。

puts "[4]"

# to_sを呼んでいないから.netのStringのはずだが、RubyのStringクラスのメソッドが使える
# IronRubyの方で互換性を確保してくれているのかもしれない
puts excel.ActiveCell.Item(1).Text.split
puts excel.ActiveCell.Item(1).Text.match("abc")
puts excel.ActiveCell.Item(1).Text.include?("abc")
puts

split, match, include?の3つのメソッドをコールしていますが、エラーは出ません。

IronRubyの方で互換性を確保してくれていると思われます。

ならば、to_sを使う必要はなさそうなのですが、振る舞いが違うメソッドがありました。

上のコードに次のコード断片を追加して実行してみてください。

puts "[5]"

# RubyのStringのinspectメソッドで文字列をよく調べると、Excelのセル内の改行は"\n"であることがわかる
# 改行が"\n"であるのはRubyの改行ルールと一致する
# つまり、改行コードに関してサニタイズは不要である
# 
# 一方で、次の2つのinspaceメソッドの表示結果は同じではない。.netとRubyのStringの違いが現れたか？
# こんなこともあるので、to_sを呼んでから文字列加工するようにした方がリスクが少ないと言える
# to_sを呼んでサニタイズしましょう！
puts excel.ActiveCell.Item(1).Text.to_s.inspect
puts excel.ActiveCell.Item(1).Text.inspect

このように表示され、.netのStringとRubyのStringで結果が違うことがわかります。

（一方がダブルクォーテーションで、他方がシングルクォーテーション）

[5]
"System\nRuntime\nInteropServices\nMarshal\nGetActiveObject"
'System\nRuntime\nInteropServices\nMarshal\nGetActiveObject'

日本語の文字列で実行すると次のようになります。

（この出力例はDOS窓ではありません。conemuでchcp 65001を実行してから動かしています）

[5]
"Ruby\u{3068}Excel\u{306e}\u{30de}\u{30af}\u{30ed}\u{306b}\u{3064}\u{3044}\u{3066}\u{3042}\u{308b}\u{7a0b}\u{5ea6}\u{77e5}\u{3063}\u{3066}\u{3044}\u{308b}\u{65b9}\u{5411}\u{3051}\u{3067}\u{3059}\u{3002}\n\u{672c}\u{8a18}\u{4e8b}\u{306e}\u{30c6}\u{30fc}\u{30de}\u{306f}Ruby\u{3084}Excel\u{30de}\u{30af}\u{30ed}\u{306e}\u{6587}\u{6cd5}\u{3084}\u{30b3}\u{30fc}\u{30c7}\u{30a3}\u{30f3}\u{30b0}\u{4f5c}\u{6cd5}\u{3067}\u{306f}\u{306a}\u{304f}\u{3001}Ruby(IronRuby)\u{3068}Excel\u{3092}\u{9023}\u{643a}\u{3055}\u{305b}\u{308b}\u{3068}\u{304d}\u{306e}\u{30c6}\u{30af}\u{30cb}\u{30c3}\u{30af}\u{3067}\u{3059}\u{3002}"
'RubyとExcelのマクロについてある程度知っている方向けです。\n本記事のテーマはRubyやExcelマクロの文法やコーディング作法ではなく、Ruby(IronRuby)とExcelを連携させるときのテクニックです。'

結果の違いが際立ちました。

よって、Excelから取り込んだ文字列はとりあえずto_sで変換してから使った方が、誤動作のリスクが少ないと思われます。

大きなセルを編集するスクリプト

ここでは、WPFとRuby(IronRuby)の文字列の性質の違いについて述べます。

サンプルコードとして、セルの中に改行を含む長大な文字列が入力されている際に便利かもしれないスクリプトを紹介します。

コード

サンプルコードは次の通りです。

XAMLファイル

excel_edit_big_cell.xml
<?xml version="1.0" encoding="UTF-8"?>
<Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="(No title)"
        Width="500"
        Height="500"
        Topmost="True" >
        <DockPanel>
                <WrapPanel DockPanel.Dock="Top">
                        <WrapPanel.Resources>
                                <Style TargetType="Button">
                                        <Setter
                                                Property="Padding"
                                                Value="5" />
                                        <Setter
                                                Property="Margin"
                                                Value="5 5 5 5" />
                                </Style>
                        </WrapPanel.Resources>
                        <Button
                                Content="Write"
                                Name="write_button" />
                        <Button
                                Content="Read"
                                Name="read_button" />
                        <Button
                                Content="Read Clipboard"
                                Name="read_clipboard_button" />
                        <Button
                                Content="Strip"
                                Name="strip_button" />
                        <Button
                                Content="Clear"
                                Name="clear_button" />
                        <Button
                                Content="Help"
                                Name="help_button" />
                </WrapPanel>
                <TextBox
                        Name="cell_text" 
                        AcceptsReturn="True" 
                        TextWrapping="Wrap" 
                        VerticalScrollBarVisibility="Auto" />
        </DockPanel>
</Window>

IronRubyスクリプト

excel_edit_big_cell.rb
# coding: utf-8
# IronRubyで実行すること


TITLE = "巨大テキストセルの編集"

USAGE = <<ENDEND
エクセルでセル内改行を使って入力されたデータの編集を楽にする。
結合されたセルへの書き込みにも便利（「セルの大きさが違う」というエラーを抑制する効果もある）。

[Write] エクセルで選択中のセルからテキストボックスにテキストを取り込む
[Read]  テキストボックスのテキストをエクセルで選択中のセルに書き込む
[Read Clipboard] クリップボードからテキストボックスにテキストを取り込む
[Strip] テキストボックスの末尾にある余計な空白を削除する
[Clear] テキストボックスをクリアする

ENDEND

XAML_FILE = "excel_edit_big_cell.xml"


require 'PresentationFramework'
require 'PresentationCore'

include System::IO
include System::Windows
include System::Windows::Markup


begin
        excel = System::Runtime::InteropServices::Marshal::GetActiveObject("Excel.Application")

rescue
        MessageBox.Show("このプログラムはExcelを起動中に実行してください")
        abort "Run this script with active Excel process."
end

stream = FileStream.new(XAML_FILE, FileMode.Open, FileAccess.Read)

window = XamlReader.load(stream)
window.Title = TITLE

cell_text = window.FindName("cell_text")

button = window.FindName("help_button")
button.Click do | sender, e |
        MessageBox.Show(USAGE)
end

button = window.FindName("clear_button")
button.Click do | sender, e |
        cell_text.Clear
end

button = window.FindName("write_button")
button.Click do | sender, e |
        ruby_text = cell_text.Text.to_s

        # エクセル行内改行\nでテキストボックスの改行は\r\n
        excel_text = ruby_text.gsub("\r\n", "\n")
        excel.ActiveWindow.Selection.Value = excel_text
end

button = window.FindName("read_button")
button.Click do | sender, e |

        # エクセル行内改行\nでテキストボックスの改行は\r\n
        excel_text = excel.ActiveWindow.Selection.Item(1).Text.to_s
        ruby_text = excel_text.to_s.gsub("\n", "\r\n")

        cell_text.Clear
        cell_text.AppendText(ruby_text)
end

button = window.FindName("read_clipboard_button")
button.Click do | sender, e |
        cell_text.Clear

        # Don't forget [require 'PresentationCore']
        cell_text.Text = Clipboard.GetText()
end

button = window.FindName("strip_button")
button.Click do | sender, e |
        text = cell_text.Text.to_s
        cell_text.Clear
        cell_text.Text = text.strip
end

app = Application.new
app.run(window)

実行方法

[1] Excelを起動します

[2] 適当なセルに適当な文字列を入力します。改行を含むテキストがよいです

[3] 文字列を入力したセルを選択します

[4] DOS窓を開き、excel_edit_big_cell.rbを実行します

> "C:\Program Files (x86)\IronRuby 1.1\bin\ir.exe" excel_edit_big_cell.rb

[5] 次のような画面が出ます

excel_edit_big_cell.PNG

[6] ［Read］ボタンを押すと選択中のセルの内容をテキストボックスに取り込みます

excel_edit_big_cell_read.PNG

[7] テキストボックスを編集して[Write]ボタンを押すと選択中のセルに書き込みます

解説

WPFの文字列は.netのStringで、改行コードは"\r\n"です。

下記は[Read]ボタンを押してセルの内容をテキストボックスに取り込むときのコードです。

button = window.FindName("read_button")
button.Click do | sender, e |

        # エクセル行内改行\nでテキストボックスの改行は\r\n
        excel_text = excel.ActiveWindow.Selection.Item(1).Text.to_s ★1
        ruby_text = excel_text.to_s.gsub("\n", "\r\n") ★2

        cell_text.Clear
        cell_text.AppendText(ruby_text)
end

ここでは、次のサニタイズ処理をしています。

  • Rubyのgsubを使うため、to_sでRubyの文字列に変換する（★1の部分）
  • gsubで改行コードをWPFの"\r\n"に変換する（★2の部分）

下記は[Write]ボタンを押してテキストボックス内容を選択中のセルに書き込むときのコードです。

button = window.FindName("write_button")
button.Click do | sender, e |
        ruby_text = cell_text.Text.to_s ★1

        # エクセル行内改行\nでテキストボックスの改行は\r\n
        excel_text = ruby_text.gsub("\r\n", "\n") ★2
        excel.ActiveWindow.Selection.Value = excel_text
end
  • Rubyのgsubを使うため、to_sでRubyの文字列に変換する（★1の部分）
  • gsubで改行コードをWPFの"\r\n"に変換する（★2の部分）
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0