📌
AngleSharpとClosedXMLを使って表示されているTableの情報をExcel出力する
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