Introduction
Have you known OOXML? Have you started using ExcelPakage to generate Excel files
or other OOXML files on your server? If yes, did you find that there is no
“CopySheet” function in this package, actually, the section of Copy is
empty, just a name there.
We must say ExcelPakage is a powerful and useful tool, we can handle OOXML files
in a very easy way by using it, and we also plan to use it in our new
development. However, CopySheet is our main requirement and it’s real a pity
that we can’t find such a function in ExcelPakage. After taking some efforts,
we fill it up ourself. Following you can see how we’ve done it.
And I'm sorry for the previous version with some mistakes, it's OK now. Thank
Felipe and Abhijit. I had a problem to the formatting.
Using the code
The following you can find the two main functions from the whole class.
1 /// <summary>
2 /// Copy Excel Worksheet in an Excel file package, Todde, Jul 15,
2007
3 /// </summary>
4 /// <param name="fileName">Excel
File</param>
5 /// <param name="sheetName">Name of the sheet that
is going to be copied</param>
6 /// <param name="fileNo">Running No, used after the
new sheet name</param>
7 /// <returns>New sheet name</returns>8 protected
string CopySheet(string fileName, string sheetName, int fileNo)
9 {
10 string partName = "/xl/workbook.xml";
11 string relFile = "/xl/_rels/workbook.xml.rels";
12 //Read the Package ==============================================13
Package xlPackage = Package.Open(fileName, FileMode.Open,
FileAccess.ReadWrite);
14 15 // If exception, return string "newSheetName"
16 // 17 string newSheetName = "";
18 19 try20 {
21 //Read workbook.xml======================================= 22
Uri documentUri = new Uri(partName, UriKind.Relative);
23 PackagePart documentPart = xlPackage.GetPart(documentUri);
24 25 XmlDocument doc = new XmlDocument();
26 doc.Load(documentPart.GetStream());
27 XmlNamespaceManager nsManager = new
XmlNamespaceManager(doc.NameTable);
28 nsManager.AddNamespace("d",
doc.DocumentElement.NamespaceURI);
29 30 string searchString =
string.Format("//d:sheet[@name='']", sheetName);31
XmlNode node = doc.SelectSingleNode(searchString, nsManager);
32 33 if (node == null)
34 return null; //The sheet does not exist.35
else
36 {
37 string relId =
node.Attributes["r:id"].Value;
38 string sheetId =
node.Attributes["sheetId"].Value;
39 string name = node.Attributes["name"].Value;
40 41 XmlNode nodeSheets =
doc.DocumentElement.SelectSingleNode("d:sheets", nsManager);
42 43 string relId1 =
node.Attributes["r:id"].Value + "_" + fileNo.ToString();
44 45 int maxSheetID = 0;
46 int tempSheetID = 0;
47 foreach (XmlNode note in nodeSheets.ChildNodes)
48 {
49 tempSheetID =
Convert.ToInt32(note.Attributes["sheetId"].Value);
50 if (maxSheetID < tempSheetID)
51 maxSheetID = tempSheetID;
52 }
53 string sheetId1 = Convert.ToString(maxSheetID + 1);
54 55 newSheetName = name + "_" +
fileNo.ToString();
56 57 //Modify /xl/_rels/workbook.xml.rels58 string
sheetFileName;
59 Uri xmlUri = new Uri(relFile, UriKind.Relative);
60 PackagePart xmlPart = xlPackage.GetPart(xmlUri);
61 XmlDocument doc1 = new XmlDocument();
62 doc1.Load(xmlPart.GetStream());
63 64 XmlNode nodeSheet1 =
SelectOneNode(doc1.DocumentElement.ChildNodes, "Id", relId);
65 sheetFileName =
nodeSheet1.Attributes["Target"].Value; //
[worksheets/sheetname.xml]66 string sheetFileName1 =
sheetFileName.Substring(sheetFileName.LastIndexOf('/') + 1,
(sheetFileName.IndexOf('.')
67 -
sheetFileName.LastIndexOf('/') - 1)) + "_" + fileNo.ToString() +
".xml";
68 69 string xmlString = """ + relId1 +
"\"70
Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/
worksheet\" 71
Target=\"worksheets/" + sheetFileName1.ToLower() + "\"
/>";
72 XmlNode node1 = doc1.DocumentElement;
73 node1.InnerXml += xmlString;
74 75 //Copy sheet xml file (if existing, throw an error and
modify[content_types].xml)76 string sheetXmlToPaste =
"/xl/worksheets/" + sheetFileName1.ToLower();
77 CopyXmlFile(xlPackage, "/xl/" +
sheetFileName, sheetXmlToPaste);
78 79 //Modify workbook.xml80 nodeSheets.InnerXml +=
"<sheet name=\"" + newSheetName + "\"
sheetId=\"" + sheetId1 + "\" r:id=\"" + relId1 +
"\" />";
81 doc.Save(documentPart.GetStream(FileMode.Create,
FileAccess.Write));
82 83 //Modify /xl/_rels/workbook.xml.rels84
doc1.Save(xmlPart.GetStream(FileMode.Create, FileAccess.Write));
85 xlPackage.Flush();
86 87 xlPackage.Close();
88 return newSheetName;
89 }
90 }
91 catch92 {
93 xlPackage.Close();
94 return newSheetName;
95 }
96 }
97 98 //Copy sheet xml file99 internal void CopyXmlFile(Package xlPackage,
string sheetXmlToCopy, string sheetXmlToPaste)
100 {
101 Uri sheetUri = new Uri(sheetXmlToCopy, UriKind.Relative);
102 PackagePart sheetPart = xlPackage.GetPart(sheetUri);
103 XmlDocument doc = new XmlDocument();
104 doc.Load(sheetPart.GetStream());
105 Uri xmlUri = new Uri(sheetXmlToPaste, UriKind.Relative);
106 if (xlPackage.PartExists(xmlUri))
107 {
108 xlPackage.Close();
109 throw new InvalidOperationException("XML part is existing.");
110 }
111 PackagePart xmlPart = xlPackage.CreatePart(xmlUri,
@"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml
");
112 113 using (Stream outputStream = xmlPart.GetStream(FileMode.Create,
FileAccess.Write))
114 {
115 using (StreamWriter writer = new StreamWriter(outputStream))
116 {
117 writer.Write(doc.InnerXml);
118 writer.Close();
119 }
120 }
121 //Modify the main relationship file: [content_types].xml122 string
schemaRelationships =
@"http://schemas.openxmlformats.org/officeDocument/2006/relationships"
;123 PackageRelationship rel = xlPackage.CreateRelationship(xmlUri,
TargetMode.Internal, schemaRelationships + "/worksheet");
124 xlPackage.Flush();
125 }
126
Points of Interest
I'm sure there must be any smarter ways for copying sheets and I will be very
happy when you can email me and share anything you want with me.
|