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

Creating Excel file using Excel COM object


Hi,

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;
#endregion

try
{   // 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
finally
{
//Quit Excel and thoroughly deallocate everything
oAppln.Quit();
//Release Com Objects
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAppln);
//Clearing all loaded memory variables
oRange = null;
oCellRange = null;
oWorkBook = null;
oWorkSheet = null;
oAppln = null;
System.GC.Collect();

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

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);
}
else
{
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;
}
else
{
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;
}
}
}

Advertisements
  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: