5

In C# windows application, I have many excel workbooks, what i want is to copy the worksheets from the excel workbook to a single workbook. This is possible, but i have to open the excel workbooks in order to do so.

        Excel.Application app = new Excel.Application();

        app.Visible = true;

        app.WindowState = XlWindowState.xlMinimized;

        app.Workbooks.Add("");
        app.Workbooks.Add(@"Path\WorkBook1.xlsx");
        app.Workbooks.Add(@"Path\WorkBook2.xlsx");

        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(true);
                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);

                sheet.Name = app.Workbooks[i].Worksheets[j].Name;
            }


        }

        app.DisplayAlerts = false;
        app.Workbooks[3].Close();
        app.Workbooks[2].Close();
        app.DisplayAlerts = true;

        Cursor = Cursors.Default;

        MessageBox.Show("Successfully Generated Excel...!", "Excel Tool", MessageBoxButtons.OK, MessageBoxIcon.Information);

Is is possible without opening the excel sheets, copy all the data with their styles ?

Achilles
  • 117
  • 1
  • 3
  • 11
  • Copying single sheets from a workbook without opening the Excel file is not possible. However, if you just want the copy operation to run in the background, you can do that in a separate Excel application. One time, I had to copy some template sheet that contained startup macros. I had to open this template in a separate (invisible) Excel application with macros turned off. – StuartRedmann May 13 '13 at 12:46

1 Answers1

6

To copy a worksheet and all its contents and formatting without selecting and copying the contents of the worksheet itself you can make use of Worksheet.Copy. You'd use it like so:

Excel._Worksheet ws = (Excel._Worksheet)app.Workbooks[i].Worksheets[j];
Excel._Worksheet sheet = (Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
                Type.Missing, Type.Missing, Type.Missing, Type.Missing
                );
ws.Copy(Before: sheet);

If, however, what you actually mean by your question is that you want to copy the contents of the workbooks into one common workbook without ever opening the file, then I don't believe that's possible. You need to open the file to access the data.

Adrian
  • 2,825
  • 1
  • 22
  • 32
  • it's not the answer to the question!! – Kazimierz Jawor May 13 '13 at 04:54
  • To quote the actual question, not just the title of the post: "Is is possible without opening the excel sheets, copy all the data with their styles ?" This doesn't open the sheet and copy all the data. It copies the sheet. – Adrian May 13 '13 at 04:59
  • can you run it to closed workbook?? I'm not sure... Have you test it?? – Kazimierz Jawor May 13 '13 at 05:01
  • I use almost this exact sequence of calls in another project of mine to copy sheets from a template workbook to another one used for report generation. I'd be interested to learn if I'm wrong about being able to copy the contents of a file without accessing the file. – Adrian May 13 '13 at 05:04
  • seems like this is an alternative in a way. I could remove this line and make it hidden app.Visible = true; then close other worksheets and save and open my workbook – Achilles May 13 '13 at 05:07