まずはセルデータから - その3

前置きが長くなってしまいました。前回、前々回の内容を踏まえて、Open XML SDKを使ってセルにデータをセットするプログラムを紹介します。

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Linq;
using System.Text.RegularExpressions;

namespace OpenXMLSDK_Sample1 {
    class Program {
        static void Main(string[] args) {
            const string FILE = @"C:\Users\Public\Documents\Book1.xlsx";
            const string SHEET_NAME = "Sheet1";
            const string CELL_ADDRESS = "A6";
            const int VALUE = 123456;

            using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(FILE, true)) {
                WorkbookPart bookPart = excelDoc.WorkbookPart;

                int sheet_index = GetWorksheetIndex(bookPart, SHEET_NAME);
                WorksheetPart sheet_part = GetWorksheetPart(bookPart, sheet_index);

                //行の特定
                uint rowIndex = GetRowIndex(CELL_ADDRESS);

                Row row = sheet_part.Worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
                if (row == null) {
                    //行がない場合は行を新規作成する
                    row = new Row() { RowIndex = new UInt32Value(rowIndex) };

                    SheetData sh_data = sheet_part.Worksheet.Descendants<SheetData>().First();
                    //場所を特定して行を挿入(行は行番号で昇順に並ばないとだめ)
                    Row rRow = sh_data.Elements<Row>().LastOrDefault(r => r.RowIndex < rowIndex);

                    if (rRow == null) {
                        sh_data.InsertAt(row, 0);
                    } else {
                        sh_data.InsertAfter(row, rRow);
                    }
                }

                uint colIndex = GetColumnIndex(CELL_ADDRESS);
                //セルを特定
                Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == CELL_ADDRESS);
                if (cell == null) {
                    cell = new Cell() { CellReference = new StringValue(CELL_ADDRESS) };

                    //cellは昇順に書き込む必要がある
                    Cell rCell = row.Elements<Cell>().LastOrDefault(c => GetColumnIndex(c.CellReference) < colIndex);
                    if (rCell == null) {
                        row.InsertAt(cell, 0);
                    } else {
                        row.InsertAfter(cell, rCell);
                    }
                }

                //値をセット(セットする値が文字列ではない場合)
                cell.CellValue = new CellValue(VALUE.ToString());

                //保存
                sheet_part.Worksheet.Save();
            }

            //ファイルを開く
            System.Diagnostics.Process.Start(FILE);
        }

        static int GetWorksheetIndex(WorkbookPart book_part, string sheet_name) {
            Sheets sheets = book_part.Workbook.GetFirstChild<Sheets>();

            //シートのエレメントの順番(0スタート)
            int sheet_index = 0;
            foreach (Sheet sh in sheets) {
                if (sh.Name == sheet_name)
                    break;
                sheet_index++;
            }
            return sheet_index;
        }

        static WorksheetPart GetWorksheetPart(WorkbookPart book_part, int index) {
            Sheets sheets = book_part.Workbook.GetFirstChild<Sheets>();
            if (index >= sheets.Count()) {
                return null;
            }

            string target_relationshipID = sheets.Descendants<Sheet>().ElementAt(index).Id;
            return (WorksheetPart)book_part.GetPartById(target_relationshipID);
        }

        public static uint GetColumnIndex(string cellName) {
            string column_name = GetColumnName(cellName).ToUpper();
            const string colChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            uint column_index = 0;
            for (int i = column_name.Length; i > 0; i--) {
                column_index += (uint)((colChars.IndexOf(column_name.Substring(column_name.Length - i, 1)) + 1) * Math.Pow(26, i - 1));
            }
            return column_index;
        }

        //https://msdn.microsoft.com/ja-jp/library/office/Cc822064.aspx
        // Given a cell name, parses the specified cell to get the row index.
        public static uint GetRowIndex(string cellName) {
            // Create a regular expression to match the row index portion the cell name.
            Regex regex = new Regex(@"\d+");
            Match match = regex.Match(cellName);
            return uint.Parse(match.Value);
        }
        
        //https://msdn.microsoft.com/ja-jp/library/office/Cc822064.aspx
        public static string GetColumnName(string cellName) {
            Match match = Regex.Match(cellName, "[A-Za-z]+");
            return match.Value;
        }
    }
}

準備

当然といえば当然ですが、OpenXML SDKを使えるようにします。(Nugetで検索して、インストールするのが簡単です。)

それから、参照設定に「WindowsBase」を追加します。

コードの説明

上記のコードにおいて、GetRowIndex、GetColumnName の二つのメソッドはMSDN (https://msdn.microsoft.com/ja-jp/library/office/Cc822064.aspx) から拝借してきました。

上記のコードは「C:\Users\Public\Documents\Book1.xlsx のSheet1のセルA6に123456をセットする」コードです。

16行目で、C:\Users\Public\Documents\Book1.xlsx を開き、17行目でそのWorkbookパーツ(/xl/workbook.xml)を特定します。
つづいて、20行目でWorksheetパーツ(/xl/worksheets/sheet1.xml)を特定します。

 using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(FILE, true)) {
                WorkbookPart bookPart = excelDoc.WorkbookPart;

                int sheet_index = GetWorksheetIndex(bookPart, SHEET_NAME);
                WorksheetPart sheet_part = GetWorksheetPart(bookPart, sheet_index);

ここまでが前回の内容(シート名を手がかりに、/xl/workbook.xml　ファイルからリレーションシップIDを取得する。取得したリレーションシップIDをキーに/xl/_res/workbook.xml.rels ファイルからその定義が記述されているWorksheetパーツファイルを特定する。)に相当します。

つづいて、23行目から25行目までで、セルアドレスの行番号を手がかりに行(r要素)を特定します。
26行目から39行目は行が存在しない場合の処理です。行が存在しない場合(注目する行にデータを持つセルがかつて存在したことがない場合は、その行は存在しません)、新たに行を作成し、SheetDataの子要素としてこれを挿入します。

                //行の特定
                uint rowIndex = GetRowIndex(CELL_ADDRESS);

                Row row = sheet_part.Worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
                if (row == null) {
                    //行がない場合は行を新規作成する
                    row = new Row() { RowIndex = new UInt32Value(rowIndex) };

                    SheetData sh_data = sheet_part.Worksheet.Descendants<SheetData>().First();
                    //場所を特定して行を挿入(行は行番号で昇順に並ばないとだめ)
                    Row rRow = sh_data.Elements<Row>().LastOrDefault(r => r.RowIndex < rowIndex);

                    if (rRow == null) {
                        sh_data.InsertAt(row, 0);
                    } else {
                        sh_data.InsertAfter(row, rRow);
                    }
                }

行を特定できたら、今度はセル(c要素)を特定します(41行目から54行目まで)。行の場合と同じく、セルが存在しない場合にはセルを作成し、行に挿入します。

                uint colIndex = GetColumnIndex(CELL_ADDRESS);
                //セルを特定
                Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == CELL_ADDRESS);
                if (cell == null) {
                    cell = new Cell() { CellReference = new StringValue(CELL_ADDRESS) };

                    //cellは昇順に書き込む必要がある
                    Cell rCell = row.Elements<Cell>().LastOrDefault(c => GetColumnIndex(c.CellReference) < colIndex);
                    if (rCell == null) {
                        row.InsertAt(cell, 0);
                    } else {
                        row.InsertAfter(cell, rCell);
                    }
                }

ここまででセルを特定できました。あとは57行目でセルのv要素をセットし、60行目でシートへの編集内容を保存します。

               //値をセット(セットする値が文字列ではない場合)
                cell.CellValue = new CellValue(VALUE.ToString());

                //保存
                sheet_part.Worksheet.Save();

このコードを実行すると、64行目でC:\Users\Public\Documents\Book1.xlsxを開きます。セルA6に正しく「123456」がセットされていることを確認してください。

GetColumnIndexの実装がちょっとわかりづらいかもしれませんが、Excelの列名は26進数とみなせるので、列名の末尾から一文字ずつ切り出し、桁の重みをかけながら加算して10進数に変換し、列番号(0スタートです)を求めています。

で、文字列をセットするには...

上記のコードはセルの値に数値をセットする例だったので、セルを特定したらあとは57行目「cell.CellValue = new CellValue(VALUE.ToString());」だけでセルデータの編集が終わります。

文字列をセットする場合には、(前々回で確認したように)SharedStringも同時に編集する必要があるので、57行目に相当する部分がもうちょっと複雑になります。

文字列をセットする場合のコードは次回、紹介予定です。