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);

        }
    }