Pythonで大量のExcelファイルをいじった話
最近「100個のテキストファイルから一部を取り出して100個のExcelファイルを作る」とか、「100個のExcelファイルの一部のセルを1つのExcelファイルにコピペする」とかいう感じの作業をする必要があって、手作業では面倒すぎるのでPythonのopenpyxlを使ってみました。今回はその時のメモです。
openpyxlは、Pythonからxlsxファイルを読み書きするためのパッケージです（xlsファイルは読み書きできないはず）。Excelを操作するわけではないので注意しないといけない点があるものの、逆にExcelなしでExcelファイルをいじることができます。
準備
Pythonとopenpyxlのインストール
Linuxだと標準でPythonが入っていることが多いのですが、今回はWindows環境で作業する必要がありました。WindowsでPythonを動かす方法はいくつかあるみたいですが、自分はAnacondaをインストールしました。この場合、普通にインストールするとopenpyxlも一緒にインストールされます。
基本的な使い方
ファイルの読み込みと書き込み
インポートは次のようにしています。
import openpyxl as px
ファイルの読み込みは
wb = px.load_workbook("src.xlsx")
書き込みは
wb.save("dst.xlsx")
みたいな感じで行います。新規ファイルを作成する場合は
wb = px.Workbook()
らしいです。
シートの操作
シートを作成するときは
ws = wb.create_sheet(title="シート名")
とします。既存のシートを名前で選択して操作するときは
ws = wb["シート名"]
現在アクティブなシートを操作するときは
ws = wb.active
とします。シートの名前は
ws.title = "シート名"
で変更できます。
セルの読み込みと書き込み
セルの中身の読み込みは
data = ws['A1'].value
書き込みは
ws['A2'] = data
でできます。数式も書けます。
ws['A3'] = "=1+2"
簡単！
または
data = ws.cell(row=1, column=1).value ws.cell(row=2, column=1).value = data
でも読み書きできます。ループではこちらの方が便利かも。
その他
他にもフォントや罫線を変えたり、セルを結合したりできるみたいなのですが、自分の用途ではExcelであらかじめ見た目だけを整えたxlsxファイルを作って、 shutil.copy() でコピーしてから値を変える方が楽だったので試していません。
はまったこと
罫線が壊れる！
結合されたセルに罫線があるExcelファイルを読み込んだ場合、openpyxlでいじってから保存すると罫線が壊れます。既知の問題らしく、海外でMonkeyPatchが紹介されていました。下記はそのままのコピペです。この記述をソースのどこかに書いておきます。 追記：v2.6で修正されるようです。
from itertools import product import types import openpyxl from openpyxl import worksheet from openpyxl.utils import range_boundaries def patch_worksheet(): """This monkeypatches Worksheet.merge_cells to remove cell deletion bug https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working Thank you to Sergey Pikhovkin for the fix """ def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): """ Set merge on a cell range. Range is a cell range (e.g. A1:E1) This is monkeypatched to remove cell deletion bug https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working """ if not range_string and not all((start_row, start_column, end_row, end_column)): msg = "You have to provide a value either for 'coordinate' or for\ 'start_row', 'start_column', 'end_row' *and* 'end_column'" raise ValueError(msg) elif not range_string: range_string = '%s%s:%s%s' % (get_column_letter(start_column), start_row, get_column_letter(end_column), end_row) elif ":" not in range_string: if COORD_RE.match(range_string): return # Single cell, do nothing raise ValueError("Range must be a cell range (e.g. A1:E1)") else: range_string = range_string.replace('$', '') if range_string not in self._merged_cells: self._merged_cells.append(range_string) # The following is removed by this monkeypatch: # min_col, min_row, max_col, max_row = range_boundaries(range_string) # rows = range(min_row, max_row+1) # cols = range(min_col, max_col+1) # cells = product(rows, cols) # all but the top-left cell are removed #for c in islice(cells, 1, None): #if c in self._cells: #del self._cells[c] # Apply monkey patch worksheet.Worksheet.merge_cells = merge_cells patch_worksheet()
値を読みたいのに数式が読まれる！
openpyxlではvalueという属性でデータにアクセスできますが、これでデータを読み出すとExcelでいうところの「値」ではなく「数式」が読み出されます。値を読み出す場合は、ワークブックを開くときに data_only オプションを指定します。
wb = px.load_workbook(filename, data_only=True)
ちなみに、Excelでxlsxファイルを保存すると、数式のセルは数式と値の両方がファイルに書き込まれますが、openpyxlでは数式しか書き込まれません。もしopenpyxlで数式を書き込んだファイルからそのまま値を読み出すとNoneが読み出されます。そのため、openpyxlで数式を書いたファイルから値を読み出すには、一度Excelでファイルを開いて保存する必要があります。
【FF14】【ID愛でる会Aegis】スカラ観光
スカラの観光に行きたい、と数日前から言っていたのですが、今日やっと行けました。一緒に行ってくれたみんなありがとう！！
いつもの雑まとめです。
1ボスまで
地面からは赤と白のちょっと変わった植物のようなものが生えています。塩湖の底なので、海藻のようなものなのかも知れません。自分はこれを見て、お刺身によくついてくる海藻を思い出しました。多分別物だとは思いますが。
近くには大きな結晶があります。他のIDだとこういうのはだいたいクリスタルなのですが、ここでは塩の結晶ではないかと思います。
そして魚卵。なんだかお腹が空いてきました…
1ボス
このボスは、戦闘が開始されるまで戦闘フィールドをうろうろしています。もしかしたら視界に入らないようにしたら後ろに回り込めるのではと思ったらできてしまいました。その後すぐに見つかってしまいSSは撮れませんでしたが。
2ボスまで
道中の柱に模様があります。下のほうの小さいものは最初文字かと思ったのですが、模様が欠けているだけのようです。その上の大きめの模様は何なんだろう。ゲルモラ関連の場所にゲルモラのシンボルがあるように、これがスカラのシンボルなのかも。
そしてこのエリアは天井にところどころ穴があります。でも真っ白で何も見えません。このSSを撮ったときはエオルゼア時間で夜だったので、このIDは時間が固定されているIDのようです。
道中、食器が置いてあるのですが、このIDの奥の方にあるものと違い、財宝という感じではありません。もしかしたらスカラの人々が使用していた物なのかも。でもなんでこんな所にぽつんと置いてあるんだろう。
2ボス
このボスは、時々ファットキャットが中にいます。今回はいなかったのですが、いるときは円形範囲攻撃のときに出てきます。以前撮影したのがこれです。いたりいなかったりするのはシュレディンガーの猫的な話なのでしょうか。それともファットキャットは何かを企んで光の戦士を倒そうとしている…？
3ボスまで
薄暗いダンジョンでよくある、誰がこの蝋燭点けているんだ、っていうやつです。細かいことは考えないことにしましょう。
このエリアの最初の方で、見たことのある大砲が置いてあり、この場所からアラミゴの財宝が置いてあるのだとわかります。
その財宝ですが、ミイラが落ちている近くだったり、ミイラの上に置かれていたりします。これは多分、スカラ時代にお墓だったものを、テオドリックさんが財宝を置くためにミイラを移動させて財宝をつめたんじゃないかと思います。しかしミイラも財宝も置き方が雑です。テオドリックさんの性格かな？
そしてこのエリアにはところどころに肖像画があります。全て違う人のようですが、一体誰なんだろう。
テオドリックさんのお抱え呪術師が設置したと思われる祭壇。中央の骨は何なんでしょう。アルドゴートかと思うのですが、呪術師がザナラーンから持ってきたのかな。
そして最後のボス前の封印。この手の模様、いろんなIDで見かけますが、この模様はここ以外で見たことない気がします。
FF14をLinuxの仮想環境で動かす話・その2
ホストOSの設定
VFIOの設定
GPUをパススルーするには、ホストOSがGPUを使わないようにしないといけません。 /etc/modprobe.d/vfio.conf に下記の設定をしています。
options vfio-pci ids=10de:1b80,10de:10f0,1b21:1242 disable_vga=1 blacklist nouveau
10de:1b80はGPU、10de:10f0はGPUのオーディオデバイス（HDMI出力）、1b21:1242はUSBコントローラです。 disable_vga と blacklist の設定は不要かも知れません。
また、 /etc/mkinitcpio.conf の MODULES に vfio vfio_iommu_type1 vfio_pci vfio_virqfd を追加して、 sudo mkinitcpio -p linux を実行します。
カーネルコマンドラインオプションの設定
カーネルコマンドラインオプションに下記の設定を追加しています。
threadirqs intel_iommu=on default_hugepagesz=1G hugepagesz=1G hugepages=8
threadirqs は不要かも知れません。 intel_iommu=on は必須です。 default_hugepagesz=1G hugepagesz=1G hugepages=8 はOSのメモリを仮想環境用に予約しておくために追加しています（この場合は8GiBの静的ヒュージページ）。
仮想環境の設定
仮想環境の設定にはlibvirtを使用しています。OVMFが必要になるので、 Arch LinuxのWiki を参考にして /etc/libvirt/qemu.conf とsystemdの設定をします。
メモリ
ここからlibvirtの定義ファイル（ /etc/libvirt/qemu/win.xml ）を順番に説明していきます。
<domain type='kvm'> <name>win</name> <uuid>xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx</uuid> <memory unit='GiB'>8</memory> <currentMemory unit='GiB'>8</currentMemory> <memoryBacking> <hugepages/> <nosharepages/> </memoryBacking>
容量はカーネルのコマンドラインオプションで指定したのと同じ8GiBです。 hugepages は予約した領域（静的ヒュージページ）を使う設定です。手元の環境では静的ヒュージページではなくても動作はしたのですが、空きメモリの断片化が進んだときに何が起こるか分からないので静的ヒュージページを使っています。 nosharepages はメモリマージを無効にしますが、無くてもいいと思います。
CPU
<vcpu placement='static'>6</vcpu> <iothreads>1</iothreads> <cputune> <vcpupin vcpu='0' cpuset='1'/> <vcpupin vcpu='1' cpuset='5'/> <vcpupin vcpu='2' cpuset='2'/> <vcpupin vcpu='3' cpuset='6'/> <vcpupin vcpu='4' cpuset='3'/> <vcpupin vcpu='5' cpuset='7'/> <emulatorpin cpuset='0'/> <iothreadpin iothread='1' cpuset='4'/> <vcpusched vcpus='0-5' scheduler='batch'/> <iothreadsched iothreads='1' scheduler='batch'/> </cputune>
CPUは3コア6スレッド分を仮想環境に割り当てています。仮想CPUの6スレッドとエミュレータスレッドとIOスレッドの8スレッドを物理CPUの8スレッドに固定しているのですが、i7-6700KとQEMUではコア番号とスレッド番号の対応が違っています。
仮想CPUと物理CPUで構成が違うとパフォーマンスに影響がありそうなので、こんな設定になっています。ちなみにコアとスレッドの関係は、適当なLinuxを起動して、 cat /proc/cpuinfo してidを見ると分かります。
システム
<os> <type arch='x86_64' machine='q35'>hvm</type> <loader readonly='yes' type='pflash'>/usr/share/ovmf/x64/OVMF_CODE.fd</loader> <nvram>/var/lib/libvirt/qemu/nvram/OVMF_CODE.fd</nvram> </os> <features> <acpi/> <apic eoi='on'/> <hyperv> <relaxed state='on'/> <vapic state='on'/> <spinlocks state='on' retries='4095'/> <vpindex state='on'/> <runtime state='on'/> <synic state='on'/> <stimer state='on'/> <reset state='on'/> <vendor_id state='on' value='0123456789ab'/> </hyperv> <kvm> <hidden state='on'/> </kvm> <vmport state='off'/> </features> <cpu mode='host-passthrough' check='none'> <topology sockets='1' cores='3' threads='2'/> </cpu> <clock offset='localtime'> <timer name='rtc' tickpolicy='catchup' track='guest'/> <timer name='pit' tickpolicy='delay'/> <timer name='hpet' present='no'/> <timer name='hypervclock' present='yes'/> </clock> <on_poweroff>destroy</on_poweroff> <on_reboot>restart</on_reboot> <on_crash>restart</on_crash> <on_lockfailure>poweroff</on_lockfailure> <pm> <suspend-to-mem enabled='no'/> <suspend-to-disk enabled='yes'/> </pm> <devices> <emulator>/usr/sbin/qemu-system-x86_64</emulator>
このあたりはよく分かっていないので、コピペしてきたものをつぎはぎしてそのまま使用しています（ぉぃ
ストレージ
<disk type='block' device='disk'> <driver name='qemu' type='raw' cache='writeback' io='threads' discard='unmap'/> <source dev='/dev/mapper/vg0-win'/> <target dev='sda' bus='scsi'/> <boot order='1'/> <address type='drive' controller='0' bus='0' target='0' unit='0'/> </disk> <disk type='block' device='cdrom'> <driver name='qemu' type='raw' cache='writeback' io='threads' discard='unmap'/> <source dev='/var/lib/libvirt/images/virtio-win-0.1.141.iso'/> <target dev='sdb' bus='sata'/> <readonly/> <address type='drive' controller='0' bus='0' target='0' unit='1'/> </disk>
io='threads' は io='native' の方がいいかも知れません。CDROMにはvirtioのドライバディスクが入っています。Windowsのインストール時にそのままではディスクが読み込めないと言われるので、このイメージ内のドライバをロードします。
PCIeルートポート
<controller type='pci' index='0' model='pcie-root'/> <controller type='pci' index='1' model='pcie-root-port'> <address type='pci' domain='0x0000' bus='0x00' slot='0x01' function='0x0' multifunction='on'/> </controller> <controller type='pci' index='2' model='pcie-root-port'> <address type='pci' domain='0x0000' bus='0x00' slot='0x01' function='0x1'/> </controller> <controller type='pci' index='3' model='pcie-root-port'> <address type='pci' domain='0x0000' bus='0x00' slot='0x01' function='0x2'/> </controller>
QEMUのドキュメント によると、PCIeバスのルートには
しか置けないそうです。それ以外のPCIeデバイスを接続するために、PCIeルートポートをPCIeルートに接続しています。
仮想コントローラ
<controller type='scsi' index='0' model='virtio-scsi'> <driver queues='6'/> <address type='pci' domain='0x0000' bus='0x01' slot='0x00' function='0x1'/> </controller> <controller type='sata' index='0'> <address type='pci' domain='0x0000' bus='0x00' slot='0x1f' function='0x2'/> </controller> <controller type='usb' index='0' model='nec-xhci'> <address type='pci' domain='0x0000' bus='0x01' slot='0x00' function='0x3'/> </controller> <interface type='bridge'> <mac address='xx:xx:xx:xx:xx:xx'/> <source bridge='br0'/> <model type='virtio'/> <address type='pci' domain='0x0000' bus='0x01' slot='0x00' function='0x0' multifunction='on'/> </interface> <graphics type='vnc' port='59xx' autoport='no' listen='0.0.0.0' keymap='en-us'> <listen type='address' address='0.0.0.0'/> </graphics> <video> <model type='virtio' vram='16384' heads='1' primary='yes'> <acceleration accel3d='yes'/> </model> <address type='pci' domain='0x0000' bus='0x01' slot='0x00' function='0x2'/> </video>
色々な仮想コントローラを接続しています。マルチファンクションでできるだけ1つにまとめていますが、デバイスが多くないのでそこまでしなくていいかも知れません。GPUは、VNC経由でマウスとキーボードを使うために接続しています（Windows側で画面は無効にしています）。
パススルーデバイス
<hostdev mode='subsystem' type='pci' managed='yes'> <source> <address domain='0x0000' bus='0x01' slot='0x00' function='0x0'/> </source> <address type='pci' domain='0x0000' bus='0x02' slot='0x00' function='0x0' multifunction='on'/> </hostdev> <hostdev mode='subsystem' type='pci' managed='yes'> <source> <address domain='0x0000' bus='0x01' slot='0x00' function='0x1'/> </source> <address type='pci' domain='0x0000' bus='0x02' slot='0x00' function='0x1'/> </hostdev> <hostdev mode='subsystem' type='pci' managed='yes'> <source> <address domain='0x0000' bus='0x03' slot='0x00' function='0x0'/> </source> <address type='pci' domain='0x0000' bus='0x03' slot='0x00' function='0x0'/> </hostdev>
GPUとUSBコントローラをパススルー接続しています。
メモリバルーン
<memballoon model='none'/> </devices> </domain>
PCIパススルーを使うときは、メモリを動的に増減させることはできないので、無効にしています。
Windowsインストール後の設定
GeForceはMessage Signaled Interrupt (MSI)に対応していますが、何故かデフォルトでは無効になっているので、Windowsインストール後に有効にする必要があります。レジストリエディタで HKEY_LOCAL_MACHINE\System\CurrentControlSet\Enum\PCI\<XXX>\Device Parameters\Interrupt Management\MessageSignaledInterruptProperties ( <XXX> は各デバイスで異なる) に MSISupported というキーを作って、値を 1 にします。
もしかしたら抜けや間違いがあるかも知れません。何かあったら教えてもらえるとありがたいです。
