title: Creating a QueryTable in an Open XML Spreadsheet using C#
body: <p>I have a number of report-generating ASMX webservices and would like to help our users by adding a link to download a .XLSX file containing a pre-configured and formatted Query Table for the webservice.</p>
  
  <p>They will then be able to build their own graphs and derived reports as they currently do, but also be able to refresh the data without rebuilding everything.</p>
  
  <pre><code>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&lt;WorksheetPart&gt;();
          worksheetPart.Worksheet = new Worksheet(new SheetData());
  
          // Add Sheets to the Workbook.
          Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild&lt;Sheets&gt;(new Sheets());
  
          ConnectionsPart connPart = workbookpart.AddNewPart&lt;ConnectionsPart&gt;();
          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&lt;QueryTablePart&gt;();
          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);
  
  }
  </code></pre>
  
  <p>When downloaded, the file does open in Excel 2010, but the query table does not appear as a sheet. The documentation for <code>DocumentFormat.OpenXml</code> doesn't describe clearly how I should work with instances of the <code>QueryTable</code> class</p>