あるまげどーーーん

某ITベンチャー会社に所属する技術者たちの自習室

CSVをExcelで開いたときに数値の先頭の"0"を表示させるには

きりんです。

プログラムでデータをCSVに出力、それをExcelで開くことって結構あると思いますが、「先頭が"0"の数字列は、Excelで開くと先頭の"0”が表示されない」って問題に前から悩まされていました。

社内で使用するプログラムやデータであればExcelの「外部データの取り込み」で列の形式を「文字列」にして読み込んでくれ、で済ませてしまうところですが、今回はお客様からご要望なので何とかするしかありません。

値をシングルクォートやダブルクォートで括ってみましたが、やはりダメ。 majirou君の記事を参考に、Excelで出力するしかない?

PHP-XLSXWriterの導入とはじめの一歩 - あるまげどーーーん

でも、お客様の使用しているレンタルサーバー上で動作するかどうか確認している時間もないし...。 半ば諦めつつネットでいろいろ検索したところ、以下のページに行き当たりました。

www.webimpact.co.jp

通常のCSVファイルは以下の様な形式ですね?

地区名,市外局番
東京,03
大阪,06

これを読み込んでも、Excelでは「03」は【数値】として扱ってしまい「3」になります。
これが0が消える原因です。
一方で、Excelでは【数値】ではなく【文字列】なら先頭の0が消えること無く扱えます。
そこで、次のようにCSVファイルを作ってみましょう。

地区名,市外局番
東京,=”03″
大阪,=”06″

=で始まる項目はExcelの方言で【数式】を表します。

CSVに記述された数式はExcelで開いた際にちゃんと解釈されるんですね。CSVデータをExcelで開くと、どんなデータであれテキストデータとして解釈される(数式、関数は実行されない)と思い込んでいました。

追加情報

これにて一件落着なのですが、他人の記事を紹介しただけでは何の学習にもつながらないので、ちょっとだけ追加で実験してみました。

使用したExcelは2016になります。

実験1

まず、出力したデータをExcelで取り込み、そこで集計等を行うとどうなるか試してみました。

f:id:nkhr22r:20181210111136p:plain

セルA1とA2がCSVの出力データです。="001"="002"ですね。ちゃんと先頭に0が表示されています。

CSVファイルをダブルクリックしてExcelで開き、そこに集計等を追加していきます。 A3で=SUM(A1:A2)、A4で=A1+A2、A5で=A1&A2の演算を行ってみました。

  • A3のSUM関数は引数に数値しかとらない、あるいは数値以外は無視する(あるいは0として扱う)ため、合計が0となる
  • 演算子を使用した場合、セルの値に応じて書式(型)が変換される
    • A4の数値を対象とした演算子(+)の場合、セルの値が計算可能な内容なら数値型に変換し計算が行われる
    • A5の文字列を対象とした演算子(&)の場合は文字列型に変換し処理が行われる

元データがどんな形式であれExcel上で開いているので、当然ですがExcel本来の挙動となります。

実験2

次に、CSV生成時点で上記の数式・関数を出力し、それをExcelで開いたときにどうなるかを試してみました。

こちらがCSVデータ。

="001"
="002"
=SUM(A1:A2)
=A1+A2
=A1&A2

それをExcelで開いた結果。

f:id:nkhr22r:20181210111920p:plain

実験1と同じ結果となりました。 CSVに数式や関数を書いても、それをExcelで開けばちゃんと動くんですね。

実験3

これは確認するまでもありませんが、実験1、2においてExcelで開いたCSVファイルを、Excel上でそのままCSVで保存したらどうなるかを確認しました。

保存前

="001"
="002"
=SUM(A1:A2)
=A1+A2
=A1&A2

保存後

001
002
0
3
001002

Excel上で関数や演算が実行された結果のみがCSVに出力されました。

以上、CSVに関する小ネタでした。

BoostnoteとMarkdownでUML

きりんです。

Markdownでドキュメントを書きたい、できれば図表もテキストベースで書きたい、という事でMarkdownUMLの編集環境を模索してきました。 そんな中、最近知った「Boostnote」の使い勝手が良かったので、今まで試した環境と合わせてメモを残しておきたいと思います。

今まで試したもの

Atomエディタ + markdown-preview-enhanced

数年前に試したこの組み合わせ、プラグインのほかにPCにGraphvizのインストールも必要だったかもしれません。Atomエディタが重く感じられたので、だんだん使わなくなりました。

Nextclowd + Markdown Editorアプリ

サーバーにNextcloudをインストール、そこにMarkdown Editorアプリを追加することでブラウザ上でMarkdownUMLが書けるようになります。 Nextcloudは編集履歴が保存されるので簡単なバージョン管理もできます。難点としてはインストール先のレンタルサーバーが非力なせいかNextcloud自体の動きが時々怪しい事です。

Crowi + PlantUML

Crowiは会社のmajirou君が社内の情報共有のために用意してくれたサーバーで、Nextcloud同様ブラウザ上でMarkdownの編集を行います。別途用意したPlantUMLにUML図の画像を描画させそれをCrowi上(というかブラウザ上)に表示する、といった感じだったと思います。こちらもバージョン管理がされるので、どこを変更したか一目瞭然で使いやすいです。

Boostnote

Boostnote | Boost Happiness, Productivity, and Creativity.

上記環境や他のMarkdownエディタと比べた時のBoostnoteの(私にとっての)優位点は以下になります。

環境構築が簡単

やはりサーバーの設定などが必要になるとハードルがぐっと上がります。その点Boostnoteはダウンロードしてインストールすればすぐに使えるので導入がとても簡単です。

早い・軽い

Boostnoteと同様にローカルにインストールするAtomエディタと比較すると、起動も動作も早く軽く、最近よくフリーズする会社のPCでも快適に動作します。また、サーバーにインストールした環境と違いログイン不要ですぐに使えます。

UMLが書ける

UMLが書けるMarkdownエディタが少ない中、Boostnoteはmermaid.jsでもPlantUMLでもどちらの形式でもUMLが書けます。

イマイチなところ

一番下の行を編集中にその行が画面の外にはみ出してしまうことがあり、編集しづらくて困ることがあります。また、オンラインでの同期や編集履歴(バージョン管理)の機能はないので、別途自分で工夫する必要があります。いずれも致命的なデメリットではありませんが、ここが解消されるとさらに使い勝手がよくなると思います。

記述例

Markdown、PlantUMLの記述例です。

チェックリスト

f:id:nkhr22r:20181206235120p:plain

- [x] 予算申請
- [x] ハードウェア購入
- [ ] OSインストール
- [ ] 開発作業
- [ ] テスト
- [ ] 公開
シーケンス図

f:id:nkhr22r:20181206230816p:plain

@startuml
autonumber
skinparam {
  defaultFontName Migu 1P
  activityFontSize 14
}

actor ユーザー
participant Webアプリ
box "外部システム"
    participant WebAPI
    participant DB
end box

activate ユーザー
ユーザー->Webアプリ:リクエスト
activate Webアプリ
Webアプリ->WebAPI:リクエスト
activate WebAPI
WebAPI->DB:検索
activate DB
DB-->WebAPI:結果
deactivate DB
WebAPI-->Webアプリ:JSONデータ
deactivate WebAPI
Webアプリ-->ユーザー:HTMLデータ
deactivate Webアプリ
note over ユーザー:画面表示
deactivate ユーザー
@enduml
ガントチャート

f:id:nkhr22r:20181209215847p:plain

@startuml
project starts the 2018/11/19
saturday are closed
sunday are closed

[機能A実装] starts at 2018/11/19 and ends at 2018/11/23
[機能B実装] starts at 2018/11/22 and ends at 2018/11/28
[機能C実装] lasts 3 days
[機能A実装] -> [機能C実装]
[機能B実装] -> [機能C実装]
[統合テスト] happens at [機能C実装]'s end
@enduml
ワイヤーフレーム

f:id:nkhr22r:20181206234628p:plain

@startuml
salt
{+
ログイン
会員ID|"abc          "
パスワード|"********     "
[    送信    ]|[   リセット   ]
}
@enduml
その他

このほかにもPlantUMLではユースケース図やクラス図などの記述も可能です。詳しくは本家サイトをご参照ください。

Open-source tool that uses simple textual descriptions to draw UML diagrams.

業務での活用方法(模索中)

アイディアを練ってる段階や、下書きレベルではBoostnoteでサクサク文書を作成していきます。

作成した文書を社内で共有したい場合はBoostnoteからPDFで印刷してメール添付、記述形式に比較的互換性のあるCrowiに張り付け、あとは印刷して渡す、のどれかになるかと思います。

すでにやり取りがある業者ならPDFで渡しても問題ないと思いますが、お客様にはやはりOffice文書で渡す方が無難なので今は手動で転記しています。

今後の目標としてはRedmineUMLが使えるようにプラグインの導入を検討することと、編集データのオンラインストレージとの同期、バージョン管理方法の検討があげられます。


(2018.12.11追記) 公式のブログにMarkdown記法の記事がありました。ご参考まで。

仕事効率、学習効率を加速させるMarkdown記法の紹介 - Boostnote

PHP-XLSXWriterの導入とはじめの一歩

majirou です!今回が初投稿となります。

社内システムではPHP-Excelでエクセル帳票をゴリゴリ作成していましたが、 ふと気づいてみると、PHP-Excelは非推奨となり、PHP-Spreadsheet なるものがあるとか。

さっそく、導入して試しに4,000行×120カラムぐらいの一覧データを作成してみたところ…

Allowed memory size of 8388608 bytes exhausted

Oh...

PHP-Excelの時も同じようなエラーが出たので、断念してCSVで運用を任せていたので、 「後継機なら、後継機ならメモリーエラーとか吐かずにやってくれるはず!」という思いは打ち砕かれました。

そんな矢先、調べれば出てくるものですね。

PHP_XLSXWriter

READMEには強気なフレーズが!!

Never run out of memory with PHPExcel again.

もう一度、書きます。

Never run out of memory with PHPExcel again.

…すばらしい!

ということで早速導入してみました。

導入

composer でインストールします。

# composer require mk-j/PHP_XLSXWriter

Using version ^0.37.0 for mk-j/php_xlsxwriter
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 1 install, 0 updates, 0 removals
Writing lock file
Generating autoload files

実装

公式サンプルをそのまま実施します。

include_once( VENDORPATH . "/mk-j/php_xlsxwriter/xlsxwriter.class.php" ) ;

$header = array(
    'c1-text'=>'string',//text
    'c2-text'=>'@',//text
    'c3-integer'=>'integer',
    'c4-integer'=>'0',
    'c5-price'=>'price',
    'c6-price'=>'#,##0.00',//custom
    'c7-date'=>'date',
    'c8-date'=>'YYYY-MM-DD',
);
$rows = array(
  array('x101',102,103,104,105,106,'2018-01-07','2018-01-08'),
  array('x201',202,203,204,205,206,'2018-02-07','2018-02-08'),
  array('x301',302,303,304,305,306,'2018-03-07','2018-03-08'),
  array('x401',402,403,404,405,406,'2018-04-07','2018-04-08'),
  array('x501',502,503,504,505,506,'2018-05-07','2018-05-08'),
  array('x601',602,603,604,605,606,'2018-06-07','2018-06-08'),
  array('x701',702,703,704,705,706,'2018-07-07','2018-07-08'),
);
$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', $header);
foreach($rows as $row){
    $writer->writeSheetRow('Sheet1', $row);
}

$writer->writeToFile( '/tmp/php_xlsxwriter_test.xlsx' ) ;

ポイント

ヘッダーで予め型を指定して流し込むようです。

$header = array(
    'c1-text'=>'string',//text
    'c2-text'=>'@',//text
    'c3-integer'=>'integer',
    'c4-integer'=>'0',
    'c5-price'=>'price',
    'c6-price'=>'#,##0.00',//custom
    'c7-date'=>'date',
    'c8-date'=>'YYYY-MM-DD',
);

結果

先ほどの4,000行×120カラムぐらいの一覧データですが、 Php-Excelで生成すると1分ほど経過したあたりで、メモリーオーバーとなっていたのですが、 Php-xlsxwriterでは、なんとたったの10秒!!

これで1分待った挙句の「何も生成できませんでした!」という絶望を味わうことのないサービスを社内に還元できそうです。