Archive

Posts Tagged ‘Excel Automation’

Possible ways: Excel Automation

April 5, 2011 Leave a comment

Hi,

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:

READ:

  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)

Write:

  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
Advertisements

Creating Excel file using Excel COM object

June 10, 2009 Leave a comment

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;
}
}
}