c# - 使用 C# 在 Open XML 电子表格中创建查询表

我有许多生成报告的 ASMX 网络服务，并希望通过添加一个链接来帮助我们的用户下载 .XLSX 文件，该文件包含网络服务的预配置和格式化的查询表。

然后，他们将能够像目前一样构建自己的图表和派生报告，而且还能够刷新数据而无需重新构建所有内容。

var machStore = IsolatedStorageFile.GetMachineStoreForAssembly();
string fileName = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx");

using (
    IsolatedStorageFileStream ifStream = new IsolatedStorageFileStream(
        fileName,
        FileMode.CreateNew,
        FileAccess.ReadWrite,
        FileShare.Read,
        machStore
    )
)
{
    using (
        SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(
            ifStream,
            SpreadsheetDocumentType.Workbook
        )
    )
    {
        // Add a WorkbookPart to the document.
        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        // Add a WorksheetPart to the WorkbookPart.
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        // Add Sheets to the Workbook.
        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        ConnectionsPart connPart = workbookpart.AddNewPart<ConnectionsPart>();
        connPart.Connections = new Connections();

        Connection c = new Connection(){
            Id = 1, //should be automatic??
            Name = "List",
            Type = 4, //Web Query
            RefreshedVersion = 4,
            MinRefreshableVersion= 1,
            Background = false,
            SaveData = true,
            RefreshOnLoad = true,
            WebQueryProperties = new WebQueryProperties()
            {
                XmlSource = true,
                SourceData = true,
                ParsePreTag = true,
                Consecutive = true,
                RefreshedInExcel2000 = true,
                Url = "http://server/data/Demand.asmx/List"
            }
        };
        connPart.Connections.Append(c);

        QueryTablePart qt = worksheetPart.AddNewPart<QueryTablePart>();
        qt.QueryTable = new QueryTable(){
            Name="List",
            ConnectionId = c.Id,
            AutoFormatId = 16,//From where?
            ApplyNumberFormats = true,
            ApplyBorderFormats = true,
            ApplyFontFormats = true,
            ApplyPatternFormats = true,
            ApplyAlignmentFormats = false,
            ApplyWidthHeightFormats = false
        };

        // Append a new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() {
            Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "mySheet"
        };
        sheets.Append(sheet);
        sheets.Append(qt.QueryTable);

        workbookpart.Workbook.Save();

        // Close the document.
        spreadsheetDocument.Close();
    }
    ifStream.Position = 0;
    response.Clear();
    response.AddHeader("content-disposition", "attachment; filename=" + fileName);
    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    ifStream.CopyTo(response.OutputStream);

}

下载后，文件会在 Excel 2010 中打开，但查询表不会显示为工作表。 DocumentFormat.OpenXml 的文档没有清楚地描述我应该如何处理 QueryTable 类的实例

最佳答案

你需要添加

DefinedNames definedNames = new DefinedNames();    //Create the collection
DefinedName definedName = new DefinedName()
    { Name = "List", Text="mysheet!$A$1:$A$1" };   // Create a new range (name matching the QueryTable name)
definedNames.Append(definedName);                  // Add it to the collection
workbookpart.Workbook.Append(definedNames);        // Add collection to the workbook

并删除该行

sheets.Append(qt.QueryTable);

关于c# - 使用 C# 在 Open XML 电子表格中创建查询表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17236839/