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 ブックに対してデータを設定したり罫線などの属性を追加、変更する方法について書きたいと思います。

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

Leave a Reply