業種を問わず、業務系のシステムではほぼ確実にエクセル形式での帳票出力は要件に挙がります。
今回から数回に渡り、バッチ処理でエクセルファイルを出力するサンプルについて検討します。
【概要】
全体の概要は以下の通りです。
 |
| Figure 1: Overall image |
エクセルのテンプレートファイルをあらかじめ用意します。実行時にパラメータとして以下の3点を指定し、SQLによって抽出されたデータをテンプレート上に展開した後、保存する仕様を想定します。
- テンプレートファイルの名前
- 保存するファイル名
- SQLスクリプト
【環境】
サンプルはC#で実装します。そのほかの環境は以下の通りです。
- .Net Framework： .Net Framework4
- Excel： Excel2010
- データベース：Oracle Database 11gR2 (11.2.0.1)
- DB接続： OleDbConnection
【ソース（本体部）】
ソースの例を以下に示します。データの抽出、およびエクセルへの出力をDLLとして定義します。
この例では、レコードセットをMoveNextで移動する毎に、テンプレートの行をコピーおよび挿入し、セル毎にデータを挿入します。
List 1: Main logic
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Collections;
namespace Util
{
public class Ora2Excel
{
// db objects //////////
private OleDbConnection cnn;
private OleDbCommand cmd;
// excel objects //////////
private Application xApplication;
private _Workbook xBook;
private _Worksheet xSheet;
// ///////////////////////////////////////////////////////////
public void OpenDb(string UserId, string Password, string HostString)
{
cnn = new OleDbConnection("Provider=OraOLEDB.Oracle.1;Data Source=" + HostString + ";User ID=" + UserId + ";Password=" + Password);
cnn.Open();
cmd = cnn.CreateCommand();
}
// ///////////////////////////////////////////////////////////
public void CloseDb()
{
cmd.Dispose();
cnn.Close();
cnn.Dispose();
}
// ///////////////////////////////////////////////////////////
public OleDbDataReader GetRecordset(string sql)
{
cmd.CommandText = sql;
return cmd.ExecuteReader();
}
// ///////////////////////////////////////////////////////////
public void InitExcel()
{
xApplication = new Application();
xBook = null;
xSheet = null;
}
// ///////////////////////////////////////////////////////////
public void QuitExcel()
{
if (xBook != null)
xBook.Close(false);
xApplication.Quit();
}
// ///////////////////////////////////////////////////////////
public void OpenBook(string FileName)
{
if (FileName == "")
xBook = xApplication.Workbooks.Add();
else
xBook = xApplication.Workbooks.Open(FileName);
xSheet = xBook.ActiveSheet;
}
// ///////////////////////////////////////////////////////////
public void SaveBook(string FileName)
{
xBook.SaveAs(FileName);
}
// ///////////////////////////////////////////////////////////
public void CloseBook()
{
if (xBook != null)
{
xBook.Close(false);
xBook = null;
}
}
// ///////////////////////////////////////////////////////////
public void Extract(string sql, string SheetName)
{
const int XL_WHOLE = 1;
const int XL_DOWN = -4121;
const string START_TAG = "$start";
int idxRow;
OleDbDataReader rst = GetRecordset(sql); // get recordset
xSheet = xBook.Worksheets[SheetName]; // activate start position
xSheet.Cells.Find(What: START_TAG, LookAt: XL_WHOLE).Activate();
idxRow = xApplication.ActiveCell.Row;
while (rst.Read()) // output loop
{
xSheet.Rows[idxRow].Copy();
xSheet.Rows[idxRow+1].Insert(Shift: XL_DOWN);
for (int idxCol = 0; idxCol < rst.FieldCount; idxCol++)
xSheet.Cells[idxRow,idxCol+1].value = rst.GetValue(idxCol);
idxRow++;
}
rst.Close();
}
}
}
【テスト用ソース（GUI部）】
上記のDLLを使用する側（今回の例ではGUI）の例を以下に示します。
 |
| Figure 2: Test GUI |
List 2: Test GUI
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Util;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Util.Ora2Excel x = new Util.Ora2Excel();
x.OpenDb(txtUser.Text, txtPassword.Text, txtConnString.Text);
x.InitExcel();
x.OpenBook(txtTemplate.Text);
x.Extract(txtSql.Text, txtSheet.Text);
x.SaveBook(txtSaveAs.Text);
x.CloseExcel();
x.CloseDb();
}
}
}
【テンプレートファイル】
テンプレートファイルの例を示します。ヘッダおよび書式、数式などを設定します（詳細はダウンロードしたファイルの内容を参照して下さい）。
データを出力する開始点に"$start"を入力します。
 |
| Figure 3: Template file |
出力結果のイメージは以下の通りです。
 |
| Figure 4: Result |
次回からは性能検証、および性能改善の方策を検討します。
エクセルのテンプレートファイル、結果ファイルは
ここからダウンロードできます。
※ファイルをダウンロード後、手元の環境でコンパイルを行う場合には、Excelへの参照設定、およびOracle Clientの設定を適宜調整する必要があります。
※上記のソースはサンプルです。例外処理などは除外しています。
※上記のソースおよびファイルを使用したことによる一切の結果について、作成者は責任を負いません。
[Summary]
In most of cases, business application system cannot ignore Excel.
This post explains the sample program to produce Excel format files.
[Outline]
Overall image is shown in Figure 1. What you need in run time is a template file (ordinary Excel file) and parameters below:
- Name of the template file
- Name of the file to save
- SQL script
[Environment]
Source is in C#. Environment are as follows:
- .Net Framework: .Net Framework4
- Excel: Excel2010
- Database: Oracle Database 11gR2 (11.2.0.1)
- DB connection: OleDbConnection
[Source (main DLL part)]
Please see List 1. The DLL retrieves rows from database, and set the data onto workbook cell by cell.
[Test source (GUI part)]
Please see Figure 2 and List 2. This example is simple Windows form. On the button click event, it calls the DLL above.
[Template file]
Please see Figure 3 (Template file) and 4 (Output). In the template, you set the report header, number format, formula and so on. The start position, the top-left position, of data area is defined by "$start". Please refer to the sample file for details.
The next post will explain performance issue.
The template files are available here.
(*) To compile the source in your own environment, you may need set reference setting for Excel components. Also, modify the DB connection part to fit your environment.
(*) The source code and other files are written as example. Exception handlings are all removed.
(*) Please use these files at your own risk.
