lunes, 30 de enero de 2012

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());
}