OpenXMLSDKを使って、Excelのシートに新しい列を挿入します。
プログラムの動作確認に、以下のようなワークブック「openxmlsdk20.xlsx」を準備します。
(前回の記事で準備した 「openxmlsdk19.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とほぼ同じ定義です。
ということで、列を挿入するコード本体も前回とそっくりになります。
コード
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;
}
/// <summary>
/// シート名、絶対参照$を除外したA1形式のセルアドレスを返す
/// </summary>
/// <param name="address">セル番地、あるいはセル範囲</param>
/// <returns></returns>
public static string GetCleanedAddress(string address) {
string cleanedReference = address;
//シート名は除外
if (cleanedReference.Contains('!')) {
cleanedReference = cleanedReference.Split('!').ElementAt(1);
}
//絶対参照$は削除
cleanedReference = cleanedReference.Replace("$", "");
return cleanedReference;
}
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}";
}
public static bool isOneCellAddress(string address) {
string cleanedReference = GetCleanedAddress(address);
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 = GetCleanedAddress(address);
//行番号、列番号のチェック
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 string GetColumnName(string address) {
Match match = Regex.Match(GetCleanedAddress(address), "[A-Za-z]+");
return match.Value;
}
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);
}
}
}
上記コードの150行目以降の部分は、以前の記事で紹介した部分です。今回の記事で新たに紹介する部分は148行目までの部分になります。
InsertColumnsメソッドも、前回の記事で定義したInsertRowsメソッドと基本的な考え方は同じです。
ただしOpenXMLSDKには「行を表すrow要素」に相当する「列を表すcolumn要素」が存在しないので注意が必要です。
85行目からの処理において、列を挿入した後(番地をずらした後)に書式を維持するためにセルを作りますが、書式のコピー元となるセルの、row要素における位置(insert_index_from)はrow要素によって異なるので、row要素内でのセルの順序が昇順になるように挿入場所に配慮する必要があります。
※昇順になっていないとExcelで開く際に「ファイルが破損している」扱いになります。
実行結果
コードを実行すると、準備したExcelファイルは以下のようになります。
次回からシートのページ設定(印刷設定)の操作に取り組みたいと思います。