📈

SharePoint で ClosedXML を使って Excel へのエクスポートを実装する

2022/01/01に公開

SharePoint

tech

はじめに

SharePoint の標準機能でもリストのデータを Excel にエクスポートできるのですが、プログラムからエクスポートしたいことがあります。今回は ClosedXML を使ってリストのデータを Excel にエクスポートしてみます。

サンプルコード

実行手順

Elements/Elements.xml

既存の Excel にエクスポート のコマンドを上書きします。今回は RegistrationId を 104 にしたので、お知らせリストに適用されます。クリックすると Layouts/ExportToSpreadsheet/Export.aspx にアクセスするようにコマンドを定義します。

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <CustomAction
        Id="ExportToSpreadsheetAction"
        RegistrationId="104"
        RegistrationType="List"
        Location="CommandUI.Ribbon"
        Sequence="20"
        Title="Excel にエクスポート">
        <CommandUIExtension>
            <CommandUIDefinitions>
                <CommandUIDefinition Location="Ribbon.List.Actions.ExportToSpreadsheet">
                    <Button
                        Id="Ribbon.List.Actions.ExportToSpreadsheet"
                        Alt="Excel にエクスポート"
                        Sequence="20"
                        Command="ExportToSpreadsheetCommand"
                        Image32by32="/_layouts/15/images/XLS32.GIF"
                        Image16by16="/_layouts/15/images/XLS16.GIF"
                        LabelText="Excel にエクスポート"
                        TemplateAlias="o1" />
                </CommandUIDefinition>
            </CommandUIDefinitions>
            <CommandUIHandlers>
                <CommandUIHandler
                  Command="ExportToSpreadsheetCommand"
                  CommandAction="/_layouts/ExportToSpreadsheet/Export.aspx?ListId={ListId}" />
            </CommandUIHandlers>
        </CommandUIExtension>
    </CustomAction>
</Elements>

Layouts/ExportToSpreadsheet/Export.aspx.cs

Page_Load イベントで、GET パラメーターで受け取ったリスト ID からアイテムの一覧を Excel ファイルに書き出します。
ここでちょっとコツなのですが、お知らせリストの本文は複数行テキストで定義されており、内部的には HTML で保存されているので、HTML を解析して書式を指定してあげる必要があります。そこで簡単なヘルパークラスを作成します。

public partial class Export : LayoutsPageBase
{

    protected void Page_Load(object sender, EventArgs e)
    {
        var listId = this.Request.QueryString["ListId"];
        using (var web = SPContext.Current.Web)
        {
            var list = web.Lists[new Guid(listId)];
            using (var workbook = new XLWorkbook())
            using (var stream = new MemoryStream())
            {
                var worksheet = workbook.Worksheets.Add("Sheet1");
                for (var index = 0; index < list.Items.Count; index++)
                {
                    var item = list.Items[index];
                    worksheet.Cell(index + 1, 1).SetValue(item["ID"]);
                    worksheet.Cell(index + 1, 2).SetValue(item["Title"]);
                    worksheet.Cell(index + 1, 3).SetHtmlValue(item["Body"]);
                }
                workbook.SaveAs(stream);
                this.Response.Clear();
                this.Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
                this.Response.AppendHeader("Content-Disposition", "attachment; filename=text.xlsx");
                this.Response.BinaryWrite(stream.ToArray());
                this.Response.End();
            }
        }
    }
}

Extensions/XLCellExtension

ClosedXML では RichText を Substring して書式を設定できるので、HTML を解析して書式を設定します。とりあえず、文字色と強調だけ実装しましたが、その他の書式も同様に実装できます。

public static class XLCellExtension
{

    public static IXLCell SetHtmlValue(this IXLCell target, string html)
    {
        var reader = new SgmlReader();
        reader.DocType = "HTML";
        reader.WhitespaceHandling = WhitespaceHandling.None;
        reader.InputStream = new StringReader(html);
        var xmlRoot = new XmlDocument();
        xmlRoot.Load(reader);
        target.SetValue(xmlRoot.InnerText);
        var rootText = xmlRoot.InnerXml;
        var spanIndex = 0;
        foreach (var item in xmlRoot.GetElementsByTagName("span").Cast<XmlElement>())
        {
            var itemText = item.OuterXml;
            var rawStart = rootText.IndexOf(itemText, spanIndex);
            var rawLength = itemText.Length;
            var trimStart = Regex.Replace(rootText.Substring(0, rawStart), "<.+?>", "").Length;
            var trimLength = item.InnerText.Length;
            var richText = target.RichText.Substring(trimStart, trimLength);
            richText.SetCssStyle(item);
            spanIndex = rawStart + rawLength;
        }
        var strongIndex = 0;
        foreach (var item in xmlRoot.GetElementsByTagName("strong").Cast<XmlElement>())
        {
            var itemText = item.OuterXml;
            var rawStart = rootText.IndexOf(itemText, strongIndex);
            var rawLength = itemText.Length;
            var trimStart = Regex.Replace(rootText.Substring(0, rawStart), "<.+?>", "").Length;
            var trimLength = item.InnerText.Length;
            var richText = target.RichText.Substring(trimStart, trimLength);
            richText.SetBold();
            richText.SetCssStyle(item);
            strongIndex = rawStart + rawLength;
        }
        return target;
    }

    public static void SetCssStyle<T>(this IXLFormattedText<T> richText, XmlElement element)
    {
        var xmlStyle = element.GetAttribute("style");
        if (string.IsNullOrEmpty(xmlStyle) != true)
        {
            var cssStyles = xmlStyle.Split(';').Select(str =>
            {
                var pair = str.Split(':');
                pair[0] = pair[0].Trim();
                pair[1] = pair[1].Trim();
                return Tuple.Create(pair[0], pair[1]);
            });
            var cssColor = cssStyles.FirstOrDefault(pair => pair.Item1 == "color");
            if (cssColor != null)
            {
                richText.SetFontColor(XLColor.FromHtml(cssColor.Item2));
            }
        }
    }
}

実行結果

お知らせリストを表示してみると Excel にエクスポート のコマンドが上書きされています。クリックすると Excel ファイルをダウンロードします。