Excel Automation in C#

Generating reports in excel is very common. With C#'s help, you can almost do anything in excel so whether it's excel based chart for funnel report or any regular analysis report.

Today, we will see how to generate reports in excel using C#. In order to do this we will create a console based application.

  1. Create a new project with template as console application:

     

  2. Add following references:
    1. Microsoft.Office.Interop.Excel
    2. Microsoft.Vbe.Interop

  3. After adding references add following using clause:
    1. using Excel = Microsoft.Office.Interop.Excel;
    2. using VBIDE = Microsoft.Vbe.Interop;

     

CS Code for Main method:

 

static void Main(string[] args)

{

// object for Excel Application

Excel.Application xlApp;

// object for Excel workbook

Excel.Workbook xlWorkbook = null;

// object for Excel worksheet

Excel.Worksheet xlWorksheet = null;

// location and name of the file

string filename =@"C:\TestFile.xls";

// object for all missing values

object misingDefault = System.Reflection.Missing.Value;

 

xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

xlWorkbook = xlApp.Workbooks.Add(misingDefault);

// set this to false to run application in background

xlApp.Visible = true;

 

xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);

// selecting and putting data in cell

xlWorksheet.Cells[1, 1] = "Header1";

xlWorksheet.Cells[1, 2] = "Header2";

xlWorksheet.Cells[2, 1] = "Test 1";

xlWorksheet.Cells[2, 2] = "Test 2";

// selecting range of two columns and setting font as bold

xlWorksheet.get_Range("A1", "B1").Font.Bold = true;

// Saving workbook

xlWorkbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misingDefault, misingDefault

, misingDefault, misingDefault, Excel.XlSaveAsAccessMode.xlExclusive, misingDefault, misingDefault, misingDefault, misingDefault, misingDefault);

// Closing connection to workbook

xlWorkbook.Close(true, misingDefault, misingDefault);

// Quit application

xlApp.Quit();

try

{

// releasing object for worksheet

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);

}

catch (Exception ee)

{

Console.Write("Error: " + ee.Message.ToString());

}

finally

{

GC.Collect();

}

}

 

Screenshot:

 

Tags: ,

Console Application