Export SQL Server tables to Excel in c#

This piece of code executes a sql query on a sql server database and returns the result into a excel workbook (with column headings included).

Remember to add the following references (Project > Add Reference > COM)

Microsoft Excel 12.0 Object Library (or an older version)

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using System.Data.SqlClient;

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExportDBTablesToExcel
{
 class ExportToExcel
 {

     private Excel.Application app ;

     private Excel.Workbook workbook;
     private Excel.Worksheet previousWorksheet;

     private Excel.Range workSheet_range;

     private string folder;

     private static string CONNECTION_STR = "Data Source=(local);Database=DATABASE_NAME;"
                                                      + "Integrated Security=SSPI;";


     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 e)
         {
             Console.Write(e.ToString());

         }
         finally
         {
         }
     }

     public void shutDown()

     {
         try
         {
             workbook = null;

             app.Quit();
         }
         catch (Exception e)

         {
             Console.Write(e.ToString());
         }

         finally
         {
         }
     }

     public void ExportTable(string query,string sheetName)

     {
         SqlConnection myConnection = new SqlConnection(CONNECTION_STR);

         SqlDataReader myReader = null;

         try
         {

             Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

             worksheet.Name = sheetName;

             previousWorksheet = worksheet;



             myConnection.Open();

         
             SqlCommand myCommand = new SqlCommand(query,
                                                      myConnection);

             myReader = myCommand.ExecuteReader();

             int columnCount = myReader.FieldCount;

             for (int n = 0; n < 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 < 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 && !myReader.IsClosed)

             {
                 myReader.Close();
             }

             if (myConnection != null)

             {
                 myConnection.Close();
             }

             myReader = null;
             myConnection = 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());

         }
     }


     static void Main(string[] args)

     {

         ExportToExcel export = new ExportToExcel(args[0]);

         export.ExportTable("SELECT * FROM t_table1","t_table1");
         export.ExportTable("SELECT * FROM t_table2","t_table2");
         export.ExportTable("SELECT * FROM t_table3","t_table3");
         export.SaveWorkbook() ;

         export.shutDown();
     }





 }

}

This code is based on the following links:

http://www.codeproject.com/KB/cs/Excel_and_C_.aspx
and
http://www.codeproject.com/KB/database/sql_in_csharp.aspx?fid=16002&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26&select=1490011

Read an Excel document and Send an email from outlook in c# .net

This is a handy example if you need to read an excel document and/or send an email from outlook in some c# code.

Remember to add the following references (Project > Add Reference > COM)

Microsoft Excel 12.0 Object Library
Microsoft Office 12.0 Object Library
Microsoft Outlook 12.0 Object Library

if you have older versions of office , they should work too.

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

using Outlook = Microsoft.Office.Interop.Outlook;
using Microsoft.Office.Core;

namespace StatusNotifier
{
  class StatusNotifier
  {
      static void Main(string[] args)

      {
          string Path = @"D:\MyExcelFile.xls";
          // initialize the Excel Application class

          Excel.ApplicationClass app = new Excel.ApplicationClass();

          // create the workbook object by opening the excel file.
          Excel.Workbook workBook = app.Workbooks.Open(Path,
                                                       0,
                                                       true,
                                                       5,
                                                       "",
                                                       "",
                                                       true,
                                                       Excel.XlPlatform.xlWindows,
                                                       "\t",
                                                       false,
                                                       false,
                                                       0,
                                                       true,
                                                       1,
                                                       0);

          // get the active worksheet using sheet name or active sheet
          Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

          int index = 0;
          // This row,column index should be changed as per your need.
          // i.e. which cell in the excel you are interesting to read.
          object rowIndex = 2;

        
          object colA = 1;
          object colB = 2 ;

        
          StringBuilder message = new StringBuilder();

          message.AppendLine("My Message");

          message.AppendLine("--------------");
          message.AppendLine("");

 
  //----------------------------

  //READ EXCEL WORKBOOK
  //------------------------------
 
          try
          {
              while (((Excel.Range)workSheet.Cells[rowIndex, colDateRaised]).Value2 != null)

              {
                  rowIndex = 2 + index;
                  DateTime dateRaised = DateTime.FromOADate(double.Parse(((Excel.Range)workSheet.Cells[rowIndex, colA]).Value2.ToString()));

                  string description = ((Excel.Range)workSheet.Cells[rowIndex, colB]).Value2.ToString();

              

                  message.AppendLine(rowIndex + " DateRaised :" + dateRaised.ToShortDateString() + "Description: " + description) ;

                    
               
                  index++;
              }
          }
          catch (Exception ex)

          {
              app.Quit();
              Console.WriteLine(ex.Message);

          }

          message.AppendLine("For more inforamtion please see:");
          message.AppendLine(Path);

          Console.WriteLine("Message:\n " + message);

  //----------------------------
  //CREATE OUTLOOK MESSAGE
  //------------------------------
 
          try
          {
              // Create the Outlook application by using inline initialization.

              Outlook.Application oApp = new Outlook.Application();

              //Create the new message by using the simplest approach.
              Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);

              //Add a recipient.
              // TODO: Change the following recipient where appropriate.
              Outlook.Recipient oRecip = (Outlook.Recipient)oMsg.Recipients.Add("someone@somewhere.com");

              oRecip.Resolve();

              //Set the basic properties.
              oMsg.Subject = "Status Update";

              oMsg.Body = message.ToString();

              //Add an attachment.

              // TODO: change file path where appropriate
              String sSource = "C:\\setupxlg.txt";
              String sDisplayName = "MyFirstAttachment";

              int iPosition = (int)oMsg.Body.Length + 1;

              int iAttachType = (int)Outlook.OlAttachmentType.olByValue;

              Outlook.Attachment oAttach = oMsg.Attachments.Add(sSource, iAttachType, iPosition, sDisplayName);

              // If you want to, display the message.
              // oMsg.Display(true);  //modal

              //Send the message.
              oMsg.Save();

              oMsg.Send();

              //Explicitly release objects.
              oRecip = null;

              oAttach = null;
              oMsg = null;
              oApp = null;

          }

              // Simple error handler.
          catch (Exception e)
          {

              Console.WriteLine("{0} Exception caught: ", e);
          }



      }
  }
}

This code is based upon examples from the following links:

Excel: http://www.codeproject.com/KB/cs/Excel_Application_in_C_.aspx
Outlook: http://support.microsoft.com/kb/310263