62,052
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 拷贝sheet
/// </summary>
/// <param name="fileName">excel文件</param>
/// <param name="sheetName">待拷贝的sheet名称</param>
/// <param name="fileNo">流水号,用于名称后的数字</param>
/// <returns>新产生的sheet名称</returns>
protected string CopySheet(string fileName, string sheetName, int fileNo)
{
string partName = "/xl/workbook.xml";
string relFile = "/xl/_rels/workbook.xml.rels";
//打开包==============================================
Package xlPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);
// 出异常时返回"newSheetName",则:如果在try{}已产生名字,也可以返回
string newSheetName = "";
try
{
Uri documentUri = new Uri(partName, UriKind.Relative);
PackagePart documentPart = xlPackage.GetPart(documentUri);
//读出workbook.xml=======================================
XmlDocument doc = new XmlDocument();
doc.Load(documentPart.GetStream());
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
XmlNode node = doc.SelectSingleNode(searchString, nsManager);
if (node == null)
return null; //指定的sheet不存在
else
{
string relId = node.Attributes["r:id"].Value;
string sheetId = node.Attributes["sheetId"].Value;
string name = node.Attributes["name"].Value;
XmlNode nodeSheets = doc.DocumentElement.SelectSingleNode("d:sheets", nsManager);
string relId1 = node.Attributes["r:id"].Value + "_" + fileNo.ToString();
int maxSheetID = 0;
int tempSheetID = 0;
foreach (XmlNode note in nodeSheets.ChildNodes)
{
tempSheetID = Convert.ToInt32(note.Attributes["sheetId"].Value);
if (maxSheetID < tempSheetID)
maxSheetID = tempSheetID;
}
string sheetId1 = Convert.ToString(maxSheetID + 1);
newSheetName = name + "_" + fileNo.ToString();
//构造更改所需内容,主要针对/xl/_rels/workbook.xml.rels文件
string sheetFileName;
Uri xmlUri = new Uri(relFile, UriKind.Relative);
PackagePart xmlPart = xlPackage.GetPart(xmlUri);
XmlDocument doc1 = new XmlDocument();
doc1.Load(xmlPart.GetStream());
XmlNode nodeSheet1 = SelectOneNode(doc1.DocumentElement.ChildNodes, "Id", relId);
sheetFileName = nodeSheet1.Attributes["Target"].Value; // [worksheets/sheetname.xml]
string sheetFileName1 = sheetFileName.Substring(sheetFileName.LastIndexOf('/') + 1, (sheetFileName.IndexOf('.') - sheetFileName.LastIndexOf('/') - 1)) + "_" + fileNo.ToString() + ".xml";
string xmlString = "<Relationship Id=\"" + relId1 + "\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\" Target=\"worksheets/" + sheetFileName1.ToLower() + "\" />";
XmlNode node1 = doc1.DocumentElement;
node1.InnerXml += xmlString;
//拷贝sheet文件(若文件已存在,则抛出异常),并修改主关系文件[content_types].xml
string sheetXmlToPaste = "/xl/worksheets/" + sheetFileName1.ToLower();
CopyXmlFile(xlPackage, "/xl/" + sheetFileName, sheetXmlToPaste);
//修改workbook.xml
nodeSheets.InnerXml += "<sheet name=\"" + newSheetName + "\" sheetId=\"" + sheetId1 + "\" r:id=\"" + relId1 + "\" />";
doc.Save(documentPart.GetStream(FileMode.Create, FileAccess.Write));
//修改/xl/_rels/workbook.xml.rels文件
doc1.Save(xmlPart.GetStream(FileMode.Create, FileAccess.Write));
xlPackage.Flush();
xlPackage.Close();
return newSheetName;
}
}
catch
{
xlPackage.Close();
return newSheetName;
}
}
internal void CopyXmlFile(Package xlPackage, string sheetXmlToCopy, string sheetXmlToPaste)
{
Uri sheetUri = new Uri(sheetXmlToCopy, UriKind.Relative);
PackagePart sheetPart = xlPackage.GetPart(sheetUri);
XmlDocument doc = new XmlDocument();
doc.Load(sheetPart.GetStream());
Uri xmlUri = new Uri(sheetXmlToPaste, UriKind.Relative);
if (xlPackage.PartExists(xmlUri))
{
xlPackage.Close();
throw new InvalidOperationException("XML part is existing.");
}
PackagePart xmlPart = xlPackage.CreatePart(xmlUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
using (Stream outputStream = xmlPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (StreamWriter writer = new StreamWriter(outputStream))
{
writer.Write(doc.InnerXml);
writer.Close();
}
}
//修改主关系文件[content_types].xml
string schemaRelationships = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships";
PackageRelationship rel = xlPackage.CreateRelationship(xmlUri, TargetMode.Internal, schemaRelationships + "/worksheet");
xlPackage.Flush();
}