Posted by: R Manimaran | February 15, 2011

Programmatically Insert to Existing Excel File using C#

Programmatically Insert to Existing Excel File using C#

I got a requirement to do an insertion of data in to an excel file. I have used Microsoft.Office.Interop.Excel to do it using C#. When I search I can able to find lot of examples in the internet stating how to insert data. But they are all creating new Excel file or Sheet. But my requirement is, if the file already exists we need to make use of that.

And also, if the sheet name I am specifying already exists then it should use the same sheet.(i..e I need to insert data at the end).

Here is the code which will do the insertion in an already exists excel file. It may be useful for someone.

private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
private static Microsoft.Office.Interop.Excel.Application oXL;
public static void ReadExistingExcel()
{
string path = @"C:\Tool\Reports1.xls";
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount= range.Rows.Count;
for (int index = 1; index < 15; index++)
{
mWSheet1.Cells[rowCount + index, 1] = rowCount +index;
mWSheet1.Cells[rowCount + index, 2] = "New Item"+index;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}

If you need to create a new Sheet use the following code.

oSheet = (Excel.Worksheet)oWB.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

oSheet.Name = SheetName;

 

About these ads

Responses

  1. Great post!
    But the problem is that the process excel.exe still remains in memory.
    So I changed your code to:
    Workbooks workbooks = oXL.Workbooks;
    mWorkBook = workbooks.Open(path, 0, false, 5, “”, “”, false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, “”, true, false, 0, true, false, false);
    according to the post http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c

    So never use 2 dots with COM objects=)

  2. Really Thanks

  3. Can this be modified to work with items in a SharePoint document library?

    I’d love to see this in action.

  4. Hi Mani,
    Many Thanks for your Code.. Its really helpful to me..

  5. Thank u MARAN… This Code worked for me.. Gr8 post

  6. Thanks you very much…you are the savior

  7. thanks Very Much

  8. It was very useful, thanks)

  9. awesome!

  10. Thanks
    For Your awesome Help

  11. thank you so much

  12. Wonderful work! This is the kind of info that should
    be shared across the net. Shame on Google for no longer positioning this post higher!
    Come on over and talk over with my website .
    Thank you =)

  13. Heya! I’m at work surfing around your blog from my new iphone!
    Just wahted to say I love reading through your blog and look
    forward to all your posts! Carry on the outstanding work!


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

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: