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 で行い、そのブックに対して必要なデータやスタイルを設定していくことで効率よく開発を進めることができ、また、メンテナンス性も向上します。

You can leave a response, or trackback from your own site.

Leave a Reply