DEV Community

panchenko-dm-aspose
panchenko-dm-aspose

Posted on • Edited on

2 1

Export/Import Excel documents to MS Project in C#/.NET using Aspose.Tasks

Export MS Project data to XLSX

Sometimes there may be requirements to work with your project data in Microsoft Excel.
In simple scenarios, you can export project data using Aspose.Tasks with two lines of code (default settings will be used):

var project = new Project("Commercial Construction.mpp");
project.Save("Commercial Construction.xlsx", SaveFileFormat.XLSX);

The resulting spreadsheet will contain three sheets (for Task, Resource, and Assignments) with default columns.
If you need a specific set of columns the resulting spreadsheet can be customized in the following way:

var project = new Project("Commercial Construction.mpp");
var taskColumns = new List<ViewColumn>
{
new GanttChartColumn("Task Mode", 50, Field.TaskManual),
new GanttChartColumn("Id", 50, Field.TaskID),
new GanttChartColumn("Name", 100, Field.TaskName),
new GanttChartColumn("Start", 100, Field.TaskStart),
new GanttChartColumn("Finish", 100, Field.TaskFinish),
new GanttChartColumn("% Complete", 50, Field.TaskPercentComplete),
new GanttChartColumn("Task Info", 100, Field.TaskText4),
new GanttChartColumn("Resource Names", 100, Field.TaskResourceNames),
new GanttChartColumn("Predecessors", 50, Field.TaskPredecessors),
new GanttChartColumn("Additional Flags", 50, task => task.Get(Tsk.IsCritical) ? "Critical" : string.Empty)
};
List<ViewColumn> assignmentColumns = new List<ViewColumn>(5)
{
new AssignmentViewColumn("ID", 25, a => a.Get(Asn.Uid).ToString()),
new AssignmentViewColumn("Task name", 100, a => a.Get(Asn.Task).Get(Tsk.Name)),
new AssignmentViewColumn("Resource name", 100, a => a.Get(Asn.Resource).Get(Rsc.Name)),
new AssignmentViewColumn("Work", 50, a => a.Get(Asn.Work).ToString()),
new AssignmentViewColumn("Duration", 75, a => a.Get(Asn.Task) == null ? string.Empty : a.Get(Asn.Task).Get(Tsk.Duration).ToString())
};
var options = new XlsxOptions
{
View = new ProjectView(taskColumns),
PresentationFormat = PresentationFormat.GanttChart,
AssignmentView = new ProjectView(assignmentColumns),
};
project.Save("Commercial Construction2.xlsx", options);

The resulting spreadsheet when opened in Microsoft Excel:
Alt Text

Import project data from XLSX

There is no API for importing XLSX to project data at the moment, but you can use OpenXml to implement an importing routine specific to your data. Here is a simplified example of how it can be done (requires ‘DocumentFormat.OpenXml‘ NuGet package ):

Project project = new Project();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("Commercial Construction.xlsx", false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart tasksSheet = workbookPart.WorksheetParts.First();
SheetData sheetData = tasksSheet.Worksheet.Elements<SheetData>().First();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
{
continue;
}
var taskProperties = r.Elements<Cell>().Select(c => GetCellValue(workbookPart, c)).ToArray();
Task task = project.RootTask.Children.Add();
task.Set(Tsk.Id, int.Parse(taskProperties[0]));
task.Set(Tsk.IsActive, new NullableBool(taskProperties[1] == "Yes"));
task.Set(Tsk.IsManual, new NullableBool(taskProperties[2] != "Auto Scheduled"));
task.Set(Tsk.Name, taskProperties[3]);
task.Set(Tsk.Duration, Duration.Parse(project, taskProperties[4]));
task.Set(Tsk.Start, DateTime.Parse(taskProperties[5]));
task.Set(Tsk.Finish, DateTime.Parse(taskProperties[6]));
task.Set(Tsk.OutlineLevel, int.Parse(taskProperties[8]));
var notes = taskProperties[9];
if (!string.IsNullOrEmpty(notes))
{
task.Set(Tsk.NotesText, taskProperties[9]);
}
}
}
project.Save("output.mpp", SaveFileFormat.MPP);
Retry later

Top comments (1)

Collapse
 
srobert2 profile image
srobert2

Thank you for this article,
very interresting indeed.
An other tip: if you want to import actual work at assignment level you can also use Project Timesheet Pro


This is for desktop versions only
cheers,
Stéphane

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more