Excel interop workbook.SaveAs() removes original formatting?

Question

How can I preserve a worksheet's formatting when using Microsoft.Office.Interop.Excel.Workbook.SaveAs(...)?

For example, I open a previously created workbook with Excel 2010 and I see that it looks beautiful: bold fonts in column headers, nice grid lines, good highlight colors showing input cells, etc.

The I switch to VS2012 and using the ExcelAppManager I wrote below I start by opening the workbook that has the beautiful formatting. I then use the Interop library to write new cell values, programmatically, to one of the worksheets. I then save the worksheet using SaveAs(), as shown below in the ExcelAppManager. I then open the worksheet using Microsoft Excel 2010: I can see the values I wrote in the respective cells - which is great, it worked - but the entire workbook no longer has formatting. It's plain vanilla formatting and all the beautiful format is gone.

I define formatting as anything that format painter would operate on: bold, font, alignment, grid lines, indents, widths and heights, etc.

Sample code:

using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace ExcelStuff
{
    public class ExcelAppManager
    {
        private Application _excelApp;
        private bool _isDefaultWorksheets = true;

        private Workbook _workBook;
        private Workbooks _workBooks;
        private Sheets _workSheets;

        public ExcelAppManager(string pathToExistingWorksheet)
        {
            _excelApp = new Application {DisplayAlerts = false};

            _workBooks = _excelApp.Workbooks;
            _workBook = _workBooks.Open(pathToExistingWorksheet, Type.Missing, false, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            _workSheets = _workBook.Worksheets;

            // NOTE: following lines are nice for debug of existing worksheets (to find the worksheets names)
            //Get the reference of second worksheet
            //            var worksheet = (Microsoft.Office.Interop.Excel.ExcelWorksheet) _workSheets.Item[1];
            //            string strWorksheetName = worksheet.Name; //Get the name of worksheet.


            _isDefaultWorksheets = true;
        }

        public ExcelAppManager()
        {
        }

        public void Initialize()
        {
            _excelApp = new Application {DisplayAlerts = false};
            _workBooks = _excelApp.Workbooks;
            _workBook = _workBooks.Add(Missing.Value);
            _workSheets = _workBook.Worksheets;
            _isDefaultWorksheets = true;
        }

        public void KillProcess()
        {
            _workBook.Close();
            _workBooks.Close();
            _excelApp.Quit();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            Marshal.FinalReleaseComObject(_workSheets);
            Marshal.FinalReleaseComObject(_workBook);
            Marshal.FinalReleaseComObject(_workBooks);
            Marshal.FinalReleaseComObject(_excelApp);
        }


        public void SaveAs(string filepath, string fileExtensionOfExcelFile)
        {
            _excelApp.DisplayAlerts = false;

            if (fileExtensionOfExcelFile == "xlsm")
            {
                _workBook.SaveAs(filepath, XlFileFormat.xlOpenXMLWorkbookMacroEnabled,
                                 Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange,
                                 XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
            }
            else
            {
                _workBook.SaveAs(filepath, Type.Missing,
                                 Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange,
                                 XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
            }
        }


        internal Sheets GetSheets()
        {
            return _workSheets;
        }

    }

And in my client code, I use the ExcelAppManager like this:

var _manager = new ExcelAppManager(_excelFilepath);

Answer 1

When you use Interop library in VS2012 it use Office 2013 by default and your saving the workbook in that format. And later you are opening it up with Office 2010. This may be the reason for the Original format loss. Try to save the excel workbook in 2010 format this might resolve the issue.