2017-08-24

PhpSpreadsheetで数値、文字列、金額、日付、画像をシートに書き込むサンプル

せっかくなので昨日書いた記事のPhpSpreadsheet版も書いてみることにした。

PHPExcelからPhpSpreadsheetに乗り換えたいという人なんかほとんど居ないと思うけど、そういうひとにとっては参考になるかもしれない。

ariarijp.hatenablog.com

環境

PHP 7.0.22
PHPExcel 1.0.0-beta

以前の記事でPhpSpreadsheetを紹介したころは開発版しか公開されていなかったけど、現在はbetaリリースまで進んでいる模様。

composer.jsonはこんな感じ。

{
    "require": {
        "phpoffice/phpspreadsheet": "1.0.0-beta"
    }
}

ちなみにドキュメントがRead the Docsで公開されているあたりに今どきっぽさがある。

PhpSpreadsheet Documentation

サンプルコード

サンプルで使用している画像はPHPExcelの記事同様PHPのロゴ。

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Cell;
use PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;

date_default_timezone_set('Asia/Tokyo');

// http://php.net/images/logos/php-med-trans.png
$imagePath = 'php-med-trans.png';

// サンプルデータを適当に生成
$rows = array_map(function ($i) {
    return [
        $i, // 数値
        uniqid(), // 文字列
        rand(10000, 100000), // 数値(金額)
        strftime('%Y-%m-%d', rand(0, time())), // 日付
    ];
}, range(1, 20));

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// セルの書式を指定する時に必要
// http://phpspreadsheet.readthedocs.io/en/develop/topics/recipes/#write-a-date-or-time-into-a-cell
Cell::setValueBinder(new AdvancedValueBinder());

$offset = 1;
foreach ($rows as $i => $row) {
    $rowNum = $i + $offset;

    // A列からD列までの値を書き込み
    $sheet->fromArray($row, null, Cell::stringFromColumnIndex(0) . $rowNum);

    // C列の書式を金額(JPY)に変更
    $sheet->getCell(Cell::stringFromColumnIndex(2) . $rowNum)
        ->getStyle()
        ->getNumberFormat()
        ->setFormatCode('"¥"#,##0');

    // D列の書式をYYYY/MM/DD形式に変更
    $sheet->getCell(Cell::stringFromColumnIndex(3) . $rowNum)
        ->getStyle()
        ->getNumberFormat()
        ->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);


    (new Drawing())
        ->setPath($imagePath)
        ->setWorksheet($sheet)
        ->setHeight(30)
        ->setCoordinates(Cell::stringFromColumnIndex(4) . $rowNum);

    // 行の高さを画像にあわせて変更
    $sheet->getRowDimension($rowNum)
        ->setRowHeight(30);
}

// Excel(.xlsx)として書き出す
$writer = new XlsxWriter($spreadsheet);
$writer->save('sample.xlsx');

生成されるファイル

こんな雰囲気。Excelが手元にないのでLibreOfficeで表示。

f:id:ariarijp:20170823220105p:plain

まとめ

PHPExcelに比べてドキュメントが整っているのが良い。PHPExcelと見比べるとわかるけど、だいたいPHPExcelと同じインターフェースなので、移行しようと思ったらそれなりにできそうではある感じ。

名前空間がPSR-4準拠なので、そのあたりの書き味もPhpSpreadsheetの方が良い感じ。

最近Excelばかり触っているので、Excelの拡張機能も作ってみたくなってきた。

【購入特典付】Microsoft Office 365 Solo (1年版)|オンラインコード版|Win/Mac対応

出版社/メーカー: マイクロソフト
発売日: 2014/10/17
メディア: Software Download
この商品を含むブログ (6件) を見る

2017-08-23

PHPExcelで数値、文字列、金額、日付、画像をシートに書き込むサンプル

最近どうもExcelを扱うことが多いので、久しぶりにPHPExcelを触ってみたメモ。

以前PhpSpreadsheetを紹介したけれど、あえてPHPExcelを選んだのは困った時に検索などで情報を得やすいため。

ariarijp.hatenablog.com

環境

PHP 7.0.22
PHPExcel 1.8.1

composer.jsonはこんな感じ。

{
    "require": {
        "phpoffice/phpexcel": "^1.8"
    }
}

サンプルコード

サンプルで使用している画像はPHPのロゴ。

<?php

require 'vendor/autoload.php';

date_default_timezone_set('Asia/Tokyo');

// http://php.net/images/logos/php-med-trans.png
$imagePath = 'php-med-trans.png';

// サンプルデータを適当に生成
$rows = array_map(function ($i) {
    return [
        $i, // 数値
        uniqid(), // 文字列
        rand(10000, 100000), // 数値(金額)
        strftime('%Y-%m-%d', rand(0, time())), // 日付
    ];
}, range(1, 20));

$book = new PHPExcel();
$sheet = $book->getActiveSheet();

$offset = 1;
foreach ($rows as $i => $row) {
    $rowNum = $i + $offset;

    // 数値をセルに書き込み
    $sheet->setCellValueByColumnAndRow(0, $rowNum, $row[0]);

    // 文字列をセルに書き込み
    $sheet->setCellValueByColumnAndRow(1, $rowNum, $row[1]);

    // 数値をセルに書き込んで、書式を日本円に変更
    $sheet->setCellValueByColumnAndRow(2, $rowNum, $row[2], true)
        ->getStyle()
        ->getNumberFormat()
        ->setFormatCode('"¥"#,##0');

    $dt = DateTime::createFromFormat('Y-m-d', $row[3]);
    $date = PHPExcel_Shared_Date::PHPToExcel($dt, true, 'Asia/Tokyo');

    // 日付をセルに書き込んで、書式をYYYY/MM/DD形式に変更
    $sheet->setCellValueByColumnAndRow(3, $rowNum, $date, true)
        ->getStyle()
        ->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

    // 画像をセルに書き込み
    (new PHPExcel_Worksheet_Drawing())
        ->setPath($imagePath)
        ->setWorksheet($sheet)
        ->setHeight(30)
        ->setCoordinates(PHPExcel_Cell::stringFromColumnIndex(4) . $rowNum);

    // 行の高さを画像にあわせて変更
    $sheet->getRowDimension($rowNum)->setRowHeight(30);
}

// ファイルに書き出し
$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('sample.xlsx');

生成されるファイル

こんな雰囲気。Excelが手元にないのでLibreOfficeで表示しています。

f:id:ariarijp:20170822232143p:plain

まとめ

見やすいドキュメントが見つからなかったものの、サンプルコードを読みつつ、PhpStormなどでコード補完が使えればなんとか使える。

レポートなどではGoogle スプレッドシートが使われることも多くなってきていますが、まだまだExcelとはお付き合いが続きますね。

仕事で使っているのがMicrosoft Office for Mac Home and Business 2011なので、そろそろOffice 365あたりに変えたいところ。特に評価シートを書いている途中で落ちるのがつらい。

【購入特典付】Microsoft Office 365 Solo (1年版)|オンラインコード版|Win/Mac対応

出版社/メーカー: マイクロソフト
発売日: 2014/10/17
メディア: Software Download
この商品を含むブログ (6件) を見る

2017-05-28

Google Spreadsheetからデータを取得してPandasのDataframeに変換する例

最近はPythonを書く機会が多いのでPythonネタをメモ程度に書き留めておきます。

事前準備

データの準備

動作確認用のスプレッドシートを作成し、サービスアカウントで編集できるように権限を設定します。

(GoogleのAPIを使用するためにサービスアカウントを使用しますが、設定手順は割愛します)

そのスプレッドシートに「iris」という名前のシートを作成し、シートの内容を以下のようにします。

f:id:ariarijp:20170528204805p:plain

Irisのデータは以下のリンクなどから入手できます。

UCI Machine Learning Repository: Iris Data Set

環境の準備

venv などを使用して、以下の requirements.txt を使用できるようにしてください。

google-api-python-client==1.6.2
httplib2==0.10.3
numpy==1.12.1
oauth2client==4.1.0
pandas==0.20.1
pyasn1==0.2.3
pyasn1-modules==0.0.8
python-dateutil==2.6.0
pytz==2017.2
rsa==3.4.2
six==1.10.0
uritemplate==3.0.0

コード例

処理についてはコメントを参照してください。

import httplib2
import os

import sys
from apiclient import discovery
from oauth2client.service_account import ServiceAccountCredentials

import pandas as pd

CLIENT_SECRET_FILE = os.environ.get('CLIENT_SECRET_FILE', 'client_secret.json')
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
DISCOVERY_URL = 'https://sheets.googleapis.com/$discovery/rest?version=v4'

credentials = ServiceAccountCredentials.from_json_keyfile_name(
    filename=CLIENT_SECRET_FILE,
    scopes=SCOPES)
http = credentials.authorize(httplib2.Http())
service = discovery.build(serviceName='sheets',
                          version='v4',
                          http=http,
                          discoveryServiceUrl=DISCOVERY_URL)

# スプレッドシートのIDをコマンドライン引数から取得する
spreadsheet_id = sys.argv[1]

# スプレッドシートからデータを取得する
values = service.spreadsheets().values() \
    .get(spreadsheetId=spreadsheet_id,
         range='iris!A:E') \
    .execute() \
    .get('values', [])

# 取得したデータをDataframeにする
df = pd.DataFrame \
    .from_records(data=values[1:],
                  columns=values[0])

# 全ての列がobject型になってしまうので、数値の列の型をfloatに変換する
df = df.astype({
    'sepal_length': float,
    'sepal_width': float,
    'petal_length': float,
    'petal_width': float,
})

# 動作確認
print('[df.describe]')
print(df.describe())
print()
print('[df.head]')
print(df.head(5))

実行

$ python example.py スプレッドシートID
[df.describe]
       sepal_length  sepal_width  petal_length  petal_width
count    150.000000   150.000000    150.000000   150.000000
mean       5.843333     3.054000      3.758667     1.198667
std        0.828066     0.433594      1.764420     0.763161
min        4.300000     2.000000      1.000000     0.100000
25%        5.100000     2.800000      1.600000     0.300000
50%        5.800000     3.000000      4.350000     1.300000
75%        6.400000     3.300000      5.100000     1.800000
max        7.900000     4.400000      6.900000     2.500000

[df.head]
   sepal_length  sepal_width  petal_length  petal_width      species
0           5.1          3.5           1.4          0.2  Iris-setosa
1           4.9          3.0           1.4          0.2  Iris-setosa
2           4.7          3.2           1.3          0.2  Iris-setosa
3           4.6          3.1           1.5          0.2  Iris-setosa
4           5.0          3.6           1.4          0.2  Iris-setosa

まとめ

とにかくなんでもPandasのデータフレームに持ち込めれば、Pandasの強力な機能の数々を使うことができるので、様々なデータソースを使った処理を書いたり、分析したりする人にとって、Pandasはうれしいツールですね。もうちょっと勉強しつつ、実践投入していく予定です。

Pythonによるデータ分析入門 ―NumPy、pandasを使ったデータ処理

作者: Wes McKinney,小林儀匡,鈴木宏尚,瀬戸山雅人,滝口開資,野上大介
出版社/メーカー: オライリージャパン
発売日: 2013/12/26
メディア: 大型本
この商品を含むブログ (19件) を見る

この本は図書館で借りたことがあったけど、手元に置いておきたいかも。

2017-04-12

React NativeでCrashlyticsを使う手順

簡単だけど記録として残しておくための記事です。

前提

Macを使っている
- この記事ではiOSアプリについての手順のみ紹介します
React Nativeがインストール済みで使ってiOSアプリを作ったことがある
- HelloWorld程度の知識で十分です
- 使用したバージョン
  - react-native-cli: 2.0.1
  - react-native: 0.43.3
Xcodeを少しだけ触ったことがある
- ビルド、実行ぐらいができれば十分です
FabricのMacアプリがインストールされている
Crashlyticsがなにをしてくれるかをざっくり知っている

手順

プロジェクトを作成する

まずはReact Nativeのプロジェクトを作成します。

$ react-native init Hello

しばらくするとプロジェクトのディレクトリが作成されます。

Fabricアプリを使ってCrashlyticsを導入する

アプリを追加する

FabricのMacアプリで「New App」を選ぶと、Mac内にある.xcodeprojを探してくれるようです。

f:id:ariarijp:20170412135814p:plain

先程作成したプロジェクト内の.xcodeprojファイルがあると思うので、それを選択します。

使用するツールを選択する

次に使用したいツールを選択します。

f:id:ariarijp:20170412135830p:plain

今回はCrashlyticsのみをInstallして次に進みます。

ビルドスクリプトを編集する

続いて、Xcodeでビルドスクリプトを編集します。Fabricアプリで以下の画面が表示されるとともに、Xcodeが起動します。

f:id:ariarijp:20170412135846p:plain

プロジェクトの設定から「Build Phases」を選択し、左上の「+」ボタンから「New Run Script Phase」を選択します

「Run Script」のブロックが追加されるので、そこにFabricアプリに表示されているコマンドを貼り付けます。

f:id:ariarijp:20170412142658p:plain

コマンドを貼り付けたら、Cmd-Bなどでアプリをビルドします。

ライブラリをインストールする

ビルドが完了すると、Fabricアプリの画面が以下のように切り替わります。

f:id:ariarijp:20170412140625p:plain

この画面に切り替わったら、Crashlyticsを使用するために必要なライブラリをXcodeプロジェクトにインストールします。

FabricアプリのバッグのようなアイコンをXcodeプロジェクトにドラッグアンドドロップすると、以下のような画面が表示されます。

f:id:ariarijp:20170412141203p:plain

「Finish」ボタンをクリックして先に進みます。

コードを編集する

ライブラリをインストールすると、以下のような画面が表示されます。

f:id:ariarijp:20170412141403p:plain

画面でハイライト表示されているコードをコピーして、プロジェクト内のAppDelegate.mファイルに貼り付けます。

f:id:ariarijp:20170412141659p:plain

せっかくReact Nativeを使っているのにObj-Cだなんて辛い！と思うかもしれませんが我慢してください。

コードを追加し終わったら、Fabricアプリの画面の「Next」をクリックして先に進みます。

アプリを起動する

Fabricアプリが以下のような画面になるので、Xcodeを使用し、Cmd-Rなどでアプリを実行します。

f:id:ariarijp:20170412141942p:plain

無事にアプリが起動すれば、iOSシミュレータで以下のような画面が表示されます。

f:id:ariarijp:20170412142233p:plain

あわせて、Fabricアプリも以下のような画面に切り替わります。

f:id:ariarijp:20170412142310p:plain

これでCrashlyticが導入できました。Fabricの画面を見てみるとHelloアプリが追加されています。

Mastering React Native

作者: Eric Masiello,Jacob Friedmann
出版社/メーカー: Packt Publishing
発売日: 2017/01/11
メディア: Kindle版
この商品を含むブログを見る

2017-03-27

PHPExcelの後継っぽいPhpSpreadsheetでExcelファイルを読み書きするサンプルコード

PHPでExcel

PHPでExcelを操作する。というと「やめておけ」という言葉の次に上がってくる有名なライブラリは、PHPExcelではないかと思います。

github.com

溜まりに溜まったIssuesとPull Requestsの数を見ると不安な気持ちになってしまいますが、後継のライブラリーが開発中のようです。

github.com

PHPExcel vs PhpSpreadsheet ? に書いてある通り、PHPExcelと互換性のない変更が含まれるため別プロジェクトになっているようで、開発もPhpSpreadsheetに注力しており、PHPExcelはもうメンテナンスされていないということについても明言されています。

サンプルコード

まだ安定版がリリースされていませんが、PhpSpreadsheetを使った簡単なサンプルコードを書いてみました。

動作環境

HomebrewでインストールしたPHP7.0で動作確認しました。

$ php -v
PHP 7.0.17 (cli) (built: Mar 18 2017 20:13:50) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies

composer.json

composer.json は以下のようになっています。

{
    "require": {
        "phpoffice/phpspreadsheet": "dev-develop"
    }
}

安定版がなので dev-develop を指定しています。

読み込みに使用するExcelファイル

以下のようなファイルを使用します、手元にExcelがないので、ファイルはNumbersで作りました。

f:id:ariarijp:20170326200437p:plain

サンプルコード本体

Excelファイルから特定の範囲を読み出し、各値を2乗したものを別ファイルに書き出すものです。

書き出し先はExcelファイルとCSVファイルの2パターン用意しました。

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Settings;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\Writer\CSV as CSVWriter;

/*
 * 読み込みサンプル
 */
$reader = new XlsxReader();
$spreadsheet = $reader->load('sample1.xlsx');
$sheet = $spreadsheet->getActiveSheet();

// B2からD4までの領域を2次元配列として取得する
$dataArray = $sheet->rangeToArray('B2:D4');

/*
 * 書き出しサンプル
 */
// 上記のサンプルコードで読み出した各値を2乗する
array_walk_recursive($dataArray, function (&$x) {
    $x = $x ** 2;
});

// B1を起点として2次元配列をシートに書き込む
$sheet->fromArray($dataArray, null, 'B2');

// Excel(.xlsx)として書き出す
$writer = new XlsxWriter($spreadsheet);
$writer->save('sample2.xlsx');

// CSVとして書き出す
$writer = new CSVWriter($spreadsheet);
$writer->save('sample2.csv');

実行結果

実行結果として書き出された sample2.xlsx は以下のようになります。

f:id:ariarijp:20170326200831p:plain

また、 sample2.csv はこのようになります。

"","列1","列2","列3","列4"
"行1","1","4","9","4"
"行2","25","36","49","8"
"行3","81","100","121","12"
"行4","13","14","15","16"

まとめ

まだ開発中ではありますが、どうしてもPHPでExcelを操作しなければならない時はPHPExcelとあわせてPhpSpreadsheetも検討してみるといいかもしれません。

なんだかんだExcelとの付き合いは続くものですね。

Microsoft Excel 2016 (最新)|オンラインコード版|Windows版

出版社/メーカー: マイクロソフト
発売日: 2015/09/30
メディア: Software Download
この商品を含むブログを見る