How to copy ExcelWorksheet

Mar 25, 2010 at 7:49 AM

Now I want to solution to copy sheet.

Coordinator
Mar 25, 2010 at 9:46 AM

Thats on the TODO list for a future version. It shouldn't be that hard to implement. You need to copy the worksheet XML document and clone the internal variables with the new sheet ID.

If you need this now, please have a look at it, otherwise I'll see what I can do to the next version (or any one else that feel they can contribute).

Mar 25, 2010 at 10:56 AM

thanks for recommend. I will try to copy worksheet XML and clone variable.

Apr 17, 2010 at 8:43 PM

Hai jankallman

I also want know abt copying worksheet ans paste it to another workbook.

i did not get the point.can you post code to copy worksheet?

 

Nitha

Coordinator
Apr 19, 2010 at 5:55 AM

Hi,

I have not implemented a worksheet copy function, so I have no have no code to post. My recomendations above is if you want to try to write it yourself.

I'll see if I can do something to a coming version.

Jan

May 19, 2010 at 9:06 PM
Edited May 19, 2010 at 9:14 PM
I just discovered this project and it looks great. I have been using Excel Package and copying has been one of my required features. I have been using a copy implementation originally posted here by slide_o_mix. I took a quick look into implementing it into EPP, but it appears the constructor for ExcelWorksheet has changed. Beyond that I haven't had the chance to examine EPP enough to know if this code will be easily adaptable. The code is as follows, the red line is causing the error:

/// <summary>
/// Copies the named worksheet and creates a new worksheet in the same workbook
/// </summary>
/// <param name="Name">The name of the existing worksheet</param>
/// <param name="NewName">The name of the new worksheet to create</param>
/// <returns></returns>
public ExcelWorksheet Copy(string Name, string NewName)
{
// we need a new sheetID
ExcelWorksheet old_sheet = this[Name];
if (old_sheet == null)
throw new Exception(string.Format("Copy worksheet error: Could not find worksheet to copy '{0}'", Name));

// first fine maximum existing sheetID
// also create a unique name
int sheetID = 0;

foreach (XmlNode sheet in _worksheetsNode.ChildNodes)
{
XmlAttribute attr = (XmlAttribute)sheet.Attributes.GetNamedItem("sheetId");
if (attr != null)
{
int curID = int.Parse(attr.Value);
if (curID > sheetID)
sheetID = curID;
}
attr = (XmlAttribute)sheet.Attributes.GetNamedItem("name");
if (attr != null)
{
if (attr.Value == NewName)
throw new Exception("Add worksheet Error: attempting to create worksheet with duplicate name");
}
}
// we now have the max existing values, so add one
sheetID++;
// add the new worksheet to the package
Uri uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative);
PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");

// create the new, empty worksheet and save it to the package
StreamWriter streamWorksheet = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write));
XmlDocument worksheetXml = old_sheet.WorksheetXml;
worksheetXml.Save(streamWorksheet);
streamWorksheet.Close();
_xlPackage.Package.Flush();

// create the relationship between the workbook and the new worksheet
PackageRelationship rel = _xlPackage.Workbook.Part.CreateRelationship(uriWorksheet, TargetMode.Internal, ExcelPackage.schemaRelationships + "/worksheet");
_xlPackage.Package.Flush();

// now create the new worksheet tag and set name/SheetId attributes in the workbook.xml
XmlElement worksheetNode = _xlPackage.Workbook.WorkbookXml.CreateElement("sheet", ExcelPackage.schemaMain);
// create the new sheet node
worksheetNode.SetAttribute("name", NewName);
worksheetNode.SetAttribute("sheetId", sheetID.ToString());
// set the r:id attribute
worksheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id);
// insert the sheet tag with all attributes set as above
_worksheetsNode.AppendChild(worksheetNode);

ExcelWorksheet worksheet = new ExcelWorksheet(_xlPackage, rel.Id, NewName, uriWorksheet, sheetID, false);


XmlDocument doc = worksheet.WorksheetXml; // this will load the worksheet XML into the member variable.
int positionID = _worksheets.Count + 1;
_worksheets.Add(positionID, worksheet);
return worksheet;
}
Coordinator
May 19, 2010 at 9:12 PM

Actually im working on that now. I will try to check it in this week or the beginning of next week.

May 19, 2010 at 9:16 PM
Good to hear. And thanks for the quick response, you already responded before I finished fixing the formating on my post.
Jun 2, 2010 at 7:28 PM

Is the worksheet copy method in the new source code checked in on June 2, 2010?

Coordinator
Jun 2, 2010 at 8:57 PM

Yes!

The Worksheets.Add method now takes an extra ExcelWorksheet parameter to copy a sheet. I removed the copy method.

 

Jun 2, 2010 at 9:04 PM

Thanks, I replied to the other topic regarding the new Worksheets.Add method.

Jun 3, 2010 at 10:17 PM
Edited Jun 3, 2010 at 10:21 PM
Jan - thank you for your efforts. A colleague and I had hacked in some copy worksheet functionality, but yours is certainly better!

I am not sure if this minor issue matters to you, but in version 59488 there is still a reserved Copy method in the ExcelWorksheets class that does not perform the functionality. In my project I added the following to implement the functionality for this method.

ExcelWorksheet Copy = this[Name];
if (Copy == null)
throw new Exception(string.Format("Copy worksheet error: Could not find worksheet to copy '{0}'", Name));
ExcelWorksheet added = Add(NewName, Copy);
return added;

As an extension of the copy worksheet function, we would very much like additional functionality to perform copying of a worksheets between differing workbooks, which would allow us to merge worksheets from multiple template workbooks into a single final product workbook.

Thanks again
Coordinator
Jun 4, 2010 at 12:37 PM

Hi,

Sure, my intention was to remove this method, but since you use it and the method is there I'll add your code.

I haven't tested to add a worksheet from another workbook using the add-method, but that is intended to work. If it's not working i'll try to fix it.

Jan

 

Jun 4, 2010 at 1:03 PM
Edited Jun 4, 2010 at 1:18 PM
Thanks Jan,

In my testing of adding a Worksheet to a differing Workbook, I encounter problems when the ExcelWorkbook Save function calls Styles.UpdateXml(). An index out of range exception happens in the ExcelStyles class at line 518 CellXfs[cell.GetCellStyleID()].useCnt++;

If I can help further let me know.
Coordinator
Jun 4, 2010 at 1:13 PM

ahh, I should have thought about that. I'll see how much work it is to fix . If its easily fixed I'll add it to the new version.

Jun 17, 2010 at 9:58 PM
Edited Jun 17, 2010 at 9:58 PM

Did this make it into the new version? I suspect it would be a big undertaking and likely was not addressed at this time.

Thanks again for your efforts

Coordinator
Jun 18, 2010 at 6:14 AM

No, you can only copy a sheet inside a package (The problem is that you need to copy the styles between the different packages).

It was a little bit to close to release to implement it, but I will fix it to the next version.

 

Jul 18, 2013 at 12:43 PM
Hi, Jan
Do you have any updates about copy worksheet from one workbook to another? Is it already works or not?

Regards, Artem