Excel ブック (.xlsx) をプログラムで扱う – 準備 1


◆Excel ブック (.xlsx) をプログラムで扱う

Excel 2007 からファイルフォーマットが「Office Open XML」形式となり、仕様が公開され、プログラムから扱いやすくなりました。しかしながら、膨大なタグの仕様を適切に把握してブックを扱うには、多大な労力を要します。システムを構築する際には、そこに労力をかけるよりもコンポーネントを使用した方が効率的です。「Excel ブック (*.xlsx) 形式概要」でも紹介した通り、Excel ブック (.xlsx) をプログラムで扱うコンポーネントは複数あります。今回からは「Open XML SDK 2.5」と「ExcelCreator 8.0 for .NET」を使用して Excel ブック (.xlsx) をプログラムで扱う方法を紹介していきたいと思います。まずは「Open XML SDK 2.5」を使用する準備と、作成したブックについて見ていきます。


◆検証環境
OS Windows 8(x64)
開発環境 Visual Studio 2012
CPU Intel(R) Core(TM)2 Duo E8400 @ 3.00GHz
メモリ 4GB
その他 SSD 搭載


◆Open XML SDK のダウンロードとインストール

Open XML SDK は、Microsoft 社が提供するライブラリで、Open XML ドキュメントを操作するための、厳密に型指定されたパーツ クラスおよびコンテンツ クラスが提供されています。現在一般公開されているバージョンは 2.0 で、CTP 版である 2.5 も公開されています。今回は 2.5 を使用してさまざまな機能を検証していきます。

Open XML SDK 2.5 CTP 版は、下記のサイトからダウンロードできます。

http://www.microsoft.com/en-us/download/details.aspx?id=30425

OpenXMLSDKV25.msi をダウンロード、実行してインストールを行います。


◆プロジェクトの作成と参照の追加

次に Visual Studio 2012 を起動し、コンソールアプリケーション「OXML25ConsoleApplication」を作成します。参照の追加で先ほどインストールした Open XML SDK 2.5 のアセンブリを追加します。ここでは WindowsBase.dll も追加しておきます。

追加したアセンブリを見ると、.NET Framework 4 のランタイムを使用していることが分かります。動作環境には注意が必要です。



Excel ブック (.xlsx) を新規に作成するコード(Open XML SDK 2.5)

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OXML25ConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            // SpreadsheetDocument を作成します。
            SpreadsheetDocument spreadsheetDocument = 
                SpreadsheetDocument.Create(@"OXMLCreate.xlsx", SpreadsheetDocumentType.Workbook);

            // WorkbookPart をドキュメントに追加します。
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // WorksheetPart を WorkbookPart に追加します。
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Sheets を Workbook に追加します。
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // 新しい worksheet を workbook に追加します。
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Sheet1"
            };
            sheets.Append(sheet);
            // ワークブックを保存します。
            workbookpart.Workbook.Save();

            // ドキュメントをクローズします。
            spreadsheetDocument.Close();
        }
    }
}

作成したブックの拡張子を .zip に変更し、解凍して構造を確認すると、必要最小限の XML ファイルで構成されていることが分かります。


XML ファイル一覧

  • [Content_Types].xml
  • _rels/.rels
  • xl/_rels/workbook.xml.rels
  • xl/workbook.xml
  • xl/worksheets/sheet.xml

Excel で作成した場合と比べ、下記の XML ファイルがありません。

  • docProps/core.xml
  • docProps/app.xml
  • xl/theme/theme1.xml
  • xl/styles.xml

また、作成されたブックを解凍して中の XML ファイルを確認すると、Excel で作成した時とは違い、ところどころにプレフィクス”x:”が付加されています。

xl/workbook.xml

<?xml version="1.0" encoding="utf-8"?>
<x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheets>
<x:sheet name="Sheet1" sheetId="1" r:id="Rb519efa492dc4cf0" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
</x:sheets>
</x:workbook>


_rels/.rels

<?xml version="1.0" encoding="utf-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="/xl/workbook.xml" Id="Rcc46d2eda9cd4bed" />
</Relationships>


xl/worksheets/sheet.xml

<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheetData />
</x:worksheet>


xl/_rels/workbook.xml.rels

<?xml version="1.0" encoding="utf-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="/xl/worksheets/sheet.xml" Id="Rb519efa492dc4cf0" />
</Relationships>


[Content_Types].xml

<?xml version="1.0" encoding="utf-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" />
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" />
<Override PartName="/xl/worksheets/sheet.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
</Types>


いずれも必要最小限の内容で作成されています。
次回は ExcelCreator 8.0 for .NET を使用する準備について紹介します。

Excel 97-2003 ブック (.xls) をプログラムで扱う – その他


◆行、列の操作と計算式

プログラム中からブックを扱う場合、そのほとんどはセルに対する操作になると思いますが、行や列に対して操作が必要になることもあります。不要な行を削除したり、足りない場合に行を追加できると便利です。

NPOI で行を操作します。
19 行目から 9 行削除します。

using System.IO;
using NPOI.HSSF.UserModel;
using System.Diagnostics;
using System;

namespace NPOIConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            // ブックのオープン
            FileStream openFileStream = new FileStream(@"D:\OpenBook\Data\納品書.xls", FileMode.Open, FileAccess.Read);
            var workbook = new HSSFWorkbook(openFileStream);
            // 行の削除
            var sheet = workbook.GetSheetAt(0);
            for (int index = 18; index <= 27; index++)
                sheet.RemoveRow(sheet.GetRow(index));
            // ブックの保存
            using (var fileStream = new FileStream(@"NPOIOpen.xls", FileMode.OpenOrCreate, FileAccess.Write))
                workbook.Write(fileStream);
            openFileStream.Dispose();
        }
    }
}

削除後の Excel ファイルを開くと、行はそのまま残っていて、セルのみがクリアされていることが分かります。他にもメソッドがないか調べてみましたが、行そのものを削除することはできないようです。同様に行の挿入もありませんでした。



次に ExcelCreator で行を操作します。
19 行目から 9 行削除します。

using ExcelCreator;
using System;
using System.Diagnostics;

namespace EC5ConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            // ブックのオープン
            var xlsCreator = new XlsCreator();
            xlsCreator.OpenBook(@"EC5Open.xls", @"D:\OpenBook\Data\納品書.xls");
            // 行の削除
            xlsCreator.RowDelete(18, 9);
            // ブックのクローズ
            // 2 番目の引数に出力する PDF ファイル名を設定します。
            // 3 番目の引数に true を設定すると、作成した Excel ブックを破棄します。
            xlsCreator.CloseBook(true);
        }
    }
}

行や列の削除や挿入をする場合に注意する必要があるのが計算式です。行や列に対する操作の対象に計算式の範囲に含まれる場合、計算式の範囲を調整する必要が出てきます。ExcelCreator で行を削除して作成した Excel ファイルを開き、計算式が設定されているセルを確認すると、範囲が調整されていることが分かります。



◆PDF 出力

ExcelCreator では、作成したブックの内容を PDF として出力する機能があります。Excel がインストールされていない環境では作成したファイルを目視で確認することができなかったり、サーバーから配信する場合にクライアントに必ずしも Excel がインストールされているとは限らないため、PDF で出力できるとシステムの利便性が向上します。

ExcelCreator で PDF ファイルを出力するには、CloseBook のオーバーロードを使用します。

using ExcelCreator;
using System;
using System.Diagnostics;

namespace EC5ConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            // ブックのオープン
            var xlsCreator = new XlsCreator();
            xlsCreator.OpenBook(@"EC5Open.xls", @"D:\OpenBook\Data\納品書.xls");
            // データの設定
            SetData(xlsCreator);
            // ブックのクローズ
            // 2 番目の引数に出力する PDF ファイル名を設定します。
            // 3 番目の引数に true を設定すると、作成した Excel ブックを破棄します。
            xlsCreator.CloseBook(true, @"EC5Open.pdf", false);
        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="xlsCreator"></param>
        private static void SetData(XlsCreator xlsCreator)
        {
            if (xlsCreator == null) return;
            int count = 2;
            int unitPrice = 48300;
            int totalPrice = 0;
            xlsCreator.Cell("A3").Value = "アドバンスソフトウェア株式会社 様";
            xlsCreator.Cell("K7").Value = "E2012-12-14";
            xlsCreator.Cell("A19").Value = "ExcelCreator 5.0 for .NET";
            xlsCreator.Cell("G19").Value = count;
            // 罫線の変更
            xlsCreator.Cell("J16:L16").Attr.Box(xlBoxType.btLtc, xlLineStyle.lsThick | (xlLineStyle)xlColor.xcBlue);
            // 表示形式の変更
            xlsCreator.Cell("H19:J19").Attr.Format = "\\\#,##0;\\\-#,##0";
            xlsCreator.Cell("H19").Value = unitPrice;
            xlsCreator.Cell("J19").Value = unitPrice * count;
            totalPrice += unitPrice * count;
            xlsCreator.Cell("J29").Func("=SUM(J19:L28)", unitPrice * count);
        }
    }
}

PDF に出力する場合、ブックに計算式が含まれていると、その計算結果が反映されません。これは、ExcelCreator では計算式を解析して実行するロジックを持ち合わせていないためで、この機能はブックには含まれず Excel 本体の機能となります。対策として、計算式と結果の両方を設定できる Func メソッドがあるので、それを使用することで PDF にも計算結果を出力することができます。



◆まとめ

サーバーからドキュメントを配信する場合、直接 Excel の形式で配信できれば便利ですが、逆に編集されないよう PDF で出力する機能を要求されるケースがあります。ExcelCreator はどちらの出力にも対応していますが、サーバー上で動作させる場合、サーバーライセンスが必要となり有償です。
また、NPOI には PDF に出力する機能がなく、他のフリーのコンポーネントでもいくつかを組み合わせれば可能ですが、その分メンテナンスの手間が増えるので、コンポーネントの選択はシステムの要件にあわせて十分に検討する必要があります。

これまで何度かに分けて Excel 97-2003 ブック (.xls) をプログラムで扱う基本的な方法を紹介しました。もっと細かい機能を紹介して深いところまで書きたいという気持ちもあるのですが、現在は XML がベースとなった xlsx 形式が主流ですので、次回からは Excel ブック (*.xlsx) をプログラムで扱う方法を紹介していきたいと思います。

Excel 97-2003 ブック (.xls) をプログラムで扱う – セルの扱い

今回は NPOI と ExcelCreator それぞれのコンポーネントでセル位置を指定する方法について考えてみます。


◆座標形式

A1 セルを(0, 0)とした座標としてセル位置を扱う方式です。NPOI では行、セルの順で作成または取得してセルを扱うため、基本的にこの方式となります。ExcelCreator でも Pos メソッドがこれに該当します。座標を変数として保持し、インクリメントしながら連続して値やスタイルを設定する場合に便利です。

NPOI 座標形式での指定

    // 座標形式による明細行の設定
    // 明細 - 商品名
    row = sheet.GetRow(18);
    if (row == null) row = sheet.CreateRow(18);
    cell = row.GetCell(0);
    if (cell == null) row.CreateCell(0);
    cell.SetCellValue("ExcelCreator 5.0 for .NET");
    // 明細 - 数量
    cell = row.GetCell(6);
    if (cell == null) row.CreateCell(6);
    cell.SetCellValue(1);
    // 明細 - 単価
    cell = row.GetCell(7);
    if (cell == null) row.CreateCell(7);
    cell.SetCellValue(48300);
    // 明細 - 金額
    cell = row.GetCell(9);
    if (cell == null) row.CreateCell(9);
    cell.SetCellValue(48300);

ExcelCreator 座標形式での指定

    // 座標形式による明細行の設定
    // 明細 - 商品名
    xlsCreator.Pos(0, 18).Value = "ExcelCreator 5.0 for .NET";
    // 明細 - 数量
    xlsCreator.Pos(6, 18).Value = 1;
    // 明細 - 単価
    xlsCreator.Pos(7, 18).Value = 48300;
    // 明細 - 金額
    xlsCreator.Pos(9, 18).Value = 48300;


◆A1 参照形式

ExcelCreator でセル位置をそのまま文字列として指定する方式です。範囲を指定することもできます。Excel の関数でも使われている方式で、セル位置が直感的に分かりやすく、Excel でファイルを開いてセル位置を確認しながらプログラムを記述する場合においては最も馴染みやすい方式だと思います。

ExcelCreator A1 参照形式での指定

    // A1 参照形式による明細行の設定
    // 明細 - 商品名
    xlsCreator.Cell("A19").Value = "ExcelCreator 5.0 for .NET";
    // 明細 - 数量
    xlsCreator.Cell("G19").Value = 1;
    // 明細 - 単価
    xlsCreator.Cell("H19").Value = 48300;
    // 明細 - 金額
    xlsCreator.Cell("J19").Value = 48300;


◆セルの名前を使用

Excel ではセルに一意の「名前」をつけることができます。名前でセルを識別できれば、座標を意識せずに目的のセルにデータやスタイルを設定することができ、プログラムのビルド後に Excel でレイアウトを変更することも可能になります。Excel 上でセルに名前を設定するには、数式バー横の名前ボックスに直接名前を入力します。

・設定した名前
セル(座標) 名前
A19(0, 18) ProductName
G19(6, 18) Count
H19(7, 18) UnitPrice
I19(9, 18) Price

Excel 上で名前を設定すると、実際のデータは下記のように名前文字列、長さ等が[ID:0x0018]でソートされて格納されます。

[0x0018(len:27)][NAME]:
00 00 00 05 07 00 00 00 00 00 00 00 00 00 00 43 | ...............C |
6F 75 6E 74 3A 00 00 12 00 06 00                | ount:......      |
[0x0018(len:27)][NAME]:
00 00 00 05 07 00 00 00 00 00 00 00 00 00 00 50 | ...............P |
72 69 63 65 3A 00 00 12 00 09 00                | rice:......      |
[0x0018(len:33)][NAME]:
00 00 00 0B 07 00 00 00 00 00 00 00 00 00 00 50 | ...............P |
72 6F 64 75 63 74 4E 61 6D 65 3A 00 00 12 00 00 | roductName:..... |
00                                              | .                |
[0x0018(len:31)][NAME]:
00 00 00 09 07 00 00 00 00 00 00 00 00 00 00 55 | ...............U |
6E 69 74 50 72 69 63 65 3A 00 00 12 00 07 00    | nitPrice:......  |

NPOI では、名前の情報を取得することができるので、取得した情報から座標を抜き出すことで目的のセルにたどり着くことができます。ざっと見た感じでは座標の変換メソッド等は見当たらなかったので、座標の抜き出しに関しては自作する必要がありそうです。

NPOI 名前での指定

    // 名前による明細行の設定
    //   Excel であらかじめセルに名前を設定しておきます。
    var name = workbook.GetName("ProductName");
    // RefersToFormula で取得される文字列から座標を抜き出して使用します。
    string s = name.RefersToFormula;    // "納品書!$A$19"が取得される
    // ここで"納品書!$A$19"から座標の抜き出し処理を記述
    // 以降目的の行、セルを取得して値を設定する

また、ExcelCreator では、セル指定の際に直接名前で指定することができます。

ExcelCreator 名前での指定

    // 名前による明細行の設定
    //   Excel であらかじめセルに名前を設定しておきます。
    // 明細 - 商品名
    xlsCreator.Cell("ProductName").Value = "ExcelCreator 5.0 for .NET";
    // 明細 - 数量
    xlsCreator.Cell("Count").Value = 1;
    // 明細 - 単価
    xlsCreator.Cell("UnitPrice").Value = 48300;
    // 明細 - 金額
    xlsCreator.Cell("Price").Value = 48300;


◆その他の指定方法

ExcelCreator では、独自の仕様として「変数名」という方式があります。これは、特定の文字列(デフォルトで”**”)で始まる文字列をセルに設定しておくと、オープン時にそれらを「変数名」として内部で座標を保持し、プログラム中でのセル位置の指定は変数名を指定することができます。こちらも「名前」と同様、プログラムのビルド後に Excel でレイアウトを変更しても目的のセルにデータやスタイルを設定すること可能となります。セルに文字列を設定するだけですので、エンドユーザーがブックを編集する場合も操作が楽です。

また、複数セルに同じ変数名を設定することができるので、共通するデータを設定する場合にも便利です。

・設定した変数
セル(座標) 名前
A19(0, 18) **ProductName
G19(6, 18) **Count
H19(7, 18) **UnitPrice
I19(9, 18) **Price

ExcelCreator 変数名での指定

    // 変数名による明細行の設定
    //   Excel であらかじめセルに"**"で始まる変数名を設定しておきます。
    // 明細 - 商品名
    xlsCreator.Cell("**ProductName").Value = "ExcelCreator 5.0 for .NET";
    // 明細 - 数量
    xlsCreator.Cell("**Count").Value = 1;
    // 明細 - 単価
    xlsCreator.Cell("**UnitPrice").Value = 48300;
    // 明細 - 金額
    xlsCreator.Cell("**Price").Value = 48300;

いずれの場合も実行結果は下記のようになります。



◆まとめ

プログラム中からのセル位置の指定は、連続した値やスタイルの設定なのか、その位置が変更される可能性があるのか等、その性質により適切な方法を選択できるのが望ましいと思います。後々のメンテナンスに影響があるので、十分検討して仕様、方式を決める必要があります。

Excel 97-2003 ブック (.xls) をプログラムで扱う – オープン

今回は NPOI と ExcelCreator を使用して既存の Excel 97-2003 ブック (.xls) のオープンして値の設定や属性(スタイル)の変更を行う方法について紹介します。
前回作成した「納品書」の Excel 97-2003 ブック (.xls) を手直ししたブックを使用します。


◆検証環境
OS Windows 8(x64)
開発環境 Visual Studio 2012
CPU Intel(R) Core(TM)2 Duo E8400 @ 3.00GHz
メモリ 4GB
その他 SSD 搭載


◆処理速度、メモリ使用量を計測

今回からは下記のコードで処理速度、メモリ使用量を計測していきます。

    Stopwatch sw = new Stopwatch();
    TimeSpan ts = new TimeSpan();
    sw.Start();
    Process currentProcess = System.Diagnostics.Process.GetCurrentProcess();
    currentProcess.Refresh();

    // ここにコンポーネントによる処理を記述

    ts = sw.Elapsed;
    Console.WriteLine("NPOI 処理時間:{0}", ts.ToString());
    Console.WriteLine("プロセス名   :" + currentProcess.ProcessName + "\n" + 
        "物理メモリ使用量:" + (currentProcess.WorkingSet64 / 1024) + " KB\n" + 
        "仮想メモリ使用量:" + (currentProcess.VirtualMemorySize64 / 1024) + " KB");


◆NPOI を使用してブックをオープン

Visual Studio 2012 で前回作成したコンソールアプリケーション「NPOIConsoleApplication」を使用してコードを変更していきます。

ブックをオープンして値やスタイルを設定するコード(NPOI)

using System.IO;
using NPOI.HSSF.UserModel;
using System.Diagnostics;
using System;

namespace NPOIConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            TimeSpan ts = new TimeSpan();
            sw.Start();
            Console.WriteLine("NPOI による Excel 97-2003 (*.xls) ブックのオープン。");
            Process currentProcess = System.Diagnostics.Process.GetCurrentProcess();
            currentProcess.Refresh();

            // ブックのオープン
            FileStream openFileStream = new FileStream(@"D:\OpenBook\Data\納品書.xls", FileMode.Open, FileAccess.Read);
            var workbook = new HSSFWorkbook(openFileStream);
            // データの設定
            SetData(workbook);
            // ブックの保存
            using (var fileStream = new FileStream(@"NPOIOpen.xls", FileMode.OpenOrCreate, FileAccess.Write))
                workbook.Write(fileStream);
            openFileStream.Dispose();

            ts = sw.Elapsed;
            Console.WriteLine("NPOI 処理時間:{0}", ts.ToString());
            Console.WriteLine("プロセス名   :" + currentProcess.ProcessName + "\n" + 
                "物理メモリ使用量:" + (currentProcess.WorkingSet64 / 1024) + " KB\n" + 
                "仮想メモリ使用量:" + (currentProcess.VirtualMemorySize64 / 1024) + " KB");
        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="workbook"></param>
        private static void SetData(HSSFWorkbook workbook)
        {
            if (workbook == null) return;
            // シートの取得
            var sheet = workbook.GetSheetAt(0);
            // 宛先
            var row = sheet.GetRow(2);
            if (row == null) row = sheet.CreateRow(2);
            var cell = row.GetCell(0);
            if (cell == null) cell = row.CreateCell(0);
            cell.SetCellValue("アドバンスソフトウェア株式会社 様");
            // お客様コード
            row = sheet.GetRow(6);
            if (row == null) row = sheet.CreateRow(6);
            cell = row.CreateCell(10);
            cell.SetCellValue("N2012-12-06");
            // 罫線の変更
            row = sheet.GetRow(15);
            if (row == null) row = sheet.CreateRow(15);
            for (int index = 9; index <= 11; index++)
            {
                cell = row.GetCell(index);
                var style1 = workbook.CreateCellStyle();
                style1.CloneStyleFrom(cell.CellStyle);
                style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
                style1.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
                style1.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
                style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
                cell.CellStyle = style1;
            }
            // 明細 - 商品名
            row = sheet.GetRow(18);
            if (row == null) row = sheet.CreateRow(18);
            cell = row.GetCell(0);
            if (cell == null) row.CreateCell(0);
            cell.SetCellValue("ExcelCreator 5.0 for .NET");
            // 明細 - 数量
            cell = row.GetCell(6);
            if (cell == null) row.CreateCell(6);
            cell.SetCellValue(1);
            // 明細 - 単価
            cell = row.GetCell(7);
            if (cell == null) row.CreateCell(7);
            cell.SetCellValue(48300);
            // 表示形式をブックに追加
            var format = workbook.CreateDataFormat().GetFormat("\\\#,##0;\\\-#,##0");
            var style = workbook.CreateCellStyle();
            style.CloneStyleFrom(cell.CellStyle);
            style.DataFormat = format;
            // 表示形式の変更
            cell.CellStyle = style;
            // 明細 - 金額
            cell = row.GetCell(9);
            if (cell == null) row.CreateCell(9);
            cell.SetCellValue(48300);
            style = workbook.CreateCellStyle();
            style.CloneStyleFrom(cell.CellStyle);
            style.DataFormat = format;
            // 表示形式の変更
            cell.CellStyle = style;
        }
    }
}

前回と比べるとレイアウトの設定が不要となり、かなりコードが短くなりました。値やスタイルの設定では、行、セルの順に取得(なければ作成)して設定していきます。

スタイルについて単価、金額のセルに対して表示形式の設定を行います。まずは表示形式を作成し、セルのスタイルにその番号を設定します。試しにあらかじめブックに設定しておいた表示形式と同じものをプログラム中から作成して設定したところ、すでにある表示形式の番号が取得され、重複することはありませんでした。

オープンしたブックを上書きするには、同じファイル名で保存を行います。

※NPOI に関してはよりよい使用方法があるかもしれません。今後の検証で調整していきたいと思います。


◆ExcelCreator を使用してブックをオープン

Visual Studio 2012 で前回作成したコンソールアプリケーション「EC5ConsoleApplication」を使用してコードを変更していきます。

ブックをオープンして値やスタイルを設定するコード(ExcelCreator 5.0 for .NET)

using ExcelCreator;
using System;
using System.Diagnostics;

namespace EC5ConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            TimeSpan ts = new TimeSpan();
            sw.Start();
            Console.WriteLine("ExcelCreator 5.0 for .NET による Excel 97-2003 (*.xls) ブックのオープン。");
            Process currentProcess = System.Diagnostics.Process.GetCurrentProcess();
            currentProcess.Refresh();

            // ブックのオープン
            var xlsCreator = new XlsCreator();
            xlsCreator.OpenBook(@"EC5Open.xls", @"D:\OpenBook\Data\納品書.xls");
            // データの設定
            SetData(xlsCreator);
            // ブックのクローズ
            xlsCreator.CloseBook(true);

            ts = sw.Elapsed;
            Console.WriteLine("ExcelCreator 5.0 for .NET 処理時間:{0}", ts.ToString());
            Console.WriteLine("プロセス名   :" + currentProcess.ProcessName + "\n" + 
                "物理メモリ使用量:" + (currentProcess.WorkingSet64 / 1024) + " KB\n" + 
                "仮想メモリ使用量:" + (currentProcess.VirtualMemorySize64 / 1024) + " KB");

        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="xlsCreator"></param>
        private static void SetData(XlsCreator xlsCreator)
        {
            if (xlsCreator == null) return;
            xlsCreator.Cell("A3").Value = "アドバンスソフトウェア株式会社 様";
            xlsCreator.Cell("K7").Value = "E2012-12-06";
            xlsCreator.Cell("A19").Value = "ExcelCreator 5.0 for .NET";
            xlsCreator.Cell("G19").Value = 1;
            // 罫線の変更
            xlsCreator.Cell("J16:L16").Attr.Box(xlBoxType.btLtc, xlLineStyle.lsThick | (xlLineStyle)xlColor.xcBlue);
            // 表示形式の変更
            xlsCreator.Cell("H19:J19").Attr.Format = "\\\#,##0;\\\-#,##0";
            xlsCreator.Cell("H19").Value = 48300;
            xlsCreator.Cell("J19").Value = 48300;
        }
    }
}

こちらも前回と比べるとかなりコードが短くなりました。ExcelCreator ではオープン時に独自の内部構造にすべてのデータを読み込み、クローズ時に書き込みを行う仕様となります。値やスタイルの設定では、行やセルの有無を気にせず目的のセルに対して設定していきます。

スタイルについて単価、金額のセルに対して表示書式の変更を行います。この場合、ExcelCreator の内部の処理において、設定された表示形式と内部で保持している表示形式とでマッチングを行い、すでに存在する場合はその書式番号を使用します。

オープンしたブックを上書きするには、引数を一つ取る OpenBook メソッドのオーバーロードを使用します。


いずれのコードも下記のブックが作成されます。
赤の楕円で囲ったところがプログラム中から変更したスタイルです。



◆まとめ

基本的なレイアウトは Excel で行い、そのブックに対して必要なデータやスタイルを設定していくことで効率よく開発を進めることができ、また、メンテナンス性も向上します。

Excel 97-2003 ブック (.xls) をプログラムで扱う – 新規作成

今回はコンポーネントを使って Excel 97-2003 ブック (.xls) をプログラム中から新規に作成する方法を紹介します。前回は空のブックを新規に作成したので、追加で罫線や背景色、文字列や数値などを設定していきます。目標として画像のような Excel 97-2003 ブック (.xls) をプログラム中から新規に作成します。


◆検証環境
OS Windows 8(x64)
開発環境 Visual Studio 2012


◆NPOI を使用してブックを新規作成

Visual Studio 2012 でコンソールアプリケーション「NPOIConsoleApplication」を新規作成し、NPOI の各アセンブリへの参照を追加してコードを述していきます。

ブックを新規に作成するコード(NPOI)

using System.IO;
using NPOI.HSSF.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.UserModel;

namespace NPOIConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            var workbook = new HSSFWorkbook();
            // ブックの新規作成
            workbook.CreateSheet("Sheet1");
            // レイアウトの設定
            SetLayout(workbook);
            // データの設定
            SetData(workbook);
            // ブックの保存
            using (var fileStream = new FileStream("NPOICreate.xls", FileMode.OpenOrCreate, FileAccess.Write))
                workbook.Write(fileStream);
        }
        /// <summary>
        /// レイアウトを設定します。
        /// </summary>
        /// <param name="workbook"></param>
        private static void SetLayout(HSSFWorkbook workbook)
        {
            if (workbook == null) return;
            int index;
            // シートの取得
            var sheet = workbook.GetSheetAt(0);
            // 列幅
            for (index = 0; index <= 11; index++)
                sheet.SetColumnWidth(index, 8 * 256);
            // 行高
            var row = sheet.CreateRow(15);
            row.Height = 750;
            for (index = 17; index <= 27; index++)
            {
                row = sheet.CreateRow(index);
                row.Height = 390;
            }
            // セルの結合
            sheet.AddMergedRegion(new CellRangeAddress(1, 3, 5, 7));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 3));
            // 明細行の結合
            for (index = 17; index <= 27; index++)
            {
                sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 5));
                sheet.AddMergedRegion(new CellRangeAddress(index, index, 7, 8));
                sheet.AddMergedRegion(new CellRangeAddress(index, index, 9, 11));
            }
            // 備考欄の結合
            sheet.AddMergedRegion(new CellRangeAddress(29, 29, 1, 10));
            sheet.AddMergedRegion(new CellRangeAddress(30, 35, 1, 10));
            sheet.AddMergedRegion(new CellRangeAddress(37, 37, 1, 10));
            sheet.AddMergedRegion(new CellRangeAddress(38, 43, 1, 10));
            // 罫線
            var style = workbook.CreateCellStyle();
            style.LeftBorderColor = HSSFColor.BLUE.index;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            style.TopBorderColor = HSSFColor.BLUE.index;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            style.RightBorderColor = HSSFColor.BLUE.index;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            style.BottomBorderColor = HSSFColor.BLUE.index;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            row = sheet.GetRow(15);
            for (index = 9; index <= 11; index++)
            {
                var cell1 = row.CreateCell(index);
                cell1.CellStyle = style;
            }
            // 明細行罫線
            for (int y = 17; y <= 27; y++)
            {
                row = sheet.GetRow(y);
                for (int x = 0; x <= 11; x++)
                {
                    var cell1 = row.CreateCell(x);
                    cell1.CellStyle = style;
                }
            }
            // 備考欄罫線
            for (int y = 29; y <= 35; y++)
            {
                row = sheet.GetRow(y);
                if (row == null) row = sheet.CreateRow(y);
                for (int x = 1; x <= 10; x++)
                {
                    var cell1 = row.CreateCell(x);
                    cell1.CellStyle = style;
                }
            }
            // お振込先欄罫線
            for (int y = 37; y <= 43; y++)
            {
                row = sheet.GetRow(y);
                if (row == null) row = sheet.CreateRow(y);
                for (int x = 1; x <= 10; x++)
                {
                    var cell1 = row.CreateCell(x);
                    cell1.CellStyle = style;
                }
            }
            // タイトル
            row = sheet.CreateRow(1);
            var cell = row.CreateCell(5);
            style = workbook.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font = workbook.CreateFont();
            font.FontHeight = 400;
            font.Boldweight = 700;
            style.SetFont(font);
            cell.CellStyle = style;
            cell.SetCellValue("納品書");
            // お客様コード
            row = sheet.CreateRow(6);
            cell = row.CreateCell(8);
            cell.SetCellValue("お客様コードNo.");
            // 会社情報
            row = sheet.CreateRow(8);
            cell = row.CreateCell(8);
            cell.SetCellValue("アドバンスソフトウェア(株)");
            style = workbook.CreateCellStyle();
            style.CloneStyleFrom(cell.CellStyle);
            font = workbook.CreateFont();
            font.FontHeight = 240;
            font.Boldweight = 700;
            style.SetFont(font);
            cell.CellStyle = style;
            row = sheet.CreateRow(9);
            cell = row.CreateCell(8);
            cell.SetCellValue("〒918-8239");
            row = sheet.CreateRow(10);
            cell = row.CreateCell(8);
            cell.SetCellValue("福井県福井市成和1-2816");
            row = sheet.CreateRow(11);
            cell = row.CreateCell(8);
            cell.SetCellValue("TEL 0776-21-9008 FAX 0776-21-9022");
            style = workbook.CreateCellStyle();
            style.CloneStyleFrom(cell.CellStyle);
            font = workbook.CreateFont();
            font.FontHeight = 160;
            style.SetFont(font);
            cell.CellStyle = style;
            row = sheet.CreateRow(13);
            cell = row.CreateCell(0);
            cell.SetCellValue("毎度ありがとうございます。");
            row = sheet.CreateRow(14);
            cell = row.CreateCell(0);
            cell.SetCellValue("下記の通り納品致します。");
            // 明細
            row = sheet.GetRow(17);
            for (index = 0; index <= 11; index++)
            {
                var cell1 = row.GetCell(index);
                var style1 = workbook.CreateCellStyle();
                style1.CloneStyleFrom(cell1.CellStyle);
                style1.FillForegroundColor = HSSFColor.BLUE.index;
                style1.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
                style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                style1.VerticalAlignment = VerticalAlignment.CENTER;
                IFont font1 = workbook.CreateFont();
                font1.Color = HSSFColor.WHITE.index;
                style1.SetFont(font1);
                cell1.CellStyle = style1;
            }
            cell = row.GetCell(0);
            cell.SetCellValue("商品名");
            cell = row.GetCell(6);
            cell.SetCellValue("数量");
            cell = row.GetCell(7);
            cell.SetCellValue("単価");
            cell = row.GetCell(9);
            cell.SetCellValue("金額");
            row = sheet.GetRow(29);
            cell = row.GetCell(1);
            cell.SetCellValue("備考欄");
            row = sheet.GetRow(37);
            cell = row.GetCell(1);
            cell.SetCellValue("お振込先");
            // データ部分
            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            style = workbook.CreateCellStyle();
            style.VerticalAlignment = VerticalAlignment.CENTER;
            style.ShrinkToFit = true;
            cell.CellStyle = style;

        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="workbook"></param>
        private static void SetData(HSSFWorkbook workbook)
        {
            if (workbook == null) return;
            // シートの取得
            var sheet = workbook.GetSheetAt(0);
            // 宛先
            var row = sheet.GetRow(2);
            if (row == null) row = sheet.CreateRow(2);
            var cell = row.GetCell(0);
            if (cell == null) cell = row.CreateCell(0);
            cell.SetCellValue("アドバンスソフトウェア株式会社 様");
            // お客様コード
            row = sheet.GetRow(6);
            if (row == null) row = sheet.CreateRow(6);
            cell = row.CreateCell(10);
            cell.SetCellValue("N2012-11-30");
            // 明細 - 商品名
            row = sheet.GetRow(18);
            if (row == null) row = sheet.CreateRow(18);
            cell = row.GetCell(0);
            if (cell == null) row.CreateCell(0);
            cell.SetCellValue("ExcelCreator 5.0 for .NET");
            // 明細 - 数量
            cell = row.GetCell(6);
            if (cell == null) row.CreateCell(6);
            cell.SetCellValue(1);
            // 明細 - 単価
            cell = row.GetCell(7);
            if (cell == null) row.CreateCell(7);
            cell.SetCellValue(48300);
            var style = workbook.CreateCellStyle();
            style.CloneStyleFrom(cell.CellStyle);
            style.DataFormat = workbook.CreateDataFormat().GetFormat("\\\#,##0;\\\-#,##0");
            cell.CellStyle = style;
            // 明細 - 金額
            cell = row.GetCell(9);
            if (cell == null) row.CreateCell(9);
            cell.SetCellValue(48300);
            cell.CellStyle = style;
        }
    }
}

行やセル、属性(style)単位で処理を行っていきます。すでに行やセルが存在する場合は GetRow、GetCell メソッドで取得し、なければ CreateRow、CreateCell メソッドで作成します。属性(style)に関しては、ブックで共通管理されているため、CellStyle プロパティで取得したインスタンスに対して追加・変更を行う場合には、他にも同じスタイルが設定されたセルに対しても変更が行われてしまうため、注意が必要です。既存の属性に追加設定する場合は CloneStyleFrom メソッドで作成したインスタンスに対して追加設定を行うとよいと思われます。

範囲指定での罫線の設定を行うメソッドも用意されていますが、使用した場合、設定したセル分の属性が追加されていたため、ファイルサイズが大きくなる等の傾向が見られました。

※NPOI に関してはよりよい使用方法があるかもしれません。今後の検証で調整していきたいと思います。


◆ExcelCreator を使用してブックを新規作成

Visual Studio 2012 でコンソールアプリケーション「EC5ConsoleApplication」を新規作成し、ExcelCreator の各アセンブリへの参照を追加してコードを述していきます。

ブックを新規に作成するコード(ExcelCreator 5.0 for .NET)

using ExcelCreator;

namespace EC5ConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            var xlsCreator = new XlsCreator();
            // ブックの新規作成
            xlsCreator.CreateBook("EC5Create.xls", 1, xlVersion.ver2003);
            // シート名の設定
            xlsCreator.SheetName = "納品書";
            // レイアウトの設定
            SetLayout(xlsCreator);
            // データの設定
            SetData(xlsCreator);
            // ブックのクローズ
            xlsCreator.CloseBook(true);
        }
        /// <summary>
        /// レイアウトを設定します。
        /// </summary>
        /// <param name="xlsCreator"></param>
        private static void SetLayout(XlsCreator xlsCreator)
        {
            if (xlsCreator == null) return;
            // 列幅
            xlsCreator.Cell("A:L").ColWidth = 6.25;
            // 行高
            xlsCreator.Cell("16").RowHeight = 37.5;
            xlsCreator.Cell("18:28").RowHeight = 19.5;
            // セルの結合
            xlsCreator.Cell("F2:H4").Attr.Joint = true;
            xlsCreator.Cell("A3:D4").Attr.Joint = true;
            xlsCreator.Cell("I6:L6").Attr.Joint = true;
            xlsCreator.Cell("I6:L6").Attr.Joint = true;
            xlsCreator.Cell("K7:L7").Attr.Joint = true;
            // 明細行の結合
            for (int i = 18; i <= 28; i++)
            {
                string s = string.Format("A{0}:F{0}", i);
                xlsCreator.Cell(s).Attr.Joint = true;
                s = string.Format("H{0}:I{1}", i, i);
                xlsCreator.Cell(s).Attr.Joint = true;
                s = string.Format("J{0}:L{1}", i, i);
                xlsCreator.Cell(s).Attr.Joint = true;
            }
            // 備考欄の結合
            xlsCreator.Cell("B30:K30").Attr.Joint = true;
            xlsCreator.Cell("B31:K36").Attr.Joint = true;
            xlsCreator.Cell("B38:K38").Attr.Joint = true;
            xlsCreator.Cell("B39:K44").Attr.Joint = true;
            // 罫線
            xlsCreator.Cell("J16:L16").Attr.Box(xlBoxType.btLtc, xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue);
            xlsCreator.Cell("A18:L28").Attr.Box(xlBoxType.btBox, xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue);
            xlsCreator.Cell("A18:L27").Attr.LineBottom = xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue;
            xlsCreator.Cell("A18:K28").Attr.LineRight = xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue;
            xlsCreator.Cell("B30:K36").Attr.Box(xlBoxType.btBox, xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue);
            xlsCreator.Cell("B30:K30").Attr.LineBottom = xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue;
            xlsCreator.Cell("B38:K44").Attr.Box(xlBoxType.btBox, xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue);
            xlsCreator.Cell("B38:K38").Attr.LineBottom = xlLineStyle.lsNormal | (xlLineStyle)xlColor.xcBlue;
            // 配置
            xlsCreator.Cell("A18:L18").Attr.PosHorz = xlPosHorz.phCenter;
            xlsCreator.Cell("G19:L88").Attr.PosHorz = xlPosHorz.phRight;
            // 背景色
            xlsCreator.Cell("A18:L18").Attr.BackColor = xlColor.xcBlue;
            // タイトル
            xlsCreator.Cell("F2").Attr.FontPoint = 20;
            xlsCreator.Cell("F2").Attr.FontStyle = xlFontStyle.xsBold;
            xlsCreator.Cell("F2").Attr.PosHorz = xlPosHorz.phCenter;
            xlsCreator.Cell("F2").Value = "納品書";
            // お客様コード
            xlsCreator.Cell("I7").Value = "お客様コードNo.";
            // 会社情報
            xlsCreator.Cell("I9").Attr.FontPoint = 12;
            xlsCreator.Cell("I9").Attr.FontStyle = xlFontStyle.xsBold;
            xlsCreator.Cell("I9").Value = "アドバンスソフトウェア(株)";
            xlsCreator.Cell("I10").Value = "〒918-8239";
            xlsCreator.Cell("I11").Value = "福井県福井市成和1-2816";
            xlsCreator.Cell("I12").Attr.FontPoint = 9;
            xlsCreator.Cell("I12").Value = "TEL 0776-21-9008 FAX 0776-21-9022";
            xlsCreator.Cell("A14").Value = "毎度ありがとうございます。";
            xlsCreator.Cell("A15").Value = "下記の通り納品致します。";
            // 明細
            xlsCreator.Cell("A18:J18").Attr.FontColor = xlColor.xcWhite;
            xlsCreator.Cell("A18").Value = "商品名";
            xlsCreator.Cell("G18").Value = "数量";
            xlsCreator.Cell("H18").Value = "単価";
            xlsCreator.Cell("J18").Value = "金額";
            xlsCreator.Cell("B30").Value = "備考欄";
            xlsCreator.Cell("B38").Value = "お振込先";
            // データ部分
            xlsCreator.Cell("A3").Attr.FontPoint = 12;
            xlsCreator.Cell("A3").Attr.Fit = true;
        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="xlsCreator"></param>
        private static void SetData(XlsCreator xlsCreator)
        {
            if (xlsCreator == null) return;
            xlsCreator.Cell("A3").Value = "アドバンスソフトウェア株式会社 様";
            xlsCreator.Cell("K7").Value = "E2012-11-30";
            xlsCreator.Cell("A19").Value = "ExcelCreator 5.0 for .NET";
            xlsCreator.Cell("G19").Value = 1;
            xlsCreator.Cell("H19:J19").Attr.Format = "\\\#,##0;\\\-#,##0";
            xlsCreator.Cell("H19").Value = 48300;
            xlsCreator.Cell("J19").Value = 48300;
        }
    }
}

NPOI と比べると少しコードが短くなり、範囲指定での属性の設定ができるため、直感的に使用することができます。また、行やセルの有無を気にすることなく使用できるため、判定が不要になります。これは、内部で既存のデータとのマッチングを行っているためで、高速に比較できるようプログラムを組んであります。


◆まとめ

いずれのコンポーネントも同じようなブックを新規に作成することができ、データや罫線、背景などの属性を設定することができますが、コード量が多く、作成、メンテナンスが大変です。実際には一からブックを作成することは少く、あらかじめ Excel で設定されたレイアウトに対してデータや罫線等を設定していく方が現実的です。次回は既存の Excel 97-2003 ブックに対してデータを設定したり罫線などの属性を追加、変更する方法について書きたいと思います。