Home > MS Office Automation > Creating Excel file using Excel COM object

Creating Excel file using Excel COM object


Recently for one of my project I required to create a excel file on fly (dynamically). Its a typical excel automation kind of work. So after bit google and RnD I built some nice solution which I am trying to pen down in this article.

Normally in case of excel automation people create TAB delimited records which they either convert/transfer/export into/as excelsheet. But very rarely some one tries to use actual excel object (COM objects I mean to say). The main reason behind this is, normally MS-Office is not installed on the servers and people generally donot want to mess-up with the COM components. Also you will get very minimum support on this on google.

Anyways below is the code that will help to create a excelsheet:

  • Add reference to Excel assembly or COM (Microsoft.Office.Interop.Excel)as below:

Drive Name:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office11\Microsoft.Office.Interop.Excel.dll

  • Add the namespace to the class: using Microsoft.Office.Interop.Excel;

public string BuildTranslationExcelSheet()
#region Declaring variables

//declaring the application

Microsoft.Office.Interop.Excel.Application oAppln= new Microsoft.Office.Interop.Excel.Application();

/*if (oAppln == null)
oAppln = new Microsoft.Office.Interop.Excel.Application();*/
//declaring work book
Microsoft.Office.Interop.Excel.Workbook oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true));
//declaring worksheet
Microsoft.Office.Interop.Excel.Worksheet oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet;
//declaring the range
Microsoft.Office.Interop.Excel.Range oRange;
Microsoft.Office.Interop.Excel.Range oCellRange = null;
Microsoft.Office.Interop.Excel.Range oHeadRange;

{   // Set Initial values of data variables here
int startingRow = 2;
int totalRows = ExcelDataTable.Rows.Count;
int totalCol = ExcelDataTable.Columns.Count + 1;
int startColumn = 1;
if (totalRows > 0)
DefineTranslationExcelColumnNames(ref oWorkSheet, ref oCellRange, ref startingRow, ref totalRows);
ExcelHelper.ApplyExcelHeaderRangeAndStyles(out oHeadRange, ref oWorkSheet, ref startingRow, ref startColumn, ref totalCol);
InsertDataTableRecordsInExcel(ref oWorkSheet, ref oCellRange);
ApplyFormatingToExcelCells(out oCellRange, ref oWorkSheet);
ExcelHelper.ConfigureWorkSheet(out oRange, ref oWorkSheet, ref totalRows, ref totalCol, ref startColumn);
oWorkBook.SaveAs(ExcelExportPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
null, null, null, null, null);
catch (Exception ex)
{ }

#region Finally block
//Quit Excel and thoroughly deallocate everything
//Release Com Objects
//Clearing all loaded memory variables
oRange = null;
oCellRange = null;
oWorkBook = null;
oWorkSheet = null;
oAppln = null;

System.Diagnostics.Process[] myProcesses;
// Returns array containing all instances of EXCEL.
myProcesses = System.Diagnostics.Process.GetProcessesByName(“EXCEL”);
foreach (System.Diagnostics.Process myProcess in myProcesses)

return ExcelExportPath;

private void ApplyFormatingToExcelCells(out Range oCellRange, ref Worksheet oWorkSheet)
int totalRows = ExcelDataTable.Rows.Count + 2;
int startRow = 3;
oCellRange = null;
// for every column in the datatable apply the cell formating
for (int colIndex = 0; colIndex <= ExcelDataTable.Columns.Count – 1; colIndex++)
//if DataColumncolumn is ID then apply narrow formating, else normal formatting
if (ExcelDataTable.Columns[colIndex].ColumnName.Contains(“ID”))
ExcelHelper.ApplyFormattingOnExcelCells(out oCellRange, ref oWorkSheet, startRow, totalRows, colIndex + 1, BusinessEntity.CommonEnums.ExcelCellType.Narrow);
ExcelHelper.ApplyFormattingOnExcelCells(out oCellRange, ref oWorkSheet, startRow, totalRows, colIndex + 1, BusinessEntity.CommonEnums.ExcelCellType.Medium);

private void InsertDataTableRecordsInExcel(ref Worksheet oWorkSheet, ref Range oCellRange)
int cellRowIndex = 0;
// Dump the data from dataTable into the Excel cells
for (int colIndex= 0; colIndex <= ExcelDataTable.Columns.Count – 1; colIndex++)
for (int rowIndex = 0; rowIndex <= ExcelDataTable.Rows.Count – 1; rowIndex++)
{// assign datarow value for a column to excel cell
if (rowIndex == 0)
cellRowIndex = 3;
cellRowIndex = cellRowIndex + 1;
oWorkSheet.Cells[cellRowIndex, colIndex + 1] = ExcelDataTable.Rows[rowIndex][colIndex].ToString();

private void DefineTranslationExcelColumnNames(ref Microsoft.Office.Interop.Excel.Worksheet oWorkSheet, ref Range oCellRange, ref int startingRow, ref int totalRows)
int indexRow = startingRow + 1; // Get the start row for adding records
int lastColumnIndex = 0;

if (totalRows > 0)
for (int colIndex = 0; colIndex <= ExcelDataTable.Columns.Count – 1; colIndex++)
{   // Define the ColumnNames for the excelsheet
oWorkSheet.Cells[startingRow, colIndex + 1] = ExcelDataTable.Columns[colIndex].ColumnName;

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: