安格斯殿堂

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Text;

   5:  using System.IO;

   6:  using System.Collections;

   7:  using DocumentFormat.OpenXml.Packaging;

   8:  using DocumentFormat.OpenXml.Wordprocessing;

   9:  using Spreadsheet = DocumentFormat.OpenXml.Spreadsheet;

  10:   

  11:  namespace ImportTableFromWordToExcel

  12:  {

  13:      class Program

  14:      {

  15:          static ArrayList colHeaders = new ArrayList();

  16:          static uint numCols = 0;

  17:          static int numRows = 0;

  18:   

  19:          static void Main(string[] args)

  20:          {

  21:              File.Copy("template.xlsx", "output.xlsx", true);

  22:   

  23:              //開啟 Word文件 

  24:              using (WordprocessingDocument myDoc = WordprocessingDocument.Open("tables.docx", true))

  25:              {

  26:                  MainDocumentPart mainPart = myDoc.MainDocumentPart;

  27:                  //開啟 Spreadsheet

  28:                  using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open("output.xlsx", true))

  29:                  {

  30:                      WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;

  31:                      

  32:                      //從文件中匯入特定的表格到指定的 Sheet 中

  33:                      ImportWordTable(mainPart, "WorldPopulationTable", workbookPart, "Sheet1");

  34:                  }

  35:              }

  36:          }

  37:   

  38:          static void ImportWordTable(MainDocumentPart mainPart, string sdtName, WorkbookPart workbookPart, string sheetName)

  39:          {

  40:              string relId = "rId1";

  41:   

  42:              //尋找特定的內容控制項以利找到我們要的表格

  43:              SdtBlock sdt = mainPart.Document.Descendants<SdtBlock>()

  44:                                      .Where(s => s.SdtProperties.GetFirstChild<Alias>().Val.Value

  45:                                      .Equals(sdtName)).First();

  46:   

  47:              Table tbl = sdt.SdtContentBlock.GetFirstChild<Table>();

  48:   

  49:              //在該欲匯入的表格中得到一個 worksheet

  50:              WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, sheetName);

  51:   

  52:              //取代先前的 Sheet 中的資料

  53:              worksheetPart.Worksheet.ReplaceChild<Spreadsheet.SheetData>(ConvertWordTableToExcel(tbl),

  54:                  worksheetPart.Worksheet.GetFirstChild<Spreadsheet.SheetData>());

  55:   

  56:              //確定第一欄欄寬是夠大足以放入資料

  57:              Spreadsheet.Columns cols = new Spreadsheet.Columns();

  58:              Spreadsheet.Column col = new Spreadsheet.Column();

  59:              col.Min = 1;

  60:              col.Max = 1;

  61:              col.CustomWidth = true;

  62:              col.Width = 25;

  63:              cols.Append(col);

  64:   

  65:              worksheetPart.Worksheet.InsertBefore<Spreadsheet.Columns>(cols, worksheetPart.Worksheet.GetFirstChild<Spreadsheet.SheetData>());

  66:   

  67:              //新增表格定義以標註匯入的表格

  68:              TableDefinitionPart tableDefPart = worksheetPart.AddNewPart<TableDefinitionPart>(relId);

  69:   

  70:              //參照已新增的表格部份

  71:              Spreadsheet.TableParts tableParts = new Spreadsheet.TableParts();

  72:              Spreadsheet.TablePart tablePart = new Spreadsheet.TablePart();

  73:              tablePart.Id = relId;

  74:              tableParts.Append(tablePart);

  75:   

  76:              worksheetPart.Worksheet.Append(tableParts);

  77:              //建立內容的關聯性

  78:              CreateTableDefinition(tableDefPart);

  79:   

  80:              //儲存

  81:              worksheetPart.Worksheet.Save();

  82:          }

  83:   

  84:          static void CreateTableDefinition(TableDefinitionPart tableDefPart)

  85:          {

  86:              //下列是定義一些 Table 中的資訊

  87:              numCols = (uint)colHeaders.Count;

  88:              int id = 1;

  89:              tableDefPart.Table = new Spreadsheet.Table();

  90:              tableDefPart.Table.Id = 1;

  91:              tableDefPart.Table.Name = "Table1";

  92:              tableDefPart.Table.DisplayName = "Table1";

  93:              char endCol = 'A';

  94:   

  95:              for (int i = 1; i < numCols; i++)

  96:                  endCol++;

  97:   

  98:              //設定範圍

  99:              string reference = "A1:" + endCol + numRows;

 100:              tableDefPart.Table.Reference = reference;

 101:   

 102:              //設定其表格是有篩選的效果

 103:              Spreadsheet.AutoFilter autoFilter = new Spreadsheet.AutoFilter();

 104:              autoFilter.Reference = reference;

 105:   

 106:              Spreadsheet.TableColumns tableColumns = new Spreadsheet.TableColumns();

 107:              tableColumns.Count = numCols;

 108:   

 109:              //新增表格中的標題列內容

 110:              foreach (string s in colHeaders)

 111:              {

 112:                  Spreadsheet.TableColumn tableColumn = new Spreadsheet.TableColumn();

 113:                  tableColumn.Id = (uint)id;

 114:                  tableColumn.Name = s;

 115:                  id++;

 116:                  tableColumns.Append(tableColumn);

 117:              }

 118:   

 119:              //套用一個已設定好的表格樣式 (套用已定義好的樣版)

 120:              Spreadsheet.TableStyleInfo tableStyleInfo = new Spreadsheet.TableStyleInfo();

 121:              tableStyleInfo.Name = "TableStyleMedium9";

 122:              tableStyleInfo.ShowRowStripes = true;

 123:   

 124:              tableDefPart.Table.Append(autoFilter, tableColumns, tableStyleInfo);

 125:   

 126:              tableDefPart.Table.Save();

 127:          }

 128:   

 129:          static Spreadsheet.SheetData ConvertWordTableToExcel(Table tbl)

 130:          {

 131:              //Sheetdata 包含表格資料

 132:              Spreadsheet.SheetData sheetdata = new Spreadsheet.SheetData();

 133:              

 134:              //在 Word 中的每一列相對應到 Excel

 135:              foreach (TableRow row in tbl.Descendants<TableRow>())

 136:              {

 137:                  numRows++;

 138:                  Spreadsheet.Row sheetRow = new Spreadsheet.Row();

 139:                  

 140:                  //把 Word 中的每個儲存格建立到相對應的 Excel  儲存格中

 141:                  foreach (TableCell cell in row.Descendants<TableCell>())

 142:                  {

 143:                      Spreadsheet.Cell sheetCell = new Spreadsheet.Cell();

 144:   

 145:                      string textValue = cell.InnerText;

 146:                      double numValue;

 147:   

 148:                      //分辨其值是數值還是字串

 149:                      //第一列勢必為字串

 150:                      if ((numRows != 1) && (Double.TryParse(textValue, System.Globalization.NumberStyles.Any, null, out numValue)))

 151:                      {

 152:                          Spreadsheet.CellValue v = new Spreadsheet.CellValue();

 153:                          textValue = numValue.ToString();

 154:                          v.Text = textValue;

 155:                          sheetCell.Append(v);

 156:                      }

 157:                      else //值是字串型態

 158:                      {

 159:                          sheetCell.DataType = Spreadsheet.CellValues.InlineString;

 160:                          Spreadsheet.InlineString inlineString = new Spreadsheet.InlineString();

 161:                          Spreadsheet.Text t = new Spreadsheet.Text();

 162:                          t.Text = textValue;

 163:                          inlineString.Append(t);

 164:                          sheetCell.Append(inlineString);

 165:                      }

 166:   

 167:                      //需要追蹤加入其表格定義中的欄位標題

 168:                      if (numRows == 1)

 169:                      {

 170:                          colHeaders.Add(textValue);

 171:                      }

 172:   

 173:                      sheetRow.Append(sheetCell);

 174:                  }

 175:   

 176:                  sheetdata.Append(sheetRow);

 177:              }

 178:   

 179:              return sheetdata;

 180:          }

 181:   

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

 183:          {

 184:              //把 ID 與 Sheetname 進行關連

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

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

 187:              .First()

 188:              .Id;

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

 190:          }

 191:      }

 192:  }