技術系掲示板でよく見かけるサーバー側でのExcel動作問題
SPRED for .NET Web Forms 3.0Jの後継製品であるSPREAD for ASP.NET 5.0Jは、Ajaxによる操作性の向上など、さまざまな新機能を備えている表計算コンポーネントです。今回注目したのは、Excelとのスムーズなデータ連携というポイントです。
技術系掲示板では、“サーバー側でExcelファイルの入出力を行うためにASP.NETからExcelをCOM連携で呼び出しているがうまく動作してくれない”といった質問が度々あがります。しかも、ASP.NETなどのWebの仕組みを理解していないために、“Visual Studioで動作させるとうまくいくが、運用するIISに配布すると動かない”という状況で質問している場合も多く、そのようなタイミングで「クライアントアプリであるExcelをサーバー側で動かすというのは、技術的にも困難な点が多くできない」といった回答をもらっても、根本的に直すには時間が足りないのではないかと心配になるときもあります。
そこで今回は、SPREAD for ASP.NET 5.0Jの新機能を使って、WebアプリでExcelデータをサーバー側で取り扱う仕組みを構築することで、Webサーバーにあるファイルをシームレスに編集するときの基本的な考え方も押さえてみたいと思います。
WebアプリでExcelファイルを扱う方法
一般的な方法
WebアプリでExcelファイルを扱う一般的な方法は、ブラウザを動かしているPC（以下、ローカルPC）にダウンロードし、編集をしてからアップロードする方法です。
Webサーバー側にあるExcelファイルをURLで指定すると、サーバーからExcelファイルがローカルPCにダウンロードされます。このとき、Webブラウザの定義によってローカルPCにあるExcelが自動的に起動し、ダウンロードしたファイルを表示します。編集したExcelファイルは、ローカルPCに保存後、ファイルアップロードを使ってWebサーバー側に送信し、保存されます。
もちろん、ダウンロードしてからアップロードするまでの間は、Webサーバー側でファイルをロックするなどの仕組みがないと同じファイルを複数の人が同時に編集してしまい、編集内容の競合などが発生してしまいます。しかし、ダウンロードした後にアップロードしてくれるかどうかは、ファイルを使用している人に依存するため、ファイルロックの解除の問題も考慮しなければなりません。
SPREADを使った方法
SPREAD for ASP.NET 5.0Jを使えば、SPREADにExcelシートを表示して編集したら、保存したデータがWebサーバーにPostBackされます。PostBackが発生するということは、Webサーバー側のプログラムにイベントが発生するという事です。つまり、ローカルPCからのファイルアップロードという手順は不要で、そのイベントプロシージャの中でSPREADの内容を元のExcelファイルに上書きすれば、Webサーバー側での保存が完了します。
SPREADを使った場合もファイルロックを考慮する必要がありますが、SPREADに対する操作のタイミングでロック制御を行えばよいので、一般的な方法よりも実装が簡単です。
今回のサンプルでは、SPREADの機能を中心に記述しているためロック制御は実装していませんが、ロック制御はSQL ServerなどのRDBMSへ、ファイル名ごとにどのPCからのリクエストでロックになったかを記録して行うのが一般的です（使用PC名があるかどうかでロックの有無を判断）。
SPREAD for ASP.NETを使うための前準備
SPREAD for ASP.NETは、ツールボックスからWebフォームにドラッグ＆ドロップして使用します。そのためには、SPREAD for ASP.NETをツールボックスに登録しなければなりません。
登録は、ツールボックスを右クリックして［アイテムの選択］メニューを選択すると表示されるダイアログボックスで行います。
図4では、3つのコンポーネントを選択していますが、今回使用するのは赤枠で囲んだ「FpSpread」です。ツールボックスに「FpSpread」が追加されたら、Default.aspxにドラッグ＆ドロップします。これで「FarPoint.Web.SpreadJ.dll」への参照設定やライセンスファイルの生成などが行われます。
今回はExcelを取り扱うので、プロジェクトのプロパティで「FarPoint.Excel.dll」の参照設定を手動で追加します。
ファイル一覧画面を作成する
SPREADの設定を最小限にして作成する
SPREADをWebフォームに配置し、リスト1のコードを記述します。
Protected Sub Page_Load(ByVal sender As Object,
ByVal e As System.EventArgs) _
Handles Me.Load
If Not Me.IsPostBack Then
Me.List_FpSpread.Sheets(0).OperationMode = FarPoint.Web.Spread.OperationMode.ReadOnly
Call GetFileList()
End If
End Sub
Private Function GetFileList() As Boolean
Dim isOK As Boolean = False
Using _proc As New CZ1010FileList
Dim filePath As String = System.IO.Path.Combine(My.Request.PhysicalApplicationPath,
"App_Data")
Me.List_FpSpread.DataSource = _proc.GetFileList(filePath).Tables("FileName")
Me.List_FpSpread.DataBind()
isOK = True
End Using
Return isOK
End Function
ASP.NETは、表示しているページで何か操作を行った場合、PostBackというモードでPage_Loadイベントが発生します。PostBackでは画面上の情報が維持されるので、画面の初期表示を行いたい場合は、Not Me.IsPostBackのときに処理を行います。
リスト1では、Not Me.IsPostBackのときにGetFileListを呼び出し、登録されているExcelファイルの一覧を設定しています。
Excelファイルの一覧を作成しているのは、GetFileListで使っているCZ1010FileListクラスです。このクラスは、指定されたフォルダにある拡張子が「.xlsx」のファイルの一覧をデータセットとして返却するためのクラスで、GetFileListでは返却されたデータセットをSPREADのDataSourceに設定しています。
これだけのコードしか記述していませんが、プログラムを実行してみると、ちゃんとファイル一覧がSPRAEDに表示されます。
操作性を考えてSPREADをデザインする
図5は“ファイル一覧を表示する”という目的は果たしていますが、“ファイルを選択して編集する”という操作を行うには不向きなデザインです。そこで、SPREAD搭載のGUIデザイナ「SPREADデザイナ」を使って、［編集］ボタンと［取得］ボタンを持ったデザインを作成し、同時に1行ごとに交互に背景色を変更して見やすくしてみましょう。
1列目の「ファイル名」列にはデータセットの「ファイル名」カラムの内容を表示するので、DataFieldプロパティに「ファイル名」と設定したかったのですがSPREADデザイナでは型付データセットから該当カラム名を選択する方法のみをサポートしているため、実行時にDataFieldプロパティは設定することにします。
Protected Sub Page_Load(ByVal sender As Object,
ByVal e As System.EventArgs) _
Handles Me.Load
If Not Me.IsPostBack Then
Me.List_FpSpread.Sheets(0).AutoGenerateColumns = False
Me.List_FpSpread.Sheets(0).OperationMode = FarPoint.Web.Spread.OperationMode.ReadOnly
Me.List_FpSpread.Sheets(0).Columns(0).DataField = "ファイル名"
'
Call GetFileList()
End If
End Sub
GetFileListの内容は、リスト1から変更していないので省略していますが、リスト2がリスト1と大きく違うのは、AutoGenerateColumnsプロパティの設定部分です。AutoGenerateColumnsが「True」の場合、事前にデザインした内容が破棄され、DataSourceに割り当てたデータセットの内容に応じて自動的にカラムが生成されてしまうので、「False」に設定しています。また、Columns(0)のDataFieldも設定しています。
Excel編集画面を作成する
一覧画面でボタンクリックイベントを検出する
ファイル一覧画面では行ごとにボタンを設置しています。このようなデザインの場合、ボタンをクリックするとどのようなイベントが起きるのでしょうか。答えはButtonCommandイベントです。
SPREADのButtonCommandイベントの特徴は、SPREADに配置したボタンをクリックすれば一律でButtonCommandイベントが発生するという事です。ボタンがクリックされた行については、イベントプロシージャの引数「e」から「e.CommandArgument.X」プロパティを参照して行を取得できます。
各ボタンの識別はe.CommandNameにクリックしたボタンのコマンド名が設定されます。例えば［編集ボタン］には「Edit_Button」というコマンド名を設定しています。
Private Sub List_FpSpread_ButtonCommand(ByVal sender As Object,
ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) _
Handles List_FpSpread.ButtonCommand
Dim filePath As String = System.IO.Path.Combine(My.Request.PhysicalApplicationPath,
"App_Data")
Dim fileName As String = Me.List_FpSpread.Sheets(0).GetText(e.CommandArgument.X, 0)
Select Case e.CommandName
Case "Edit_Button"
Call GetExcel(filePath, fileName)
Me.FileName_Label.Text = fileName
Case "Get_Button"
Call DownloadExcelFile(filePath, fileName)
End Select
End Sub
ExcelファイルをSPREADに表示する
Private Function GetExcel(ByVal filePath As String,
ByVal fileName As String) As Boolean
Dim isOK As Boolean = False
Try
Me.Excel_FpSpread.OpenExcel(System.IO.Path.Combine(filePath, fileName))
Me.Excel_FpSpread.ActiveSheetView.PageSize = Me.Excel_FpSpread.Rows.Count
Catch ex As Exception
Me.Message_Label.Text = ex.Message
End Try
isOK = True
Return isOK
End Function
SPREADにExcelファイルの内容を表示するのに必要なのはリスト4のように、OpenExcelメソッドの実行とActiveSheetView.PageSizeにRows.Countを入れて、Excelシートの内容がすべて表示できるようにPageSizeを調整するコードです。なお、Excelシートを表示するExcel_FpSpreadは、Webフォームに貼りつけただけで利用できます。
編集内容を保存する
SPREADのコマンドバーにある「コマンドアイコン」は、SPREAD自体への操作を行うためのものです。例えば、コマンドバーの左端にある「更新アイコン」についても画面で入力した値をSPREADに保存するためのものなので、サーバー側でExcelファイルを保存するときのイベントに利用する事はお勧めできません。
そこで、SPREADとは別に、［更新］ボタンを画面内に配置し、それがクリックされたらUpdate_Button.Clickイベントを発生させて、そのイベントプロシージャでExcelファイルの保存を行うようにします。
Protected Sub Update_Button_Click(ByVal sender As Object,
ByVal e As EventArgs) _
Handles Update_Button.Click
Try
Dim filePath As String = System.IO.Path.Combine(My.Request.PhysicalApplicationPath,
"App_Data")
Call SetExcel(filePath, Me.FileName_Label.Text)
Catch ex As Exception
Me.Message_Label.Text = ex.Message
End Try
End Sub
Private Function SetExcel(ByVal filePath As String,
ByVal fileName As String) As Boolean
Dim isOK As Boolean = False
Try
Me.Excel_FpSpread.SaveChanges()
Me.Excel_FpSpread.SaveExcel(System.IO.Path.Combine(filePath, fileName),
FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat)
Catch ex As Exception
Me.Message_Label.Text = ex.Message
End Try
isOK = True
Return isOK
End Function
クリックイベントの中ではSetExcelを呼び出していますが、前述のとおり「更新アイコン」をクリックするまで、画面で編集した内容はSPREAD内に保存されません。「更新アイコン」をクリックする代わりにSaveChangesメソッドで編集内容を確定してから、SaveExcelメソッドでWebサーバー上のExcelファイルに保存します。SaveExcelメソッドの第2パラメタはファイル形式であり、省略するとxlsファイル形式になります。xlsxファイル形式にしたいときは、UseOOXMLFormatを指定します。
ダウンロード機能を実装する
Private Function DownloadExcelFile(ByVal filePath As String,
ByVal fileName As String) As Boolean
Dim isOK As Boolean = False
If GetExcel(filePath, fileName) Then
Using ms As New System.IO.MemoryStream()
'http://support.microsoft.com/kb/436616/ja を参考に文字化け対策
fileName = HttpUtility.UrlEncode(fileName)
'
Me.Excel_FpSpread.SaveExcel(ms, FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat)
'クライアント側に応答
Response.Clear()
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("Content-Disposition",
"attachment;filename=" & fileName)
Response.BinaryWrite(ms.ToArray())
ms.Flush()
ms.Close()
Response.End()
End Using
isOK = True
End If
Return isOK
End Function
Excelファイルをダウンロードする方法は何通りかありますが、今回のサンプルではSPREADにExcelファイルを読み込んで、それをMemroyStream経由でWebアプリからのレスポンスとして返却する方法を採用しています。
このような方法をとった場合、Excelファイルのファイル名が英数字であれば問題ないのですが、日本語ファイル名の場合は「ファイルのダウンロード」ダイアログで表示されるファイル名が文字化けしてしまいます。そのため、UrlEncodingを使ってファイル名を変換しています。文字化けする理由や対策の詳しい内容などはマイクロソフトサポートオンラインの「ファイルをダウンロードする ASP.NET Web ページで日本語ファイル名が文字化けする」の項目を参考にしてください。
まとめ
SPREADに対してあまり手間をかけずに、Excelデータを中心にしたWebアプリを作ってみましたがいかがだったでしょうか。実用的なアプリにするには、まだ手を加える必要はありますが、手を加えなければならない大部分はSPREAD以外の部分になると思います。
高機能な製品でも、初期状態や意図に合致したプロパティやメソッドがあれば利用難易度は機能の高低と比例はしません。もちろん、コンポーネントの初期状態が業務に一致し、業務提案がコンポーネント製品の初期状態に近い形であれば、相乗効果により効率的に高機能なシステムを作ることも可能です。
ぜひ、評価版をダウンロードして実際に手を動かし、コンポーネントの初期状態、そして製品に添付されているサンプルで、さまざまな使い方を身につけていってほしいと思います。
製品情報
