Excel互操作工作簿.SaveAs()删除原始格式

使用Microsoft.Office.Interop.Excel.Workbook.SaveAs(...)? 时如何保留工作表的格式

例如，我用Excel2010打开了一个以前创建的工作簿，发现它看起来很漂亮：列标题中的粗体字体、漂亮的网格线、显示输入单元格的突出显示颜色等等

当我切换到VS2012并使用我在下面写的ExcelAppManager时，我首先打开了格式优美的工作簿。然后，我使用Interop库以编程方式将新的单元格值写入其中一个工作表。然后，我使用SaveAs（）保存工作表，如下所示，位于ExcelAppManager中。然后，我使用Microsoft Excel 2010打开工作表：我可以看到我在各个单元格中写的值——这很好，很有效——但整个工作簿不再有格式。这是普通的香草格式，所有漂亮的格式都不见了。

我将格式化定义为格式绘制者可以操作的任何内容：粗体、字体、对齐、网格线、缩进、宽度和高度等。

样本代码：

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;
        }
    }

在我的客户端代码中，我使用ExcelAppManager，如下所示：

var _manager = new ExcelAppManager(_excelFilepath);