読者です 読者をやめる 読者になる 読者になる

アーキテクチャをスマートに。

株式会社ネオジニア代表。ITアーキテクトとしてのお仕事や考えていることなどをたまに綴っています。(記事の内容は個人の見解に基づくものであり、所属組織を代表するものではありません)

Excelシートに入力されたテキストと画像をSQLiteデータベースに登録する(1)

Windows

Androidアプリで SQLiteデータベースを使うのですが、そのデータベースをExcelでエントリーさせたい、というのがそもそもの始まりです。

DBにエントリーするデータは、短いテキストと画像の集まりです。

Excelシートからテキストを抜き出すのは簡単なのですが、貼り付けられた画像が問題です。

画像はJPEGまたはPNG形式でBLOBとして登録したいのです。

これをプログラムを使ってスマートに処理させる方法はないか?

今回はそういうお話です。(かなりエンジニア向けの内容です)

アーキテクチャを考える

最初はVBAを使ってやろうかと思っていたのですが、Excel起動時にマクロのセキュリティ警告が出るのがわずらわしいし、シートにデータを入力するのは別の人なので、マクロの修正をしたいときに困ります。

で次に考えたのがVBScriptでやる方法。

Excelオブジェクトを作ってシートからデータを読み出し、ADODBでSQLiteにデータ登録してやろう、というわけです。
ただしADODBを使うからにはSQLiteODBCドライバをインストールする必要があり、これはこれでちょっとイヤだったのですが、これくらいは目をつぶることにしました。

VBScript」「Excel」「SQLite」などといったキーワードでググりながら調査を進めていたところ、
Excelシートに貼り付けられた画像をBLOBに入れる、という例がなく、難しそうです。

まず、Excelシートに貼り付けられた画像をJPEG形式のバイナリデータとして取得する方法。これもネット上にいろいろ情報がありますが、試行錯誤した結果、VBScriptでは直接画像オブジェクトをバイナリデータとして取得する方法が見つけられませんでした。

そこで、今回はExcelグラフのExport機能と、ADODB.Streamのバイナリ読み込みの合わせ技でを使うことにしました。

これで JPEGデータの Byte() が得られるので、これをSQLiteのBLOBに突っ込んでやろうというわけです。

Excelの画像オブジェクトをJPEG保存し、バイナリデータ化する

ソースコードを以下に貼りつけておきます。

ポイント
  1. 特定のセルにある画像オブジェクトを取得するには、全オブジェクトでまわして objExcel.Intersect で希望のセルにあるかどうかを調べる。
  2. 画像オブジェクトを特定したら、sheet.ChartObjects.Add() でチャートオブジェクトを作り、画像をコピーしてチャートに貼り付ける
  3. Chart.Export でチャートオブジェクトをJPEG形式で保存する。
  4. 保存したJPEGファイルを ADODB.Stream を使ってバイナリモードで読み込む。
'===============================================================================
'Excelシートの セル(6,4) にある画像オブジェクトをJPEG保存し、バイナリ配列化する
'===============================================================================
Option Explicit

'引数チェック
Dim oParam : Set oParam = WScript.Arguments

If oParam.Count = 0 Then
	MsgBox "Excelファイルをドロップしてください。", vbOKOnly, "エラー"
	WScript.Quit -2  'エラー終了
End If

'オブジェクト生成
Dim objFSO : Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Dim objExcel : Set objExcel = CreateObject("Excel.Application")

'一つ目の引数をExcelファイルのパスとする
Dim excelPath : excelPath = oParam(0)  

'Excelでファイルオープン
objExcel.Workbooks.Open(excelPath)     

'1つ目のシートを取得
Dim s : Set s = objExcel.Worksheets(1) 

'シート内のPictureオブジェクトを全件走査
Dim shp, area
For Each shp In s.Pictures
	'セル(D6) の位置にあるかどうかを判定
	Set area = objExcel.Intersect(shp.TopLeftCell, s.Cells(6,4))
	If Not (area Is Nothing) Then
		Dim jpeg : jpeg = XlsPictureToBlob(s, shp)
		MsgBox "JPEGデータ取得。サイズ=" & (UBound(jpeg)+1 - LBound(jpeg))
		
		'あとはお好きなように
		
		Exit For
	End If
Next
	
'Excelファイルを閉じる(保存しますか?の確認を表示しないようにする)
objExcel.DisplayAlerts = False
objExcel.Workbooks(1).Close

objExcel.Quit
Set objExcel = Nothing


'-------------------------------------------------------------------------------
'Excelの画像オブジェクトをバイナリデータにする(Byte配列を返す)
'-------------------------------------------------------------------------------
Function XlsPictureToBlob(sheet, pic)
	
	'一時ファイルのパス
	Dim tempFilePath : tempFilePath = objFSO.BuildPath(objFSO.GetParentFolderName(WScript.ScriptFullName), "temp.jpg")
	
	Dim cht : Set cht = sheet.ChartObjects.Add(10, 10, pic.Width, pic.Height)
	'画像コピー
	pic.Copy   ' または CopyPicture
	'貼り付ける
	cht.Chart.Paste
	'JPEG形式で保存
	cht.Chart.Export tempFilePath, "JPG"
	'グラフを削除
	cht.Delete
	
	'バイナリデータとして読み取る
	Dim objStream : Set objStream = WScript.CreateObject("ADODB.Stream") 
	objStream.Open : objStream.Type = 1
	objStream.LoadFromFile tempFilePath
	objStream.position = 0
	Dim objBin : objBin = objStream.Read
	objStream.Close
	
	'戻り値に設定
	XlsPictureToBlob = objBin
	
	'一時ファイルを削除
'	objFSO.DeleteFile tempFilePath, True
End Function

バイナリ読み込みした結果は、Byte() つまりByteオブジェクトの配列で返されます。
あとはこれをSQLiteのBLOBに登録できればよいのですが、長くなるので次のポストへ。