まずはセルデータから - その3

  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

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

namespace OpenXMLSDK_Sample1 {
    class Program {
        static void Main(string[] args) {
            const string FILE = @"C:\Users\Public\Documents\Book1.xlsx";
            const string SHEET_NAME = "Sheet1";
            const string CELL_ADDRESS = "A6";
            const int VALUE = 123456;

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

                //行の特定
                uint rowIndex = GetRowIndex(CELL_ADDRESS);

                Row row = sheet_part.Worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
                if (row == null) {
                    //行がない場合は行を新規作成する
                    row = new Row() { RowIndex = new UInt32Value(rowIndex) };

                    SheetData sh_data = sheet_part.Worksheet.Descendants<SheetData>().First();
                    //場所を特定して行を挿入(行は行番号で昇順に並ばないとだめ)
                    Row rRow = sh_data.Elements<Row>().LastOrDefault(r => r.RowIndex < rowIndex);

                    if (rRow == null) {
                        sh_data.InsertAt(row, 0);
                    } else {
                        sh_data.InsertAfter(row, rRow);
                    }
                }

                uint colIndex = GetColumnIndex(CELL_ADDRESS);
                //セルを特定
                Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == CELL_ADDRESS);
                if (cell == null) {
                    cell = new Cell() { CellReference = new StringValue(CELL_ADDRESS) };

                    //cellは昇順に書き込む必要がある
                    Cell rCell = row.Elements<Cell>().LastOrDefault(c => GetColumnIndex(c.CellReference) < colIndex);
                    if (rCell == null) {
                        row.InsertAt(cell, 0);
                    } else {
                        row.InsertAfter(cell, rCell);
                    }
                }

                //値をセット(セットする値が文字列ではない場合)
                cell.CellValue = new CellValue(VALUE.ToString());

                //保存
                sheet_part.Worksheet.Save();
            }

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

        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://learn.microsoft.com/ja-jp/office/open-xml/spreadsheet/how-to-get-a-column-heading-in-a-spreadsheet
        // 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);
        }
        
        //https://learn.microsoft.com/ja-jp/office/open-xml/spreadsheet/how-to-get-a-column-heading-in-a-spreadsheet
        public static string GetColumnName(string cellName) {
            Match match = Regex.Match(cellName, "[A-Za-z]+");
            return match.Value;
        }
    }
}