セルの書式設定 - 罫線その1 · P-SPACE(ピースペース)

openxmlsdk - この記事は連載の一部です

パート 16: この記事

パート 17: セルの書式設定 - 罫線その2

パート 18: セルの結合

パート 19: 新しい行の挿入

パート 20: 新しい列の挿入

今回はセルに罫線をセットするプログラムを紹介します。初めに単一のセルに罫線を引くプログラムを作り、次回にセル範囲を対象に罫線を引くプログラムを作ります。

このシートのファイル名は「C:\Users\Public\Documents\openxmlsdk16.xlsx」としています。

以下、C列, G列、I列のセルに罫線を引くサンプルです。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202


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

namespace OpenXMLSDK_Sample9 {
    class Program {
        static void Main(string[] args) {
            const string FILE = @"C:\Users\Public\Documents\openxmlsdk16.xlsx";
            const string SHEET_NAME = "Sheet1";

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

                //罫線を引く
                Border BorderC2 = new Border();
                BorderC2.TopBorder = new TopBorder() { Style = BorderStyleValues.Thin };
                AppendCellBorder(sheet_part, "C2", BorderC2);

                Border BorderC3 = new Border();
                BorderC3.RightBorder = new RightBorder() { Style = BorderStyleValues.Thin };
                AppendCellBorder(sheet_part, "C3", BorderC3);

                Border BorderC4 = new Border();
                BorderC4.BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin };
                AppendCellBorder(sheet_part, "C4", BorderC4);

                Border BorderC5 = new Border();
                BorderC5.LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thin };
                AppendCellBorder(sheet_part, "C5", BorderC5);

                Border BorderC6 = new Border();
                BorderC6.DiagonalBorder = new DiagonalBorder() { Style = BorderStyleValues.Thin };
                BorderC6.DiagonalUp = new BooleanValue(true);
                BorderC6.DiagonalDown = new BooleanValue(false);
                AppendCellBorder(sheet_part, "C6", BorderC6);

                Border BorderC7 = new Border();
                BorderC7.DiagonalBorder = new DiagonalBorder() { Style = BorderStyleValues.Thin };
                BorderC7.DiagonalUp = new BooleanValue(false);
                BorderC7.DiagonalDown = new BooleanValue(true);
                AppendCellBorder(sheet_part, "C7", BorderC7);

                Border BorderC8 = new Border();
                BorderC8.DiagonalBorder = new DiagonalBorder() { Style = BorderStyleValues.Thin };
                BorderC8.DiagonalUp = new BooleanValue(true);
                BorderC8.DiagonalDown = new BooleanValue(true);
                AppendCellBorder(sheet_part, "C8", BorderC8);

                //罫線を追加
                Border BorderG2 = new Border();
                BorderG2.TopBorder = new TopBorder() { Style = BorderStyleValues.Medium };
                AppendCellBorder(sheet_part, "G2", BorderG2);

                Border BorderG3 = new Border();
                BorderG3.RightBorder = new RightBorder() { Style = BorderStyleValues.Medium };
                AppendCellBorder(sheet_part, "G3", BorderG3);

                Border BorderG4 = new Border();
                BorderG4.BottomBorder = new BottomBorder() { Style = BorderStyleValues.Medium };
                AppendCellBorder(sheet_part, "G4", BorderG4);

                Border BorderG5 = new Border();
                BorderG5.LeftBorder = new LeftBorder() { Style = BorderStyleValues.Medium };
                AppendCellBorder(sheet_part, "G5", BorderG5);

                Border BorderG6 = new Border();
                BorderG6.DiagonalBorder = new DiagonalBorder() { Style = BorderStyleValues.Medium };
                BorderG6.DiagonalUp = new BooleanValue(true);
                BorderG6.DiagonalDown = new BooleanValue(false);
                AppendCellBorder(sheet_part, "G6", BorderG6);

                Border BorderG7 = new Border();
                BorderG7.DiagonalBorder = new DiagonalBorder() { Style = BorderStyleValues.Medium };
                BorderG7.DiagonalUp = new BooleanValue(false);
                BorderG7.DiagonalDown = new BooleanValue(true);
                AppendCellBorder(sheet_part, "G7", BorderG7);

                Border BorderG8 = new Border();
                BorderG8.DiagonalBorder = new DiagonalBorder() { Style = BorderStyleValues.Medium };
                BorderG8.DiagonalUp = new BooleanValue(true);
                BorderG8.DiagonalDown = new BooleanValue(true);
                AppendCellBorder(sheet_part, "G8", BorderG8);

                //くねくね
                for (int i = 0; i < 8; i++) {
                    Border border = new Border();
                    if (i % 2 == 0) {
                        border.RightBorder = new RightBorder() { Style = BorderStyleValues.Thick, Color = new Color() { Rgb = new HexBinaryValue("FFFF0000") } };
                    } else {
                        border.LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thick, Color = new Color() { Rgb = new HexBinaryValue("FF0000FF") } };
                    }
                    border.BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thick, Color = new Color() { Rgb = new HexBinaryValue("FF00FF00") } };

                    AppendCellBorder(sheet_part, string.Format("I{0}", 2 + i), border);
                }

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

        public static void AppendCellBorder(WorksheetPart target_ws_part, string cell_address, Border border) {
            Cell cell = GetCell(target_ws_part, cell_address);
            WorkbookPart bookPart = target_ws_part.GetParentParts().First() as WorkbookPart;

            CellFormat cell_format;
            if (cell.StyleIndex == null) {
                cell_format = new CellFormat();
            } else {
                cell_format = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
            }

            Border merged_border;
            if (cell_format.BorderId == null) {
                merged_border = border;
            } else {
                Border current_border = bookPart.WorkbookStylesPart.Stylesheet.Borders.ElementAt((int)cell_format.BorderId.Value) as Border;
                merged_border = MergeBorder(current_border, border);
            }

            Border same_border = bookPart.WorkbookStylesPart.Stylesheet.Borders.FirstOrDefault(b => b.OuterXml == merged_border.OuterXml) as Border;
            int border_id;
            if (same_border == null) {
                bookPart.WorkbookStylesPart.Stylesheet.Borders.Append(merged_border);
                bookPart.WorkbookStylesPart.Stylesheet.Borders.Count++; //Count属性を更新
                border_id = bookPart.WorkbookStylesPart.Stylesheet.Borders.Count() - 1;
            } else {
                border_id = bookPart.WorkbookStylesPart.Stylesheet.Borders.ToList().IndexOf(same_border);
            }

            //border_idを持ち、かつその他の属性が「cell_formatのborder以外」と一致するようなCellFormatを探す
            IEnumerable<Cellformat> cell_format_enumerable = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<Cellformat>();

            CellFormat target_format = cell_format_enumerable.FirstOrDefault(xf =>
                xf.GetAttributes().Where(at => at.LocalName != "borderId").SequenceEqual(cell_format.GetAttributes().Where(at => at.LocalName != "borderId"))
                && (xf.BorderId != null && xf.BorderId == border_id) && xf.InnerXml == cell_format.InnerXml);

            if (target_format == null) {
                target_format = cell_format.CloneNode(true) as CellFormat;
                if (target_format.BorderId == null) {
                    target_format.BorderId = new UInt32Value((uint)border_id);
                } else {
                    target_format.BorderId.Value = (uint)border_id;
                }

                bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(target_format);
                bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;

                cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
            } else {
                cell.StyleIndex = new UInt32Value((uint)(cell_format_enumerable.ToList().IndexOf(target_format)));
            }
        }

        private static Border MergeBorder(Border border_modified, Border border_append) {
            Border merged_border = border_modified.CloneNode(true) as Border;

            //TopBorder
            if (border_append.TopBorder != null && border_append.TopBorder.HasAttributes) {
                merged_border.TopBorder = border_append.TopBorder.CloneNode(true) as TopBorder;
            }

            //LeftBorder
            if (border_append.LeftBorder != null && border_append.LeftBorder.HasAttributes) {
                merged_border.LeftBorder = border_append.LeftBorder.CloneNode(true) as LeftBorder;
            }

            //BottomBorder
            if (border_append.BottomBorder != null && border_append.BottomBorder.HasAttributes) {
                merged_border.BottomBorder = border_append.BottomBorder.CloneNode(true) as BottomBorder;
            }

            //RightBorder
            if (border_append.RightBorder != null && border_append.RightBorder.HasAttributes) {
                merged_border.RightBorder = border_append.RightBorder.CloneNode(true) as RightBorder;
            }

            //DiagonalBorder
            if (border_append.DiagonalBorder != null && border_append.DiagonalBorder.HasAttributes) {
                merged_border.DiagonalBorder = border_append.DiagonalBorder.CloneNode(true) as DiagonalBorder;
            }

            //Diagonal attribute
            if (border_append.DiagonalDown != null) {
                merged_border.DiagonalDown = border_append.DiagonalDown.Clone() as BooleanValue;
            }

            if (border_append.DiagonalUp != null) {
                merged_border.DiagonalUp = border_append.DiagonalUp.Clone() as BooleanValue;
            }

            return merged_border;
        }
    // 以下省略...
    }
}

「以下省略」とした部分に定義されているメソッドについては「セルの書式設定 - 表示形式」など、以前の記事をご覧ください。

罫線を引くセルが多いのでサンプルプログラムが長くなっていますが、「引きたいBorderを作って、指定されたセルの罫線に追加」という処理を繰り返しているだけです。

「引きたいBorderを作って、指定されたセルの罫線に追加」するために今回AppendCellBorderを定義します。(108行目～159行目)

この関数AppendCellBorderにおいて、112行目～117行目でセルのStyleIndexから現在のCellFormatを取得します。

112
113
114
115
116
117


CellFormat cell_format;
if (cell.StyleIndex == null) {
    cell_format = new CellFormat();
} else {
    cell_format = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
}

つづいてセットするBorderを準備し(詳しくは後述)、Border以外の書式を保持するために「セットするBorder以外が現在の書式と一致する」ようなCellFormatを探します(139行目～143行目)。

137
138
139
140
141
142


//border_idを持ち、かつその他の属性が「cell_formatのborder以外」と一致するようなCellFormatを探す
IEnumerable<Cellformat> cell_format_enumerable = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<Cellformat>();
 
CellFormat target_format = cell_format_enumerable.FirstOrDefault(xf =>
    xf.GetAttributes().Where(at => at.LocalName != "borderId").SequenceEqual(cell_format.GetAttributes().Where(at => at.LocalName != "borderId"))
    && (xf.BorderId != null && xf.BorderId == border_id) && xf.InnerXml == cell_format.InnerXml);

もし見つかればそのスタイルをセットし、見つからなければ新たにCellFormatを定義します(145行目～159行目)。

145
146
147
148
149
150
151
152
153
154
155
156
157
158
159


if (target_format == null) {
    target_format = cell_format.CloneNode(true) as CellFormat;
    if (target_format.BorderId == null) {
        target_format.BorderId = new UInt32Value((uint)border_id);
    } else {
        target_format.BorderId.Value = (uint)border_id;
    }
 
    bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(target_format);
    bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
 
    cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
} else {
    cell.StyleIndex = new UInt32Value((uint)(cell_format_enumerable.ToList().IndexOf(target_format)));
}

処理の流れとしては、前回の記事「セルの書式設定 - セルの背景色」とほぼ同じです。

背景色の代わりに罫線をセットする(119行目～136行目)のですが、引数に指定したborderをそのまま適用すると、元々引かれていた罫線と置き換わってしまいます。

そこで元々引かれていた罫線に引数borderを反映する(上書きする)よう、関数MergeBorderを定義して元々の罫線を生かすように工夫しています。

119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135


    Border merged_border;
    if (cell_format.BorderId == null) {
        merged_border = border;
    } else {
        Border current_border = bookPart.WorkbookStylesPart.Stylesheet.Borders.ElementAt((int)cell_format.BorderId.Value) as Border;
        merged_border = MergeBorder(current_border, border);
    }

    Border same_border = bookPart.WorkbookStylesPart.Stylesheet.Borders.FirstOrDefault(b => b.OuterXml == merged_border.OuterXml) as Border;
    int border_id;
    if (same_border == null) {
        bookPart.WorkbookStylesPart.Stylesheet.Borders.Append(merged_border);
        bookPart.WorkbookStylesPart.Stylesheet.Borders.Count++; //Count属性を更新
        border_id = bookPart.WorkbookStylesPart.Stylesheet.Borders.Count() - 1;
    } else {
        border_id = bookPart.WorkbookStylesPart.Stylesheet.Borders.ToList().IndexOf(same_border);
    }