如何将两个 Excel 文件及其工作表名称合并为一个?

发布于 2024-12-07 07:44:32 字数 1438 浏览 0 评论 0原文

为了合并两个 Excel 工作表,我使用下面的代码。

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection; 

namespace MergeWorkBooks
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application app = new Excel.Application();

            app.Visible = true;
            app.Workbooks.Add("");
            app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
            app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");


            for (int i = 2; i <= app.Workbooks.Count; i++)
            {
                int count = app.Workbooks[i].Worksheets.Count;

                app.Workbooks[i].Activate();
                for (int j=1; j <= count; j++)
                {
                    Excel._Worksheet ws = (Excel._Worksheet)app.Workbooks[i].Worksheets[j];
                    ws.Select(Type.Missing);
                    ws.Cells.Select();

                    Excel.Range sel = (Excel.Range)app.Selection;
                    sel.Copy(Type.Missing);

                    Excel._Worksheet sheet = (Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    );

                    sheet.Paste(Type.Missing, Type.Missing);

                }
            }
        }
    }
}

这段代码对于我合并 Excel 工作簿很有用。但在合并时我没有得到 Excel 工作表名称。这里我需要当Excel同时合并时,工作表名称也应该转到合并的Excel工作表。

For merging of two excel sheet, I am using below code.

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection; 

namespace MergeWorkBooks
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application app = new Excel.Application();

            app.Visible = true;
            app.Workbooks.Add("");
            app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
            app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");


            for (int i = 2; i <= app.Workbooks.Count; i++)
            {
                int count = app.Workbooks[i].Worksheets.Count;

                app.Workbooks[i].Activate();
                for (int j=1; j <= count; j++)
                {
                    Excel._Worksheet ws = (Excel._Worksheet)app.Workbooks[i].Worksheets[j];
                    ws.Select(Type.Missing);
                    ws.Cells.Select();

                    Excel.Range sel = (Excel.Range)app.Selection;
                    sel.Copy(Type.Missing);

                    Excel._Worksheet sheet = (Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    );

                    sheet.Paste(Type.Missing, Type.Missing);

                }
            }
        }
    }
}

This code is working good for me for merging excel workbook. But at the time of merging I am not getting the excel sheet names. Here I need that when the excel is merging at the same time the sheet names should also go to the merged excel sheet.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

独孤求败 2024-12-14 07:44:32

以下对我来说效果很好,包括复制名称以及名称冲突的地方,甚至处理了 Sheet1(2) 等。

Excel.Application app = new Excel.Application();
app.Visible = true;
app.Workbooks.Add("");
app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
  app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");
for (int i = 2; i <= app.Workbooks.Count; i++)
{
    for (int j = 1; j <= app.Workbooks[i].Worksheets.Count;j++ )
    {
        Excel.Worksheet ws = app.Workbooks[i].Worksheets[j];
        ws.Copy(app.Workbooks[1].Worksheets[1]);
    }
}

The following worked fine for me, including copying the name and where the name clashed it kindly even handled the Sheet1(2) etc.

Excel.Application app = new Excel.Application();
app.Visible = true;
app.Workbooks.Add("");
app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
  app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");
for (int i = 2; i <= app.Workbooks.Count; i++)
{
    for (int j = 1; j <= app.Workbooks[i].Worksheets.Count;j++ )
    {
        Excel.Worksheet ws = app.Workbooks[i].Worksheets[j];
        ws.Copy(app.Workbooks[1].Worksheets[1]);
    }
}
苦妄 2024-12-14 07:44:32

无错误并改进答案

在同一位置创建 result2.xlsx 文件,您可以根据需要找到最终的 Excel 工作表

 class Program
     {        
      static void Main(string[] args)
        {
        Application app = new Application();
        app.Visible = true;
        Workbook w1 = app.Workbooks.Add(@"D:\MyDownload\result2.xlsx");
        Workbook w2 = app.Workbooks.Add(@"D:\MyDownload\merge1.xlsx");
        Workbook w3 = app.Workbooks.Add(@"D:\MyDownload\merge2.xlsx");
        for (int i = 2; i <= app.Workbooks.Count; i++)
        {
            for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
            {
                Worksheet ws = (Worksheet)app.Workbooks[i].Worksheets[j];
                ws.Copy(app.Workbooks[1].Worksheets[1]);
            }
        }
        app.Workbooks[1].SaveCopyAs(@"D:\MyDownload\result2.xlsx");
        w1.Close(0);
        w2.Close(0);
        w3.Close(0);
        app.Quit();
    }
}

Error Free and Improve Answer

create result2.xlsx file as same location and you find final excel sheet as you want

 class Program
     {        
      static void Main(string[] args)
        {
        Application app = new Application();
        app.Visible = true;
        Workbook w1 = app.Workbooks.Add(@"D:\MyDownload\result2.xlsx");
        Workbook w2 = app.Workbooks.Add(@"D:\MyDownload\merge1.xlsx");
        Workbook w3 = app.Workbooks.Add(@"D:\MyDownload\merge2.xlsx");
        for (int i = 2; i <= app.Workbooks.Count; i++)
        {
            for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
            {
                Worksheet ws = (Worksheet)app.Workbooks[i].Worksheets[j];
                ws.Copy(app.Workbooks[1].Worksheets[1]);
            }
        }
        app.Workbooks[1].SaveCopyAs(@"D:\MyDownload\result2.xlsx");
        w1.Close(0);
        w2.Close(0);
        w3.Close(0);
        app.Quit();
    }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文