LibreOffice で MySQL連動 PDF帳票を作る

# 関連記事一覧
LibreOfficeでPDF文書を自動生成
https://qiita.com/nanbuwks/items/8ea939ad497fec29c069

LibreOffice で MySQL連動 PDF帳票を作る (この記事)
https://qiita.com/nanbuwks/items/d4696542760cf4b8a24a

WebアプリでPDF文書を自動生成 on Ubuntu14.04 LTS
https://qiita.com/nanbuwks/items/7dbd2a0a79b232f864b4

WebアプリでPDF文書を自動生成 on Ubuntu20.04 LTS
https://qiita.com/nanbuwks/items/f11e4e3aabac33f9a32b






# 概要

LibreOfficeを使い、データベース連動してPDF帳票を作るシステムを作ってみます。
(初出：2020/04/04)

(2021/03/29追記)
この記事を元に、Webサーバーで PDF 作成できるようにした記事を書きました
「WebアプリでPDF文書を自動生成 on Ubuntu20.04 LTS」
https://qiita.com/nanbuwks/items/f11e4e3aabac33f9a32b


# 環境

- LibreOffice バージョン: 6.4.6.2
- Ubuntu Linux 18.04LTS/20.04 LTS
- MariaDB mysql Ver 15.1 Distrib 10.3.25-MariaDB
- Python3 

だけれども、基本的には OS はどれでも大丈夫なハズ。
他の OS の場合は、unzip などのコマンドを相当するものに読み替えてください。

# Draw で領収書を作る


誰も使っていない LibreOffice Draw 。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/4d69943d-5b76-8294-5197-7d4fca1bae94.png)

 これを使います。

Drawだと好きな位置に好きな体裁で「領収書」と書ける。
ネ申エクセルよりもずっと直感的。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/a88a37ba-9093-6f82-a59c-21cd44d58b7c.png)

以下に述べる手法は、Writer でも Calc でもできる。けれども人類は Draw の可能性に気づくべき。

## 注意

センタリングや右揃えの必要な書式は、図形描画で透明＆透明枠の四角を作り、その中にテキストを配置します。


# 自動で日付を入れる

先のものの日付を ##TODAY## としておきます。

キーワードを ## に挟み込む形にしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/5d380898-1de3-ad08-f6e2-5e5c6110492b.png)

```

##TODAY##

```

として、## で囲んだキーワードを置き換えることにします。


## なぜ ## を使うのか

なぜ %% とかではなく、 ## を使うか？ 20年ほど前から似たようなものを作ってましたが、その当時の SJIS や EUC などの文字コードでは %% だと誤動作することがあったからです。
今では UTF-8 が前提となり、## でなくても十分動作すると思いますが以前の流儀で ## としています。

## 操作


作業フォルダを決めて、odgファイルをそこに保存します。
そのフォルダ上で作業をします。

まずは、odgファイルから、content.xmlを抽出し、名前を original.xml と変更します。



```

$ unzip test1.odg content.xml
$ mv content.xml original.xml
```

加工しやすくするためにxmllint をかけ、sed でキーワードを変更します。

```

$ xmllint --format  original.xml | sed s/##TODAY##/"`date +%Y年%-m月%-d日`"/g  > content.xml     
```

ちなみに、2020/04/04 みたいにするには以下の通り

```

$ xmllint --format  original.xml | sed s/##TODAY##/"`date +%Y年\\\/%m\\\/%d`"/g  > content.xml     
```

できた content.xml を odgファイルに収容する。

```

$ zip test1.odg content.xml


```
pdf変換

```

$ soffice --headless --convert-to pdf test1.odg

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/dd9ec59d-a903-32ea-7574-b0113e72346c.png)



# データベースと連動する

mysqlに入っているデータを引っ張ってくるようにしてみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/7afd11ba-d416-99db-c8dd-468e9f2ed839.png)

(2021/03/25 追記： 以下のコマンドを実行する前に、pip3 がインストールされていない場合は python3-pip をインストールしておいてください)

```

$ sudo pip3 install mysql-connector-python3

```
(2021/03/25 追記：)
Ubuntu 20.04では以下のようにエラーが出ました。

```

$ sudo pip3 install mysql-connector-python3
ERROR: Could not find a version that satisfies the requirement mysql-connector-python3 (from versions: none)
ERROR: No matching distribution found for mysql-connector-python3
```
以下のようにするとインストールできました。

```

$ sudo pip3 install mysql-connector-python
```
もしかしたら、過去の記録が間違えていたかも知れません。
(：2021/03/25 追記終わり)

以下の db.py を作っておく。

```

import sys
import mysql.connector
import re
global キー
global キーフィールド

キー=sys.argv[2]
キーフィールド="会員番号"


def dbload(キー,テーブル,フィールド):
  db=mysql.connector.connect(host="127.0.0.1", user="webdb", password="password")
  cursor=db.cursor()
 
  cursor.execute("USE test")
  db.commit()
  sql=('SELECT '+フィールド+' FROM '+テーブル+' where 会員番号="'+キー+'"')
  cursor.execute(sql)
  i=(cursor.fetchone())
  cursor.close()
  db.close()
#  return(i[0])  # Ubuntu20.04でエラー
  return(i[0].decode())

def replaceline(source):
  p = re.search(r'##SQL:(.+)@(.+)##',source)
  if p != None:
    テーブル=(p.groups()[1])
    フィールド=(p.groups()[0])
#    print(テーブル)
#    print(フィールド)
    ret = dbload(キー,テーブル,フィールド)
    p = re.sub(r'##SQL:(.+)@(.+)##',ret,source)
    return(p)
  else:
    return(source)

#source = '  ##SQL:会員@フリガナ## 様'
#dist=replaceline(source)
#print(dist)

filename = sys.argv[1]
with open(filename, 'r') as f:
    fileText = f.read()
    replacedtext = replaceline(fileText)
    print(replacedtext)
```
(2021/03/26追記：Ubuntu20.04で ` TypeError: sequence item 1: expected str instance, bytes found ` というエラーが出たので該当箇所を以下のように修正しました。


```

#  return(i[0])  # Ubuntu20.04でエラー
  return(i[0].decode())
```

あとは、同じように

```

$ unzip test1.odg content.xml
$ xmllint --format content.xml > original_xmllint.xml
$ python3 db.py original_xmllint.xml 4 | sed s/##TODAY##/"`date +%Y年%-m月%-d日`"/g  > content.xml   
$ zip test1.odg content.xml
updating: content.xml (deflated 85%)
$ soffice --headless --convert-to pdf test1.odg

```
(2021/03/25修正：操作中のファイル名に誤りがあったのを直しました)

とすると pdf ファイルができる。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/2ad3d62e-678e-77ea-e13e-ee812d9c3bc5.png)