dotnet core × Closed XML × 人口推計のExcel情報を取得するサンプルを作ってみた

目的

Closed XMLをDotnet Coreで利用する方法をまとめる。
簡単な表データから値を抽出する実装サンプルを作成する。

Closed XML

dotnet coreがサポートされているかどうか

Closed XMLによると、.NET Standard 2.0以降がサポートされている。.NET Core 3.1は、.NET 実装のサポートから、.NET Standard 2.0であるため、サポート内である。

InstallからHelloworldまで

コンソールアプリケーションのサンプルを作成してから、Closed XMLのRead Meに従って、サンプルエクセルを出力してみる。

class Program
{
    static void Main(string[] args)
    {
        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("Sample Sheet");
            worksheet.Cell("A1").Value = "Hello World!";
            worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
            workbook.SaveAs("HelloWorld.xlsx");
        }
    }
}

できた。

表からデータを取り出す。

サンプルのゴールについて

2019念の人口推計のサイトから、調査結果をExcel形式でDLして利用する。各年代ごとの男女の人口を読みとり、コンソール上に表示することをゴールとする。

コードとやったこと

ソースコード格納場所

class Program
{
    static void Main(string[] args)
    {
        var filePath = @".\source.xlsx";// args[0];
        if (File.Exists(filePath))
        {
            new Program().Run(filePath);
        }
    }
    class PopulationByAge
    {
        public int Age { get; set; }
        public int NumberOfMale { get; set; }
        public int NumberOfFemale { get; set; }
    }
    void Run(string filePath)
    {
        using (var workbook = new XLWorkbook(filePath))
        {
            IXLWorksheet worksheet = null;
            workbook.Worksheets.TryGetWorksheet("第１表", out worksheet);
            var result = this.GetAllDataFromWorksheet(worksheet);
            Console.WriteLine("Age,Male,Female");
            foreach (var item in result)
            {
                Console.WriteLine($"{item.Age},{item.NumberOfMale},{item.NumberOfFemale}");
            }
        }
    }
    List<PopulationByAge> GetAllDataFromWorksheet(IXLWorksheet worksheet)
    {
        var result = new List<PopulationByAge>();

        var headerCells = worksheet.Cells();

        //Headerの列を特定する。
        var ageColumn = GetSpecificHeaderColumn(headerCells, "Age");
        var maleColumn = GetSpecificHeaderColumn(headerCells, "Male");
        var femaleColumn = GetSpecificHeaderColumn(headerCells, "Female");

        //表のBody部の設定をする。
        var lastRowNumber = worksheet.LastRowUsed().Cell(ageColumn.ColumnNumber).Address.RowNumber;
        var lastColNumber = worksheet.LastColumnUsed().Cell(ageColumn.RowNumber).Address.ColumnNumber;
        var bodyCells = worksheet.Range($"A1:{ToColumnName(lastColNumber)}{lastRowNumber}").Cells();

        //Headerの列毎に、情報を取得する。
        var ageColumns = bodyCells.Where(i => i.Address.ColumnNumber == ageColumn.ColumnNumber).Select(i => i);
        var maleColumns = bodyCells.Where(i => i.Address.ColumnNumber == maleColumn.ColumnNumber).Select(i => i);
        var femaleColumns = bodyCells.Where(i => i.Address.ColumnNumber == femaleColumn.ColumnNumber).Select(i => i);

        //必要な情報だけをPOCOに設定していく。
        //主キーは、年齢列とする。
        foreach (var age in ageColumns)
        {
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("^[0-9]");
            if (regex.IsMatch(age.Value.ToString().Trim()) == false) continue;
            var excelRow = age.Address.RowNumber;
            result.Add(new PopulationByAge()
            {
                Age = int.Parse(age.Value.ToString().Trim().Split(" ").First()),
                NumberOfFemale = maleColumns.Where(i => i.Address.RowNumber == excelRow)
                                     .Select(i => int.Parse(i.Value.ToString().Trim().Replace(",", string.Empty)))
                                     .FirstOrDefault(),
                NumberOfMale = femaleColumns.Where(i => i.Address.RowNumber == excelRow)
                                          .Select(i => int.Parse(i.Value.ToString().Trim().Replace(",", string.Empty)))
                                          .FirstOrDefault()
            });
        }
        return result;
    }
    IXLAddress GetSpecificHeaderColumn(IXLCells cells, string headerString)
    {
        return cells.Where(i => string.Equals(i.Value.ToString().Trim(), headerString, StringComparison.OrdinalIgnoreCase))
                    .Select(i => i.Address).FirstOrDefault();
    }
    //https://qiita.com/omochi_motimoti/items/b6261e3cacda11d460dd#%E6%95%B0%E5%80%A4--%E3%82%AB%E3%83%A9%E3%83%A0%E6%96%87%E5%AD%97 を参照。
    string ToColumnName(int source)
    {
        var numberOfAlphabet = 26;
        if (source < 1) return string.Empty;
        return ToColumnName((source - 1) / numberOfAlphabet) + (char)('A' + ((source - 1) % numberOfAlphabet));
    }
}

今回は、年齢列を主キーとしてデータを取得した。男性、女性の人数についてNullとなる項目がなかったため、そのチェック処理は入れていない。しかし、空白項目も含まれるような場合は、直接数字に変換できないため、適宜バリデーションを追加していく必要がある。
また、ヘッダーで使われる名前については、同じものが使われることはないという前提で実装している。今回は、本当は、同じ列名（例：総人口のMaleと、日本人人口のMaleなど）が複数存在したが、総人口だけをターゲットとしているため、日本人人口の方のデータは無視した。
処理の順番を、ヘッダーの特定 → 列の特定 → 情報の読み取りとすることで、ヘッダーの位置が変更になったり、新しい列が追加されたときでも動作ができるようにできたつもり。改善点としては、ヘッダー名の指定方法を固定名から動的なものに変換することと、ＰＯＣＯに値を設定するときも動的に設定できるようにすること。セルの値を読み取るときの変数の型、不要な空白の除去に関するオプションを追加すること。