Export database to excel using MS Excel

//Add Reference (COM) Microsoft Office List 12.0
//Add Reference (.NET) Microsoft.Office.Interop.Excel 12.0
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflexion;
using System.IO;
using System.Diagnostics;
using System.Data.OleDb;

try
{
Excel.Application xlsApp = new Excel.Application(); //Create XLS
xlsApp.DisplayAlerts = false; //Block Alerts
Excel.Worksheet xlsSheet; //Declare Sheet
Excel.Workbook xlsBook; //Declare Workbook
xlsApp.Visible = false; //Turn off the aplicación
xlsBook = xlsApp.Workbooks.Add(true); //Add Workbook
xlsSheet = (Excel.Worksheet)xlsBook.ActiveSheet; //Add Sheet

//TABLE HEADER
for (irow = 0; irow < 1; irow++)
{
for (icol = 1; icol < Lista.Tables[0].Columns.Count; icol++)
{
xlsSheet.Cells[1, icol] = Lista.Tables[0].Columns[icol].ColumnName;
MessageBox.Show(Lista.Tables[0].Rows[irow].ItemArray[icol].ToString());
}
}


//TABLE ROWS
for (irow = 1; irow < Lista.Tables[0].Rows.Count; irow++)
{
for (icol = 1; icol < Lista.Tables[0].Columns.Count; icol++)
{
xlsSheet.Cells[irow + 1, icol] = Lista.Tables[0].Rows[irow - 1].ItemArray[icol - 1].ToString();
MessageBox.Show(Lista.Tables[0].Rows[irow].ItemArray[icol].ToString());
}
}

xlsSheet.Columns.AutoFit(); //Resize columns
xlsApp.Visible = true;
try
{
xlsApp.SaveWorkspace(); //Save XLS
}
catch
{ }
xlsApp.Quit();

MessageBox.Show("Insert Terminado");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

Export database to excel without MS Excel

try
{
if (File.Exists(@"c:\exportado.xls"))
File.Delete(@"c:\exportado.xls");

using(System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\exportado.xls';Extended Properties='Excel 8.0;HDR=Yes'"))
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("", con))
{
cmd.Connection = con;
con.Open();

string sql = "CREATE TABLE [Precios] ( columna1 VARCHAR(100) )";
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

//sql = "UPDATE [Precios$A1:A1] SET columna1 = '12345678'";
sql = "INSERT INTO [Precios] (columna1) VALUES('12345678')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

con.Close();
}

MessageBox.Show("Insert Terminado");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

Creating temporal table, inserting and pivoting

--CREATING TEMPORAL TABLE
DECLARE @Tabla TABLE (
FName VARCHAR(25),
School VARCHAR(15),
Team VARCHAR(15),
Points INT
)

--INSERTING VALUES
INSERT INTO @Tabla VALUES ('Pablo','OLOF','MEN',5)
INSERT INTO @Tabla VALUES ('Pedro','OLOF','MEN',5)
INSERT INTO @Tabla VALUES ('Ruby','OLOF','WOMEN',5)
INSERT INTO @Tabla VALUES ('Ruby','OLOF','WOMEN',5)
INSERT INTO @Tabla VALUES ('Paco','AMAT','WOMEN',5)

--VIEW
SELECT * FROM @Tabla

--PIVOTING VALUES
SELECT School,[MEN],[WOMEN]
FROM (SELECT School,Team,Points FROM @Tabla) As T
PIVOT (SUM(Points)
FOR Team IN ([MEN],[WOMEN])) As PivotT

SELECT FName As FullName,School,[MEN],[WOMEN]
FROM (SELECT Fname,School,Team,Points FROM @Tabla) As T
PIVOT (SUM(Points)
FOR Team IN ([MEN],[WOMEN])) AS PivotT