Publishing Excel 2007 Workbooks to SharePoint Server 2007

Summary: Learn how to publish Microsoft Office Excel 2007 Workbooks to Microsoft Office SharePoint Server 2007 programmatically. (12 printed pages)

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office SharePoint Server 2007

Joel Krist, iSoftStone

July 2009

You can use Microsoft Office Excel 2007 to publish workbooks to Excel Services. At publishing time, you can specify which items Excel Services displays in the browser as well as the workbook parameters that tell Excel Services which cells are editable. The sample code in this visual how-to article shows you how to create a workbook that has a visible worksheet that uses named ranges as workbook parameters. The code uses the HTTP PUT method to publish the workbook to a SharePoint document library that is an Excel Services trusted file location.

Watch the Video Length: 06:21 | Size: 8.37 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Sample Code

To illustrate how to publish Excel 2007 workbooks to SharePoint Server 2007 programmatically, this section walks through the following steps:

1. Creating a Windows console application solution in Visual Studio 2008.

2. Adding a reference to the Excel 2007 Primary Interop Assembly.

3. Importing the Excel 2007 Primary Interop Assembly namespace.

4. Adding the sample code to the solution.

Creating a Windows Console Application in Visual Studio 2008

This visual how-to article uses a Windows console application to provide the framework for the sample code. However, you could use the same approach that is illustrated here with other application types as well.

To create a Windows Console Application in Visual Studio 2008

1. Start Microsoft Visual Studio 2008.

2. On the File menu, point to New, and then click Project.

3. In the New Project dialog box select the Visual Basic Windows or Visual C# Windows type in the Project types pane.

4. Select Console Application in the Templates pane.

5. Name the project PublishWorkbook and choose to create a new solution.

   Figure 1. Create new solution in the New Project dialog box

   

    

6. Click OK to create the solution.

Adding a reference to the Excel 2007 Primary Interop Assembly

This visual how-to article uses the Excel 2007 Primary Interop Assembly (PIA) to create and manipulate an Excel workbook. The Excel 2007 Primary Interop Assembly exposes the Excel 2007 object model to managed code and is installed with Excel 2007. It is also available as a download as part of the 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies.

To add a Reference to the Excel 2007 Primary Interop Assembly

1. Select Visual Studio Project from the menu, and then click Add Reference.

2. Select version 12.0.0.0 of the Microsoft.Office.Interop.Excel component and then click OK to add the reference.

   Figure 2. Add the reference to the 12.0.0.0 interop assembly

   

Importing the Excel 2007 Primary Interop Assembly Namespace

After you import the Excel 2007 PIA namespace, your code can work with the objects and the types that are defined in the namespace without having to specify the fully qualified namespace path each time. The following sample code also uses classes that are defined in the System.IO and System.Net class to publish the workbook, so those namespaces are imported as well.

For a Visual Basic project, add the following line to the top of the Module1.vb source file.

Imports System.Net
Imports System.IO
Imports Microsoft.Office.Interop.Excel

For a C# project, add the following line to the top of the Program.cs source file below the other using statements generated by Visual Studio.

using System.Net;
using System.IO;
using Microsoft.Office.Interop.Excel;

Adding the Sample Code to the Solution

For a Visual Basic project, replace the contents of the Module1 module in the Module1.vb source file with the following code.

Sub Main()
    ' Declare variables that hold references to Excel objects
    Dim excelApplication As ApplicationClass = Nothing
    Dim excelWorkBook As Workbook = Nothing
    Dim excelWorkSheet As Worksheet = Nothing

    ' Declare helper variables.
    ' Change the sharePointPath variable to point to a list or
    ' document library on a SharePoint site running Excel Services.
    ' Make sure the list or document library has been configured as
    ' an Excel Services trusted location so published workbooks
    ' can be viewed in the web browser.
    Dim workBookName As String = "SharePointPublish.xlsx"
    Dim workSheetName As String = "Published Sheet"

    Dim localPath As String = "C:\Temp\"
    Dim sharePointPath As String = _
        "http://<Server>/<DocumentLibrary>/"

    Try
        Try
            ' Create an instance of Excel
            excelApplication = New ApplicationClass()

            ' Create a workbook and set up the default sheets.
            excelWorkBook = excelApplication.Workbooks.Add()

            ' Hide the default sheets 2 and 3 so they are not visible
            ' in the published workbook.
            excelWorkSheet = excelWorkBook.Worksheets(3)
            excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden
            excelWorkSheet = Nothing

            excelWorkSheet = excelWorkBook.Worksheets(2)
            excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden
            excelWorkSheet = Nothing

            ' Set the default sheet 1 as visible and give it a name.
            excelWorkSheet = excelWorkBook.Worksheets(1)
            excelWorkSheet.Visible = XlSheetVisibility.xlSheetVisible
            excelWorkSheet.Name = workSheetName

            ' Create 3 named ranges at workbook scope.
            excelWorkBook.Names.Add("CellA1", _
                "='Published Sheet'!$A$1", True)
            excelWorkBook.Names.Add("CellA2", _
                "='Published Sheet'!$A$2", True)
            excelWorkBook.Names.Add("CellA3", _
                "='Published Sheet'!$A$3", True)

            ' Put some default values in the named range cells and mark
            ' the cells as workbook parameters.
            For Each name As Name In excelWorkBook.Names
                If name.Name = "CellA1" Then
                    name.RefersToRange(1, 1) = "Cell A1"
                    name.WorkbookParameter = True
                ElseIf name.Name = "CellA2" Then
                    name.RefersToRange(1, 1) = "Cell A2"
                    name.WorkbookParameter = True
                ElseIf name.Name = "CellA3" Then
                    name.RefersToRange(1, 1) = "Cell A3"
                    name.WorkbookParameter = True
                End If
            Next

            ' Save a local copy of the workbook.
            If Not excelWorkBook Is Nothing Then
                excelApplication.DisplayAlerts = False
                excelWorkBook.SaveAs(localPath + workBookName,,,, _
                    ,, XlSaveAsAccessMode.xlNoChange)
                excelApplication.DisplayAlerts = True
            End If

        Catch ex1 As Exception
            Throw ex1
        Finally
            ' Release the references to the workbook/worksheet.
            excelWorkSheet = Nothing
            excelWorkBook = Nothing

            ' Close the ApplicationClass object.
            If Not excelApplication Is Nothing Then
                excelApplication.Quit()
                excelApplication = Nothing
            End If

            ' Garbage collection
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try

        ' Publish the workbook to SharePoint.
        PublishWorkbook(localPath + workBookName, _
            sharePointPath + workBookName)

    Catch ex2 As Exception
        Console.WriteLine("Exception: {0}", ex2.Message)
    End Try

End Sub

Sub PublishWorkbook(ByVal LocalPath As String, _
                    ByVal SharePointPath As String)

    Dim response As WebResponse = Nothing

    Try
        ' Create a PUT Web request to upload the file.
        Dim request As WebRequest = WebRequest.Create(SharePointPath)

        request.Credentials = CredentialCache.DefaultCredentials
        request.Method = "PUT"

        ' Allocate a 1K buffer to transfer the file contents.
        ' The buffer size can be adjusted as needed depending on
        ' the number and size of files being uploaded.
        Dim buffer() As Byte = New Byte(1023) {}

        ' Write the contents of the local file to the request stream.
        Using stream As Stream = request.GetRequestStream()
            Using fsWorkbook As FileStream = File.Open(LocalPath, _
                FileMode.Open, FileAccess.Read)
                Dim i As Integer = fsWorkbook.Read(buffer, 0, _
                    buffer.Length)

                Do While i > 0
                    stream.Write(buffer, 0, i)
                    i = fsWorkbook.Read(buffer, 0, buffer.Length)
                Loop

            End Using
        End Using

        ' Make the PUT request.
        response = request.GetResponse()
    Catch ex As Exception
        Throw ex
    Finally
        response.Close()
    End Try

End Sub

For a C# project, replace the contents of the Program class in the Program.cs source file with the following code.

static void Main(string[] args)
{
    // Declare variables that hold references to Excel objects.
    ApplicationClass excelApplication = null;
    Workbook excelWorkBook = null;
    Worksheet excelWorkSheet = null;

    // Declare helper variables.
    // Change the sharePointPath variable to point to a list or
    // document library on a SharePoint site running Excel Services.
    // Make sure the list or document library has been configured as
    // an Excel Services trusted location so published workbooks
    // can be viewed in the web browser.
    string workBookName = "SharePointPublish.xlsx";
    string workSheetName = "Published Sheet";

    string localPath = @"C:\Temp\";
    string sharePointPath = "http://<Server>/<DocumentLibrary>/";
    object useDefault = Type.Missing;

    try
    {
        try
        {
            // Create an instance of Excel
            excelApplication = new ApplicationClass();

            //Create a workbook and set up the default sheets.
            excelWorkBook =
                excelApplication.Workbooks.Add(useDefault);

            // Hide the default sheets 2 and 3 so they are not visible
            // in the published workbook.
            excelWorkSheet =
                (Worksheet)(excelWorkBook.Worksheets[3]);
            excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden;
            excelWorkSheet = null;

            excelWorkSheet = (Worksheet)(excelWorkBook.Worksheets[2]);
            excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden;
            excelWorkSheet = null;

            // Set the default sheet 1 as visible and give it a name.
            excelWorkSheet = (Worksheet)(excelWorkBook.Worksheets[1]);
            excelWorkSheet.Visible = XlSheetVisibility.xlSheetVisible;
            excelWorkSheet.Name = workSheetName;

            // Create 3 named ranges at workbook scope.
            excelWorkBook.Names.Add("CellA1",
                "='Published Sheet'!$A$1", true, useDefault,
                useDefault, useDefault, useDefault, useDefault,
                useDefault, useDefault, useDefault);

            excelWorkBook.Names.Add("CellA2",
                "='Published Sheet'!$A$2", true, useDefault,
                useDefault, useDefault, useDefault, useDefault,
                useDefault, useDefault, useDefault);

            excelWorkBook.Names.Add("CellA3",
                "='Published Sheet'!$A$3", true, useDefault,
                useDefault, useDefault, useDefault, useDefault,
                useDefault, useDefault, useDefault);

            // Put some default values in the named range cells and
            // mark the cells as workbook parameters.
            foreach (Name name in excelWorkBook.Names)
            {
                if (name.Name == "CellA1")
                {
                    name.RefersToRange[1, 1] = "Cell A1";
                    name.WorkbookParameter = true;
                }
                else if (name.Name == "CellA2")
                {
                    name.RefersToRange[1, 1] = "Cell A2";
                    name.WorkbookParameter = true;
                }
                else if (name.Name == "CellA3")
                {
                    name.RefersToRange[1, 1] = "Cell A3";
                    name.WorkbookParameter = true;
                }
            }

            // Save a local copy of the workbook.
            if (excelWorkBook != null)
            {
                excelApplication.DisplayAlerts = false;

                excelWorkBook.SaveAs(localPath + workBookName,
                    useDefault, useDefault, useDefault, useDefault,
                    useDefault, XlSaveAsAccessMode.xlNoChange,
                    useDefault, useDefault, useDefault, useDefault,
                    useDefault);

                excelApplication.DisplayAlerts = true;                       
            }
        }
        catch (Exception ex1)
        {
            throw ex1;
        }
        finally
        {
            // Release the references to the workbook/worksheet.
            excelWorkSheet = null;
            excelWorkBook = null;

            // Close the ApplicationClass object.
            if (excelApplication != null)
            {
                excelApplication.Quit();
                excelApplication = null;
            }

            // Garbage collection
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        // Publish the workbook to SharePoint.
        PublishWorkbook(localPath + workBookName,
            sharePointPath + workBookName);
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception: {0}", ex.Message);
    }
}

static void PublishWorkbook(string LocalPath, string SharePointPath)
{
    WebResponse response = null;

    try
    {
        // Create a PUT Web request to upload the file.
        WebRequest request = WebRequest.Create(SharePointPath);

        request.Credentials = CredentialCache.DefaultCredentials;
        request.Method = "PUT";

        // Allocate a 1K buffer to transfer the file contents.
        // The buffer size can be adjusted as needed depending on
        // the number and size of files being uploaded.
        byte[] buffer = new byte[1024];

        // Write the contents of the local file to the
        // request stream.
        using (Stream stream = request.GetRequestStream())
        using (FileStream fsWorkbook = File.Open(LocalPath,
            FileMode.Open, FileAccess.Read))
        {
            int i = fsWorkbook.Read(buffer, 0, buffer.Length);

            while (i > 0)
            {
                stream.Write(buffer, 0, i);
                i = fsWorkbook.Read(buffer, 0, buffer.Length);
            }
        }

        // Make the PUT request.
        response = request.GetResponse();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        response.Close();
    }
}

When you build and run the sample code, it creates a local copy of a workbook named SharePointPublish.xlsx that is located in the C:\Temp folder. To change the name or the location of the local workbook, modify the sample code and change the values of the localPath and workBookName variables.

The code currently has a placeholder for the SharePoint site and document library to use to publish the workbook. Modify the SharePointPath variable to specify a working SharePoint site and document library. If you want to open the published workbook in a Web browser via Excel Web Access, you must publish the workbook in a location that Excel Services designates as a trusted file location.

Read It

The sample code in this visual how-to article illustrates how to publish an Excel 2007 workbook to a SharePoint document library that Excel Services has designated as a trusted location. The code first creates a workbook, hides two of the default worksheets so that they are not visible when the workbook is viewed with Excel Web Access, and then sets the name of the remaining worksheet.

' Create a workbook and set up the default sheets.
excelWorkBook = excelApplication.Workbooks.Add()

' Hide the default sheets 2 and 3 so they are not visible
' in the published workbook.
excelWorkSheet = excelWorkBook.Worksheets(3)
excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden
excelWorkSheet = Nothing

excelWorkSheet = excelWorkBook.Worksheets(2)
excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden
excelWorkSheet = Nothing

' Set the default sheet 1 as visible and give it a name.
excelWorkSheet = excelWorkBook.Worksheets(1)
excelWorkSheet.Visible = XlSheetVisibility.xlSheetVisible
excelWorkSheet.Name = workSheetName

//Create a workbook and set up the default sheets.
excelWorkBook = 
    excelApplication.Workbooks.Add(useDefault);

// Hide the default sheets 2 and 3 so they are not visible
// in the published workbook.
excelWorkSheet =
    (Worksheet)(excelWorkBook.Worksheets[3]);
excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden;
excelWorkSheet = null;

excelWorkSheet = (Worksheet)(excelWorkBook.Worksheets[2]);
excelWorkSheet.Visible = XlSheetVisibility.xlSheetHidden;
excelWorkSheet = null;

// Set the default sheet 1 as visible and give it a name.
excelWorkSheet = (Worksheet)(excelWorkBook.Worksheets[1]);
excelWorkSheet.Visible = XlSheetVisibility.xlSheetVisible;
excelWorkSheet.Name = workSheetName;

The code then creates three named ranges for three cells in the visible worksheet, sets the values of the cells, and marks the cells as workbook parameters so that they are editable cells via parameters when a user views the workbook in a Web browser via Excel Web Access. The new workbook is then saved locally.

' Create 3 named ranges at workbook scope.
excelWorkBook.Names.Add("CellA1", _
    "='Published Sheet'!$A$1", True)
excelWorkBook.Names.Add("CellA2", _
    "='Published Sheet'!$A$2", True)
excelWorkBook.Names.Add("CellA3", _
    "='Published Sheet'!$A$3", True)

' Put some default values in the named range cells and mark
' the cells as workbook parameters.
For Each name As Name In excelWorkBook.Names
    If name.Name = "CellA1" Then
        name.RefersToRange(1, 1) = "Cell A1"
        name.WorkbookParameter = True
    ElseIf name.Name = "CellA2" Then
        name.RefersToRange(1, 1) = "Cell A2"
        name.WorkbookParameter = True
    ElseIf name.Name = "CellA3" Then
        name.RefersToRange(1, 1) = "Cell A3"
        name.WorkbookParameter = True
    End If
Next

// Create 3 named ranges at workbook scope.
excelWorkBook.Names.Add("CellA1",
    "='Published Sheet'!$A$1", true, useDefault,
    useDefault, useDefault, useDefault, useDefault,
    useDefault, useDefault, useDefault);

excelWorkBook.Names.Add("CellA2",
    "='Published Sheet'!$A$2", true, useDefault,
    useDefault, useDefault, useDefault, useDefault,
    useDefault, useDefault, useDefault);

excelWorkBook.Names.Add("CellA3",
    "='Published Sheet'!$A$3", true, useDefault,
    useDefault, useDefault, useDefault, useDefault,
    useDefault, useDefault, useDefault);

// Put some default values in the named range cells and
// mark the cells as workbook parameters.
foreach (Name name in excelWorkBook.Names)
{
    if (name.Name == "CellA1")
    {
        name.RefersToRange[1, 1] = "Cell A1";
        name.WorkbookParameter = true;
    }
    else if (name.Name == "CellA2")
    {
        name.RefersToRange[1, 1] = "Cell A2";
        name.WorkbookParameter = true;
    }
    else if (name.Name == "CellA3")
    {
        name.RefersToRange[1, 1] = "Cell A3";
        name.WorkbookParameter = true;
    }
}

Finally, the code uses a helper method named PublishWorkbook that uses the HTTP PUT method to publish the local workbook to the SharePoint site.

Sub PublishWorkbook(ByVal LocalPath As String, _
                    ByVal SharePointPath As String)

    Dim response As WebResponse = Nothing

    Try
        ' Create a PUT Web request to upload the file.
        Dim request As WebRequest = WebRequest.Create(SharePointPath)

        request.Credentials = CredentialCache.DefaultCredentials
        request.Method = "PUT"

        ' Allocate a 1K buffer to transfer the file contents.
        ' The buffer size can be adjusted as needed depending on
        ' the number and size of files being uploaded.
        Dim buffer() As Byte = New Byte(1023) {}

        ' Write the contents of the local file to the request stream.
        Using stream As Stream = request.GetRequestStream()
            Using fsWorkbook As FileStream = File.Open(LocalPath, _
                FileMode.Open, FileAccess.Read)
                Dim i As Integer = fsWorkbook.Read(buffer, 0, _
                    buffer.Length)

                Do While i > 0
                    stream.Write(buffer, 0, i)
                    i = fsWorkbook.Read(buffer, 0, buffer.Length)
                Loop

            End Using
        End Using

        ' Make the PUT request.
        response = request.GetResponse()
    Catch ex As Exception
        Throw ex
    Finally
        response.Close()
    End Try

End Sub

static void PublishWorkbook(string LocalPath, string SharePointPath)
{
    WebResponse response = null;

    try
    {
        // Create a PUT Web request to upload the file.
        WebRequest request = WebRequest.Create(SharePointPath);

        request.Credentials = CredentialCache.DefaultCredentials;
        request.Method = "PUT";

        // Allocate a 1K buffer to transfer the file contents.
        // The buffer size can be adjusted as needed depending on
        // the number and size of files being uploaded.
        byte[] buffer = new byte[1024];

        // Write the contents of the local file to the
        // request stream.
        using (Stream stream = request.GetRequestStream())
        using (FileStream fsWorkbook = File.Open(LocalPath,
            FileMode.Open, FileAccess.Read))
        {
            int i = fsWorkbook.Read(buffer, 0, buffer.Length);

            while (i > 0)
            {
                stream.Write(buffer, 0, i);
                i = fsWorkbook.Read(buffer, 0, buffer.Length);
            }
        }

        // Make the PUT request.
        response = request.GetResponse();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        response.Close();
    }
}

Explore It

• Excel 2007 Sample: Publishing Excel Workbooks to SharePoint Server

• Introduction to Excel Services and Excel Web Access

• Add a trusted file location

• WebRequest Class

• WebRequest.Method Property

• HTTP Method Definitions

• Excel Developer Portal