How to copy excel worksheets Into another excel workbook without opening the excel file in c# winforms?
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.
Achilles
Enthusiast of all things technology, Developer React, .Net C#
Updated on June 22, 2022Comments
-
Achilles almost 2 years
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 ?