Home > Programming > Editing Excel Sheets using Microsoft.Office.Interop.Excel in C#

Editing Excel Sheets using Microsoft.Office.Interop.Excel in C#

I found my self repetitively entering data into an excel sheet and needed to automate it using C#.

First add a reference of Microsoft.Office.Interop.Excel to your project.

To open a work book


using Microsoft.Office.Interop.Excel;
...
...
var m_TemplateFileName = "/path/to/excel/file/to/edit.xls";
var m_InputWorkSheetName = "input";
var application = new ApplicationClass();
var workBook = application.Workbooks.Open(m_TemplateFileName);
...
...


After opening the work book, you will need to get access to the excel sheet in the work book


...
...
var inputWorkSheet = workBook.Worksheets.Cast<WorkSheet>().Where(w => w.Name == m_InputWorkSheetName).FirstOrDefault();
...
...

In order to be able to modify cell values, I added an extension method to the Range Class.


public static class Extensions
{
public static void SetValue(this Range range, String cellAddress, object value)
{
var cell = range.get_Range(cellAddress);
Object[] args1 = new Object[1];
args1[0] = value;
cell.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, cell, args1);
}

}

to set cell values


...
...
inputWorkSheet.Cells.SetValue("B6",1000);
...
...

Finally I needed to save the workbook to another file


...
...
workBook.SaveAs(@"d:\workbook.xls", XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
...
...
// finally close workbook
workBook.Close();
...
...

Hope this helps someone.

Advertisements
Categories: Programming Tags: , ,
  1. Herbert Buletwenda
    November 8, 2010 at 3:17 pm

    Great post
    its really helpfull. I have been using another .Net package for handling office excel convertion http://excelpackage.codeplex.com/, check it out too it looks great.

  2. Joseph Ssenyange
    November 10, 2010 at 4:21 am

    Checked it out and it seems good though it seems to deal with only the 2007 format.

  3. Bess
    September 7, 2012 at 7:06 am

    Is it possible to edit the same file and save it in the same location

  4. September 20, 2013 at 8:54 am

    There is another .NET Excel API that can edit excel files in c# and .net known as Aspose.Cells for .NET and they also provide code examples for developers so that they can use that in their APIs.

  5. February 24, 2014 at 6:16 am

    check this one…lot of c# excelexamples

    http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

    c# tutorial

  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: