[Office 2010 開發 ] 如何把工作表透過 OpenXML 來複製

2010-05-18

在本文中將會介紹及實作下方幾項動作：

--- 這是我們的來源檔案，即 Book1.xlsx ，其工作表名稱為「 MyData 」

--- 這是我們複製的檔案，檔名為 output.xlsx ，而分別複雜 MyData 工作表及另一個更名的 CopiedData 工作表

◇ 請先建立一個 Concole Application

◇ 再把 Program.cs 檔更改成如下的程式碼：

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Text;

   5:  using System.IO;

   6:  using System.Reflection;

   7:  using DocumentFormat.OpenXml;

   8:  using DocumentFormat.OpenXml.Packaging;

   9:  using DocumentFormat.OpenXml.Spreadsheet;

10:

  11:  namespace CopyWorksheet

  12:  {

  13:      class Program

  14:      {

  15:          static int tableId = 0;

16:

  17:          static void Main(string[] args)

  18:          {

  19:              // 把 Book1.xlsx 複製到 output.xlsx 中

  20:              string filename = "output.xlsx";

  21:              File.Copy("Book1.xlsx", filename, true);

  22:              CopySheet(filename, "MyData", "CopiedData");

  23:          }

24:

  25:          static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName)

  26:          {

  27:              //取得工作表的ID關聯

  28:              string relId = workbookPart.Workbook.Descendants<Sheet>()

  29:                  .Where(s => s.Name.Value.Equals(sheetName))

  30:                  .First()

  31:                  .Id;

32:

  33:              return (WorksheetPart)workbookPart.GetPartById(relId);

  34:          }

35:

  36:          static void CopySheet(string filename, string sheetName, string clonedSheetName)

  37:          {

  38:              // 開啟 活頁簿

  39:              using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))

  40:              {

  41:                  WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;

  42:                  //取得要複製的來源工作表

  43:                  WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName);

44:

  45:                  //利用 AddPart 來進行複製

  46:                  SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), mySpreadsheet.DocumentType);

  47:                  WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();

  48:                  WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);

49:

  50:                  //新增 sheet 並與工作表進行關聯

  51:                  WorksheetPart clonedSheet = workbookPart.AddPart<WorksheetPart>(tempWorksheetPart);

52:

53:

  54:                  int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();

  55:                  tableId = numTableDefParts;

56:

  57:                  //清除 Table 的定義部份 (Tables 是為唯一的 ID)

  58:                  if (numTableDefParts != 0)

  59:                      FixupTableParts(clonedSheet, numTableDefParts);

60:

  61:                  CleanView(clonedSheet);

62:

  63:                  //新增新的工作表到主要的活頁簿中

  64:                  Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();

  65:                  Sheet copiedSheet = new Sheet();

  66:                  copiedSheet.Name = clonedSheetName;

  67:                  copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);

  68:                  copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;

  69:                  sheets.Append(copiedSheet);

70:

  71:                  //儲存

  72:                  workbookPart.Workbook.Save();

  73:              }

  74:          }

75:

  76:          static void CleanView(WorksheetPart worksheetPart)

  77:          {

  78:              //此部份是將已參照複製的工作表進行移除動作

  79:              SheetViews views = worksheetPart.Worksheet.GetFirstChild<SheetViews>();

80:

  81:              if (views != null)

  82:              {

  83:                  views.Remove();

  84:                  worksheetPart.Worksheet.Save();

  85:              }

  86:          }

87:

  88:          static void FixupTableParts(WorksheetPart worksheetPart, int numTableDefParts)

  89:          {

  90:              //每個 Table 都附有一個 ID 及其名稱

  91:              foreach (TableDefinitionPart tableDefPart in worksheetPart.TableDefinitionParts)

  92:              {

  93:                  tableId++;

  94:                  tableDefPart.Table.Id = (uint)tableId;

  95:                  tableDefPart.Table.DisplayName = "CopiedTable" + tableId;

  96:                  tableDefPart.Table.Name = "CopiedTable" + tableId;

  97:                  tableDefPart.Table.Save();

  98:              }

  99:          }

 100:      }

 101:  }

>> 檔案下載：點我下載