OpenXMLSDKを使って、Excelのシートに新しい行を挿入します。
プログラムの動作確認に、以下のようなワークブック「openxmlsdk19.xlsx」を準備します。
H2:H6, C7:G7にはそれぞれSUM関数をセットしています。またセルH7には絶対参照を含む数式をセットしています。
解説
大まかな流れとしては、
- 挿入する場所に存在する行とその行以降の行・セルについて、その番地をずらす
- 新しい行を挿入する場所に追加する
- ブック内のセル番地への参照について、つじつまが合うようにそのアドレスをずらす
という処理を作りこんでいくことになります。
「3.ブック内のセル番地への参照について、つじつまが合うようにそのアドレスをずらす」という処理ですが、これはブック内で行の挿入の影響を受けるすべてのセル参照について行う必要があります。
「行の挿入の影響を受けるすべてのセル参照」といっても、一般的な(高度な機能を使っていない)シートではさほど多くないのでは？と思います。
さてその「つじつまが合うようにその参照をずらす」操作は、新しい行を挿入するという一点から考えれば、『セル範囲(またはセル)のアドレスの行番号に注目し、行の挿入の影響を受ける場合(すなわちセルのアドレスの行番号、あるいはセル範囲の行番号が新しい行を挿入する場所よりも後ろである場合)にセルの行番号を挿入する行数だけ増やし、あるいはセル範囲を拡大する』という処理を行うことになります。
この処理をShiftedRangeOnInsertRowメソッドとして定義しています(131行目～142行目)。
public static string ShiftedRangeOnInsertRow(string range, uint row_number_at, uint new_row_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 (GetRowIndex(range_element_address) >= row_number_at) {
range_shifted = range_shifted.Replace(range_element_address, getOffsetAddress(range_element_address, (int)new_row_count, 0));
}
}
return range_shifted;
}仮引数rangeには行番号をずらすことになるかもしれないセル番地またはセル範囲を渡します。row_number_atには行を挿入する場所(行番号)を渡します。new_row_countにはその場所に挿入する行数を渡します。
135行目～140行目ではMatchCollectionの後ろから置換(Replace)をするようにしています。これは置換した番地が、続く繰り返しで再び置換されてしまわないようにするための配慮です。
(たとえば仮引数rangeに"H3:H6"が、new_row_countが3がそれぞれ渡された場合、昇順で処理するとH3 -> H6, H6 -> H9 の順でreplaceすることになり、得られる結果(H9:H9)は期待したものになりません。セル範囲(range)は一般に「左上:右下」なので逆順で処理すればH9 -> H12, H3 -> H6 の順でreplaceすることになり、期待した通りのH6:H12を得ることになります。)
コード
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_Sample12{
class Program {
static void Main(string[] args) {
const string FILE = @"C:\Users\Public\Documents\openxmlsdk19.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);
InsertRows(sheet_part, 3, 1);
InsertRows(sheet_part, 5, 2);
InsertRows(sheet_part, 8, 3);
}
//ファイルを開く
System.Diagnostics.Process.Start(FILE);
}
public static void InsertRows(WorksheetPart ws_part, uint row_number_at, uint new_row_count = 1) {
if (row_number_at == 0) {
throw new Exception("row_number_at must be positive");
}
if (new_row_count <= 0) {
throw new Exception("new_row_count must be positive");
}
SheetData sheet_data_part = ws_part.Worksheet.GetFirstChild<SheetData>();
Regex regex = new Regex(@"\$?[A-Za-z]+\$?[1-9][0-9]*", RegexOptions.IgnoreCase);//セル番地にマッチ
/*** 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 (row_number_at <= GetRowIndex(last_cell_address)) {
dimension_modified.Reference = new StringValue(ShiftedRangeOnInsertRow(dimension_modified.Reference, row_number_at, new_row_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 = ShiftedRangeOnInsertRow(merge_cell.Reference, row_number_at, new_row_count);
}
}
/*** Shift Row and Cell Reference ***/
foreach (var row in sheet_data_part.Elements<Row>().Where(r => r.RowIndex >= row_number_at)) {
row.RowIndex+= new_row_count;
foreach (var cell in row.Elements<Cell>()) {
cell.CellReference = $"{GetColumnName(cell.CellReference)}{row.RowIndex}";
}
}
/*** 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 = ShiftedRangeOnInsertRow(formula, row_number_at, new_row_count);
}
/*** Shift Formula Reference Attribute ***/
string reference = cell.CellFormula.Reference;
if (!string.IsNullOrEmpty(reference)) {
cell.CellFormula.Reference = ShiftedRangeOnInsertRow(cell.CellFormula.Reference, row_number_at, new_row_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 = ShiftedRangeOnInsertRow(cell.CellReference, row_number_at, new_row_count);
}
}
/*** name definition ***/
/*** graph ***/
/*** pivot table & graph ***/
/*** Add new Cell and Copy cell style from the previous row ***/
var row_previous = sheet_data_part.Elements<Row>().FirstOrDefault(r => r.RowIndex == row_number_at - 1);
SheetDimension dimension = ws_part.Worksheet.GetFirstChild<SheetDimension>();
uint[] col_range;
if (row_previous == null) {
col_range = dimension.Reference.Value.Split(':').Select(s => GetColumnIndex(s)).ToArray();
} else {
col_range = row_previous.Spans.InnerText.Split(':').Select(s => Convert.ToUInt32(s)).ToArray();
}
for (uint i = 0; i < new_row_count; i++) {
var row_new = new Row();
if (row_previous != null) {
foreach (var attr in row_previous.GetAttributes()) {
row_new.SetAttribute(new OpenXmlAttribute() { LocalName = attr.LocalName, NamespaceUri = attr.NamespaceUri, Prefix = attr.Prefix, Value = attr.Value });
}
}
row_new.RowIndex = row_number_at + i;
for (uint j = col_range[0]; j <= col_range[1]; j++) {
Cell new_cell = new Cell() { CellReference = $"{GetColumnName(j)}{row_new.RowIndex}" };
if (row_previous != null) {
Cell previous_cell = GetCell(ws_part, $"{GetColumnName(j)}{row_number_at - 1}");
new_cell.StyleIndex = previous_cell.StyleIndex;
}
row_new.Append(new_cell);
}
sheet_data_part.InsertAt<Row>(row_new, (int)row_new.RowIndex.Value - 1);
}
}
public static string ShiftedRangeOnInsertRow(string range, uint row_number_at, uint new_row_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 (GetRowIndex(range_element_address) >= row_number_at) {
range_shifted = range_shifted.Replace(range_element_address, getOffsetAddress(range_element_address, (int)new_row_count, 0));
}
}
return range_shifted;
}
public static string getOffsetAddress(string source_cell_address, int biasRow, int biasColumn) {
if (string.IsNullOrEmpty(source_cell_address) || !isOneCellAddress(source_cell_address)) {
return string.Empty;
}
long offset_address_row = GetRowIndex(source_cell_address) + biasRow;
if (offset_address_row < 1) {
offset_address_row = 1;
}
if (offset_address_row > MAX_ROW_INDEX) {
offset_address_row = MAX_ROW_INDEX;
}
long offset_address_col = GetColumnIndex(source_cell_address) + biasColumn;
if (offset_address_col < 1) {
offset_address_col = 1;
}
if (offset_address_col > MAX_COL_INDEX) {
offset_address_col = MAX_COL_INDEX;
}
//absolute address
string col_name = GetColumnName((uint)offset_address_col);
if (source_cell_address.StartsWith("$")) {
col_name = $"${col_name}";
}
string row_number = offset_address_row.ToString();
if (source_cell_address.LastIndexOf('$') > 0) {
row_number = $"${row_number}";
}
//return GetColumnName((uint)offset_address_col) + offset_address_row;
return $"{col_name}{row_number}";
}
}
}
InsertRowsの定義において、
/*** Shift Row and Cell Reference ***/
foreach (var row in sheet_data_part.Elements>Row<().Where(r => r.RowIndex >= row_number_at)) {
row.RowIndex+= new_row_count;
foreach (var cell in row.Elements>Cell<()) {
cell.CellReference = $"{GetColumnName(cell.CellReference)}{row.RowIndex}";
}
}の部分が「1. 挿入する場所に存在する行とその行以降の行・セルについて、その番地をずらす」処理をしています。
「2. 新しい行を挿入する場所に追加する」にあたる処理は
/*** Add new Cell and Copy cell style from the previous row ***/
var row_previous = sheet_data_part.Elements>Row<().FirstOrDefault(r => r.RowIndex == row_number_at - 1);
SheetDimension dimension = ws_part.Worksheet.GetFirstChild>SheetDimension<();
uint[] col_range;
if (row_previous == null) {
col_range = dimension.Reference.Value.Split(':').Select(s => GetColumnIndex(s)).ToArray();
} else {
col_range = row_previous.Spans.InnerText.Split(':').Select(s => Convert.ToUInt32(s)).ToArray();
}
for (uint i = 0; i < new_row_count; i++) {
var row_new = new Row();
if (row_previous != null) {
foreach (var attr in row_previous.GetAttributes()) {
row_new.SetAttribute(new OpenXmlAttribute() { LocalName = attr.LocalName, NamespaceUri = attr.NamespaceUri, Prefix = attr.Prefix, Value = attr.Value });
}
}
row_new.RowIndex = row_number_at + i;
for (uint j = col_range[0]; j <= col_range[1]; j++) {
Cell new_cell = new Cell() { CellReference = $"{GetColumnName(j)}{row_new.RowIndex}" };
if (row_previous != null) {
Cell previous_cell = GetCell(ws_part, $"{GetColumnName(j)}{row_number_at - 1}");
new_cell.StyleIndex = previous_cell.StyleIndex;
}
row_new.Append(new_cell);
}
sheet_data_part.InsertAt<Row>(row_new, (int)row_new.RowIndex.Value - 1);
}
の部分になります。
挿入する場所(行)の一つ前(上)の行が存在する場合には、その一つ前の行(row_previous)とその行のセルの書式を新しい行にコピーして、行の挿入を行います。
40行目～51行目では行の挿入に伴って、xl/worksheets/sheet1.xmlのdimensionの定義
<x:dimension ref="B1:H7" />を変更します。(例えば新しい行を1行目から7行目までのどこかに2行挿入する場合はref="B1:H9"となるように変更します。)
同様に、54行目～59行目ではセルの結合に関する部分について、セルの結合についての定義を変更します。(例えば新しい行を2行目から6行目までのどこかに2行挿入する場合はref="B2:H8"となるように変更します。)
他にもセルにハイパーリンクが張られていたり、名前の定義がされていたり、グラフがあったり、...などがあれば、これらについても行の挿入に伴ってセルの参照部分を適切に編集する必要があります。 が、今回のコードでは面倒くさいのでこれらの部分を省略しています。
数式の参照
数式に含まれるセル参照も必要に応じてずらす必要があります(70行目～82行目)。
xl/worksheets/sheet1.xml をProductive Toolで見てみると、数式をオートフィルで入力した場合には、コピー元となるセルのref属性にそのコピー先セルの範囲がセットされていることが分かります。 78行目～81行目のコードでコピー先のセル範囲をずらします。
<x:c r="H4">
<x:f t="shared" ref="H4:H12" si="0">SUM(C4:G4)</x:f>
<x:v>10</x:v>
</x:c>それから、CalcChainパートについてもセル参照をずらします。(85行目～94行目)
CalcChainパートは数式の計算の順番を記しているパートです。Excelファイルを扱うアプリケーションによってはCalcChainパートを含んでいる場合にこれをヒントに計算処理の効率化を図ったりするそうです。
Microsoft Excelで作成した「数式を含むExcelファイル」にはCalcChainパートが作られるので、これについてもつじつまが合うようにセル参照をずらす必要があります。
<x:calcChain xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:c r="H13" i="1" l="1" />
<x:c r="H4" i="1" />
<x:c r="H7" i="1" />
<x:c r="H11" i="1" />
<x:c r="H12" i="1" />
<x:c r="H2" i="1" />
<x:c r="C13" i="1" />
<x:c r="D13" i="1" />
<x:c r="E13" i="1" />
<x:c r="F13" i="1" />
<x:c r="G13" i="1" />
</x:calcChain>CalcChainパートは必須ではなく、無くってもかまいません。それゆえ面倒くさければCalcChainパートを削除してしまうのも一つの手です。その場合は85行目～94行目を以下のように書き換えます。
WorkbookPart wb_part = ws_part.GetParentParts().ElementAt(0) as WorkbookPart;
CalculationChainPart calc_chain_part = wb_part.GetPartsOfType<CalculationChainPart>().FirstOrDefault();
if (calc_chain_part != null) {
wb_part.DeletePart(calc_chain_part);
}実行結果
コードを実行すると、以下のようなファイルになります。
次回は列の挿入に取り組みます。