Posts Tagged ‘Excel Automation’

Possible ways: Excel Automation

April 5, 2011 Leave a comment


This post is outcome of love & relationship with excel automation and recent hit back with the same. I would like to praise the courage & efforts of colleagues who restlessly kept on working so that I can write this post 🙂

So starting with the post we’ll go to history from where this all started 🙂

As a aspiring software developer in mid-of  2006 I first came across this notorious requirement where I was required to read excelsheets from one location on the server process the data and then dump it into database. Believe me this requirement put into nodus situation. I had never that this kind of situation could ever arise and if then should not possible at all.

But when I did bit of goggle (I’m god follower from start of my carrier) then I came to know this is very common requirement and there are fair enough solutions (which actually work for all:) ). And that point when I had my first hate and love relationship with excel automation(hate=bcz I never thought of this, love = bcz many handy solutions are around).

Following are the possible approaches by which I had read and wrote to excel file:


  1. Microsoft OleDB driver:
    1. Jet OleDB for 32 bit applications
    2. ACE.OleDB for 64 bit applications
  2. Microsoft Office COM/ Excel Objects
  3. Stream objects
  4. Excel Services (applicable only for MOSS)


  1. CSV or delimiter while exporting data to client through HTTPHeaders of ASP.NET
  2. XML and XSLT while exporting data to client through HTTPHeaders of ASP.NET
  3. Microsoft Office COM/ Excel Objects
  4. Open Office SDK (applicable for MSOffice 2007 and above [.xlsx extensions])
  5. Microsoft OleDB driver with SQL queries that pushes data into excel

Creating Excel file using Excel COM object

June 10, 2009 Leave a comment


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;