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) をプログラムで扱う方法を紹介していきたいと思います。

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

Leave a Reply