読者です 読者をやめる 読者になる 読者になる

からめもぶろぐ。

ワタシ SharePoint チョット デキル

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

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

事前準備

ClosedXML、Open XML SDK および SgmlReader をダウンロードし、SharePoint サーバーの GAC に登録します。SgmlReader は HTML の解析に使います。

gacutil.exe /i ClosedXML.dll
gacutil.exe /i DocumentFormat.OpenXml.dll
gacutil.exe /i SgmlReader.dll

サンプル コード

github.com

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 を解析して書式を指定してあげる必要があります。そこで簡単なヘルパー クラスを作成します。

using ClosedXML.Excel;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Karamem0.Samples.SharePoint.ExportClosedXmlRibbon {

    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 を解析して書式を設定します。とりあえず、文字色と強調だけ実装しましたが、その他の書式も同様に実装できます。

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using System.Xml.Linq;
using Sgml;
using System.IO;
using System.Xml;

namespace Karamem0.Samples.SharePoint.ExportClosedXmlRibbon {

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

        private 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 ファイルをダウンロードします。

f:id:karamem0:20150617232552p:plain

Excel ファイルを開くとこんな感じになります。ちゃんと書式も設定されています。

f:id:karamem0:20150623110504p:plain

Gacutil.exe (グローバル アセンブリ キャッシュ ツール)
[SharePoint 2010 開発] カスタムの SharePoint リボンの作成 – Tsmatz
Default Server Ribbon Customization Locations
SharePoint: RegistrationId List Template Type IDs

(2015/06/23追記) コードにバグがあったので修正しました。
(2016/02/22追記) HTML の解析に SgmlReader を使うように修正しました。