This project has moved and is read-only. For the latest updates, please go here.

Copy Multiple WorkSheets to a single Workbook

Dec 27, 2010 at 2:32 PM
Edited Dec 27, 2010 at 2:46 PM

Hi All,

I have multiple xlsx files with a single worksheet in each of them. This worksheet contains and chart and and its data. Now i want to create a new workbook and copy the sheet from each of this xlsx files into the new workbook. I want this to work something similar to the manualy Copy

Please let me know how I can achieve this.

I also wanted to know how i can delete a worksheet from an existing workbook.

Thank you in advance.

Regards,

SP :)

Dec 27, 2010 at 3:23 PM

Hi,

To add a copy use the Add method of the Worksheets collection like...

package.Workbook.Worksheets.Add("NewSheet", copyWorksheet);

where copyWorksheet is the worksheet you want to copy.

To delete use the Delete method of the worksheets collection.

Jan

Dec 27, 2010 at 5:18 PM
Edited Dec 27, 2010 at 5:20 PM

Hi jankallman,

Thank you for your reply. My code looks like this:


ExcelPackage pckTemplate = new ExcelPackage();

using (ExcelPackage p = new ExcelPackage(newFile, templateFile))
{

    //Set up the headers
    ExcelWorksheet ws = p.Workbook.Worksheets[1];
  

    ExcelChart chart = ((ExcelChart)ws.Drawings["Chart 1"]);

    for (int intColCtr = 1; intColCtr < dtChartdata.Columns.Count; intColCtr++)
    {
	strColumn = fnGetCellAddress(intColNo);

	chart.Series[intColCtr - 1].Header = dtChartdata.Columns[intColCtr].ColumnName.ToString();
	chart.Series[intColCtr - 1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(intCurrentRow, 3, intCurrentRow + dtChartdata.Rows.Count, 3);
	chart.Series[intColCtr - 1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(intCurrentRow, intColNo, intCurrentRow + dtChartdata.Rows.Count, intColNo);

	intColNo++;
    }


    p.SaveAs(newFile);
    
    var wsTemplate = pckTemplate.Workbook.Worksheets.Add("NewSheet",ws);


}
In the line "var wsTemplate = pckTemplate.Workbook.Worksheets.Add("NewSheet",ws);" i get error "Index was out of range. Must be non-negative and less than the size of the collection." 
Am i doing anything wrong here?
Thanks in advance for your help :)

Dec 28, 2010 at 7:52 AM

Hi,

First of all make sure you use version 2.8. This did not work between packages in previous versions.

Also, skip or move the SaveAs. Saving the package will close it, so if you want to save it, do that after the Add.

Jan

Dec 28, 2010 at 7:53 PM

Hi Jan,

Thanks for your reply.

Tried your suggestions but still ran into trouble, still get an exception. I am using version 2.8, maybe the method i am using is not correct. Is there any sample code available in the package that i could have a look at?

I have now tried a workaround where i have all the sheets i need in a single workbook template and i just use that to insert my data and resets the chart series. This works fine for the problem i have on hand. I will meanwhile try a prototype code and see if your solution works.

Thanks,

San

Dec 28, 2010 at 8:10 PM

Create an issue and upload your template and an example, so I can have a look at it.

Jan

Oct 16, 2013 at 6:08 AM
Hi San,

Did you find any solution to this isse you raised? Do u have the code to combile multiple excel sheet into one?