Posts Tagged 'Excel Automation'

Export To Excel

This is a pretty simple console application which I wrote for exporting query results into excel. We prepare a lot of reports which we send it as excel files. It was getting tedious for us to copy the results and then paste it in excel. So Ron asked me to create a utility to export the query results directly into Excel. I used the Office 2007 excel interop. Given below is the code for the utility:

using System;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace ExportToExcel
{
    class Program
    {
        //Usage:
        //ExportToExcel -c, ConnectionString,-q, Query, -d, Directory (exclude the last \),-f FileName (add .xls)
        static void Main(string[] args)
        {
            Console.WriteLine(“Initializing Variables…..”);
            ApplicationClass objExcel = new ApplicationClass();
            Workbook objWorkbook;
            Worksheet objWorksheet;
            DirectoryInfo objDirectoryInfo;
            try
            {
                if (args != null)
                {
                    object objMissingValue = Missing.Value;
                    if (args.Length != 8)
                    {
                        Console.WriteLine(“\nIncorrect number of arguments. (-c connectionstring -q query -d directory -f filename)”);
                        return;
                    }
                   
                    string ConnectionString = “”;
                    string Query = “”;
                    string Directory = “”;
                    string Filename = “”;

                    for (int counter = 0; counter <= args.Length - 1; counter++)                     {                         switch (args[counter])                         {                             case "-c":                                 ConnectionString = args[counter + 1];                                 break;                             case "-q":                                 Query = args[counter + 1];                                 break;                             case "-d":                                 Directory = args[counter + 1];                                 break;                             case "-f":                                 Filename = args[counter + 1];                                 break;                             default:                                 break;                         }                     }                     Console.WriteLine("Initializing Excel.....");                     objWorkbook = objExcel.Workbooks.Add(objMissingValue);                     objWorksheet = (Worksheet)objWorkbook.Worksheets.Add(objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     objWorksheet.Name = "Query Data";                     Console.WriteLine("Opening Data Connection.....");                     SqlConnection objConn = new SqlConnection(ConnectionString);                     objConn.Open();                     SqlCommand objCommand = new SqlCommand(Query, objConn);                     SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);                     DataSet objDataset = new DataSet();                     Console.WriteLine("Filling Dataset.....");                     objAdapter.Fill(objDataset);                     System.Data.DataTable objDatatable;                     objDatatable = objDataset.Tables[0];                     int ColCounter = 0;                     int RowCounter = 0;                     int Column, Row;                     Row = 1;                     Column = 1;                     Console.WriteLine("Setting Header Information.....");                     //Settting the header                     while (ColCounter <= objDatatable.Columns.Count - 1)                     {                         objWorksheet.Cells[Row, Column] = objDatatable.Columns[ColCounter].ColumnName;                         Column++;                         ColCounter++;                     }                     Console.WriteLine("Generating Data.....");                     //Generating the data                                Row++;                     while (RowCounter <= objDatatable.Rows.Count - 1)                     {                         Column = 1;                         ColCounter = 0;                         while (ColCounter <= objDatatable.Columns.Count - 1)                         {                             objWorksheet.Cells[Row, Column] = objDatatable.Rows[RowCounter][ColCounter].ToString();                             Column++;                             ColCounter++;                         }                         Row++;                         RowCounter++;                     }                     Console.WriteLine("Checking for Directory Existence and Saving file.....");                     objDirectoryInfo = new DirectoryInfo(Directory);                     //Updated on 05/08/2009 by Raja                     //Checking for the existence of Directory before saving the file since Excel is not automatically creating the file                     //Starts here                     if (objDirectoryInfo.Exists) //all is well 🙂                         objWorkbook.SaveAs(Directory + "\\" + Filename, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue, XlSaveAsAccessMode.xlNoChange, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     else                     {                         //oops....create the directory and then save the file                         objDirectoryInfo.Create();                         objWorkbook.SaveAs(Directory + "\\" + Filename, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue, XlSaveAsAccessMode.xlNoChange, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     }                     //Ends here                     objWorkbook.Close(false, objMissingValue, objMissingValue);                     Console.WriteLine("Successfully exported the data to the given file");                                    }             }             catch (Exception ex)             {                 objExcel.Quit();                 objWorksheet = null;                 objWorkbook = null;                 objExcel = null;                 objDirectoryInfo = null;                 Console.WriteLine(ex.Message);             }                     }             } } [/sourcecode] As always I would appreciate your feedback. Hope this helps. Happy Programming!!! Cheers, Raja