excel 2010 - Creating a QueryTable in an Open XML Spreadsheet using C# -

Text after "div class =" itemprop = "text">

I want to help our users by adding a link to a number of ASMX webservices being generated and downloading a .XLSX The file contains a pre-configured and formatted query table for the web service.

They will then be able to build their own graphs and derivative reports as they currently do, but also be able to refresh the data without rebuilding everything. var machStore = isolated storagefile.getmachinestorphoraeassempton (); String filename = path.change extension (path.getRandomFileName (), ".xlsx"); (IsolatedStorageFileStream ifStream = new IsolatedStorageFileStream (using filename, FileMode.CreateNew, FileAccess.ReadWrite, FileShare.Read, machStore)) (spreadsheet document spreadsheet document = spreadsheet document .create (ifStream, SpreadsheetDocumentType.Workbook)) // Add Workbook Document. Workbookpart Workbook part = Spreadsheet document. Workbookpart.Workbook = new workbook (); // Add the worksheet part to the workbook. Worksheetparty WorksheetPart = WorkbookPage. Addiction & Lieutenant; Worksheet part & gt; (); Worksheet Worksheet = new worksheet (new sheet data); // Add Sheet to Workbook. Sheet Sheet = Spreadsheet Document. Workbookper Workbook Appadyl & lt; Sheets & gt; (New sheets ()); Connection Part connPart = Workbook. AddNewPart & lt; ConnectionsPart & gt; (); ConnPart.Connections = New Connection (); Connection c = new connection () {id = 1, // should be automated ?? Name = "list" type = 4, // web query RefreshedVersion = 4, MinRefreshableVersion = 1, background = false, savedata = true, RefreshOnLoad = true, WebQueryProperties = new WebQueryProperties () {XmlSource = true, SourceData = true Is ParsePreTag = true, constant = true, refreshedInExcel2000 = true, url = "http: //server/data/Demand.asmx/List"}}; ConnPart.Connections.Append (c); QueryTablePart qt = worksheetPart.AddNewPart & lt; QueryTablePart & gt; (); Qt.QueryTable = New Qualified () {name = "list", connection id = c. Id, automatic format iid = 16, // where from? ApplyNumberFormats = True, ApplyBorderFormats = True, ApplyFontFormats = True, ApplyPatternFormats = True, ApplyAlignmentFormats = false, ApplyWidthHeightFormats = false}; // Attach a new worksheet and add it to 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-type", "attachment; file name =" + filename); Response.ContentType = "application / vnd.openxmlformats-officedocument.spreadsheetml.sheet"; IfStream.CopyTo (response.OutputStream); }

When downloaded, the file opens in Excel 2010, but the query table does not appear as a sheet. The documentation for DocumentFormat.OpenXml does not explicitly tell me that the QueryTable class

You must add

  defined define names = new defined name (); // Create the DefinitionName DefinedName = New Defined Names () {name = "list", text = "mysheet! $ A $ 1: $ A $ 1"}; // Create a new range (name matching query query name) definedNames.Append (definedName); // Add it to the collection workbook. Workbook Append (defined name); // Add collection to the workbook   
 and remove the line  
  sheets.epand (qt.QueryTable);