open xml sdk 在excel中复制指定的工作表

1.首先在测算表中新增一张工作表sheet;

2.克隆数据源表的数据节；

3.将克隆好的工作表添加到测算表中；

 1 public void CopyExcel()
 2         {
 3 
 4             using (dop.SpreadsheetDocument doc = dop.SpreadsheetDocument.Open(excelpath, true))
 5             {
 6                    var sheetName ="sheet";
 7                     //复制相应表格
 8                     InsertWorksheet(doc, sheetName );
 9                 
10                 doc.Close();
11             }
12         }
 1 public void InsertWorksheet(dop.SpreadsheetDocument doc, string sourceSheetName)
 2         {
 3             try
 4             {
　　　　　　　　　　　　　var sheetName="新建表";
 5                     var IsTrue = doc.WorkbookPart.Workbook.Descendants<dos.Sheet>().Where(p => p.Name == sheetName).Any();
 6                     if (!IsTrue)
 7                     {
 8                         // Add a blank WorksheetPart.
 9                         dop.WorksheetPart newWorksheetPart = doc.WorkbookPart.AddNewPart<dop.WorksheetPart>();
10                         newWorksheetPart.Worksheet = new dos.Worksheet(new dos.SheetData());
11                         dos.Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild<dos.Sheets>();
12                         string relationshipId = doc.WorkbookPart.GetIdOfPart(newWorksheetPart);
13 
14                         // Get a unique ID for the new worksheet.
15                         uint sheetId = 1;
16                         if (sheets.Elements<dos.Sheet>().Count() > 0)
17                         {
18                             sheetId = sheets.Elements<dos.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
19                         }
20                         // Give the new worksheet a name.
21 
22                         // Append the new worksheet and associate it with the workbook.
23                         dos.Sheet sheet = new dos.Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
24                         sheet.State = dos.SheetStateValues.Hidden;
25                         GetCopyExcel(doc, sourceSheetName, newWorksheetPart);
26                         sheets.Append(sheet);
27                         System.Diagnostics.Debug.Write(sheetName + "\n");
28                     }
29 
30                 }
31             }
32             catch (Exception ex)
33             {
34                 EasyLogs.LogInstance.Write(ex.Message, MessageType.Error);
36             }
37 
38         }
 1 public void GetCopyExcel(dop.SpreadsheetDocument document, string sourceSheetName, dop.WorksheetPart newWorkSheet, int columnIdnex)
 2         {
 3             try
 4             {
 5                 dop.WorkbookPart workbook = document.WorkbookPart;
 6                 dos.Sheet sheet = document.WorkbookPart.Workbook.Descendants<dos.Sheet>().Where(p => p.Name == sourceSheetName).First();
 7                 dop.WorksheetPart sourcePart = (dop.WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
 8                 //克隆源表的数据节
 9                 newWorkSheet.Worksheet = (dos.Worksheet)sourcePart.Worksheet.CloneNode(true);
10 
11                 workbook.Workbook.Save();
12             }
13             catch (Exception ex)
14             {
15                 EasyLogs.LogInstance.Write(ex.Message, MessageType.Error);
16             }
17         }

 

posted @ 2019-12-24 16:59  .Alive  阅读(664)  评论(0)  编辑  收藏  举报