【Python】ExcelのVLookUpはPythonに任せよう(txt + txt編)

# 対象ファイルデータ
今回は、サンプルとして簡単に以下を用意しました。

##### テキストファイル1
![スクリーンショット 2022-03-10 16.54.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2526592/80b49337-3f23-c88a-3969-2ee3118d59df.png)

##### テキストファイル2
![スクリーンショット 2022-03-10 16.36.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2526592/738eed60-f251-c220-d2c2-86685cbb659f.png)


この２つのデータを結合して以下のようなexcelファイルを作成しようと思います。
![スクリーンショット 2022-03-10 15.41.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2526592/085a0110-9224-62fe-5363-3da68fe83b9a.png)


# はじめに
今回はChromeの拡張サービスである「Colaboratory」を使用してPythonのコードを記述していきます。
Google Driveから「新規 → その他 → アプリを追加」からインストールしてください。(Chromeで検索してもインストールできます)

# 全体コード
```python
import openpyxl
import datetime
from google.colab import drive

start = datetime.datetime.now()
print('処理開始')
print(start)

drive.mount('/content/drive')

OUTPUT_FILE_NAME = "/content/drive/My Drive/sample/file/sample_txt.xlsx"

departmentDict = {}
positionDict = {}
detailHeaderList = []
with open('/content/drive/My Drive/sample/file/detail.txt', mode='rt', encoding='utf-8') as f:    
  for index, line in enumerate(f.read().splitlines()):
    splitData = line.split("\t")

    if index == 0:
      for headerIndex, name in enumerate(splitData):
        if headerIndex != 0:
          detailHeaderList.append(name)
    else:
      departmentDict[splitData[0]] = splitData[1]
      positionDict[splitData[0]] = splitData[2]

# ブック作成
workbook = openpyxl.Workbook()
# 先頭シート取得
worksheet = workbook.worksheets[0]

# データ出力
culmnIndex = 0
with open('/content/drive/My Drive/sample/file/employee.txt', mode='rt', encoding='utf-8') as f:    
  for index, line in enumerate(f.read().splitlines()):
    splitData = line.split("\t")
    culmnIndex = len(splitData)

    for splitIndex, data in enumerate(splitData):
      worksheet.cell(index + 1, splitIndex + 1, data)

      if index != 0 and splitIndex == 0:
        # 部署
        if data in departmentDict.keys():
          worksheet.cell(index + 1, culmnIndex + 1, departmentDict[data])
        else:
          worksheet.cell(index + 1, culmnIndex + 1, 'データなし')
        # 役職
        if data in positionDict.keys():
          worksheet.cell(index + 1, culmnIndex + 2, positionDict[data])
        else:
          worksheet.cell(index + 1, culmnIndex + 2, 'データなし')

# 結合ファイルのヘッダーを出力
for index, name in enumerate(detailHeaderList):
  worksheet.cell(1, culmnIndex + index + 1, name)

# 保存
workbook.save(OUTPUT_FILE_NAME)

end = datetime.datetime.now()
print('処理完了')
print(end)
print(end - start)

```
以下で各コード、手順について解説していきます。

# インポート
```python
import openpyxl
from google.colab import drive
```
まずは必要なライブラリをインストールします。
Google Drive, Colaboratoryを使用する場合、ドライブのマウントが必要なので`google.clab`から`drive`もインポートしておきます。

### ドライブのマウント
```python
drive.mount('/content/drive')
```
Google Drive, Colaboratoryを使用する場合、ドライブのマウントを行います。

# 作成するファイル名の定義
```python
OUTPUT_FILE_NAME = "/content/drive/My Drive/sample/file/sample_txt.xlsx"
```
作成するファイル名を配置したいディレクトリーから定義します。


# テキストファイル2の読み込み
```python
departmentDict = {}
positionDict = {}
detailHeaderList = []
with open('/content/drive/My Drive/sample/file/detail.txt', mode='rt', encoding='utf-8') as f:    
  for index, line in enumerate(f.read().splitlines()):
    splitData = line.split("\t")

    if index == 0:
      for headerIndex, name in enumerate(splitData):
        if headerIndex != 0:
          detailHeaderList.append(name)
    else:
      departmentDict[splitData[0]] = splitData[1]
      positionDict[splitData[0]] = splitData[2]
```
↓
```python
with open('/content/drive/My Drive/sample/file/detail.txt', mode='rt', encoding='utf-8') as f: 
```
こちらでテキストファイル2を読み込み

```python
for index, line in enumerate(f.read().splitlines()):
```
行ごとに分割します。

```python
splitData = line.split("\t")
```
行ごとに分割したデータを`\t`タブで更に分割し、

```python
if index == 0:
  for headerIndex, name in enumerate(splitData):
    if headerIndex != 0:
      detailHeaderList.append(name)
else:
  departmentDict[splitData[0]] = splitData[1]
  positionDict[splitData[0]] = splitData[2]
```
１行目はヘッダーなので`id`以外をdetailHeaderList(テキストファイル2のヘッダーリスト)に追加し、
２行目以降は部署、役職のそれぞれの辞書に`key=id`でデータを定義します。


# ワークブックの作成
```python
workbook = openpyxl.Workbook()
worksheet = workbook.worksheets[0]
```
Excelのワークブックを作成し、先頭のシートを取得します。
今回は使用しませんが、`workbook.create_sheet()`で２シート目以降を作成することができます。

# データ出力
```python
culmnIndex = 0
with open('/content/drive/My Drive/sample/file/employee.txt', mode='rt', encoding='utf-8') as f:    
  for index, line in enumerate(f.read().splitlines()):
    splitData = line.split("\t")
    culmnIndex = len(splitData)

    for splitIndex, data in enumerate(splitData):
      worksheet.cell(index + 1, splitIndex + 1, data)

      if index != 0 and splitIndex == 0:
        # 部署
        if data in departmentDict.keys():
          worksheet.cell(index + 1, culmnIndex + 1, departmentDict[data])
        else:
          worksheet.cell(index + 1, culmnIndex + 1, 'データなし')
        # 役職
        if data in positionDict.keys():
          worksheet.cell(index + 1, culmnIndex + 2, positionDict[data])
        else:
          worksheet.cell(index + 1, culmnIndex + 2, 'データなし')
```
↓
```python
with open('/content/drive/My Drive/sample/file/employee.txt', mode='rt', encoding='utf-8') as f:    
  for index, line in enumerate(f.read().splitlines()):
    splitData = line.split("\t")
```
同じく、テキストファイル1を読み込み、行ごとに分割、タブで分割します。

```python
culmnIndex = len(splitData)
```
後ほど使うのでテキストファイル1の列数を格納しておきます。

```python
for splitIndex, data in enumerate(splitData):
  worksheet.cell(index + 1, splitIndex + 1, data)
```
テキストファイル1のデータを出力していきます。
`worksheet.cell(行, 列, 値)`で該当のセルにデータを出力することができます。

```python
if index != 0 and splitIndex == 0:
    # 部署
    if data in departmentDict.keys():
      worksheet.cell(index + 1, culmnIndex + 1, departmentDict[data])
    else:
      worksheet.cell(index + 1, culmnIndex + 1, 'データなし')
    # 役職
    if data in positionDict.keys():
      worksheet.cell(index + 1, culmnIndex + 2, positionDict[data])
    else:
      worksheet.cell(index + 1, culmnIndex + 2, 'データなし')
```
`index != 0 and splitIndex == 0`テキストファイル１の１行目（ヘッダー）じゃないかつ1列目(社員ID)のとき、
社員IDがテキストファイルから作成した辞書に定義されていればその値を出力します。
今回はあえてデータがない社員も作ったので、データがない場合は`データなし`と出力しておくようにしておきます。

### テキストファイル2のヘッダーの出力
```python
for index, name in enumerate(detailHeaderList):
  worksheet.cell(1, culmnIndex + index + 1, name)
```
先に定義したテキストファイル2のヘッダーを出力します。


# ワークブックの保存
```python
workbook.save(OUTPUT_FILE_NAME)
```
最後にワークブックを保存すると、最初に定義したディレクトリにExcelファイルが作成されます。

# 最後に
今回は社内共有用を兼ねて簡単なサンプルでの解説となりましたが、応用していくことで業務の効率化、ヒューマンエラーの防止につながるので積極的に自動化していきましょう。