title: Export SQL to Excel
body: <p>How can i export my data from SQL server 2008 into Excel 2010 or later ?</p>
  
  <p>i have tried on SQL way:</p>
  
  <pre><code>sp_configure 'show advanced options', 0;
  GO
  RECONFIGURE;
  GO
  sp_configure 'Ad Hoc Distributed Queries', 0;
  GO
  RECONFIGURE;
  GO
  INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  'Excel 8.0;Database=C:\testing.xls;Extended Properties=EXCEL 12.0;HDR=YES', 
  'SELECT NO_ORDRE, Date FROM [Sheet1$]') 
  SELECT [NO_ORDRE], GETDATE() FROM ORDRE
  GO
  </code></pre>
  
  <p>Unfortuntely i receive error:
  The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' can not be used for distributed queries because the provider is configured to run in STA mode.</p>
  
  <p>and then i tried on C# way:</p>
  
  <pre><code> public class ExportToExcel
      {
          private Excel.Application app;
  
          private Excel.Workbook workbook;
          private Excel.Worksheet previousWorksheet;
         // private Excel.Range workSheet_range;
          private string folder;
  
          public ExportToExcel(string folder)
          {
  
              this.folder = folder;
              this.app = null;
              this.workbook = null;
              this.previousWorksheet = null;
             // this.workSheet_range = null;
  
              createDoc();
          }
  
          private void createDoc()
          {
              try
              {
                  app = new Excel.Application();
                  app.Visible = false;
                  workbook = app.Workbooks.Add(1);
              }
              catch (Exception excThrown)
              {
                  throw new Exception(excThrown.Message);
              }
              finally
              {
              }
          }
  
          public void shutDown()
          {
              try
              {
                  workbook = null;
                  app.Quit();
              }
              catch (Exception excThrown)
              {
                  throw new Exception(excThrown.Message);
              }
              finally
              {
              }
          }
  
          public void ExportTable(string query, string sheetName)
          {
              SqlDataReader myReader = null;
              try
              {
                  using (var connectionWrapper = new Connexion())
                  {
                      var connectedConnection = connectionWrapper.GetConnected();
                      Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);
  
                      worksheet.Name = sheetName;
                      previousWorksheet = worksheet;
  
                      SqlCommand myCommand = new SqlCommand(query, connectionWrapper.conn);
  
                      myReader = myCommand.ExecuteReader();
  
                      int columnCount = myReader.FieldCount;
  
                      for (int n = 0; n &lt; columnCount; n++)
                      {
                          //Console.Write(myReader.GetName(n) + "\t");
                          createHeaders(worksheet, 1, n + 1, myReader.GetName(n));
                      }
  
                      int rowCounter = 2;
                      while (myReader.Read())
                      {
                          for (int n = 0; n &lt; columnCount; n++)
                          {
                              //Console.WriteLine();
                              //Console.Write(myReader[myReader.GetName(n)].ToString() + "\t");
                              addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString());
                          }
                          rowCounter++;
                      }
  
                  }
              }
  
              catch (Exception e)
              {
                  Console.WriteLine(e.ToString());
  
              }
              finally
              {
                  if (myReader != null &amp;&amp; !myReader.IsClosed)
                  {
                      myReader.Close();
                  }   
                  myReader = null;
              }
  
          }
  
          public void createHeaders(Excel.Worksheet worksheet, int row, int col, string htext)
          {
              worksheet.Cells[row, col] = htext;
          }
  
          public void addData(Excel.Worksheet worksheet, int row, int col, string data)
          {
              worksheet.Cells[row, col] = data;
          }
  
          public void SaveWorkbook()
          {
  
              String folderPath = "C:\\My Files\\" + this.folder;
  
              if (!System.IO.Directory.Exists(folderPath))
              {
                  System.IO.Directory.CreateDirectory(folderPath);
              }
  
              string fileNameBase = "db";
              String fileName = fileNameBase;
              string ext = ".xlsx";
              int counter = 1;
  
              while (System.IO.File.Exists(folderPath + fileName + ext))
              {
                  fileName = fileNameBase + counter;
                  counter++;
              }
  
              fileName = fileName + ext;
  
              string filePath = folderPath + fileName;
  
              try
              {
                  workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
  
              }
              catch (Exception e)
              {
                  Console.WriteLine(e.ToString());
  
              }
          }
  
  
  
      }
  </code></pre>
  
  <p>unfortunately i got error:
  Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).</p>
  
  <p>Any idea how can i export SQL to Excel ?</p>