Below is the C# code to create a new Excel workbook based on a template file & populated by an SQL database.

You need to add references to:

DocumentFormat.OpenXml
System.Data.Linq
Windows.Base

using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ElitetoExcel
{
    class Program
    {
        static string[] headerColumns =
            new string[] { "A", "B", "C", "D", "E"};

        static void Main(string[] args)
        {
            //Make a copy of the template file
            File.Copy(@"c:\templatedoc.xlsx", @"c:\newdoc.xlsx", true);

            //Open up the copied workbook
            using (SpreadsheetDocument myWorkbook =
                SpreadsheetDocument.Open(@"c:\newdoc.xlsx", true))
            {
                //Access the main workbook part which contains all the references
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                //Grab the first worksheet
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

                //Sheet data will contain all the data
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Connect to Database LINQ
                DataClasses1DataContext
                DataClasses1DataContext = new
                DataClasses1DataContext();
                int index = 2;

                //select * from SPOpenMatters table
                var MatterQuery = from t in DataClasses1DataContext.SPOpenMatters
                                     select t;

                //For each record in database add row in spreadsheet

                Console.WriteLine("Creating Excel report...........");

                foreach (var item in MatterQuery)
                {
                    string MatterNum = item.matterno;
                    string Description = item.description;
                    string OpenedDate = item.openeddate.ToString();
                    string MatterStatus = item.matterstatus;
                    string BillingTKPR = item.billingTKPR;

                    //Add a new row
                    Row contentRow =
                        CreateContentRow(index, MatterNum, Description, OpenedDate, MatterStatus, BillingTKPR);
                    index++;

                    //Append new row to sheet data
                    sheetData.AppendChild(contentRow);
                }
                worksheetPart.Worksheet.Save();
            }
        }

        private static Row CreateContentRow(int index, string MatterNum, string Description, string OpenedDate, string MatterStatus, string BillingTKPR)
        {
            //Create new row
            Row r = new Row();
            r.RowIndex = (UInt32)index;

            //First cell is a text cell, so create it and append it
            Cell firstCell =
                CreateTextCell(headerColumns[0], MatterNum, index);
            r.AppendChild(firstCell);//

            //create cells that contain data
            for (int i = 1; i < headerColumns.Length; i++)
            {
                Cell c = new Cell();
                c.CellReference = headerColumns[i] + index;
                CellValue v = new CellValue();
                if (i == 1)

                    v.Text = Description.ToString();

                if (i == 2)
                    v.Text = Convert.ToDateTime(OpenedDate).ToString("D");

                if (i == 3)
                    v.Text = MatterStatus.ToString();

                if (i == 4)
                    v.Text = BillingTKPR.ToString();

                c.AppendChild(v);
                r.AppendChild(c);

            }
            return r;
        }

        private static Cell CreateTextCell(string header,
            string MatterNum, int index)
        {
            //Create a new inline string cell
            Cell c = new Cell();
            c.DataType = CellValues.InlineString;
            c.CellReference = header + index;

            //Add text to text cell
            InlineString inlineString = new InlineString();
            Text t = new Text();
            t.Text = MatterNum;
            inlineString.AppendChild(t);
            c.AppendChild(inlineString);
            return c;
        }
    }
}

Here is some simple code that updates a word document template that contains 2 content controls in the template document called:

MatterNumber
DocType

The template document is – C:\Users\stilgoep\WordTest\test-contentcontrol.docx

The output document is – C:\Users\stilgoep\WordTest\test-contentcontrol-updated.docx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using System.Xml;
using System.IO;

namespace BasicOpenXMLUpdater
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Starting up Word template updater ...");

            //get path to template and instance output
            string docTemplatePath = @"C:\Users\stilgoep\WordTest\test-contentcontrol.docx";
            string docOutputPath = @"C:\Users\stilgoep\WordTest\test-contentcontrol-updated.docx";

            //create copy of template so that we don't overwrite it
            File.Copy(docTemplatePath, docOutputPath);

            Console.WriteLine("Created copy of template ...");

            //stand up object that reads the Word doc package
            using (WordprocessingDocument doc = WordprocessingDocument.Open(docOutputPath, true))
            {
                //create XML string matching custom XML part
                string newXml = "<root>" +
                    "<MatterNumber>00011111</MatterNumber>" +
                    "<DocType>Contract</DocType>" +
                    "</root>";

                MainDocumentPart main = doc.MainDocumentPart;
                main.DeleteParts<CustomXmlPart>(main.CustomXmlParts);

                //add and write new XML part
                CustomXmlPart customXml = main.AddCustomXmlPart(CustomXmlPartType.CustomXml);
                using (StreamWriter ts = new StreamWriter(customXml.GetStream()))
                {

                    ts.Write(newXml);
                }

                //closing WordprocessingDocument automatically saves the document
            }

            Console.WriteLine("Done");
            Console.ReadLine();

        }
    }
}

You need to add the following 2 references:

DocumentFormat.OpenXml
WindowsBase.dll

You will also need to download the Word Content Control Toolkit from CodePlex to map your content controls to your XML data.

More From pstilgoe

• How to auto populate InfoPath forms using the people picker
• Auto populate your InfoPath form with the current user name
• Modifying an Open XML Document in a SharePoint Document Library

Ask pstilgoe To Recommend Your Posts

If you get the following error Method ‘post’ of object ‘IOWSPostData’ failed, you can fix it by doing the below:

Do a search on you C: drive for a file named: EXPTOOWS.XLA

Open it and Enable Macros

Select the Developer tab and the Visual Bacis or press ALT+11. Same result. You are ready to alter the file.

Search for:

lVer = Application.SharePointVersion(URL)

Add a new line just below:

lVer = 2

Save and exit.

Now try the import again & it should work.

NOTE: this is a problem with excel as opposed to Sharepoint so this would need to be done on all workstations requiring the ability to import Excel 2007 spreadsheets as custom lists.

More From pstilgoe

• How to easily move / copy list items from one sharepoint list to another
• How to configure Kerberos for SharePoint / MOSS 2007
• Sharepoint list error <!– #RENDER FAILED –>

Ask pstilgoe To Recommend Your Posts

1) MOSS2007 uses the “Comments” field (from Document Properties) as its description.

2) MOSS2007 does not search the keywords assigned to an Office document via Document Properties.

3) For Word, Excel and PowerPoint documents, if you search for a word that appears in the document’s description (aka the “Comments” field), then the description is displayed in the search results with the search term highlighted in bold.

4) For Word, Excel and PowerPoint documents, if you search for a word that only appears in the body of the document, then:

# for Office 2003 documents, a snippet from the body of the document is displayed in the search results, with the search term highlighted in bold. The description is not displayed, even when it exists.

# for Office 2007 documents, the description is displayed in the search results.

5) If you search for a term that only appears in the document’s “Keywords” field, then nothing is found in the search.

6) If an Office document has a title assigned in its Document Properties, then the title is used in the search results. If no title is specified, then the document’s filename (including the file extension) is used instead.

http://www.thismuchiknow.co.uk/?p=41

More From pstilgoe

• How to delete the Shared Service Provider in MOSS 2007
• Making the Contact Selector mandatory in an InfoPath form
• How to make the filter choices display by default in the DVWP

Ask pstilgoe To Recommend Your Posts

Havnt tried it but apparently works:

http://mysharepointblog.com/post/2007/02/Suppress-Microsoft-Office-Document-Authentication-Prompt-In-SharePoint-WSS-30.aspx

More From pstilgoe

• This page cannot be edited in SharePoint Designer
• SharePoint/MOSS Active Directory Self Service Web Part
• Sharepoint and JQuery

Ask pstilgoe To Recommend Your Posts