Google App Engine(Python)用フレームワークKayを使い、動的にExcelファイルを作成してZIP形式で圧縮しダウンロードさせる

Google App Engine(Python)用フレームワークKayを使い、動的にExcelファイルを作成してZIP形式で圧縮しダウンロードさせる方法。

Google App Engine(Python)用フレームワークKayを使い、動的にZIP形式で圧縮してダウンロードさせる」と「Google App Engine用フレームワークKayでExcelファイルをダウンロードさせる」の合わせ技になります。

Excelファイルの作成には、xlwtを使用します。
xlwtはpure pythonなので、Google App Engineで使用することが出来ます。

xlwtからソースをダウンロードして展開します。
xlwtフォルダーをKayのプロジェクトのルートフォルダーにコピーします。
xlwtフォルダー中のdocフォルダーやexamplesフォルダーは不要です

次のようなフォルダー構成になります。

myproject/
 ├ kay/
 ├ myapp/
 └ xlwt/

xlwtでExcelデータを作成します。

wb = xlwt.Workbook()
ws1 = wb.add_sheet('Sheet1')
ws1.write(0, 0, u'セル:A1')
ws1.write(1, 0, u'セル:A2')
ws1.write(0, 1, u'セル:B1')
ws1.write(1, 1, u'セル:B2')
data = StringIO.StringIO()
wb.save(data)

xlwt.save()は引数にファイルライクなオブジェクトをとります。
引数に指定したオブジェクトにExcelデータが書き込まれます。

ZipFileオブジェクトを作成します。

ZipFileオブジェクトのコンストラクタの1番目の引数にファイルライクなオブジェクトを指定します。
引数に指定したファイルライクなオブジェクトに、ZIPデータが書き込まれます。

zipdata = StringIO.StringIO()
zipobj =  zipfile.ZipFile(zipdata, 'w', zipfile.ZIP_DEFLATED)

ZipFileオブジェクトにファイルを登録します。
1番目の引数にファイル名を、2番目の引数にファイルのデータ(バイト列)を指定します。

zipobj.writestr('example.xls', data.getvalue())

ここで、1番目の引数にZipInfoオブジェクトを指定すると、ファイルの情報を細かく設定することが出来ます。

最後に閉じます。

zipobj.close()

以上で、ZIPデータを作成できました。

作成したZIPデータをダウンロードさせるために、HTTPヘッダの設定を行います。

header = Headers()
header.add('Content-Type', 'application/octet-stream');
header.add('Content-Disposition', 'attachment', filename='foo.zip')

最後に、ZIPデータをレスポンスとして返します。

return Response(zipdata.getvalue(), headers=header)

全体のソースコードは以下のようになります。

def index(request):
    import xlwt
    import StringIO
    import zipfile
    from werkzeug.datastructures import Headers
    from werkzeug import Response

    wb = xlwt.Workbook()
    ws1 = wb.add_sheet('Sheet1')
    ws1.write(0, 0, u'セル:A1')
    ws1.write(1, 0, u'セル:A2')
    ws1.write(0, 1, u'セル:B1')
    ws1.write(1, 1, u'セル:B2')
    data = StringIO.StringIO()
    wb.save(data)

    zipdata = StringIO.StringIO()
    zipobj =  zipfile.ZipFile(zipdata, 'w', zipfile.ZIP_DEFLATED)
    zipobj.writestr('example.xls', data.getvalue())
    zipobj.close()

    header = Headers()
    header.add('Content-Type', 'application/octet-stream');
    header.add('Content-Disposition', 'attachment', filename='foo.zip')
    return Response(zipdata.getvalue(), headers=header)

関連ページ

nextCSVを使って、ExcelにCSVファイルのデータを取り込むとき8桁の数字を日付にする方法

nextCSVを使って、ExcelにCSVファイルのデータを取り込むとき8桁の数字を日付にする方法を解説します。

CSVファイルの日付の列が「20100219」のように数字で構成されている場合、CSVファイルをExcelで開いても、数値として認識されます。

Excelで開いたときに日付として認識されるように、CSVファイルのセルの値を変換する方法を紹介します。

今回紹介する処理を行うには、nextCSV バージョン2.1.0以降のバージョンが必要です。

  1. nextCSVを起動して、CSVファイルを開きます。

  2. 日付のセルをダブルクリックして列編集画面を表示します。

  3. 変換条件設定の「追加」ボタンを押し、値変換条件編集画面を表示します。

  4. プルダウンメニューから「セルの値を正規表現で置換する」を選択します。

  5. 検索文字列欄に次のように入力します。

    (\d\d\d\d)(\d\d)(\d\d)
    
  6. 置換文字列欄に次のように入力します。
    $1/$2/$3
    

  7. 「OK」ボタンを押し、列編集画面に戻ります。

  8. 「OK」ボタンを押し、メイン画面に戻ります。

  9. プレビュー欄でセルの値が日付形式になっていることを確認します。

  10. メニューの「変換」→「CSVファイルの作成」を選択します。

  11. ファイル名を指定して保存します。

以上で、CSVファイルの列の値が日付形式に変換できました。

作成したCSVファイルをExcelで開くと、日付として認識されます。

同じ作業を繰り返し行う場合は、nextCSVの自動実行バッチファイルを作成すると、作業を自動化できます。

また、3桁または4桁の数字を日付にしたい場合は、次のように設定します。

検索文字列欄
(\d+)(\d\d)

置換文字列欄
$1/$2

「1001」は「10/01」に変換され、Excelで開いたときは「10月1日」と認識されます。
「123」は「1/23」に変換され、Excelで開いたときは「1月23日」と認識されます。

メールで受信した注文データをExcelで取り込んで注文書・請求書・領収書を作成する方法

メールで受信した注文データをExcelで取り込んで注文書・請求書・領収書を作成する方法。

  1. 受注メールのデータをExcelに取り込む

    (悪い例)
    メールをコピーしてExcelに貼り付け、の作業を繰り返し。

    手作業でコピー&ペーストを繰り返すのは、ミスが発生しやすく効率の悪い方法です。
    一般に、繰り返し行う作業は、ソフトウェアで自動化することができます。

    (良い例)
    D2 メール自動データベース変換ソフト」を使えば、メールを解析してCSVファイルに変換できます。
    自動実行バッチファイルを作成すれば、バッチファイルを起動するだけで作業は完了します。

  2. 表記揺れの修正

    たとえば、郵便番号が半角で入力されているものもあれば、全角で入力されているものもあります。
    ふりがながひらがなで入力されているものもあれば、半角カナで入力されているものもあります。
    このように表記に違いがあるとデータが使いにくくなるため、表記を統一します。

    (悪い例)
    一つ一つ目視でチェックして修正する。

    全角と半角の見分けは難しく、見逃しも起こりがちです。
    ソフトウェアを使って自動化しましょう。

    (良い例)
    nextCSV CSVファイル編集ソフト」を使えば、このような修正を自動化できます。
    自動実行バッチファイルを作成すれば、バッチファイルを起動するだけで作業は完了です。

  3. CSVファイルをExcelに追記する

    (悪い例)
    CSVファイルをダブルクリックしてExcelで開いてコピーし、目的のExcelファイルに貼り付ける。

    CSVファイルをExcelで開くと、セルの値の先頭の「0」が消えてしまう問題が発生します。
    また、毎回同じ作業を繰り返すのも、ミスが入り込む可能性があります。

    (悪い例)
    Excelのテキストファイルウィザードを使ってインポートする。

    Excelのテキストファイルウィザードを使えばCSVファイルのデータを正しくExcelに追加することができます。
    ただ、この方法は手間がかかるのが欠点です。

    (良い例)
    Excel CSV Importer」で、インポート処理を自動化します。
    データ型を判別して正しくインポートしてくれます。
    もちろん自動実行バッチファイルによって自動化できます。

  4. Excelで取り込んで注文書・請求書・領収書を作成する

    (悪い例)
    注文ごとにワークシートをコピーして注文書・請求書・領収書を作成する。

    よくある失敗例です。
    テンプレートのワークシートをコピーして使い回す方法です。
    すぐにワークシートが増えてしまい、収拾がつかなくなります。

    Excelの正しい使い方を知らないと、このような誤った方法をとりがちです。

    データ用のシートと表示・計算用のシートは分けます。
    そして、Excelでは1データ1行で登録してください。
    参考
    「Excelのワークシート設計:データ用ワークシートと表示用ワークシートは分ける」
    「Excelのワークシート設計:データは1データ1行」

    (良い例)
    注文データは、注文データ用のワークシートに1データ1行で登録します。
    新しい注文データがあると、ワークシートの最終行に追加していきます。
    Excel CSV Importer」を使えば自動化できます。

    注文書・請求書・領収書用のワークシートはそれぞれ別に用意します。
    ExcelのVLOOKUP関数を使い、注文番号を入力すると注文データワークシートからデータを引用するようにします。

    納品書ワークシート画像

    このようなワークシート設計にすれば、注文データが増えても破綻しません。

ソフトウェアを上手に組み合わせて使えば、作業が大幅に効率化できることがおわかりいただけたと思います。

Excelのワークシート設計:データは1データ1行

前回は「Excelのワークシート設計の基本「データ用ワークシートと表示用ワークシートは分ける」について解説しました。

今回は、Excelのワークシート設計の基本「データは1データ1行」について解説します。

前回説明したとおりに、データ用ワークシートと表示用ワークシートを別々に作成したとき、データ用ワークシートの見栄えが気になる方が少なくないようです。

納品書データ

上の納品書データの場合、商品ごとに行を分けています。
一つの納品データが2行以上になっています。

確かに見た目はいいですから、そのように作りたいという気持ちはわかります。
ですが、素直に1データ1行にしましょう。

というのも、Excelでは1データ1行でデータを扱うようにできています。
複数行のデータは、Excelでは扱いにくいのです。
たとえば定番の関数VLOOKUPも行単位でデータを取り出しますよね。

データ用ワークシートは、見た目よりも使い勝手を重視して設計してください。

また、データベースについての知識がある人は、正規化したくなるかもしれません。
主キーを注文番号にして、注文者と注文明細を別のワークシートに登録するような方法です。

正規化

気持ちは大変よくわかります。
ですが、このワークシートもExcelでは扱いにくいのです。
正規化したワークシートでは、次の納品書のようなワークシートの作成が難しくなります。
(不可能ではありませんが)

納品書

結局、Excelを上手に使うには、1データ1行でワークシートを設計するのが一番いいようです。
データ用ワークシートは見た目ではなくて、使い勝手を優先して設計してください。
表示用のワークシートは別に用意すればいいのです。

上手にワークシートを設計することで、Excelの使い勝手はよくなり、作業効率も上がります。

Excelをうまく使えていないなと思ったら、ワークシート設計の基本「データ用ワークシートと表示用ワークシートは分ける」「データは1データ1行」を意識して使ってみてください。