記錄程式相關的一些實用語法: [轉貼] 使用 OpenXML 將資料庫中的資料整合到 Word 文件中，且不假手 Office 物件模型

出處：http://www.dotblogs.com.tw/regionbbs/archive/2010/04/30/office.openxml.templated.document.introduction.aspx

長久以來 Office 文件的存取一直是開發人員頭大的問題，因為要存取 Office 文件就得要動到 Office 物件模型，Office 文件在以前是一個專有格式，若不透過 Office 物件模型的話，不是用 Microsoft Jet (for EXCEL) 就是要用 HTML 來偽裝成 Office 的文件，Java 陣營有 Project POI (NPOI 即是此專案移植到 .NET 上的函式庫)，但微軟自己的開發陣營卻遲遲沒有一個可以存取 Office 文件的 solution，像是在 server 上要產生 Office 文件本身就是一件很討厭又不好解決的工作，微軟自己也不建議在伺服器使用 Office 自動化 (即存取 Office 物件模型)，這個問題在 Office 2007 終於看到解決的曙光，OpenXML 規格的定義讓 Office 文件變成了開放格式的檔案 (雖然很複雜)，只要使用 XML 剖析器即可存取它。但 Office 2007 乃至 Office 2010 的檔案格式都是被封裝的壓縮檔 (zip 格式)，所幸 Open XML SDK 中所提供的各式文件類別都可以直接讀取，或是由 WindowBase.dll 中引入 System.IO.Packaging 命名空間來讀取它們。

Open XML 的出現，代表開發人員不用再綁住 Office 物件模型即可存取 Office 文件的內容，而大多數企業使用的文件範本 (document template) 和資料庫的整合也因為 Open XML 的出現而變得容易，例如下圖是一份 Open XML 的產品清單文件的樣板：

我們可以透過程式的處理，將產品清單 (Northwind 資料庫的 Products 表格中的資料) 直接加到這份文件中，下圖就是加入的成果：

如何？而且程式並不是寫在 VSTO 或是 VBA，而是一支外部的 console 程式而已：

static void Main(string[] args)
{
    FileStream fs = ReadTemplateFile();
    WordprocessingDocument doc = WordprocessingDocument.Open(fs, true);
    Document document = doc.MainDocumentPart.Document;

var table = document.Body.GetFirstChild<Table>();

    using (NorthwindEntities context = new NorthwindEntities()) // ADO.NET Entity Data Model
    {
        var query = from productItem in context.Products
                    select productItem;
        TableRow lastRow = table.GetFirstChild<TableRow>();

        foreach (var item in query)
        {
            TableRow newRow = CreateProductItemRow(item.ProductID, item.ProductName, item.QuantityPerUnit, item.UnitsInStock.Value, item.UnitPrice.Value);
            table.InsertAfter(newRow, lastRow);
            lastRow = newRow;
        }
    }

    doc.MainDocumentPart.Document.Save();
    doc.Close();
    fs.Close();
}

private static FileStream ReadTemplateFile()
{
string newFileName = DateTime.Now.ToString("yyyyMMdd HHmmss") + ".docx";
File.Copy("Northwind Product List.docx", newFileName);

return new FileStream(newFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
}

當然，上列的程式我們也可以放到 ASP.NET 中，讓 Web 應用程式來幫我們產生 Office 的文件。

其實 Open XML 文件本身就是標準的 XML，內含許多的命名空間，而且具有順序的規則性，同時 Open XML 2.0 SDK 就已經將很多文件的部份包裝成類別庫，開發人員不必使用 XmlDocument 或是 LINQ to XML 一項一項去解就能享有快速存取 Office 文件的能力了。例如下面的程式碼就是用來產生產品表格列的程式：

private static TableRow CreateProductItemRow(int Serial, string Name, string Unit, short Qty, decimal Price)
{
TableRow tableRow1 = new TableRow() { RsidTableRowMarkRevision = "0055440F", RsidTableRowAddition = "00257DF3", RsidTableRowProperties = "0055440F" };

TableRowProperties tableRowProperties1 = new TableRowProperties();
ConditionalFormatStyle conditionalFormatStyle1 = new ConditionalFormatStyle() { Val = "000000100000", FirstRow = false, LastRow = false, FirstColumn = false, LastColumn = false, OddVerticalBand = false, EvenVerticalBand = false, OddHorizontalBand = true, EvenHorizontalBand = false, FirstRowFirstColumn = false, FirstRowLastColumn = false, LastRowFirstColumn = false, LastRowLastColumn = false };

tableRowProperties1.Append(conditionalFormatStyle1);

TableCell tableCell1 = new TableCell();

    TableCellProperties tableCellProperties1 = new TableCellProperties();
    ConditionalFormatStyle conditionalFormatStyle2 = new ConditionalFormatStyle() { Val = "001000000000", FirstRow = false, LastRow = false, FirstColumn = true, LastColumn = false, OddVerticalBand = false, EvenVerticalBand = false, OddHorizontalBand = false, EvenHorizontalBand = false, FirstRowFirstColumn = false, FirstRowLastColumn = false, LastRowFirstColumn = false, LastRowLastColumn = false };
    TableCellWidth tableCellWidth1 = new TableCellWidth() { Width = "817", Type = TableWidthUnitValues.Dxa };

tableCellProperties1.Append(conditionalFormatStyle2);
tableCellProperties1.Append(tableCellWidth1);

Paragraph paragraph1 = new Paragraph() { RsidParagraphMarkRevision = "0055440F", RsidParagraphAddition = "00257DF3", RsidParagraphProperties = "0055440F", RsidRunAdditionDefault = "00257DF3" };

ParagraphProperties paragraphProperties1 = new ParagraphProperties();

ParagraphMarkRunProperties paragraphMarkRunProperties1 = new ParagraphMarkRunProperties();
RunFonts runFonts1 = new RunFonts() { Ascii = "Centaur", HighAnsi = "Centaur" };

paragraphMarkRunProperties1.Append(runFonts1);

paragraphProperties1.Append(paragraphMarkRunProperties1);
BookmarkStart bookmarkStart1 = new BookmarkStart() { Name = "_GoBack", ColumnFirst = 4, ColumnLast = 4, Id = "0" };

Run run1 = new Run();

RunProperties runProperties1 = new RunProperties();
RunFonts runFonts2 = new RunFonts() { Hint = FontTypeHintValues.EastAsia, Ascii = "Centaur", HighAnsi = "Centaur" };

    runProperties1.Append(runFonts2);
    Text text1 = new Text();
    text1.Text = Serial.ToString();

run1.Append(runProperties1);
run1.Append(text1);

    paragraph1.Append(paragraphProperties1);
    paragraph1.Append(bookmarkStart1);
    paragraph1.Append(run1);

tableCell1.Append(tableCellProperties1);
tableCell1.Append(paragraph1);

TableCell tableCell2 = new TableCell();

TableCellProperties tableCellProperties2 = new TableCellProperties();
TableCellWidth tableCellWidth2 = new TableCellWidth() { Width = "2687", Type = TableWidthUnitValues.Dxa };

tableCellProperties2.Append(tableCellWidth2);

Paragraph paragraph2 = new Paragraph() { RsidParagraphMarkRevision = "0055440F", RsidParagraphAddition = "00257DF3", RsidParagraphProperties = "006C263F", RsidRunAdditionDefault = "00257DF3" };

ParagraphProperties paragraphProperties2 = new ParagraphProperties();
ConditionalFormatStyle conditionalFormatStyle3 = new ConditionalFormatStyle() { Val = "000000100000", FirstRow = false, LastRow = false, FirstColumn = false, LastColumn = false, OddVerticalBand = false, EvenVerticalBand = false, OddHorizontalBand = true, EvenHorizontalBand = false, FirstRowFirstColumn = false, FirstRowLastColumn = false, LastRowFirstColumn = false, LastRowLastColumn = false };

ParagraphMarkRunProperties paragraphMarkRunProperties2 = new ParagraphMarkRunProperties();
RunFonts runFonts3 = new RunFonts() { Ascii = "Centaur", HighAnsi = "Centaur" };

paragraphMarkRunProperties2.Append(runFonts3);

paragraphProperties2.Append(conditionalFormatStyle3);
paragraphProperties2.Append(paragraphMarkRunProperties2);

Run run2 = new Run();

RunProperties runProperties2 = new RunProperties();
RunFonts runFonts4 = new RunFonts() { Hint = FontTypeHintValues.EastAsia, Ascii = "Centaur", HighAnsi = "Centaur" };

    runProperties2.Append(runFonts4);
    Text text2 = new Text();
    text2.Text = Name;

run2.Append(runProperties2);
run2.Append(text2);

paragraph2.Append(paragraphProperties2);
paragraph2.Append(run2);

tableCell2.Append(tableCellProperties2);
tableCell2.Append(paragraph2);

TableCell tableCell3 = new TableCell();

TableCellProperties tableCellProperties3 = new TableCellProperties();
TableCellWidth tableCellWidth3 = new TableCellWidth() { Width = "2274", Type = TableWidthUnitValues.Dxa };

tableCellProperties3.Append(tableCellWidth3);

Paragraph paragraph3 = new Paragraph() { RsidParagraphMarkRevision = "0055440F", RsidParagraphAddition = "00257DF3", RsidParagraphProperties = "006C263F", RsidRunAdditionDefault = "00257DF3" };

ParagraphProperties paragraphProperties3 = new ParagraphProperties();
ConditionalFormatStyle conditionalFormatStyle4 = new ConditionalFormatStyle() { Val = "000000100000", FirstRow = false, LastRow = false, FirstColumn = false, LastColumn = false, OddVerticalBand = false, EvenVerticalBand = false, OddHorizontalBand = true, EvenHorizontalBand = false, FirstRowFirstColumn = false, FirstRowLastColumn = false, LastRowFirstColumn = false, LastRowLastColumn = false };

ParagraphMarkRunProperties paragraphMarkRunProperties3 = new ParagraphMarkRunProperties();
RunFonts runFonts5 = new RunFonts() { Ascii = "Centaur", HighAnsi = "Centaur" };

paragraphMarkRunProperties3.Append(runFonts5);

paragraphProperties3.Append(conditionalFormatStyle4);
paragraphProperties3.Append(paragraphMarkRunProperties3);

Run run3 = new Run();

RunProperties runProperties3 = new RunProperties();
RunFonts runFonts6 = new RunFonts() { Hint = FontTypeHintValues.EastAsia, Ascii = "Centaur", HighAnsi = "Centaur" };

    runProperties3.Append(runFonts6);
    Text text3 = new Text();
    text3.Text = Unit;

run3.Append(runProperties3);
run3.Append(text3);

paragraph3.Append(paragraphProperties3);
paragraph3.Append(run3);

tableCell3.Append(tableCellProperties3);
tableCell3.Append(paragraph3);

TableCell tableCell4 = new TableCell();

TableCellProperties tableCellProperties4 = new TableCellProperties();
TableCellWidth tableCellWidth4 = new TableCellWidth() { Width = "851", Type = TableWidthUnitValues.Dxa };

tableCellProperties4.Append(tableCellWidth4);

Paragraph paragraph4 = new Paragraph() { RsidParagraphMarkRevision = "0055440F", RsidParagraphAddition = "00257DF3", RsidParagraphProperties = "006C263F", RsidRunAdditionDefault = "00257DF3" };

ParagraphProperties paragraphProperties4 = new ParagraphProperties();
ConditionalFormatStyle conditionalFormatStyle5 = new ConditionalFormatStyle() { Val = "000000100000", FirstRow = false, LastRow = false, FirstColumn = false, LastColumn = false, OddVerticalBand = false, EvenVerticalBand = false, OddHorizontalBand = true, EvenHorizontalBand = false, FirstRowFirstColumn = false, FirstRowLastColumn = false, LastRowFirstColumn = false, LastRowLastColumn = false };

ParagraphMarkRunProperties paragraphMarkRunProperties4 = new ParagraphMarkRunProperties();
RunFonts runFonts7 = new RunFonts() { Ascii = "Centaur", HighAnsi = "Centaur" };

paragraphMarkRunProperties4.Append(runFonts7);

paragraphProperties4.Append(conditionalFormatStyle5);
paragraphProperties4.Append(paragraphMarkRunProperties4);

Run run4 = new Run();

RunProperties runProperties4 = new RunProperties();
RunFonts runFonts8 = new RunFonts() { Hint = FontTypeHintValues.EastAsia, Ascii = "Centaur", HighAnsi = "Centaur" };

    runProperties4.Append(runFonts8);
    Text text4 = new Text();
    text4.Text = Qty.ToString("###,###,##0");

run4.Append(runProperties4);
run4.Append(text4);

paragraph4.Append(paragraphProperties4);
paragraph4.Append(run4);

tableCell4.Append(tableCellProperties4);
tableCell4.Append(paragraph4);

TableCell tableCell5 = new TableCell();

TableCellProperties tableCellProperties5 = new TableCellProperties();
TableCellWidth tableCellWidth5 = new TableCellWidth() { Width = "1893", Type = TableWidthUnitValues.Dxa };

tableCellProperties5.Append(tableCellWidth5);

Paragraph paragraph5 = new Paragraph() { RsidParagraphMarkRevision = "0055440F", RsidParagraphAddition = "00257DF3", RsidParagraphProperties = "006C263F", RsidRunAdditionDefault = "00257DF3" };

ParagraphProperties paragraphProperties5 = new ParagraphProperties();
ConditionalFormatStyle conditionalFormatStyle6 = new ConditionalFormatStyle() { Val = "000000100000", FirstRow = false, LastRow = false, FirstColumn = false, LastColumn = false, OddVerticalBand = false, EvenVerticalBand = false, OddHorizontalBand = true, EvenHorizontalBand = false, FirstRowFirstColumn = false, FirstRowLastColumn = false, LastRowFirstColumn = false, LastRowLastColumn = false };

ParagraphMarkRunProperties paragraphMarkRunProperties5 = new ParagraphMarkRunProperties();
RunFonts runFonts9 = new RunFonts() { Ascii = "Centaur", HighAnsi = "Centaur" };

paragraphMarkRunProperties5.Append(runFonts9);

paragraphProperties5.Append(conditionalFormatStyle6);
paragraphProperties5.Append(paragraphMarkRunProperties5);

Run run5 = new Run();

RunProperties runProperties5 = new RunProperties();
RunFonts runFonts10 = new RunFonts() { Hint = FontTypeHintValues.EastAsia, Ascii = "Centaur", HighAnsi = "Centaur" };

    runProperties5.Append(runFonts10);
    Text text5 = new Text();
    text5.Text = Price.ToString("$###,###,###,##0");

run5.Append(runProperties5);
run5.Append(text5);

paragraph5.Append(paragraphProperties5);
paragraph5.Append(run5);

tableCell5.Append(tableCellProperties5);
tableCell5.Append(paragraph5);

    tableRow1.Append(tableRowProperties1);
    tableRow1.Append(tableCell1);
    tableRow1.Append(tableCell2);
    tableRow1.Append(tableCell3);
    tableRow1.Append(tableCell4);
    tableRow1.Append(tableCell5);
    return tableRow1;
}

你沒有眼花，不錯，就是這麼長，因為表格中還包了很多的樣式設定，如果是很單純的空白文件基本上就沒那麼複雜，像是建立一個 Hello World 的 Word Open XML 文件的程式碼，就很簡單：

public void HelloWorld(string docName)

{
// Create a Wordprocessing document.
using (WordprocessingDocument package = WordprocessingDocument.Create(docName, WordprocessingDocumentType.Document))
{
    // Add a new main document part.
    package.AddMainDocumentPart();
    // Create the Document DOM.
    package.MainDocumentPart.Document =
        new Document(
            new Body(
                new Paragraph(
                    new Run(
                        new Text("Hello World!")))));
    // Save changes to the main document part.
    package.MainDocumentPart.Document.Save();
}
}

(以上程式碼取自：http://blogs.msdn.com/brian_jones/archive/2008/10/06/open-xml-format-sdk-2-0.aspx)

因此可以將 Office 物件模型完全甩開的樣板化 Office 文件的解決方案已經可以完美的實現了，但我們還有一個問題：要如何簡單的產生複雜樣式的 C# 程式碼，請待下回分解。