Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C#
Article

Excel Report from Microsoft Project

Rate me:
Please Sign up or sign in to vote.
4.84/5 (12 votes)
8 Jan 20077 min read 121.1K   4.5K   52   15
Extract project data from Microsoft Project and import it into Microsoft Excel.

Sample Image - maximum width is 600 pixels

Introduction

Microsoft Project files contain much information that is needed for project reports, but the Project application does not contain all data that is needed for projects reports, such as an open item list, weekly summary, etc. Through .NET Interoperability, the data from the Microsoft Project file can be exported to other formats and combined with other data to generated comprehensive project reports. For this example I am concentrating on demonstrating how to export the data from the Microsoft Project not how to use the data, thus I am creating a simple Excel file that summarizes the project's activities for the current week, displays the tasks that are late, and displays the tasks that will be starting.

For a more detailed discussion on project reporting, refer to the discussion at the end of this article.

Background

This article uses the .NET Interop functionality and the Excel XML format. Though these technologies are used, this article does not go into detail explaining these technologies as there are many other articles that do that. This article focuses on exporting the Microsoft Project data.

Using the code

The MainForm class is the application and a good place to start a top down evaluation of the code. The MainForm class does not contain any functon other that being a GUI. All the functionality is contained in the Project class and the Task class.

The Task class contains the work breakdown structure id, the name, the start data, and the finish date of the Microsoft Project task. The Microsoft Project task object contains tons more data, but for this example, these pieces are all that we are interested in. The Task class was created for two reasons: (1) I wanted to extract the data from the Microsoft Project file as fast as possible so that I can close it, thus I needed to copy the data into my own class, and (2) per proper OO design, I wanted the task to be able to format its own data for the Excel XML. The Task class is basically a data storage class--it does not manipulate the data. The only item to note about the Task class is that for Excel XML, the DateTime must be formatted as YYYY-MM-DDThh.mm.ss.mmmm and this is handled through the StartDateExcelFormat and FinishDateExcelFormat properties.

The use of the Task class is very simple. The Project class just calls the constructor passing the Microsoft Project task object, uses the StartData and FinishDate properties to determine if the task is current, late, or future, and uses the ToXml method to generate the Excel XML.

The Project class is the work horse class--this class is the key to the application, but again it was designed for easy use in that the MainForm class, which only uses three Project class methods: Load, Evaluate, and Save. The Project class complies with good OO design by having the class manipulate its own data.

All the magic with the Microsoft Project data occurs in the Load method of Project:

C#
MSProject.ApplicationClass app = null;
string retVal = "";
Initialize();
                        
try {
    // execute the Microsoft Project Application
    app = new MSProject.ApplicationClass();
    // Do not display Microsoft Project
    app.Visible = false;
    // open the project file.
    if(app.FileOpen(fileName, true, Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing, 
        Type.Missing, Type.Missing, 
        MSProject.PjPoolOpen.pjPoolReadOnly, Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing)) {
        // go through all the open projects--there should 
        // only be one
        foreach(MSProject.Project proj in app.Projects) {
            // go through all the tasks in the project
            foreach(MSProject.Task task in proj.Tasks) {
                // we are only interested in tasks that do 
                // not have any child tasks--these are the 
                // tasks that we want to track.
                if(task.OutlineChildren.Count == 0) {
                    // copy the Microsoft Project Task to 
                    // our task and add it to our task list
                    tasks.Add(new PrjXlsRpt.Task(task));
                }
            }
        }
    } else {
        retVal = "The MS Project file " + fileName + 
                 " could not be opened.";
    }
} catch (Exception ex) {
    retVal =  "Could not process the MS Project file " + 
        fileName + "." + System.Environment.NewLine +  
        ex.Message + System.Environment.NewLine + 
        ex.StackTrace;
}

// close the application if is was opened.
if(app != null) {
    app.Quit(MSProject.PjSaveType.pjDoNotSave);
}
return retVal;

As the example above demonstrates the steps are: (1) Open the Microsoft Project Application, (2) Open the Microsoft Project File, (3) for the Project go through all the tasks and do what ever you want to them, and (4) close the application. The only two items left to discuss are: Adding the Microsoft Project COM components and Creating the Excel file.

Adding Microsoft Project COM Components

The Microsoft Project data is accessed using Microsoft Project COM components. Wrappers to these COM components need to be created. Microsoft Visual Studio 2003 provides an easy method for including non-managed COM components into your managed code. Microsoft Project must be loaded on your development system for you to follow these instructions. Microsoft Project 2003 and Visual Studio 2003 are used in this example.

  1. From the main menu select Project > Add Reference.
  2. Go to the "COM" tab. Scroll down and select "Microsoft Project 11.0 Object Library"
  3. Click Select and click OK

When you go to Solution Explorer, you should see Microsoft.Office.Core, MSHTML, MSProject, System.XML, VBIDE under the References folder. The next step is to add the using statement to your code. For the using statement I use the assignment format because for library specific code I prefer to use the namespace part of the class name so that I know where the class is located. By using the assignment format of the using statement I can make the code more readable and still indicate the location of the class.

C#
using MSProject = Microsoft.Office.Interop.MSProject;

This is all that is required to add the Interop functionality to your project.

Creating the Excel File

The Excel report file could be created using the COM Interop like the Microsoft Project part of this example, but whenever possible I try not to have my programs rely on the software installed on a user's PC. I could also use the Excel ODBC driver, but that means that I would have to have a properly formatted Excel file. To keep the implementation easy for me, I used Excel XML to create the Excel file.

The easiest way of working with Excel XML is to create the file in Excel, save the file in XML, and extract the pieces that you will use as strings in your program. If you do this frequently like I do, you will have a tool that will convert the XML text into code. Having such a tool saves a lot of time, but if you do not have a tool, you should be able to make one quick.

The Excel XML that is created in the application is done through text strings. I do not use the System.Xml classes because that takes too long. These strings are located in the XmlPart1, XmlPart2, XmlPart3, and XmlPart4 methods located in the XML region of the Project class.

One important part to note is that for the <Table> element, remove the ss:ExpandedRowCount parameter. The row count will grow based upon the number of tasks and we really do not need to keep track of this. If this parameter is removed, the XML code is still functional.

Project Reports Discussion

I am a project manager and for my customers I need to submit weekly project status reports. These reports change from customer to customer but normally contain schedule information, open item information, budgetary information, and a summary of what was accomplished during the week. This information is scattered across multiple systems/files with Microsoft Project being used to manage the schedule. Our customers do not want to spend too much time evaluating the status of the project. They want these project reports to be concise such that they can determine the status of the project in about 5 minutes. Submitting just the Microsoft Project file is unacceptable because depending upon the size of the project, it can take more than 5 minutes to just review the schedule. Also, these Project Reports need to be in a format that can be distributed to people who do not have Microsoft Project. So for us, there is a need to export the data from Microsoft Project and use it else where.

The example in this article is basic--extremely basic, but it is not supposed to be an application in itself but rather a demonstration on how to export the data from Microsoft Project and use it within your application. Microsoft Project does provide methods for exporting data, but the goal here is not to have the user complete many steps to get the data, but have the software perform this repetitive task. In my company we suck in data from Microsoft Project, an Open Item database (Access), and a time card system (SQL Server) into an Access database using a one click action. This database contains forms for authoring summary data and contains a report to pull all the data together. The report is printed to a PDF file and emailed to our customer. This application is trivial, but it saves our project managers about 30 minutes per report per project--a good cost savings to our company.

I concentrated on discussing the use of Microsoft Project data for project reports, but it can be used anywhere else in your organization. We also use Microsoft Project data to feed our estimating tools. What ever you use the Microsoft Project data for, I hope that this article was a valuable starting point.

Good luck with your projects.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Architect
United States United States
Michael Carey is the Head of Development for an Automation Integrator in Philadelphia, PA. Michael specializes in Batch Automation, Process History, and Factory to Enterprise Integration.

Comments and Discussions

 
GeneralThanks Pin
ashavi9-Dec-13 22:40
professionalashavi9-Dec-13 22:40 
GeneralConvert from Microsoft Project MPP to Microsoft Excel XLS format Pin
Kosh201019-Jul-12 12:36
Kosh201019-Jul-12 12:36 
Thank you for sharing your code example. I've reviewed your code and was wondering how to export the fields to an Excel XLS format, instead of XML? I am trying to replicate the approach using a Console application in which the source MPP file is defined as an argument and a generic XLS file is generated.

The ultimate objective is to apply the console application in a SSIS Execute Process Task to pull the records from multiple Microsoft Project MPP files as the first step in an automated process of exporting them to a SQL Server database each week.

Thanks.
GeneralMy vote of 5 Pin
Manoj Kumar Choubey7-Feb-12 18:57
professionalManoj Kumar Choubey7-Feb-12 18:57 
Questionhave a problem Pin
askez25-Oct-11 10:49
askez25-Oct-11 10:49 
GeneralMy vote of 4 Pin
guanx16-Aug-10 22:57
guanx16-Aug-10 22:57 
GeneralSuper Material Pin
Udayakumar Venkataravanappa29-Sep-09 5:50
Udayakumar Venkataravanappa29-Sep-09 5:50 
GeneralExtracting a field from Microsoft Project in Cash Flow [modified] Pin
gprinc29-Apr-09 0:21
gprinc29-Apr-09 0:21 
GeneralChanging the project work timings using .net Pin
mahesvs7-Apr-09 19:18
mahesvs7-Apr-09 19:18 
QuestionHow can I access projects directly? Pin
serious7-Feb-08 3:15
serious7-Feb-08 3:15 
GeneralRe: How can I access projects directly? Pin
zam6648-Feb-08 4:50
zam6648-Feb-08 4:50 
GeneralExcellent Program! Pin
Fasike16-Oct-07 13:59
Fasike16-Oct-07 13:59 
GeneralS-Curve Pin
oamart2-Oct-07 2:35
oamart2-Oct-07 2:35 
GeneralVersion Pin
EyalSherman5-Feb-07 5:31
EyalSherman5-Feb-07 5:31 
AnswerRe: Version Pin
zam6645-Feb-07 8:29
zam6645-Feb-07 8:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.