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

Open XML SDL 2.5 CTP 版と ExcelCreator を使用し、プログラム中から既存の 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 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.Open(@"明細.xlsx", true);

            // WorkbookPart を取得します。
            workbookPart = spreadsheetDocument.WorkbookPart;

            // WorksheetPart を取得します。
            IEnumerable<Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where
                (s => s.Name == "Sheet1");
            worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheets.First().Id);

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

            // データの設定
            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>
        /// <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);
            SetValue("B", 5, "Open XML SDK 2.5 CTP 版によるデータの書き込み。");
        }
        /// <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;
            }
        }
    }
}

前回の新規作成と比べ、スタイルの追加が不要になるため若干コードが短くなりますが、それでも結構なコード量になります。WorkbookPart や WorksheetPart は既存のものを使用するため、新規作成時と比べて取得方法が異なります。行やセル情報は既存のものから検索して存在すればそのまま使用、なければ追加しますので、対象のセルが多い場合は処理速度が低下します。高速に処理を行うには、それらのデータをリストやツリーなど追加や検索に有利なデータ構造に展開して個別に実装する必要があるものと思われます。

また、Open XML SDK 2.5 でブックをオープンした場合、オープンしたブックを直接編集して保存するため、元のファイルを残したい場合はオープン前にコピーを作成する必要があります。


◆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.OpenBook("EC8Open.xlsx", "明細.xlsx");
            // データの設定
            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 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;
            xlsxCreator.Cell("B5").Value = "ExcelCreator 8.0 for .NET によるデータの書き込み。";
        }
    }
}

前回のコードからレイアウトの設定を省き、オープン用のメソッドに変更するだけで実装が可能です。Open XML SDK 2.5 と比べ、ブックやシートのパーツを個別に取得する必要がないため、シンプルなコードで処理が書けます。ExcelCreator では、オープン時の引数により既存のブックをオープンして上書きするか別ファイルに保存(オーバレイ)するか処理を選択できるため、元のファイルを残す場合も個別にコピーを作成する必要はありません。


◆まとめ

プログラム中からブックを扱う場合、ほとんどの場合において、新規にブックを作成するよりも既存のブックをオープンして目的のセルに値を入れていく使い方になると思われます。Open XML SDK 2.5 を使用する場合は、行やセル、スタイルの扱いにおいてある程度処理をクラス化、あるいはメソッド化しておかないとメンテナンスが大変そうです。また、大量のデータを処理する場合は、別途、行やセルのデータの管理に工夫が必要になります。処理速度については、いずれ比較検証を行いたいと思っています。

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

Leave a Reply