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;
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=)
By: Andrey Myasnikoff on July 13, 2011
at 12:51 pm
Really Thanks
By: vinay on August 16, 2011
at 4:34 pm
Can this be modified to work with items in a SharePoint document library?
I’d love to see this in action.
By: Panoone on September 30, 2011
at 1:49 am
Hi Mani,
Many Thanks for your Code.. Its really helpful to me..
By: Srini on November 9, 2011
at 5:09 pm
Thank u MARAN… This Code worked for me.. Gr8 post
By: Tarun on December 6, 2011
at 6:11 am
Thanks you very much…you are the savior
By: Tanvi Jog on April 7, 2012
at 1:03 pm
thanks Very Much
By: Supun on August 13, 2012
at 8:12 am
It was very useful, thanks)
By: Grigorii on August 29, 2012
at 1:04 pm
awesome!
By: Valeriy on September 4, 2012
at 12:54 pm
Thanks
For Your awesome Help
By: Rohit Garg on November 24, 2012
at 1:05 pm
thank you so much
By: Erik on September 27, 2013
at 1:04 pm
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 =)
By: Belstaff Segunda Mano on March 28, 2014
at 11:12 am
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!
By: free date on April 15, 2014
at 3:24 am
you save my time! Thank you very much!
By: seydacaglar on May 28, 2015
at 11:58 pm
How can I use this method in windows 2008 server because I used this method in my local machine and it was working properly but when I published on server it did not work .
How can I solve this.
By: yetkinmehmet on August 6, 2015
at 6:10 am
For this to work you need to have Microsoft.Interop DLLs. Check Office is installed in your Windows server 2008. Else you need to use the Open XML if you don’t have Office.
By: R Manimaran on August 7, 2015
at 1:57 am