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

If you have a Sharepoint list containing multiple views, one view shows all approved items and another view shows all items that are pending approval. You have an Infopath form where you have a dropdown control that you want to be populated with only ‘Approved’ list items from your Sharepoint list.

If you create your Infopath data connection & copy the URL to this list view as your source you will notice it still returns ALL items as opposed to your filtered view listing only the approved items.

The way round this is to create you data connection using an XML source, use this link to find out how to view your list data as XML and get the source URL – http://www.petestilgoe.com/2009/11/converting-a-sharepoint-list-into-xml/

Now you need to expand on this & add the particular view to the url to make the data connection get the filtered data for you.

Currently your URL looks something like this:
/_vti_bin/owssvr.dll?Cmd=Display&List={GUID}&XMLDATA=TRUE&noredirect=true

1) Now in your Sharepoint list click ‘Settings’ –> ‘List Settings’

2) Scroll down to the bottom & click on the view you want to use as your datasource

3) Now your URL contains the {List GUID} as well as the {View GUID}

eg. /_vti_bin/owssvr.dll?Cmd=Display&List=%7BD66DCB51%2D80B0%2D43AB%2DB80B%2DCD7D23E63B09%7D&View=%7B6D0CEF69%2DF899%2D40D0%2D86B2%2D6E01CC7D9CA1%7D&XMLDATA=TRUE

4) The URL is encoded so you will need to replace “%7B” with “{” and “%7D” with “}” and “%2D” with “-” so now your url will look something like

/_vti_bin/owssvr.dll?Cmd=Display&List={D66DCB51-80B0-43AB-B80B-CD7D23E63B09}&View={6D0CEF69-F899-40D0-86B2-6E01CC7D9CA1}&XMLDATA=TRUE

5) Use this url as your XML data source in Infopath and your drop down data will be filtered as per your chosen view.

More From pstilgoe

• Converting a Sharepoint List into XML | Sharepoint Consultant, Automation & Stuff
• How to auto populate InfoPath forms using the people picker
• Displaying the week number using a Sharepoint calculated column

Ask pstilgoe To Recommend Your Posts

Came accross an interesting solution on Linkedin to display most viewed Documents from a Document Library in a webpart. I havnt tried it myself but sounds like it would work:

1) Install LogParser on Web App Server (Sharepoint Index Server)

2) Setup a batch file with the LogParse query of your choice.
For example:
“C:\Program Files\Log Parser 2.2\logparser” -i:IISW3C -o:XML “SELECT TOP 10 REPLACE_CHR(cs-uri-stem,’+',’ ‘) AS sUrl, EXTRACT_FILENAME(cs-uri-stem) AS sName, EXTRACT_EXTENSION(cs-uri-stem) AS sExt, COUNT(*) AS [Total Hits] INTO MYFILE.XML FROM \\WFE1\C$\Windows\system32\LogFiles\W3SVC1\*.log, \\WFE2\C$\Windows\system32\LogFiles\W3SVC1\*.log WHERE TO_UPPERCASE(sUrl) Like ‘/SITES/SPECIALSITE%%’ AND TO_UPPERCASE( sExt ) IN (‘DOCX’;'PPTX’;'PNG’;'GIF’;'JPG’;'BMP’) AND TO_UPPERCASE(sUrl) Not Like ‘%%/_T/%%’ AND TO_UPPERCASE(sUrl) Not Like ‘%%/_W/%%’ GROUP BY sUrl,sName,sExt ORDER BY [Total Hits] DESC

3) This query code search only for some documents (docx, pptx, etc.) on the site ‘/sites/specialsite’ on the default web application and site collection W3SVC1. The logs are located on WFE1 and WFE2. The log parser result file is an XML file named MYFILE.XML.

4) Daily Schedule the execute of the batch and the upload of the xml file to a sharepoint document library

5) Create an XSLT file for presentation and upload to the sharepoint document library.

6) Finally show the XML with the XML WebPart with the xml and xslt files as the parameters.

1) Get your Sharepoint list into XML format

2) Set up a data connection using the above XML file as your datasource

3) Now you need to configure rules and filters on your list box. Let’s say that your list box has customer name and you wish to fill a field with the customer phone. When you set your rule, you would first select Set a field’s value then select the field where you want the phone number.

4) To filter for the correct phone number, select the formula button next to the Value field. In the formula window, select Insert Field or Group.

5) Select your secondary data source and the phone number field. Select the Filter Data button. Select Add.

6) In the first drop down, select the customer name field from your secondary data source. Leave the middle one set to Is equal to. For the third drop down, select the field on your form that is bound to the control with the customer name in it.

At this point, what you are basically telling the rule is: Go get me a phone number. I want it to belong to the same customer as the one I just selected in my List Box.

If your datasource is fairly static, when creating the data connection for the XML file in the Data Connection Wizard, ensure that the option to Include the data as a resource file in the form template or template part is selected. This will enhance the speed of your data queries to your datasource significantly.

Steps taken from Hilary Stoupa over @ InfopathDev.com

More From pstilgoe

• How to auto populate InfoPath forms using the people picker
• Converting a Sharepoint List into XML | Sharepoint Consultant, Automation & Stuff
• Import data from CSV and SQL to a SharePoint list

Ask pstilgoe To Recommend Your Posts

This easiest way is to use the URL below & insert your list GUID

http://servername/site/_vti_bin/owssvr.dll?Cmd=Display&List=*YOURUNIQUELISTID*&XMLDATA=TRUE

To get the GUID of the list you need to go into the list & click ‘Settings’ -> ‘List Settings’. Now if you look at your URL you will see something like:

/_layouts/listedit.aspx?List=%7B7B476F98%2D1075%2D4A6D%2D8F63%2D4C754B3F41FC%7D

The URL is encoded so you will need to replace “%7B” with “{” and “%7D” with “}” and “%2D” with “-” so the end result would look something like: {7B476F98-1075-4A6D-8F63-4C754B3F41FC}

How to create a XML test page for search results:

How to create a XML test page for search results