none
How to edit excel cells without changing format of cells in C# RRS feed

  • Question

  • I created an excel file and changed colors and widths of some cells. I am opening this existing excel file for editing using C#. If I run the programm it writes the values into cells but it also resets the format of the cells. How can I avoid this? How can I add values into cells without changing their formats i.e. colors, width, etc.

    Environment: Visual Studio Express 2013 V12, .NET 4.5, Microsoft Excel 15.0 Object Library, Microsoft Office 2013

    I tried it already with and without .Value:

    xlWorkSheet.Cells[RegOffsetValues[regIdx], columnOffset + (16-colIndex)].Value

     void DumpRegistersToFile(bool openFileInNotepad, uint[] registers, params uint[] registerStartEnds)
        {
            string excelFileName = "RegisterDump.xlsx";
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
    
            int[] RegOffsetValues = (int[])Enum.GetValues(typeof(enumRegOffset));
            int columnOffset = 3;
    
            object misValue = System.Reflection.Missing.Value;
    
            xlApp = new Excel.Application();
    
            xlWorkBook = xlApp.Workbooks.Open(excelFileName,
                                                    0,      // Updatelinks
                                                    false,  // Readonly
                                                    5,      // Format
                                                    "",
                                                    "",
                                                    true,   // IgnoreReadOnlyRecommended
                                                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                                                    0,      // Delimiter
                                                    true,   // Editable
                                                    true,   // Notify
                                                    0,      // Converter
                                                    true,   // Add workbook to recently used files
                                                    true,   // Local
                                                    0);     // CorruptLoad
    
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
            uint regIdx = 0;
            int colIndex = 0;
    
            for (int i = 0; i < registerStartEnds.Count(); i += 2)
            {
                    uint startAddress = registerStartEnds[i];
                    uint endAddress = registerStartEnds[i + 1];
    
                    uint readLength = endAddress - startAddress; 
    
                    for (int j = 0; j < readLength; j++)
                    {
                        xlWorkSheet.Cells[RegOffsetValues[regIdx], columnOffset-1] = registers[regIdx];
    
                        for (colIndex = 0; colIndex < 16; colIndex++)
                        {
                            xlWorkSheet.Cells[RegOffsetValues[regIdx], columnOffset + (16-colIndex)].Value = ( (registers[regIdx] >> colIndex) & 1 );                     
                        }
    
                        regIdx++;
                    }            
              }
    
    
               xlApp.DisplayAlerts = true;
    
               xlWorkBook.SaveAs(excelFileName,
                                Excel.XlFileFormat.xlOpenXMLWorkbook, 
                                misValue, 
                                misValue,
                                false, 
                                false, 
                                Excel.XlSaveAsAccessMode.xlNoChange,
                                misValue, 
                                misValue, 
                                misValue, 
                                misValue, 
                                misValue);
    
    
    
                xlWorkBook.Close(true, misValue, misValue);
    
                xlApp.DisplayAlerts = true;
                xlApp.Quit();
    
    
    
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
    
                xlWorkSheet = null;
                xlWorkBook = null;
                xlApp = null;
    
                GC.Collect();
                GC.WaitForPendingFinalizers();
    
                Process.Start("Excel.exe", excelFileName);
    
        }
    
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }



    Thursday, September 25, 2014 12:54 PM

Answers