通过outxml 复制sheet ,不完整
更改后的
internal class Program { static void Main(string[] args) { //CopySheet(@"D:\Works\2025-03-24 8D\2025-03-24\test.xlsx", "Sheet1", "8D表格 Copy"); //CopySheet(@"D:\Works\2025-03-24 8D\2025-03-24\KP_TEMPLATE.xlsx", "8D表格", "8D表格 Copy"); CopySheet(@"D:\Works\2025-03-24 8D\2025-03-24\开平依利安达-8D不合格 VCAR_for panasonic (003) .xlsx", "8D表格", "8D表格 Copy"); Console.WriteLine("Hello World!"); } static void CopySheet(string file, string sheetName, string newSheetName) { MemoryStream stream = new MemoryStream(); byte[] buff = File.ReadAllBytes(file); stream.Write(buff, 0, buff.Length); SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true); Sheet sheetRelation = null; uint maxSheetId = 0; uint sourceSheetIndex = 0; uint maxSheetCount = 0; foreach (OpenXmlElement s in doc.WorkbookPart.Workbook.Sheets) { if (s is Sheet st) { if (st.Name?.Value == sheetName) { sheetRelation = st; sourceSheetIndex = maxSheetCount; } //记录最大id if (st.SheetId?.Value != null) { if (st.SheetId.Value > maxSheetId) maxSheetId = st.SheetId.Value; } maxSheetCount++; } } if (sheetRelation == null) throw new Exception("sheet not found"); WorksheetPart sheetPart = doc.WorkbookPart.GetPartById(sheetRelation.Id) as WorksheetPart; Worksheet sheet = sheetPart.Worksheet; WorksheetPart newSheetPart = doc.WorkbookPart.AddNewPart<WorksheetPart>(); newSheetPart.Worksheet = new Worksheet(sheet.OuterXml); SheetView sheetView = (SheetView?)newSheetPart.Worksheet.SheetViews?.Elements().FirstOrDefault(); if (sheetView != null && sheetView.TabSelected != null) { sheetView.TabSelected = false; } var listNewParts = new List<OpenXmlPart>(); foreach (IdPartPair idPart in sheetPart.Parts) { OpenXmlPart openxmlPart = null; if (idPart.OpenXmlPart is EmbeddedObjectPart) { openxmlPart = newSheetPart.AddNewPart<EmbeddedObjectPart>(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); } else if (idPart.OpenXmlPart is VmlDrawingPart) { openxmlPart = newSheetPart.AddNewPart<VmlDrawingPart>(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); listNewParts.Add(openxmlPart); foreach (IdPartPair vmlChildPart in idPart.OpenXmlPart.Parts) { openxmlPart.CreateRelationshipToPart(vmlChildPart.OpenXmlPart, vmlChildPart.RelationshipId); } } else if (idPart.OpenXmlPart is DrawingsPart) { openxmlPart = newSheetPart.AddNewPart<DrawingsPart>(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); foreach (IdPartPair drawingChildPart in idPart.OpenXmlPart.Parts) { openxmlPart.CreateRelationshipToPart(drawingChildPart.OpenXmlPart, drawingChildPart.RelationshipId); } listNewParts.Add(openxmlPart); } else if (idPart.OpenXmlPart is SpreadsheetPrinterSettingsPart) { openxmlPart = newSheetPart.AddNewPart<SpreadsheetPrinterSettingsPart>(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); } else if (idPart.OpenXmlPart is WorksheetThreadedCommentsPart) { openxmlPart = newSheetPart.AddNewPart<WorksheetThreadedCommentsPart>(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); } else if (idPart.OpenXmlPart is WorksheetCommentsPart) { openxmlPart = newSheetPart.AddNewPart<WorksheetCommentsPart>(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); } // else if (idPart.OpenXmlPart is ImagePart) // { // openxmlPart = newSheetPart.AddNewPart(idPart.OpenXmlPart.ContentType, idPart.RelationshipId); // } if (openxmlPart != null) openxmlPart.FeedData(idPart.OpenXmlPart.GetStream()); else openxmlPart = idPart.OpenXmlPart; newSheetPart.CreateRelationshipToPart(openxmlPart, idPart.RelationshipId); } ResetOleId(sheet, newSheetPart.Worksheet, listNewParts); if (newSheetPart.Parts.Any(d => d.OpenXmlPart is WorksheetThreadedCommentsPart) && newSheetPart.Parts.Any(d => d.OpenXmlPart is WorksheetCommentsPart)) { ResetCommentId(newSheetPart.Parts.First(d => d.OpenXmlPart is WorksheetCommentsPart).OpenXmlPart as WorksheetCommentsPart, newSheetPart.Parts.First(d => d.OpenXmlPart is WorksheetThreadedCommentsPart).OpenXmlPart as WorksheetThreadedCommentsPart); } Sheet newSheetRelation = new Sheet(); newSheetRelation.SheetId = (uint)(maxSheetId + 1); newSheetRelation.Id = doc.WorkbookPart.GetIdOfPart(newSheetPart); newSheetRelation.Name = newSheetName; doc.WorkbookPart.Workbook.Sheets.AppendChild(newSheetRelation); doc.Save(); stream.Seek(0, SeekOrigin.Begin); buff = new byte[stream.Length]; stream.Read(buff, 0, buff.Length); stream.Close(); File.WriteAllBytes($@"{Path.GetDirectoryName(file)}\{Path.GetFileNameWithoutExtension(file)}-Copy{Path.GetExtension(file)}", buff); } static void ResetOleId(Worksheet sheetOld, Worksheet sheetNew, List<OpenXmlPart> newParts) { uint maxOleId = 0; OleObjects oles = sheetOld.Elements<OleObjects>().FirstOrDefault(); if (oles != null) { var ids = oles.Elements().Select(d => d.Descendants<OleObject>().FirstOrDefault()?.ShapeId?.Value).Where(d => d != null).Select(d => d.Value); if (ids.Any()) { maxOleId = ids.Max(); } } if (maxOleId < 1) return; var dic = new Dictionary<uint, uint>(); foreach (AlternateContent alternateContent in sheetNew.Elements<DocumentFormat.OpenXml.Spreadsheet.OleObjects>().Last()?.Elements<AlternateContent>()) { foreach (OleObject oleObject in alternateContent.Descendants<OleObject>()) { if (oleObject.ShapeId == null) continue; if (dic.ContainsKey(oleObject.ShapeId.Value)) { oleObject.ShapeId = dic[oleObject.ShapeId.Value]; } else if (oleObject.ShapeId <= maxOleId) { maxOleId = maxOleId * 2; dic.Add(oleObject.ShapeId.Value, maxOleId); oleObject.ShapeId = maxOleId; } } } foreach (OpenXmlPart part in newParts) { Stream stream = part.GetStream(); string str = GetStreamString(stream); foreach (KeyValuePair<uint, uint> item in dic) { str = str.Replace($"s{item.Key}", $"s{item.Value}"); str = str.Replace($"id=\"{item.Key}\"", $"id=\"{item.Value}\""); } stream.Seek(0, SeekOrigin.Begin); stream.Write(Encoding.UTF8.GetBytes(str)); stream.Close(); } } static void ResetCommentId(WorksheetCommentsPart commentsPart, WorksheetThreadedCommentsPart threadedCommentsPart) { if (commentsPart.Comments.CommentList == null) return; var dic = new Dictionary<string, string>(); foreach (Comment comment in commentsPart.Comments.CommentList.Elements()) { try { OpenXmlAttribute att = comment.GetAttribute("uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision"); string id = att.Value; string newId = Guid.NewGuid().ToString("B").ToUpper(); OpenXmlAttribute attNew = new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", newId); comment.RemoveAttribute("uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision"); comment.SetAttribute(attNew); dic.Add(id, newId); } catch (Exception e) { continue; } } foreach (ThreadedComment comment in threadedCommentsPart.ThreadedComments.Elements()) { try { if (comment.Id == null) continue; if (dic.ContainsKey(comment.Id.Value)) comment.Id = dic[comment.Id.Value]; } catch (Exception e) { continue; } } } static string GetStreamString(Stream stream) { stream.Seek(0, SeekOrigin.Begin); byte[] buff = new byte[stream.Length]; stream.Read(buff, 0, buff.Length); return Encoding.UTF8.GetString(buff); } }
点到为止