C#Excel-将每个工作表保存到一个新的工作簿中

本文关键字：一个 工作簿 工作 C#Excel- 保存 | 更新日期: 2023-09-27 17:57:54

我有一个excel，里面有25个左右的工作表，我只想把每个工作表都保存为自己的新工作簿。当我运行代码时，它会复制整个工作簿，而不是单个工作表。任何帮助都会很棒。

string FileDropLocation = @"C:'ExcelFiles";
        string file_FullFileName = @"C:'ts'Conversion'v2.xlsx";
        Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(file_FullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "'t", false, false, 0, true, 1, 0);
for (int i = 0; i < workBook.Worksheets.Count; i++)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[i+1];
            workSheet.SaveAs(FileDropLocation + "''" + workSheet.Name);
        }
        workBook.Close();

C#Excel-将每个工作表保存到一个新的工作簿中

在当前for循环和以下尝试此操作

    foreach(Worksheet sheet in workBook.Worksheets)
    {
        var newbook = app.Workbooks.Add(1);
        sheet.Copy(newbook.Sheets[1]);
        newbook.SaveAs(FileDropLocation + "''" + sheet.Name);
        newbook.Close();
    }
    workBook.Close();

需要注意的是，我相信Workbooks.Add()会放在默认的空白工作表中（通常是Sheet1），所以如果你只想要复制的工作表，你必须明确地将其删除。

我知道这很管用。

Microsoft.Office.Interop.Excel.Application xl = null;
            Microsoft.Office.Interop.Excel._Workbook wb = null;
            xl = new Microsoft.Office.Interop.Excel.Application();
            xl.SheetsInNewWorkbook = 1;
            xl.Visible = true;
            wb = (Microsoft.Office.Interop.Excel._Workbook)(xl.Workbooks.Add(Type.Missing));
            wb.SaveAs(FileDropLocation + "''" + workBook.Sheets[i + 1].Name, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.Close(true, Type.Missing, Type.Missing);
            xl.Quit();
            Microsoft.Office.Interop.Excel.Workbook destWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
            Microsoft.Office.Interop.Excel.Worksheet newWorksheet = null;
            destWorkbook = app.Workbooks.Open(FileDropLocation + "''" + workBook.Sheets[i + 1].Name + ".xls", 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, Type.Missing);
            workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[i + 1];
            newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)destWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            app.DisplayAlerts = false;
            workSheet.Copy(Type.Missing,newWorksheet);
            destWorkbook.Save();

。。。您需要在循环中创建一个新工作簿，并将工作表移动到该工作簿中。我用VB编程，所以我猜，但你的foor循环中的代码应该是这样的：

Microsoft.Office.Interop.Excel.Workbook workBook2 = app.Workbooks.Add(Missing.Value)
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[i+1];
workSheet.Copy(workBook2.Sheets(1))

然后你可以添加代码来删除其他表格等。

希望它能有所帮助。

相关文章：