新しい列の挿入

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

パート 20: この記事

OpenXMLSDKを使って、Excelのシートに新しい行を挿入します。

プログラムの動作確認に、以下のようなワークブック「openxmlsdk20.xlsx」を準備します。
(前回の記事で準備した「openxmlsdk19.xlsx」の表をコピーして「行列を入れ替えて貼り付け」して、罫線を調整すると簡単に作れます)

編集対象のワークブック: C:\Users\Public\Documents\openxmlsdk20.xlsx

G3:G7, B8:F8にはそれぞれSUM関数をセットしています。またセルG8には絶対参照を含む数式をセットしています。

解説
#

大まかな流れは、前回の記事で扱った「行」の挿入と同じ考え方です。

挿入する場所に存在する列とその列以降の列・セルについて、その番地をずらす
新しい列を、挿入する場所に追加する
ブック内のセル番地への参照について、つじつまが合うようにそのアドレスをずらす

「3.ブック内のセル番地への参照について、つじつまが合うようにそのアドレスをずらす」について、前回の記事ではセル参照を行方向にシフトするShiftedRangeOnInsertRowメソッドを定義しました。

今回紹介するコードにおいても同様に、セル参照を列方向にシフトするメソッド「ShiftedRangeOnInsertColumn」を定義します。

public static string ShiftedRangeOnInsertColumn(string range, uint column_index_at, uint new_column_count) {
    Regex regex = new Regex(@"\$?[A-Za-z]+\$?[1-9][0-9]*", RegexOptions.IgnoreCase);//セル番地にマッチ
    string range_shifted = range;
    MatchCollection mc = regex.Matches(range);
    for (int i = mc.Count - 1; i >= 0; i--) {
        string range_element_address = mc[i].ToString();
        if (GetColumnIndex(range_element_address) >= column_index_at) {
            range_shifted = range_shifted.Replace(range_element_address, getOffsetAddress(range_element_address, 0, (int)new_column_count));
        }
    }
    return range_shifted;
}

…ご覧のとおり、ShiftedRangeOnInsertRowとほぼ同じ定義です。

ということで、列を挿入するコード本体も前回とそっくりになります。

コード
#

  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


using System;
using System.IO;
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_Sample13_article {
    class Program {
        static void Main(string[] args) {
            const string FILE = @"C:\Users\Public\Documents\openxmlsdk20.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);

                InsertColumns(sheet_part, "C", 1);
                InsertColumns(sheet_part, "E", 2);
                InsertColumns(sheet_part, "H", 3);
            }
            //ファイルを開く
            System.Diagnostics.Process.Start(FILE);
        }

        /// <summary>
        /// Excel最大行数
        /// </summary>
        public const uint MAX_ROW_INDEX = 1048756;

        /// <summary>
        /// Excel最大列数
        /// </summary>
        public const uint MAX_COL_INDEX = 16384;


        public static void InsertColumns(WorksheetPart ws_part, string column_name, uint new_column_count = 1) {
            if (string.IsNullOrEmpty(column_name)) {
                throw new Exception("column_name should not be null or empty.");
            }

            if (new_column_count <= 0) {
                throw new Exception("new_row_count must be positive.");
            }

            uint column_index_at = GetColumnIndex(column_name);
            if (column_index_at == 0) {
                throw new Exception("column_name is not valid column/cell address.");
            }

            SheetData sheet_data_part = ws_part.Worksheet.GetFirstChild<SheetData>();

            /*** dimension  ***/
            SheetDimension dimension_modified = ws_part.Worksheet.GetFirstChild<SheetDimension>();
            if (isRangeAddress(dimension_modified.Reference)) {
                string last_cell_address = dimension_modified.Reference.Value.Split(':')[1];
                if (column_index_at <= GetColumnIndex(last_cell_address)) {
                    dimension_modified.Reference = new StringValue(ShiftedRangeOnInsertColumn(dimension_modified.Reference, column_index_at, new_column_count));
                } else {
                    return;
                }
            } else {
                return;
            }

            /*** Cell Merge ***/
            MergeCells mergeCells = ws_part.Worksheet.GetFirstChild<MergeCells>();
            if (mergeCells != null) {
                foreach (MergeCell merge_cell in mergeCells) {
                    merge_cell.Reference = ShiftedRangeOnInsertColumn(merge_cell.Reference, column_index_at, new_column_count);
                }
            }

            /*** Shift Column and Cell Reference ***/
            foreach (var row in sheet_data_part.Elements<Row>()) {

                foreach (var cell in row.Elements<Cell>().Where(c => GetColumnIndex(c.CellReference) >= column_index_at)) {
                    cell.CellReference = ShiftedRangeOnInsertColumn(cell.CellReference, column_index_at, new_column_count);
                }

                //書式を維持するためにセルを作る
                Cell cell_previous = row.Elements<Cell>().LastOrDefault(c => GetColumnIndex(c.CellReference.Value) < column_index_at);
                if (cell_previous != null) {
                    int insert_index_from = row.Elements<Cell>().ToList().IndexOf(cell_previous) + 1;
                    for (uint column_index = column_index_at; column_index < column_index_at + new_column_count; column_index++) {
                        Cell cell = new Cell() {
                            CellReference = $"{GetColumnName(column_index)}{row.RowIndex}",
                            StyleIndex = cell_previous.StyleIndex,
                            DataType = cell_previous.DataType
                        };

                        row.InsertAt(cell, insert_index_from);

                        insert_index_from++;
                    }
                }
            }
            /*** Shift Formula Reference ***/
            foreach (var cell in sheet_data_part.Descendants<Cell>().Where(c => c.CellFormula != null)) {
                /*** Shift Formula ***/
                string formula = cell.CellFormula.Text;
                if (!string.IsNullOrEmpty(formula)) {
                    cell.CellFormula.Text = ShiftedRangeOnInsertColumn(formula, column_index_at, new_column_count);
                }

                string reference = cell.CellFormula.Reference;
                if (!string.IsNullOrEmpty(reference)) {
                    cell.CellFormula.Reference = ShiftedRangeOnInsertColumn(cell.CellFormula.Reference, column_index_at, new_column_count);
                }
            }

            /*** CalcChain ***/
            WorkbookPart wb_part = ws_part.GetParentParts().ElementAt(0) as WorkbookPart;

            Sheets sheets = wb_part.RootElement.Elements<Sheets>().First();
            string sheet_part_id = wb_part.GetIdOfPart(ws_part);
            string sheet_id = (sheets.First(sh => (sh as Sheet).Id == sheet_part_id) as Sheet).SheetId;
            CalculationChainPart calc_chain_part = wb_part.GetPartsOfType<CalculationChainPart>().FirstOrDefault();
            if (calc_chain_part != null) {
                foreach (CalculationCell cell in calc_chain_part.CalculationChain.Elements<CalculationCell>().Where(c => c.SheetId == sheet_id)) {
                    cell.CellReference = ShiftedRangeOnInsertColumn(cell.CellReference, column_index_at, new_column_count);
                }

                //wb_part.DeletePart(calc_chain_part);
            }

            /*** name definition ***/
            /*** graph ***/
            /*** pivot table & graph ***/

        }

        public static string ShiftedRangeOnInsertColumn(string range, uint column_index_at, uint new_column_count) {
            Regex regex = new Regex(@"\$?[A-Za-z]+\$?[1-9][0-9]*", RegexOptions.IgnoreCase);//セル番地にマッチ
            string range_shifted = range;
            MatchCollection mc = regex.Matches(range);
            for (int i = mc.Count - 1; i >= 0; i--) {
                string range_element_address = mc[i].ToString();
                if (GetColumnIndex(range_element_address) >= column_index_at) {
                    range_shifted = range_shifted.Replace(range_element_address, getOffsetAddress(range_element_address, 0, (int)new_column_count));
                }
            }
            return range_shifted;
        }

        // 以下省略
    }
}

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

InsertColumnsメソッドも、前回の記事で定義したInsertRowsメソッドと基本的な考え方は同じです。

ただしOpenXMLSDKには「行を表すrow要素」に相当する「列を表すcolumn要素」が存在しないので注意が必要です。

85行目からの処理において、列を挿入した後(番地をずらした後)に書式を維持するためにセルを作りますが、書式のコピー元となるセルの、row要素における位置(insert_index_from)はrow要素によって異なるので、row要素内でのセルの順序が昇順になるように挿入場所に配慮する必要があります。

※昇順になっていないとExcelで開く際に「ファイルが破損している」扱いになります。