how to insert values into cells in excel programatically C#

Question

Hi there

Im trying to pass 8 string values in to a row in an excel spreadsheet and then be able to select that row all programmatically using C#. I am using a Windows Service with a file system watcher that is constantly monitoring a directory for a file to be added once the file is added it needs to extract the file contents in to object properties and then write them to an excel sheet. Would anyone have any idea how to do this without having to pay for any additional excel extensions? Any help would be much appreciated.

Thanks

Jignesh Trivedi · Answer

hi Please refer below link it might help you. http://www.codeproject.com/Articles/33850/Generate-Excel-files-without-using-Microsoft-Excel also OpenOffice provide the API for same. refer http://wiki.openoffice.org/wiki/API hope this will help you.

Bernard Sheridan · Answer

Hi Jignesh Thanks for the reply I am sure this way would work also but I actually found a solution and I used the following code to write to a specific cell in an already existing excel sheet: string path = @C:\ProjectTesting\TwsDde.xlsm; 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(Basic Orders); Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange; mWSheet1.Cells[12 1] = bla bla bla; This code was taken from this article which also goes on to save the excel sheet: http://rmanimaran.wordpress.com/2011/02/15/programmatically-insert-to-existing-excel-file-using-c/ I hope this helps somebody else. Thanks Bernard

darjeam nh · Answer

Try this method to set Excel value in C# please check full article here - Edit Excel Data in C# VB.NET //Load Workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(@fileName); //Edit Cell Value sheet.Range[E2].Value = xx; sheet.Range[E2].Style.Font.FontName = Book Antiqua; sheet.Range[E2].Style.Font.Color = Color.DarkOrange;

Bernard Sheridan · Answer

Thanks for your reply. Very good points something I hadn't thought about at the time. Much appreciated.

Megan Foster · Answer

Bernard just a heads up when you are working with Excel Interop in C# you must not forget that it's COM based which is out of the scope of managed environment. So in short don't forget to release ever used COM object: mWorkBook.Close(); oXL.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(mWSheet1); System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); Otherwise your excel application will still be running in the background. Note that you can avoid this COM hassle by using some managed (C#) library for excel . For example here is how: string path = @C:\ProjectTesting\TwsDde.xlsm ; var mWorkBook = ExcelFile .Load(path); var mWSheet1 = mWorkBook.Worksheets[ Basic Orders ]; mWSheet1.Cells[12 1].Value = bla bla bla ;

Jignesh Trivedi · Answer

hi Bernard that Good.. Thanks for sharing.. :)