最初に。参考にしたページ
- 本家ページ -> opencsv – General
- API ドキュメント -> Overview (opencsv 4.1 API)
- リポジトリ -> opencsv download | SourceForge.net
- 実際のエクスポート使用例 -> java-read-write-csv-file/OpenCSVWriter.java at master · callicoder/java-read-write-csv-file
タグ: CSV
ポイント
- CSV 形式のファイル、つまりカンマで区切られたデータのファイルを読み込むことができる。ファイル拡張子が .csv でなくても読み込める。
- 必ずテキスト形式でインポートされるように TextFileColumnDataTypes に指定する配列の作り方を工夫した。
- コネクションは生成されたものだけ削除するように改善した。
- Name オブジェクトの削除方法を改善し、シート名に影響を受けないようにした。
- 他のエクセルなどからも呼び出せるようにブック名を指名するようにした。
CSV ファイルのをエクセルにインポートする条件
- CSV ファイルを VBA からエクセルにインポートしたい。
- CSV の各項目にダブルクオーテーションが付いていてもいなくても、インポートできるようにしたい。
- 読み込み先エクセルの列のデータ型、つまり表示形式を指定したい (先頭の 0 が自動的に除去されないように、などしたい) 。
読み込まれる CSV ファイル
No,氏名,電話番号 1,伊藤健一,0112223333 2,高橋哲也,0114445555 3,佐藤誠,0116667777
VBA コード
- クエリーテーブルを使用する方法で CSV をインポートしている。
- クエリーテーブルを使用してインポートする場合、次の副作用、想定外の出来事、悪影響が発生する。
- インポート対象 CSV ファイルとのコネクションが張られてしまう (エクセルで、データ > 接続、で確認可能のはず) 。これにより、エクセルに読み込んだデータを編集すると元データも同期されてしまう。
- インポート範囲を対象に Name オブジェクトが作成されてしまう (エクセルので、数式 > 名前の管理、で確認可能) 。これといって害は無いと思う。
- 副作用を取り除くために、インポート処理完了後に次の処理を行った。
- コネクションはすべて削除するようにした。
- Name オブジェクトは自動的に作成されたものを狙って削除するようにした。他にもセル範囲に名前をつけた Name オブジェクトが予め存在していたため。
- 自動生成の Name オブジェクトは、名前を指定しても連続して CSV インポートを行うと接尾辞が付与された Name オブジェクトが追加されてしまう。これにより、指定した文字列で Name オブジェクトを削除できない場合がある。
- そこで Name オブジェクトが生成されるときに接尾辞を予め付与することで自動付与の接尾辞を抑制した。接尾辞のフォーマットは、他の Name オブジェクトと重複しないよう yyyymmddhhnnss とした。
- データの先頭に 0 がある場合、取り除かれてインポートされてしまう。そこで、TextFileColumnDataTypes で各列に適用されるデータ型を指定できるようにしている。
- クエリーテーブルの各プロパティについて、必須かどうか、デフォルト値は何か、は調べていない。省略できるプロパティはあると思う。
- TextFilePlatform の値は 932 で、これはマクロの記録で設定された値をそのまま使用している。公式ドキュメントのページを見ても、932 に該当する値は記載されていない。しかし次のページを見てみると正しい値と思うのでこのまま使用する。
追記: 2015年11月22日
Excel のデータコネクション、セルなどにつけた名前を全てく削除するプロシージャコード
''' <summary>
''' エクセルファイルに存在するコネクションを全て削除します。
''' </summary>
Private Sub DeleteConnections()
Do While ActiveWorkbook.Connections.Count > 0
ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
Loop
End Sub
''' <summary>
''' エクセルファイルに存在する名前を全て削除します。
''' </summary>
Private Sub DeleteNames()
Do While ActiveWorkbook.Names.Count > 0
ActiveWorkbook.Names.Item(ActiveWorkbook.Names.Count).Delete
Loop
End Sub
にて DB データをエクスポートする機能を紹介いたしました。
今回は、対となるインポート機能を開発いたします♪
インポート機能のポイント
こちらのシステムでインポート機能を考えます。ポイントは次の 2 点です。
- CSV の 1 行に Parent とその Child を記入する。
- インポートできる子ども (Child) は 2 人まで。
Parent 1-n Child という関係を持っております。
ですのでシステム上は、Parent は必ず 1 つですけれども、それに紐づく Child はいくつあっても問題ありません。
ですけれども、インポート機能におきましては子どもの数の最大数を制限しませんと CSV ファイルの仕様を固めることが難しくなります。
したがいまして、今回は Child の数を最大 2 つとし、それ以上登録したい場合はウェブブラウザから追加する、という運用方法を想定いたしました。
CSV インポート開発の流れ
に沿ってサンプルプロジェクトを作成したところからスタートです。
ソリューションおよびプロジェクト名は Sample3 といたしました。
- コントローラー CsvController.cs をスキャフォールディング作成
- アップロードファイルに対応するモデル CsvFile.cs を作成
- アップロードするファイルの種類を検証する属性クラス UploadFileAttribute の作成
- ビュー Import.cshtml の作成
- インポート処理を司るサービスクラス CsvImportService の作成
- テスト CSV ファイル作成と、インポートの確認
環境
- エクセル 2013
チュートリアル。起動方法から、オブジェクト指向まで。
VBA でクラスを扱うときのやり方。他の言語でのクラスとの比較が嬉しい。
複数データの扱いは、配列ではなくコレクションを使うのが良さそう。「vba コレクション」「vba コレクション for」で検索
コーディングスタイル。命名規則。
- Constant and Variable Naming Conventions
変数の接頭辞をつける時の指針となる。個人的には接頭辞は好みではないけれども。 - 大文字の使用規則
- Excel VBAコーディング ガイドライン案 – Qiita
エディタの設定からプロジェクト例まで。一通り読んでエディタを設定しておくとよさそう。
CSV 取り込みについて
追記: インポート版も作りましたの♪
DB のデータを CSV に出力してウェブブラウザからダウンロードしたいですの！今回、それを実現するコードを残しておきますわ♪
CSV エクスポートの対象となるシステム
エクスポートされる CSV の仕様
- ヘッダ行を出力する。
- 1 行に Parent とその Child を 2 人分まで出力する。
- セルは常にダブルクォーテーション「”」で囲む。
- セルの区切り文字はカンマ「,」とする。
- 行末に区切り文字は付けない。
「1 行に Parent とその Child を 2 人分まで出力」いたしますので、子供が 0 人の場合は子どもの部分が空白となりますし、1 人の場合は 1 人分のみ埋まります。Child が 3 人の場合は、2 人分のみ CSV に書き込まれ、3 人目は記録されない点に注意ですの！
手順 1. CSV エクスポート用のコントローラー、ビューを作成
MySQL には CSV 出力する機能が備わっていることに、初めて気がつきました。
喜び勇んで試してみたものの、Excel ではどうにもうまく読み込めません。特に、セル内で改行があると、次の行という扱いになってしまうのですよね。。。
そこで、何とかしてみたメモを残しておきます。
ポイント
- 今時なので MySQL は UTF-8 です。Excel で読むために Shift-JIS に変更する必要があります。
- セル内での改行は LF、行末の改行は CRLF にする必要があります。
- セルを「”」で囲む場合があるため、「”」をエスケープする必要があります。