c# - C# VSTO - 如何将活动表保存到新的工作簿/新文件？

Question

我正在使用 .NET 4.7.2 开发 Excel 加载项。因此，当用户使用此插件时，我的程序的操作几乎总是来自 GetInstance() 函数，该函数返回

Globals.ThisAddIn.Application.ActiveWorkbook

实际的挑战是将活动表保存为新的工作簿/Excel 文件。我在所有课程中都使用这样的 Office.Interop.Excel

using Excel = Microsoft.Office.Interop.Excel;

我已经尝试了很多方法来解决这个问题，但都没有真正奏效。

一： - - - - - - - - -

        OpenFileDialog ofd = new OpenFileDialog();
        ofd.Title = "Vorlage auswählen";
        ofd.InitialDirectory = Stx.DIRECTORY_VORLAGE;
        DialogResult ofdResult = ofd.ShowDialog();

        if (ofdResult == DialogResult.OK)
        {
            Excel.Workbook activeBook = GetInstance();
            Excel.Worksheet activeSheet = (Excel.Worksheet)GetInstance().ActiveSheet;
            string masterFile = activeSheet.Name + "_Master.xltx";

            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "Speicherort neues Masterfile";
            sfd.InitialDirectory = Stx.DIRECTORY_MASCHINEN;
            sfd.FileName = masterFile;
            sfd.Filter = "Excel Template (*.xltx)|*.xltx";
            DialogResult sfdResult = sfd.ShowDialog();

            if (sfdResult == DialogResult.OK)
            {
                string strFileTemplate = ofd.FileName.Trim();
                string strNewFile = sfd.FileName.Trim();
                
                // copies the template .xltx to the destination
                System.IO.File.Copy(strFileTemplate, strNewFile);

                Excel.Application app = new Excel.Application();
                //Excel.Workbook wb = app.Workbooks.Open(path, ReadOnly: false);
                //Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
                Excel.Workbook book = app.Workbooks.Open(strNewFile, 0, true, Type.Missing, "", "", false, Type.Missing, "", true, false, 0, false, false, false);
                Excel.Worksheet sheet = book.Sheets[1];

                //Copy sheet to new Workbook
                activeSheet.Copy(System.Reflection.Missing.Value, sheet);

                // by the way, this is working (vice versa)
                //     sheet.Copy(System.Reflection.Missing.Value, nativeWorkbook.Sheets[nativeWorkbook.Worksheets.Count]);

                book.Save();
                book.Close();
                app.Quit();
                Marshal.ReleaseComObject(app);

我也尝试使用 .xlsx 文件，但这也没有用。

对我来说，复制整个工作簿并删除不必要的工作表是可以的，但这也行不通。

二： - - - - - - -

                string abc = activeSheet.Name;
                GetInstance().SaveAs(strNewFile, FileFormat: Excel.XlFileFormat.xlOpenXMLWorkbook);
                //GetInstance().SaveAs(strNewFile, FileFormat: Excel.XlFileFormat.xlOpenXMLTemplate);

                Excel.Application app = new Excel.Application();
                Excel.Workbook wb = app.Workbooks.Open(strNewFile, ReadOnly: false);

                foreach (Excel.Worksheet sheet in wb.Worksheets)
                    if (!sheet.Name.Equals(abc))
                        sheet.Delete();  // doesn't work

                wb.Save();
                wb.Close();
                app.Quit();
                Marshal.ReleaseComObject(app);

标签： c#.netexcelvb.netvisual-studio

Answer 1

您可以尝试 workbook.saveas 方法将活动工作表保存到新的 xltx 文件中。

这是您可以参考的代码示例。

 private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application app = new Excel.Application();
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.InitialDirectory = "E:\\";
            DialogResult ofdResult = ofd.ShowDialog();
            if (ofdResult == DialogResult.OK)
            {

                Excel.Workbook activeBook = app.Workbooks.Open(ofd.FileName);
                Excel.Worksheet activeSheet = (Excel.Worksheet)activeBook.ActiveSheet;
                string masterFile = activeSheet.Name + "_Master.xltx";

                SaveFileDialog sfd = new SaveFileDialog();
                sfd.InitialDirectory = "E:\\";
                sfd.FileName = masterFile;
                sfd.Filter = "Excel Template (*.xltx)|*.xltx";
                DialogResult sfdResult = sfd.ShowDialog();
                if (sfdResult == DialogResult.OK)
                {
                    string strFileTemplate = ofd.FileName.Trim();
                    string strNewFile = sfd.FileName.Trim();
                    activeBook.SaveAs(strNewFile, Excel.XlFileFormat.xlOpenXMLTemplate);
                    activeBook.Close(false);
                    app.Quit();
           
                    Marshal.ReleaseComObject(app);
           
                }
            }
        }