c# create and write to excel file

Following my previous post c# how to read write excel spreedsheet and C# quit excel appliction after using COM objectI wanted to continue the topic with a focus on write to excel spreedsheet.

Using Microsoft.Office.Interop.Excel is the easiest option and with Excel 2007 installed, you can get version 12 of this dll. It allows user to save files as Excel 2007 format, or the old 97-03 format.

Firstly, Let’s look at some sample code.

        private static void NAR( object o )
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject( o );
            }
            catch( Exception ex )
            {
                Console.WriteLine( ex.ToString( ) );
            }
            finally
            {
                o = null;
            }
        }

        public override void save( FileWriter writer )
        {
            string filename = writer.FileName;

            TimeSeries[] series = checkCompatibleTimeSeries( (Data[])data.ToArray( typeof( Data ) ) );
            int count = series[0].itemCount( );
            if( count == 0 )
                throw new DataFileIOException( this, "Time series to save to " + filename + " are empty" );

            // Instantiate Excel and start a new workbook.
            Excel.Application objApp;
            Excel._Workbook objBook;
            Excel.Workbooks objBooks;
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;

            objApp = new Excel.Application( );
            objBooks = objApp.Workbooks;
            objBook = (Excel._Workbook)( objBooks.Add( Missing.Value ) );
            objSheets = (Excel.Sheets)objBook.Worksheets;
            objSheet = (Excel._Worksheet)( objSheets.get_Item( 1 ) );

            if( objSheet == null )
            {
                throw new Exception( "Worksheet could not be created. Check that your office installation and project references are correct." );
            }

            try
            {
                ( (Excel.Range) objSheet.Cells[ 1, 1 ] ).set_Value( Missing.Value, "Date" );
                ( (Excel.Range) objSheet.Cells[ 1, 2 ] ).set_Value( Missing.Value, filename );


                for( int rowIndex = 0; rowIndex < count; rowIndex++ )
                {
                    ( (Excel.Range) objSheet.Cells[ rowIndex + 2, 1 ] ).set_Value( Missing.Value,
                                                                                   series[ 0 ].timeForItem( rowIndex ) );

                    for( int columnIndex = 0; columnIndex < data.Count; columnIndex++ )
                    {
                        ( (Excel.Range) objSheet.Cells[ rowIndex + 2, columnIndex + 2 ] ).set_Value( Missing.Value, 
                                                                                                     series[ columnIndex
                                                                                                         ][ rowIndex ] );
                    }
                }
                objApp.DisplayAlerts = false;
                objApp.AlertBeforeOverwriting = false;
                objBook.SaveAs( filename, format , Missing.Value, Missing.Value, Missing.Value, Missing.Value,                              Excel.XlSaveAsAccessMode.xlShared, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value,                               Missing.Value, Missing.Value );

                objBooks.Close( );
                objApp.Quit( );
            }
            catch( Exception theException )
            {
                throw theException;
            }
            finally
            {

                if( objBooks != null )
                    objBooks.Close( );
                if( objApp != null )
                    objApp.Quit( );
                NAR( objSheet );
                NAR( objSheets );
                NAR( objBooks );
                NAR( objBook );
                NAR( objApp );
            }

            GC.Collect( );
            GC.WaitForPendingFinalizers( );    

        }

Some tricky part of the code is the SaveAs Method. In order to disable the pop up alert messagebox, you need to do few things. (I believe most people do want to disable it since this must have been handled in the GUI part). Excel.XlSaveAsAccessMode.xlShared is the essential key to solve this problem. If it is not used, then  Excel.XlSaveConflictResolution.xlLocalSessionChanges wouldn’t work. Both “objApp.DisplayAlerts = false;” and “ objApp.AlertBeforeOverwriting = false; ” are used in the Excel dll Version 11 and will be overwrite by the XlSaveConflictResolution.

The code is fairly straight forward, the please refer to my old post on how i clean up the resources before quit.