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.

c# detect excel install location in registry and open file

public void OpenInExcel(string filename)
        {
            string dir = "";
            RegistryKey key = Registry.LocalMachine;
            RegistryKey excelKey = key.OpenSubKey(@"SOFTWARE\MicroSoft\Office");
            if (excelKey != null)
            {
                foreach (string valuename in excelKey.GetSubKeyNames())
                {
                    int version = 9;
                    double currentVersion=0;
                    if (Double.TryParse(valuename, out currentVersion) && currentVersion >= version)
                    {
                        RegistryKey rootdir = excelKey.OpenSubKey(currentVersion + @".0\Excel\InstallRoot");
                        if (rootdir != null)
                        {
                            dir = rootdir.GetValue(rootdir.GetValueNames()[0]).ToString();
                            break;
                        }
                    }
                }                
            }
            if (dir != "")
            {
                ProcessStartInfo startInfo = new ProcessStartInfo();

                startInfo.FileName = dir + @"Excel.exe";
                startInfo.Arguments = "\"" + fileName + "\"";
                startInfo.UseShellExecute = false;

                using (Process process = new Process())
                {
                    process.StartInfo = startInfo;
                    try
                    {
                        process.Start();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("\n\nCould not start Excel process.");
                        Console.WriteLine(ex);
                    }
                }
            }
            else
            {
                MessageBox.Show("Can't Open in excel because excel is not installed.");
            }
        }     

c# copy excel data and paste into datagridview table

This post is about managing the memory data object after ctrl+c is used in Excel. You will learn how data are organised in memory and should be able to apply it anywhere. The code example tries to read the data in DataObject out and paste it in datagridview.            

DataObject o = (DataObject) Clipboard.GetDataObject(); 
           if (o.GetDataPresent(DataFormats.Text)) 
           { 
               int rowOfInterest = DataGridView.CurrentCell.RowIndex; 
               string[] selectedRows = Regex.Split(o.GetData(DataFormats.Text).ToString(  ).TrimEnd( "\r\n".ToCharArray() ), "\r\n"); 

               if (selectedRows == null || selectedRows.Length == 0) 
                   return; 

               foreach (string row in selectedRows) 
               {                    
                   if (rowOfInterest >= DataGridView.Rows.Count) 
                       break; 

                   try 
                   { 
                       string[] data = Regex.Split(row, "\t"); 
                       int col = DataGridView.CurrentCell.ColumnIndex; 

                       foreach (string ob in data) 
                       { 
                           if (col >= DataGridView.Columns.Count) 
                               break; 
                           if (ob!=null) 
                               DataGridView[col, rowOfInterest].Value = Convert.ChangeType( ob, DataGridView[col,rowOfInterest].ValueType ); 
                           col++; 
                       } 
                   } 
                   catch (Exception enterException) 
                   { 
                         //do something here 
                   } 
                   rowOfInterest++; 
               } 
           }

C# quit excel appliction after using COM object

Following my last post of how to read and write excel files in C#, which can be found here: http://www.idothink.com/2009/03/c-read-write-excel-spreedsheet.html

I discovered some serious bug. The Excel process didn’t quit sometimes and leaves in the tast manager. Some one suggest that kill the process, but it is a dangerous move.

After some research, I have come up with the following solution.

Firstly, I create the following method to release the comobject.

 

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

Secondly, I put the release call in the finally block, and try to release everything. The reason is that

“When Visual Studio .NET calls a COM object from managed code, it automatically creates a Runtime Callable Wrapper (RCW). The RCW marshals calls between the .NET application and the COM object. The RCW keeps a reference count on the COM object. Therefore, if all references have not been released on the RCW, the COM object does not quit.”

            finally
            {

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

After doing all these, the problem still wasn’t solved when I automate the whole process and try to have lots of Excel object open and close, few of them are still not release. I then use the very dangerous GC call.

 

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

Have Fun. Oh, BTW, the application is now significant slower :)

c# how to read write excel spreedsheet

用.net读写excel文件有很多种方法，这里介绍一种比较简单的:

使用Microsoft.Office.Interop.Excel

下面是一个我项目中的一个读取函数的删减版本，要用上using Excel = Microsoft.Office.Interop.Excel;

除了读取一个Range里的东西，下面的函数演示了怎么枚举所有的行列，这样就可以不用事先知道Range了。

private void LoadExcelFile(string annotationFile)
        {
            Excel.Workbooks objBooks;
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;
            
            try
            {
                // Instantiate Excel and start a new workbook.
                objApp = new Excel.Application();
                objBooks = objApp.Workbooks;
                objBook = objBooks.Open(annotationFile, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                objSheets = objBook.Worksheets;
                objSheet = (Excel._Worksheet)objSheets.get_Item(1);


                //Get a range of data.
                range = objSheet.get_Range("A1", Missing.Value);
                range = range.get_Resize(1, 1);
                double videoTimeOffset = (double)range.get_Value(Missing.Value);

                //Another way of getting data
                string output = "";
                int rowIndex = 3;
                //object colIndex2 = 11;
                while (((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2 != null)
                {
                    string tmp = "";
                    int lapID;
                    SwimLap.StrokeTypeEnum strokeType;
                    string lapType;  //Warm Up/Down Main
                    
                    tmp = (((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2.ToString();
                    if (tmp.Equals(" ") || tmp.Equals("-"))
                        lapID = 0;
                    else
                        lapID = Convert.ToInt32(tmp);

                    tmp = (((Excel.Range)objSheet.Cells[rowIndex, 2]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 2]).Value2.ToString();
                    strokeType = SwimLap.GetStrokeTypeEnum(tmp);

                    lapType = (((Excel.Range)objSheet.Cells[rowIndex, 3]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 3]).Value2.ToString();

                    
                    SwimLap sl = new SwimLap(videoTimeOffset,lapID,strokeType,lapType,lapTime,lapVideoStartTime,lapVideoEndTime,strokeCount,confidence,isTurn,isVideoFullLap,startType,strokeRate);

                    ss.AddLap(sl);

                    rowIndex++;
                    
                }

                objBooks.Close();
                objApp.Quit();

            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                MessageBox.Show(errorMessage, "Error");
            }
        }