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

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


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


◆Open XML SDK 2.5 を使用してブックを新規作成

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

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

using System;
using System.Diagnostics;
using System.Linq;
using System.Drawing;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OXML25ConsoleApplication
{
    /// <summary>
    /// Program クラス
    /// </summary>
    class Program
    {
        /// <summary>
        /// private メンバ
        /// </summary>
        private static SpreadsheetDocument spreadsheetDocument = null;
        private static WorkbookPart workbookPart = null;
        private static WorksheetPart worksheetPart = null;
        private static WorkbookStylesPart workbookStylesPart = null;
        private static SharedStringTablePart sharedStringPart = null;
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            TimeSpan ts = new TimeSpan();
            sw.Start();
            Console.WriteLine("Open XML SDK 2.5 による Excel (*.xlsx) ブックの新規作成。");

            // SpreadsheetDocument を作成します。
            spreadsheetDocument = 
                SpreadsheetDocument.Create(@"OXMLCreate.xlsx", SpreadsheetDocumentType.Workbook);

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

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

            // WorkbookStylesPart を WorkbookPart に追加します。
            workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
            // 標準のスタイル情報を作成します。
            CreateDefaultStyles();

            // 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 = "明細"
            };
            sheets.Append(sheet);

            // SharedStringTablePart を作成します。
            OpensharedStringPart();

            // レイアウトの設定
            SetLayout();
            // データの設定
            SetData();

            // ワークブックを保存します。
            workbookPart.Workbook.Save();

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

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

        }
        /// <summary>
        /// 標準のスタイル情報を作成します
        /// </summary>
        private static void CreateDefaultStyles()
        {
            Stylesheet stylesheet = new Stylesheet();
            // フォント
            Fonts fonts = new Fonts() { Count = (UInt32Value)2U };
            DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontSize fontSize1 = new FontSize() { Val = 11D };
            DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U };
            FontName fontName1 = new FontName() { Val = "MS Pゴシック" };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontScheme1);
            DocumentFormat.OpenXml.Spreadsheet.Font font2 = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontSize fontSize2 = new FontSize() { Val = 11D };
            DocumentFormat.OpenXml.Spreadsheet.Color color2 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = "FF006100" };
            FontName fontName2 = new FontName() { Val = "MS Pゴシック" };
            FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
            font2.Append(fontSize2);
            font2.Append(color2);
            font2.Append(fontName2);
            font2.Append(fontFamilyNumbering2);
            font2.Append(fontScheme2);
            fonts.Append(font1);
            fonts.Append(font2);
            // 塗りつぶし
            Fills fills = new Fills() { Count = (UInt32Value)2U };
            Fill fill1 = new Fill();
            PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
            fill1.Append(patternFill1);
            Fill fill2 = new Fill();
            PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
            fill2.Append(patternFill2);
            fills.Append(fill1);
            fills.Append(fill2);
            // 罫線
            Borders borders = new Borders() { Count = (UInt32Value)1U };
            Border border = new Border();
            LeftBorder leftBorder = new LeftBorder();
            RightBorder rightBorder = new RightBorder();
            TopBorder topBorder = new TopBorder();
            BottomBorder bottomBorder = new BottomBorder();
            DiagonalBorder diagonalBorder = new DiagonalBorder();
            border.Append(leftBorder);
            border.Append(rightBorder);
            border.Append(topBorder);
            border.Append(bottomBorder);
            border.Append(diagonalBorder);
            borders.Append(border);

            CellStyleFormats cellStyleFormats = new CellStyleFormats() { Count = (UInt32Value)1U };
            CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };

            cellStyleFormats.Append(cellFormat1);

            CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)1U };
            CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };

            cellFormats.Append(cellFormat2);

            CellStyles cellStyles = new CellStyles() { Count = (UInt32Value)1U };
            CellStyle cellStyle = new CellStyle() { Name = "標準", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

            cellStyles.Append(cellStyle);
            DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)0U };
            TableStyles tableStyles = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" };

            stylesheet.Append(fonts);
            stylesheet.Append(fills);
            stylesheet.Append(borders);
            stylesheet.Append(cellStyleFormats);
            stylesheet.Append(cellFormats);
            stylesheet.Append(cellStyles);
            stylesheet.Append(differentialFormats);
            stylesheet.Append(tableStyles);
            workbookStylesPart.Stylesheet = stylesheet;
        }
        /// <summary>
        /// レイアウトを設定します。
        /// </summary>
        /// <param name="sheet"></param>
        private static void SetLayout()
        {
            // 罫線
            Border border = new Border()
            {
                BottomBorder = new BottomBorder()
                {
                    Style = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Medium,
                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = 12 }
                },
                LeftBorder = new LeftBorder()
                {
                    Style = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Medium,
                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = 12 }
                },
                RightBorder = new RightBorder()
                {
                    Style = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Medium,
                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = 12 }
                },
                TopBorder = new TopBorder()
                {
                    Style = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Medium,
                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Indexed = 12 }
                }
            };
            // 罫線情報を追加
            int count = workbookStylesPart.Stylesheet.Borders.Count();
            workbookStylesPart.Stylesheet.Borders.Append(border);
            workbookStylesPart.Stylesheet.Borders.Count = new UInt32Value((uint)++count);
            // 塗りつぶし情報を追加
            int fillCount = workbookStylesPart.Stylesheet.Fills.Count();
            Fill fill = new Fill();
            PatternFill patternFill = new PatternFill() { PatternType = PatternValues.Solid, ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Indexed = 12 } };
            fill.Append(patternFill);
            workbookStylesPart.Stylesheet.Fills.Append(fill);
            workbookStylesPart.Stylesheet.Fills.Count = new UInt32Value((uint)++fillCount);
            // フォント情報を追加
            int fontCount = workbookStylesPart.Stylesheet.Fonts.Count();
            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontSize fontSize = new FontSize() { Val = 11D };
            DocumentFormat.OpenXml.Spreadsheet.Color color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = "FFFFFFFF" };
            FontName fontName = new FontName() { Val = "MS Pゴシック" };
            FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
            font.Append(fontSize);
            font.Append(color);
            font.Append(fontName);
            font.Append(fontFamilyNumbering);
            font.Append(fontScheme);
            workbookStylesPart.Stylesheet.Fonts.Append(font);
            workbookStylesPart.Stylesheet.Fonts.Count = new UInt32Value((uint)++fontCount);
            // 属性を追加
            CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyBorder = true, ApplyAlignment = false, ApplyProtection = false };
            count = workbookStylesPart.Stylesheet.CellFormats.Count();
            workbookStylesPart.Stylesheet.CellFormats.Append(cellFormat);
            workbookStylesPart.Stylesheet.CellFormats.Count = new UInt32Value((uint)++count);
            // スタイルをセルに設定
            InsertCellInWorksheet("B", 2, count - 1);
            InsertCellInWorksheet("C", 2, count - 1);
            InsertCellInWorksheet("D", 2, count - 1);
            InsertCellInWorksheet("E", 2, count - 1);
            // 属性を追加
            cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = false, ApplyBorder = true, ApplyAlignment = false, ApplyProtection = false };
            count = workbookStylesPart.Stylesheet.CellFormats.Count();
            workbookStylesPart.Stylesheet.CellFormats.Append(cellFormat);
            workbookStylesPart.Stylesheet.CellFormats.Count = new UInt32Value((uint)++count);
            // スタイルをセルに設定
            for (int i = 3; i <= 4; i++)
            {
                InsertCellInWorksheet("B", i, count - 1);
                InsertCellInWorksheet("C", i, count - 1);
                InsertCellInWorksheet("D", i, count - 1);
                InsertCellInWorksheet("E", i, count - 1);
            }
            // タイトル行
            SetValue("B", 2, "商品名");
            SetValue("C", 2, "数量");
            SetValue("D", 2, "単価");
            SetValue("E", 2, "金額");
        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="sheet"></param>
        private static void SetData()
        {
            // データ
            SetValue("B", 3, "ExcelCreator 2012");
            SetValue("C", 3, 1);
            SetValue("D", 3, 63000);
            SetValue("E", 3, 63000);
            SetValue("B", 4, "VB-Report 7");
            SetValue("C", 4, 1);
            SetValue("D", 4, 81900);
            SetValue("E", 4, 81900);
        }
        /// <summary>
        /// SharedStringTable を開く
        /// </summary>
        private static void OpensharedStringPart()
        {
            if (spreadsheetDocument == null) return;
            if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                sharedStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            else
                sharedStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }
        /// <summary>
        /// SharedStringTable への値の設定
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        private static int InsertSharedStringItem(string text)
        {
            if (sharedStringPart == null) return -1;
            // 共有文字列テーブルのデータがない場合新規で作成する。
            if (sharedStringPart.SharedStringTable == null)
                sharedStringPart.SharedStringTable = new SharedStringTable();
            int index = 0;
            foreach (SharedStringItem item in sharedStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    return index;
                index++;
            }
            // 共有文字列に文字を設定
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            return index;
        }
        /// <summary>
        /// 値の設定
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="rowIndex"></param>
        /// <param name="value"></param>
        private static void SetValue(string columnName, int rowIndex, object value)
        {
            Type type = value.GetType();

            string valueString = "";
            if (value != null)
                valueString = value.ToString();
            Cell cell = InsertCellInWorksheet(columnName, rowIndex, -1);
            if (type == typeof(string))
            {
                // Insert the result into the SharedStringTablePart.
                int index = InsertSharedStringItem(valueString);

                cell.CellValue = new CellValue(index.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
            }
            else
            {
                cell.CellValue = new CellValue(valueString);
                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
            }
            return;
        }
        /// <summary>
        /// セル情報の検索と取得(存在しない場合は新規追加)
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="rowIndex"></param>
        /// <param name="styleIndex"></param>
        /// <returns></returns>
        private static Cell InsertCellInWorksheet(string columnName, int rowIndex, int styleIndex)
        {
            if (worksheetPart == null) return null;
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex.ToString();

            // 列情報があるかを確認し、無い場合は新規で作成する。
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            else
            {
                Row refRow = null;
                foreach (Row r in sheetData.Elements<Row>())
                {
                    if (r.RowIndex > rowIndex)
                    {
                        refRow = r;
                        break;
                    }
                }
                row = new Row() { RowIndex = (uint)rowIndex };
                sheetData.InsertBefore(row, refRow);
            }

            // 列番号から、セルの存在を確認する。
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex.ToString()).Count() > 0)
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            else
            {
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference, cellReference) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }
                Cell newCell = new Cell() { CellReference = cellReference };
                if (styleIndex >= 0) newCell.StyleIndex = new UInt32Value((uint)styleIndex);
                row.InsertBefore(newCell, refCell);
                return newCell;
            }
        }
    }
}

ワークブックに対してシートやスタイル情報等のパーツを追加していく手順となります。スタイルの追加では、フォントや罫線、塗りつぶし情報を追加した上で、それらの組み合わせでセルのスタイルを追加してそのインデックス番号をセル情報の”s”タグに設定します。今回は省略しましたが、スタイルを設定する場合、本来は既存のスタイル情報とのマッチングを行い、すでに存在する場合はそのスタイル番号を指定する必要があります。フォント、罫線、塗りつぶしそれぞれでマッチングを行う必要があるため、全てのスタイル情報を扱うには相当なコード量となるものと思われます。

データの設定に関しても既存の行やセル情報とのマッチングが必須となり、データ量が多くなる場合、比較において高速な処理が求められます。また、文字列は別の構造で管理されるため、個別の処理が必要です。


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

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

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

using System;
using System.Diagnostics;
using AdvanceSoftware.ExcelCreator;
using AdvanceSoftware.ExcelCreator.Xlsx;
using System.Drawing;

namespace EC8ConsoleApplication
{
    /// <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 8.0 for .NET による Excel (*.xlsx) ブックの新規作成。");

            var xlsxCreator = new XlsxCreator();
            // ブックの新規作成
            xlsxCreator.CreateBook("EC8Create.xlsx", 1, xlsxVersion.ver2010);
            // シート名の設定
            xlsxCreator.SheetName = "明細";
            // レイアウトの設定
            SetLayout(xlsxCreator);
            // データの設定
            SetData(xlsxCreator);
            // ブックのクローズ
            xlsxCreator.CloseBook(true);

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

        }
        /// <summary>
        /// レイアウトを設定します。
        /// </summary>
        /// <param name="xlsxCreator"></param>
        private static void SetLayout(XlsxCreator xlsxCreator)
        {
            if (xlsxCreator == null) return;
            // 罫線
            xlsxCreator.Cell("B2:E4").Attr.Box(BoxType.Ltc, BorderStyle.Medium, xlColor.Blue);
            // 背景色
            xlsxCreator.Cell("B2:E2").Attr.BackColor = Color.Blue;
            // タイトル行
            xlsxCreator.Cell("B2:E2").Attr.FontColor = Color.White;
            xlsxCreator.Cell("B2").Value = "商品名";
            xlsxCreator.Cell("C2").Value = "数量";
            xlsxCreator.Cell("D2").Value = "単価";
            xlsxCreator.Cell("E2").Value = "金額";
        }
        /// <summary>
        /// データを設定します。
        /// </summary>
        /// <param name="xlsxCreator"></param>
        private static void SetData(XlsxCreator xlsxCreator)
        {
            if (xlsxCreator == null) return;
            // データ
            xlsxCreator.Cell("B3").Value = "ExcelCreator 2012";
            xlsxCreator.Cell("C3").Value = 1;
            xlsxCreator.Cell("D3").Value = 63000;
            xlsxCreator.Cell("E3").Value = 63000;
            xlsxCreator.Cell("B4").Value = "VB-Report 7";
            xlsxCreator.Cell("C4").Value = 1;
            xlsxCreator.Cell("D4").Value = 81900;
            xlsxCreator.Cell("E4").Value = 81900;
        }
    }
}

Open XML SDK 2.5 と比べると少しコードが短くなり、必要最小限の設定で目的のブックを作成することができます。xls 形式のプログラムと比べてもよく似たコーディングでブックの作成ができるため、両形式をサポートするプログラムを作成する場合も最小限の変更で対応ができます。色の指定では System.Drawing.Color を使用したプロパティ、メソッドがサポートされます。


◆まとめ

Open XML SDK では、提供されるクラスやプロパティ、メソッドが細かく構造化されていて、xlsx 形式のファイルを細かく扱うことが可能です。しかしながら、それらのクラスを効率よく扱うにはデータ構造にかなりの工夫が必要で、ブックやクラスの構造を熟知する必要があります。

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

Leave a Reply