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

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

パート 17: この記事

今回はセル範囲を対象に罫線を引くプログラムを作ります。

…といっても、OPEN XML SDKに「セル範囲を指定して罫線を引く」APIが(私が調べた限りで)存在しない様子なので、前回定義したAppendCellBorderをつかって「セル範囲を指定して罫線を引く」メソッドを定義します。

準備するファイルはExcelを操作して「新規作成」したまっさらなブック(C:\Users\Public\Documents\openxmlsdk17.xlsx)です。
(そういえばOpenXML SDKを使って新規ファイルを作成する方法についてはまだ取り上げていません…)

プログラムを実行すると以下のような罫線が引かれます。

  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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
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\openxmlsdk17.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 border = new Border();
                border.TopBorder = new TopBorder();
                border.RightBorder = new RightBorder();
                border.BottomBorder = new BottomBorder();
                border.LeftBorder = new LeftBorder();

                const uint box_size = 3;
                const uint box_count = 10;
                BorderStyleValues[] border_styles = new BorderStyleValues[] {
                    BorderStyleValues.Thin,
                    BorderStyleValues.Medium,
                    BorderStyleValues.Thick,
                    BorderStyleValues.Double
                };

                Color[] border_colors = new Color[] {
                    new Color() { Rgb = new HexBinaryValue("FFFF0000")}, //赤
                    new Color() { Rgb = new HexBinaryValue("FF00FF00") }, //青
                    new Color() { Rgb = new HexBinaryValue("FF0000FF") }, //緑
                    new Color() { Rgb = new HexBinaryValue("FF000000") } //黒
                };

                for (uint i = 1; i <= box_count; i++) {
                    BorderStyleValues bs = border_styles[(i - 1) % border_styles.Length];
                    border.TopBorder.Style = bs;
                    border.RightBorder.Style = bs;
                    border.BottomBorder.Style = bs;
                    border.LeftBorder.Style = bs;

                    Color bc = border_colors[(i - 1) % border_colors.Length];
                    border.TopBorder.Color = bc.Clone() as Color;
                    border.RightBorder.Color = bc.Clone() as Color;
                    border.BottomBorder.Color = bc.Clone() as Color;
                    border.LeftBorder.Color = bc.Clone() as Color;

                    string top_left_cell_address = GetColumnName(i) + i;
                    string bottom_right_cell_address= GetColumnName(i + box_size - 1) + (i + box_size - 1);

                    AppendRangeBorder(sheet_part, top_left_cell_address + ":" + bottom_right_cell_address, border);
                }
            }
            //ファイルを開く
            System.Diagnostics.Process.Start(FILE);
        }

        public const uint MAX_ROW_INDEX = 1048756;
        public const uint MAX_COL_INDEX = 16384;

        public enum Side {
            Top,
            Right,
            Bottom,
            Left
        }

        public static bool isOneCellAddress(string address) {
            string cleanedReference = address;

            //シート名は除外
            if (cleanedReference.Contains('!')) {
                if (cleanedReference.Split('!').ElementAt(0) == "") { //"!"で始まる(シート名の指定がない)
                    return false;
                }
                cleanedReference = cleanedReference.Split('!').ElementAt(1);
            }
            //絶対参照$は削除
            cleanedReference = cleanedReference.Replace("$", "");

            Regex regex = new Regex(@"\$?[A-Za-z]+[1-9][0-9]*", RegexOptions.IgnoreCase);//セル番地

            bool result_flag;
            result_flag = regex.IsMatch(cleanedReference);
            result_flag &= regex.Split(cleanedReference).Count() == 2;
            result_flag &= regex.Replace(cleanedReference, "") == "";
            return result_flag;
        }

        public static bool isRangeAddress(string address) {
            string cleanedReference = address;

            //シート名は除外
            if (cleanedReference.Contains('!')) {
                cleanedReference = cleanedReference.Split('!').ElementAt(1);
            }
            //絶対参照$は削除
            cleanedReference = cleanedReference.Replace("$", "");

            //行番号、列番号のチェック
            uint col_index = GetColumnIndex(cleanedReference);
            uint row_index = GetRowIndex(cleanedReference);

            Regex range_regex = new Regex(@"\$?[A-Za-z]+[1-9][0-9]*:\$?[A-Za-z]+[1-9][0-9]*", RegexOptions.IgnoreCase);

            bool result_flag;
            result_flag = range_regex.IsMatch(cleanedReference);
            result_flag &= range_regex.Split(cleanedReference).Count() == 2;
            result_flag &= range_regex.Replace(cleanedReference, "") == "";
            result_flag &= col_index <= MAX_COL_INDEX;
            result_flag &= row_index <= MAX_ROW_INDEX;
            return result_flag;
        }

        public static string GetColumnName(uint colindex) {
            if (colindex < 1 || MAX_COL_INDEX < colindex) {
                throw new Exception("column index is out of range.");
            }
            const string colChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; //colindex = 1 -> "A", 26 -> "Z"
            Stack<string> stack = new Stack<string> ();

            while (colindex > 0) {
                int mod;
                if (colindex <= 26) { //列番号が1桁
                    mod = (int)(colindex - 1);
                } else {
                    mod = (int)((colindex - 1) % 26);
                }

                stack.Push(colChars.Substring(mod, 1));
                colindex = (colindex - 1) / 26;
            }

            StringBuilder colname_builder = new StringBuilder();
            while (stack.Count > 0) {
                colname_builder.Append(stack.Pop());
            }
            return colname_builder.ToString();
        }

        public static IEnumerable<string> GetCellAddressesFromRange(string range_address, Side side) {
            string cleaned_address = range_address;
            if (range_address.Contains('!')) {
                cleaned_address = range_address.Split('!')[1];
            }

            if (isOneCellAddress(cleaned_address)) {
                yield return cleaned_address;
            } else if (isRangeAddress(cleaned_address)) {
                string topleft_address = cleaned_address.Split(':')[0];
                string rightbottom_address = cleaned_address.Split(':')[1];

                uint edge_row_index;
                uint edge_col_index;
                switch (side) {
                    case Side.Top:
                        edge_row_index = GetRowIndex(topleft_address);
                        for (uint colindex = GetColumnIndex(topleft_address); colindex <= GetColumnIndex(rightbottom_address); colindex++) {
                            yield return GetColumnName(colindex) + edge_row_index;
                        }
                        break;

                    case Side.Right:
                        edge_col_index = GetColumnIndex(rightbottom_address);
                        for (uint rowindex = GetRowIndex(topleft_address); rowindex <= GetRowIndex(rightbottom_address); rowindex++) {
                            yield return GetColumnName(edge_col_index) + rowindex;
                        }
                        break;

                    case Side.Bottom:
                        edge_row_index = GetRowIndex(rightbottom_address);
                        for (uint colindex = GetColumnIndex(topleft_address); colindex <= GetColumnIndex(rightbottom_address); colindex++) {
                            yield return GetColumnName(colindex) + edge_row_index;
                        }
                        break;

                    case Side.Left:
                        edge_col_index = GetColumnIndex(topleft_address);
                        for (uint rowindex = GetRowIndex(topleft_address); rowindex <= GetRowIndex(rightbottom_address); rowindex++) {
                            yield return GetColumnName(edge_col_index) + rowindex;
                        }
                        break;
                }
            }
        }

        public static void AppendRangeBorder(WorksheetPart target_ws_part, string range_address, Border border) {
            string cleaned_address = range_address;
            if (range_address.Contains('!')) {
                cleaned_address = range_address.Split('!')[1];
            }

            if (isOneCellAddress(cleaned_address)) {
                AppendCellBorder(target_ws_part, cleaned_address, border);
                return;
            }

            if (!isRangeAddress(cleaned_address)) {
                return;
            }

            if (border.TopBorder != null && border.TopBorder.HasAttributes) {
                Border applying_border = new Border() { TopBorder = border.TopBorder.CloneNode(true) as TopBorder };
                foreach (string cell_address in GetCellAddressesFromRange(cleaned_address, Side.Top)) {
                    AppendCellBorder(target_ws_part, cell_address, applying_border);
                }
            }

            if (border.RightBorder != null && border.RightBorder.HasAttributes) {
                Border applying_border = new Border() { RightBorder = border.RightBorder.CloneNode(true) as RightBorder };
                foreach (string cell_address in GetCellAddressesFromRange(cleaned_address, Side.Right)) {
                    AppendCellBorder(target_ws_part, cell_address, applying_border);
                }
            }

            if (border.BottomBorder != null && border.BottomBorder.HasAttributes) {
                Border applying_border = new Border() { BottomBorder = border.BottomBorder.CloneNode(true) as BottomBorder };
                foreach (string cell_address in GetCellAddressesFromRange(cleaned_address, Side.Bottom)) {
                    AppendCellBorder(target_ws_part, cell_address, applying_border);
                }
            }

            if (border.LeftBorder != null && border.LeftBorder.HasAttributes) {
                Border applying_border = new Border() { LeftBorder = border.LeftBorder.CloneNode(true) as LeftBorder };
                foreach (string cell_address in GetCellAddressesFromRange(cleaned_address, Side.Left)) {
                    AppendCellBorder(target_ws_part, cell_address, applying_border);
                }
            }
        }
        // 以下省略...
    }

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

いろいろと長くなりました。

76行目～96行目の「isOneCellAddress」は指定された文字列が単一のセル番地として評価できるかどうかを返します。
同様に98行目~121行目の「isRangeAddress」は指定された文字列がセル範囲として評価できるかどうかを返します。
この部分の説明は今回の本質ではないので割愛します。(123行目～147行目のGetColumNameも。)

前回、セル1つの番地を指定して罫線を引く「AppendCellBorder」を定義したので、これを使うことを考えます。

指定されたセル範囲から、セル範囲の「ふち」にあるセルをそれぞれ取得し、それらセルのひとつひとつに、前回の「AppendCellBorder」を使って「お手本となる(指定された)Borderオブジェクト」と同じ罫線をセットしていけば、目的が達成されることになります。