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

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

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

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

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

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

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

アーキテクチャを考える

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

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

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

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

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

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

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

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

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

ポイント

特定のセルにある画像オブジェクトを取得するには、全オブジェクトでまわして objExcel.Intersect で希望のセルにあるかどうかを調べる。
画像オブジェクトを特定したら、sheet.ChartObjects.Add() でチャートオブジェクトを作り、画像をコピーしてチャートに貼り付ける
Chart.Export でチャートオブジェクトをJPEG形式で保存する。
保存した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