protected void Button5_Click(object sender, System.EventArgs e)
{
  //Connessione server        
  const string stCon = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=XX\\MYSQL2008;Initial Catalog=Northwind";
  //query
  const string stSQL = "SELECT * FROM ORDERS";

  //creo istanza excel 
  Excel.Application xlApp = default(Excel.Application);


  try {
    xlApp = Marshal.GetActiveObject("Excel.Application");

  } catch (COMException ex) {
    xlApp = new Excel.Application();
  }

  //variabili cartella,foglio,range
  Excel.Workbook xlWBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
  Excel.Worksheet xlWSheet = (Excel.Worksheet)xlWBook.Worksheets(1);
  Excel.Range xlRange = ((Excel.Worksheet)xlWSheet).Range("B2");

  //memoria pivot
  Excel.PivotCache ptCache = xlWBook.PivotCaches.Add(SourceType: Excel.XlPivotTableSourceType.xlExternal);

  //comando per pivot      
  var _with1 = ptCache;
  _with1.Connection = stCon;
  _with1.CommandText = stSQL;
  _with1.CommandType = Excel.XlCmdType.xlCmdSql;

  //set pivot
  Excel.PivotTable ptTable = xlWSheet.PivotTables.Add(PivotCache: ptCache, TableDestination: xlRange, TableName: "PT_Report");


  //creo la pivot (riga,colonna,valore,...ecc)
  var _with2 = ptTable;
  _with2.ManualUpdate = true;

  _with2.PivotFields("ShipCity").Orientation = Excel.XlPivotFieldOrientation.xlRowField;

  _with2.PivotFields("ShipCountry").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

  _with2.PivotFields("Freight").orientation = Excel.XlPivotFieldOrientation.xlDataField;

  _with2.Format(Excel.XlPivotFormatType.xlTable3);
  _with2.ManualUpdate = false;

  //salvo il file xls
  xlWBook.SaveAs("c:\\Tmp\\Report.xls");

  //apro il file
  var _with3 = xlApp;
  _with3.Visible = true;
  _with3.UserControl = true;

  //pulisco
  ptTable = null;
  ptCache = null;
  xlWSheet = null;
  xlWBook = null;
  xlApp = null;

  ResponseFile("c:\\Tmp\\Report.xls");
}
private void ResponseFile(string filename)
{
  Response.Clear();

  dynamic cd = new ContentDisposition();
  cd.FileName = "Report.xls";
  // questo dovrebbe aprire il programma automaticamente se ci riesce, ma forse con excel non funziona, per i PDF è così che si fa per farglieli aprire nel browser, però almeno explorer se ne accorge e ti fa un bel popup grande chiedentoti conferma se vuoi aprirlo con excel
  cd.Inline = true;
  Response.AddHeader("Content-Disposition", cd.ToString());

  Response.ContentType = "application/excel";

  Response.WriteFile(filename);

  Response.End();
}