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;
        }
    }
}

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