📌

AngleSharpとClosedXMLを使って表示されているTableの情報をExcel出力する

2023/03/02に公開
ExcelOutput.razor
@using AngleSharp.Html.Dom;
@using AngleSharp.Html.Parser;
@inject IJSRuntime JS

<Button Type="primary" @onclick="OutPut">
    出力
</Button>
@code
{
    /// <summary>出力対象となるID属性</summary>
    [Parameter]
    public List<string> IdNames { get; set; }

    /// <summary>
    ///
    /// </summary>
    /// <remarks>
    /// 読み込むテーブルが大きすぎるため、Singal R のサイズを大きくしなければならない
    /// </remarks>
    /// <see cref="https://stackoverflow.com/questions/60311852/error-connection-disconnected-with-error-error-server-returned-an-error-on-cl"/>
    /// <see cref="https://qiita.com/nobu17/items/7465006ce24a4490183b#2javascript%E3%81%AE%E5%88%86%E9%9B%A2%E3%81%A8%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E5%8F%82%E7%85%A7">
    /// <see cref="https://qiita.com/Matter/items/9743cd793bdc1f2b9444">
    private async Task OutPut()
    {
        var module = await JS.InvokeAsync<IJSObjectReference>("import", "./Pages/Excels/ExcelOutput.razor.js");
        // 一度タブを開かないと描画されていない
        List<string> idNames = IdNames;
        List<Sheet> sheets = new List<Sheet>();
        // 取得したいTableを囲うDivのIdを外から指定する
        foreach (var idName in idNames)
        {
            var container = await module.InvokeAsync<string>("getHtmlContentId", idName);
            HtmlParser parser = new HtmlParser();
            IHtmlDocument doc = parser.ParseDocument(container);
            // HeaderとBodyでTableが分かれている場合を考慮する
            var tables = doc.GetElementsByTagName("Table");
            Sheet sheet = new Sheet();
            foreach (var table in tables)
            {
                var trs = table.GetElementsByTagName("tr");
                foreach (var tr in trs)
                {
                    var ths = tr.GetElementsByTagName("th");
                    Row row = new Row();
                    if (ths.Any())
                    {
                        //header
                        foreach (var td in ths)
                        {
                            Cell cell = new Cell();
                            var rowCell = (AngleSharp.Html.Dom.IHtmlTableCellElement)td;
                            cell.Text = td.TextContent.Trim();
                            cell.RowSpan = rowCell.RowSpan;
                            cell.ColumnSpan = rowCell.ColumnSpan;
                            var style = rowCell.GetAttribute("style");
                            var style2 = ExtractString(style, "text-align: ", ";");
                            if (style2 != null) cell.SetTextAlign(style2);
                            row.Data.Add(cell);
                        }
                        sheet.Header.Add(row);
                    }
                    else
                    {
                        //body
                        var tds = tr.GetElementsByTagName("td");
                        foreach (var td in tds)
                        {
                            Cell cell = new Cell();
                            var rowCell = (AngleSharp.Html.Dom.IHtmlTableCellElement)td;
                            cell.Text = rowCell.TextContent.Trim();
                            cell.RowSpan = rowCell.RowSpan;
                            cell.ColumnSpan = rowCell.ColumnSpan;
                            var style = rowCell.GetAttribute("style");
                            var style2 = ExtractString(style, "text-align: ", ";");
                            if (style2 != null) cell.SetTextAlign(style2);
                            row.Data.Add(cell);
                        }
                        sheet.Body.Add(row);
                    }
                }
                if (sheet.Body.Any() && sheet.Header.Any())
                {
                    sheet.Name = idName;
                    sheets.Add(sheet);
                    sheet = new Sheet();
                }
            }
        }
        ExcelCreator creator = new ExcelCreator();
        creator.Execute(sheets);
    }

    public static string? ExtractString(string? source, string start, string end)
    {
        if (source == null)
            return null;

        int startIndex = source.IndexOf(start);
        if (startIndex < 0)
            return null;

        startIndex += start.Length;

        int endIndex = source.IndexOf(end, startIndex);
        if (endIndex < 0)
            return null;

        return source.Substring(startIndex, endIndex - startIndex);
    }
}
ExcelOutput.razor.js
export function getHtmlContentId(id) {
    var element = document.getElementById(id);
    if (element !== null) {
        return element.innerHTML;
    } else {
        return null;
    }
}
ExcelCreator
using ClosedXML.Excel;

namespace EmptyBlazorApp1.Pages.Excels
{
    public class ExcelCreator
    {
        //やること
        //列幅の設定
        //文字の中央寄せ

        private const int StartRow = 1;
        private const int StartColumn = 2;

        public void Execute(List<Sheet> sheets)
        {
            // 新しいワークブックを作成する
            XLWorkbook workbook = new XLWorkbook();


            foreach (Sheet sheet in sheets) 
            {
                // ワークブックに新しいワークシートを作成する
                IXLWorksheet worksheet = workbook.AddWorksheet(sheet.Name);

                // テーブルデータをワークシートに書き込む
                int row = StartRow;
                int column = StartColumn;
                Dictionary<Tuple<int, int>, bool> IsData = new Dictionary<Tuple<int, int>, bool>();
                foreach (var headerRow in sheet.Header)
                {
                    foreach (var headerCell in headerRow.Data)
                    {
                        SetHeaderValue(row, column, IsData, worksheet, headerCell);
                    }
                    row++;
                }

                // データの先頭で表示の固定
                worksheet.SheetView.Freeze(row - 1, column - 1);

                foreach (var bodyRow in sheet.Body)
                {
                    int c = column;
                    foreach (var bodyCell in bodyRow.Data)
                    {
                        worksheet.Cell(row, c).SetValue(bodyCell.Text);
                        //worksheet.Cell(row, column).Style.Alignment.SetHorizontal(Convert(bodyCell.TextAlign));
                        worksheet.Cell(row, column).Style.Alignment.Horizontal = Convert(bodyCell.TextAlign);
                        c++;
                    }
                    row++;
                }

                // 罫線の設定(点線)
                worksheet.Range(1, 1, row, 1 + sheet.Body.First().Data.Count).Style
                        .Border.SetTopBorder(XLBorderStyleValues.Dotted)
                        .Border.SetBottomBorder(XLBorderStyleValues.Dotted)
                        .Border.SetLeftBorder(XLBorderStyleValues.Dotted)
                        .Border.SetRightBorder(XLBorderStyleValues.Dotted);

                // 列幅の調整
                // コンテンツが日本語の場合は日本語フォントにしておかないと AdjustToContents が正しく動作しない
                worksheet.Style.Font.FontName = "游ゴシック";
                worksheet.ColumnsUsed().AdjustToContents();
            }

            // ワークブックを保存する
            workbook.SaveAs("テーブルデータ.xlsx");
        }

        private void SetHeaderValue(int row, int column, Dictionary<Tuple<int, int>, bool> IsData, IXLWorksheet sheet, Cell cell)
        {
            if (IsData.ContainsKey(Tuple.Create(row, column)))
            {
                column++;
                SetHeaderValue(row, column, IsData, sheet, cell);
            }
            else
            {
                sheet.Cell(row, column).SetValue(cell.Text);
                sheet.Cell(row, column).Style.Alignment.Horizontal = Convert(cell.TextAlign);
                for (int r = 0; r < cell.RowSpan; r++)
                {
                    for (int c = 0; c < cell.ColumnSpan; c++)
                    {
                        IsData.Add(Tuple.Create(row + r, column + c), true);
                    }
                }
                sheet.Range(row, column, row + cell.RowSpan - 1, column + cell.ColumnSpan - 1).Merge();
            }
        }

        private XLAlignmentHorizontalValues Convert(TextAlign textAlign)
        {
            return textAlign switch
            {
                TextAlign.right => XLAlignmentHorizontalValues.Right,
                TextAlign.left => XLAlignmentHorizontalValues.Left,
                TextAlign.center => XLAlignmentHorizontalValues.Center,
                _ => XLAlignmentHorizontalValues.Center
            };
        }
    }
}

sheet
namespace EmptyBlazorApp1.Pages.Excels
{
    public class Sheet
    {
        public List<Row> Header = new List<Row>();
        public List<Row> Body = new List<Row>();
        public string Name = string.Empty;
    }

    public class Row
    {
        public List<Cell> Data = new List<Cell>();
    }
}
Cell
namespace EmptyBlazorApp1.Pages.Excels
{
    public class Cell
    {
        public int RowSpan;
        public int ColumnSpan;
        public string Text;
        public TextAlign TextAlign;
        
        public void SetTextAlign(string textAlign)
        {
            TextAlign = Enum.Parse<TextAlign>(textAlign);
        }
    }
    public enum TextAlign
    {
        left, right, center
    }
}

Discussion