ColdFusion 9から追加されたExcel操作は、Excelのセルの値をColdFusionで読み込んでクエリオブジェクトに変換したり、Excelに値やフォーマットを指定する事ができる便利な機能です。Excelファイルへのさまざまな処理に対応するためにcfspreadsheetタグや多数のCF関数が用意されていますが、CFタグや関数に汎用化されることによって、どうしても制限等が発生する事もあります。今回はXLSファイルにセルのスタイルを設定するにあたって起こる制限を回避する例を紹介します。

このページ（ColdFusion 実験室）で紹介する内容について

ColdFusion 実験室で公開している情報は、ColdFusionの標準の機能では無いため動作の保証外（未サポート）となります。下記の事項も含めてそれらを予めご認識頂いた上で、参考にされるかどうかをご判断下さい。

• このページ内容や参考プログラムは、内容の正確さや最新さを保証するものではありません
• 今後メーカーより提供されるアップデートやバージョンアップなどで、この記事の内容が動作しなくなるかもしれません
• 紹介している外部のライブラリ等についての使用許諾範囲は、ライブラリのダウンロードサイト等にて各自ご確認下さい
• 特に記載が無い限りは、Windows 64bit OS上の 64bit 版ColdFusion10 評価版で試しています。それ以外の異なるOS（32bit/64bit）やエディションなどでは確認していませんので、動き等が異なる場合はご了承下さい

最初に

Excelのファイル形式にはxls形式とxlsx形式があります。xlsx形式はExcel2007から追加されたXMLベースの新しいファイル形式です。
（参考： http://office.microsoft.com/ja-jp/word-help/HA010006935.aspx）

ColdFusionでは「Apache POI」と呼ばれるJavaベースのライブラリを使用し、xls, xlsxの両方の形式に対応しています。 xlsxの形式の方が使用できる行・列が増えるなどメリットもあるのですが、私はなるべくxls形式を利用しています。 というのも、ファイルサイズの大きいxlsx形式のExcelファイルに対して処理を行った際、一部で非常に長い時間がかかったり out of memory エラーが発生する事を確認しているからです。この傾向はColdFusionだけでなく、Apache POIでも多くの報告がされていました。
（参考： http://ap.atmarkit.co.jp/bbs/core/fjava/26689）

ただ、xls形式で処理を行ってセルに書式をセットした際、Excel2003などでそのファイルを開いた際に「セルの書式が多すぎるため、書式を追加できません。」というエラーが表示される場合があります。このエラーは、xls形式の制限（4000）を超えて書式のセットが行われたファイルを開いた際などに発生するエラーです。
（参考：http://okwave.jp/qa/q3220052.html）

セルに多数の書式をセットする状況としては、現在のColdFusion 9, 10の動作では SpreadsheetFormatCellRange()関数などを使用して複数のセルにスタイルをセットした場合、セルの数だけ内部でスタイルが作られているようです。セルの選択範囲によっては異なる動作になる場合もあるようですが、私が「セルの書式が多すぎる〜」エラーに最も遭遇したのは、このパターンの処理でした。この問題の回避するために、あらかじめExcelにスタイルを定義しておきColdFusionではセルに値を入れることだけを行うなどをして対処していましたが、やっぱり動的な処理でセルに罫線を引きたいときなどは不便です。そこで、POIライブラリをColdFusionから直接呼び出し、POIでスタイルをコピーして行こうと思い、試してみました。

この方法でもうまくいかない場合はあるかと思いますが、試した限りの内容をご紹介します。

準備

これまでのラボ（１．２．）とは違い、Apache POIはColdFusionに同梱されています。従ってPOIライブラリのダウンロードなどの準備は必要ありません。

呼び出すメソッドなどは、Webで検索して引っかかったページを参考に、そのコードをColdFusionベースに移植していきました。注意が必要なのは、ColdFusionでは .xls も .xlsx も汎用的に呼び出せるように実装されていますが、Apache POIを直接呼び出す際は、ファイル形式に合わせて呼び出すライブラリが異なります。例えばExcelワークブックを読み込むために使用するクラスは、

　　.xls形式の場合…org.apache.poi.hssf.usermodel.HSSFWorkbook
　　.xlsx形式の場合…org.apache.poi.hssf.usermodel.XSSFWorkbook

となり、使用するクラスが異なるので注意が必要して下さい。

セルにどのようなスタイルを適用するか

通常のColdFusionで処理を行った場合

SpreadsheetFormatCellRange()関数を使ってセルにスタイルを設定した例は下記となります。あらかじめ同じフォルダにsample.xlsと名前で.xlsファイルを置いておき、そのファイルを読み込みスタイルをセットした後、output.xlsという名前でファイルを書き出しています。

<cfscript>
//対象のExcelファイル(xlsファイル)のファイル・パスを指定
sourceXLSFile = ExpandPath('sample.xls');

//編集したExcelファイルの保存先（ファイル・フォルダ）を指定
outputXLSFile = ExpandPath('output.xls');

objExcel = SpreadsheetRead(sourceXLSFile, 1);
xlsFormat = StructNew();
xlsFormat.FONT = "ＭＳ ゴシック";
xlsFormat.BOLD = "true";
xlsFormat.ALIGNMENT = "center";
xlsFormat.VERTICALALIGNMENT = "vertical_center";
xlsFormat.TOPBORDER = "thin";
xlsFormat.BOTTOMBORDER = "thin";
xlsFormat.LEFTBORDER = "thin";
xlsFormat.RIGHTBORDER = "thin";
SpreadsheetFormatCellRange(objExcel, xlsFormat, 1, 1,1000,20);

SpreadSheetWrite(objExcel,outputXLSFile,true);
</cfscript>

上記のプログラムを実行すると、同じフォルダにoutput.xlsファイルが作成されます。オリジナルのsample.xlsとoutput.xlsとのファイルサイズに大きな違いが出ています。これは20000セルにスタイルを適用したことが影響していると思われます。

このoutput.xlsファイルを Excel2003 で開くと、下記のような「セルの書式が多すぎる」エラーや、「エラーが発生し、ブックの破損を回避するため、いくつかの書式情報を削除する必要がありました。書式情報を確認し直して下さい。」が表示されます。

そのあと開いたブックを確認すると、セットしたスタイルが途中までしか適用されていませんでした。.xlsファイルを開いた際のエラーメッセージの通り、以後の書式情報が削除された結果のようです。

実験�@： 最初にスタイルをColdFusionでセットし、そのスタイルをPOIの処理で他のセルに反映させる

まずはPOIの処理を最低限にするために、スタイルのセットまでをColdFusionの処理で行いました。ただ、この場合は、一旦ファイルを書き出す必要があるようです（もしかしたらExcelオブジェクトをそのままPOIの処理に継続できるかもしれませんが、現時点では分かりませんせんでした）。そのため、ColdFusionのTempDirectory（GetTempDirectory()関数でどこのディレクトリかを確認できます）に一時ファイルを書き出して保存。そのあと、POIを使ってファイルを読み込んで、最終的にpoi_output.xlsというファイルを出力する方法を試してみました。

<cfscript>
//対象のExcelファイル(xlsファイル)のファイル・パスを指定
sourceXLSFile = ExpandPath('sample.xls');
//編集したExcelファイルの保存先（ファイル・フォルダ）を指定
outputXLSFile = ExpandPath('poi_output.xls');
//対象のシート番号を指定
sheetNum = 1;
//スタイルを取得するセル位置（rowとcolumn）を指定
rowNum = 1;
columnNum = 1;
//対象のセル範囲を指定
startRow = 1;
startColumn = 1;
endRow = 1000;
endColumn = 20;

//コピー対象となるスタイルをColdFusion関数で指定
objExcel = SpreadsheetRead(sourceXLSFile, sheetNum);
xlsFormat = StructNew();
xlsFormat.FONT = "ＭＳ ゴシック";
xlsFormat.BOLD = true;
xlsFormat.ALIGNMENT = "center";
xlsFormat.VERTICALALIGNMENT = "vertical_center";
xlsFormat.TOPBORDER = "thin";
xlsFormat.BOTTOMBORDER = "thin";
xlsFormat.LEFTBORDER = "thin";
xlsFormat.RIGHTBORDER = "thin";
SpreadsheetFormatCell(objExcel, xlsFormat, rowNum, columnNum);

//ColdFusionで編集したExcelファイルを一時的に保存
tempXLSFile = GetTempFile(GetTempDirectory(), "tempXLS");
SpreadSheetWrite(objExcel,tempXLSFile,true);

//createobject()でPOIオブジェクトを呼び出す
f = createObject("java", "java.io.File").init(tempXLSFile);
objTempfile = createObject("java", "java.io.FileInputStream").init(f);
objXLS = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook");
objXLS.init(objTempfile);

objXLS.setActiveSheet(sheetNum-1);
sheet = objXLS.getSheetAt(sheetNum-1); //sheet名で選択する場合は getSheet()

//列1・行1にセットしたスタイルを取得し、getCS変数にセット
targetRow = sheet.getRow(rowNum-1);
if(not IsDefined("targetRow")){
	writeOutput("指定した row は定義されていません");
	abort;
}
targetRowColumn = targetRow.getCell(columnNum-1);
if(not IsDefined("targetRowColumn")){
	writeOutput("指定した column は定義されていません");
	abort;
}
getCS = targetRowColumn.getCellStyle();
	
//列ループ→行ループとループを入れ子にして対象セルにスタイルをセット（setCellStyle）
for(idxR = startRow-1; idxR LT endRow; idxR = idxR+1){
	setRow = sheet.getRow(idxR);
	if(not IsDefined("setRow")){
		setRow = sheet.createRow(idxR);
	}
	for(idxC = startColumn-1; idxC LT endColumn; idxC = idxC+1){
		setRowColumn = setRow.getCell(idxC);
		if(not IsDefined("setRowColumn")){
			setRowColumn = setRow.createCell(idxC);
		}
		setRowColumn.setCellStyle(getCS);
	}
}

//Excelファイルを書き出し
objFileOutputStream = CreateObject("java", "java.io.FileOutputStream").Init(JavaCast("string", outputXLSFile));
objXLS.Write(objFileOutputStream);

objFileOutputStream.Close();
objTempfile.Close();

//一時的に保存したExcelファイルを削除
FileDelete(tempXLSFile);
</cfscript>

上記のプログラムを実行すると、同じフォルダにpoi_output.xlsファイルが作成されます。オリジナルのsample.xlsとpoi_output.xlsとのファイルサイズにはほとんど違いがありません。これはセルにセットしたのは一つのスタイルだけで、あとは他のセルにそのスタイルを反映した結果だと思います。

このpoi_output.xlsファイルを Excel2003 で開いても、先ほどのような「セルの書式が多すぎる」エラーも出ずにスタイルを反映することができました。

実験�A： 最初のスタイルのセットもPOIの処理で行う

先ほどの実験�@でも目的はかなっているのですが、やはり中間ファイルを作成するのはちょっと、、、なので、やはりスタイルの定義もPOIでやってみたいと思って調べてみました。

<cfscript>
//対象のExcelファイル(xlsファイル)のファイル・パスを指定
sourceXLSFile = ExpandPath('sample.xls');
//編集したExcelファイルの保存先（ファイル・フォルダ）を指定
outputXLSFile = ExpandPath('poi2_output.xls');
//対象のシート番号を指定
sheetNum = 1;
//スタイルを取得するセル位置（rowとcolumn）を指定
rowNum = 1;
columnNum = 1;
//対象のセル範囲を指定
startRow = 1;
startColumn = 1;
endRow = 1000;
endColumn = 20;

//createobject()でPOIオブジェクトを呼び出す
f = createObject("java", "java.io.File").init(sourceXLSFile);
objTempfile = createObject("java", "java.io.FileInputStream").init(f);
objXLS = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook");
objXLS.init(objTempfile);

objXLS.setActiveSheet(sheetNum-1);
sheet = objXLS.getSheetAt(sheetNum-1); //sheet名で選択する場合は getSheet()

//スタイルを作成
cellStyle = objXLS.createCellStyle();
cellStyle.setAlignment(cellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(cellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(cellStyle.BORDER_THIN);
cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
cellStyle.setBorderRight(cellStyle.BORDER_THIN);
  //フォントスタイルを作成
  fontStyle = objXLS.createFont();
  fontStyle.setFontName("ＭＳ ゴシック");
  fontStyle.setBoldweight(fontStyle.BOLDWEIGHT_BOLD);
//スタイルにフォントをセット
cellStyle.setFont(fontStyle);

//列ループ→行ループとループを入れ子にして対象セルにスタイルをセット（setCellStyle）
for(idxR = startRow-1; idxR LT endRow; idxR = idxR+1){
	setRow = sheet.getRow(idxR);
	if(not IsDefined("setRow")){
		setRow = sheet.createRow(idxR);
	}
	for(idxC = startColumn-1; idxC LT endColumn; idxC = idxC+1){
		setRowColumn = setRow.getCell(idxC);
		if(not IsDefined("setRowColumn")){
			setRowColumn = setRow.createCell(idxC);
		}
		setRowColumn.setCellStyle(cellStyle);
	}
}

//Excelファイルを書き出し
objFileOutputStream = CreateObject("java", "java.io.FileOutputStream").Init(JavaCast("string", outputXLSFile));
objXLS.Write(objFileOutputStream);

objFileOutputStream.Close();
objTempfile.Close();
</cfscript>

コードが複雑になって難しくなるかと思っていたら意外と簡単でした。違いはセルからスタイルを取りだす代わりに、ワークブックオブジェクトからスタイルを作成（createCellStyle）してそのスタイルを先ほどと同じようにセットするだけです。一つのセルにセットして、そのあとにそのセルからスタイルを取得して取り回さないといけないかな？と思っていましたが、その必要はなかったようです。

まとめ

今回の実験室はいかがだったでしょうか？Apache POIを操作する事自体、難しい事だと思っていましたが、多くの方がWebサイトで情報を紹介されていたので、それを参考にしてなんとか形にできたかなと思います。

ただ、私はJavaもExcelもそれほど詳しくないため、Webの情報をもとに使ってみる（試してみる）程度では不足している事項があるかもしれません。私が紹介したコードに誤っている箇所やもっと別の方法をご存知でしたら教えて頂けると幸いです。私も、より多くのColdFusionに関する情報をお届けできるよう、次のネタを考えてみます。