セルの書式設定 - 表示形式

Open XML SDKをつかって、セルの表示形式を設定します。

「セルの書式設定 - その1 」でとりあげた、セルデータの表示形式をOpen XML SDKを使って編集します。

編集対象のワークブック「Book1.xlsx」のSheet1シートは、あらかじめ以下のような入力がされているものとします。

定義済みの表示形式をセット

セルA1に定義済みの表示形式「 #,##0.00 」をセットすることを考えます。この表示形式のnumFmtIdは4です。

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

namespace OpenXMLSDK_Sample4 {
    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 = "A1";
            const uint CELL_NUMBER_FORMAT_ID = 4; //#,##0.00

            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);

                Cell cell = GetCell(sheet_part, CELL_ADDRESS);

                //現在のs属性から現在のxfを取得
                CellFormat current_xf;
                IEnumerable<CellFormat> cell_formats = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<CellFormat>();

                if (cell.StyleIndex == null) {
                    current_xf = new CellFormat();
                    current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
                    bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
                    cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
                } else {
                    current_xf = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
                    if(current_xf.NumberFormatId != CELL_NUMBER_FORMAT_ID) {
                        current_xf = current_xf.CloneNode(true) as CellFormat;
                        current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
                        bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
                        cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
                    }
                }

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

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

        private static Cell GetCell(WorksheetPart sheet_part, string cell_address) {
            //行の特定
            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);
                }
            }

            return cell;
        }

        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null) {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>()) {
                //if (item.InnerText == text) {
                if (item.Text != null && item.Text.InnerText == text) {
                    return i;
                }
                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save(); //Flush the changes.

            return i;
        }

        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;
        }
    }
}

セルの表示形式を設定するのは以下の部分です。それ以外の部分は以前の記事「まずはセルデータから - その3 」で紹介した部分と同一です。

                //現在のs属性から現在のxfを取得
                CellFormat current_xf;
                IEnumerable<CellFormat> cell_formats = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<CellFormat>();

                if (cell.StyleIndex == null) {
                    current_xf = new CellFormat();
                    current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
                    bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
                    cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
                } else {
                    current_xf = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
                    if(current_xf.NumberFormatId != CELL_NUMBER_FORMAT_ID) {
                        current_xf = current_xf.CloneNode(true) as CellFormat;
                        current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
                        bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
                        cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
                    }
                }

29行目でcellにスタイルの適用があるかどうかをチェックします。スタイルの指定がなければ、新たにxf要素を作成してNumberFormatIDを4にセットし、CellFormats末尾に追加します。

cellにスタイルの適用がある場合はそのxf要素を取得します。

36行目で現在のxf要素のNumberFormatIDが4ではないことをチェックしていますが、このプログラムを複数回実行した場合に不要なxfを39行目で追加してしまわないようにするための処置です。

不要なxfがたくさんあったからといってファイル(C:\Users\Public\Documents\Book1.xlsx)を破損することはありませんが、実行するたびにファイルサイズが大きくなるのは嫌だなぁ...と。

37行目では変数current_xfをそのCloneで置き換えています。
今回の処理対象ファイル(Book1.xlsx)に限ってはこの行は不要ですが、実務に用いる場合は必要になります。

Excelを使ってファイルを編集する場合、セルをコピーして作業をしますよね？このとき、セルのスタイルも一緒にコピーされます。
37行目でCloneせずに38行目でNumberFormatIDを書き換えてしまうと、同じスタイルが適用されている(=同じxfを参照している)セルについても表示形式が変わってしまうことになってしまいます。