Gantt chart Excel-lence

Importing/exporting Gantt chart data from/to Microsoft Excel®

Photo by Volodymyr Hryshchenko on Unsplash

Excel? But why?

When you say data, you most likely say Excel. It’s just the way it is, at least for now, during the first decades of the 21st century.

Even if there are dedicated apps for many types of jobs around the world, people often go back to use plain Microsoft Office tools instead. And they do it for a reason: being very generic, yet so powerful, they are indeed very good at customizing the output for the user — customizing it to the extreme, in ways that specifically crafted apps won’t ever be able to.

It’s therefore obviously so important that developers accept this as a fact and embrace it, ensuring their apps are able to import and export data from and to end users’ Excel sheets with ease. Be it with simple copy/paste operations directly in your table views, or by using formal Open and Save file dialogs, as needed.

(This article will focus on the second option — working with Excel files — since the former simply requires generating and parsing tab delimited values out of your content — and that’s very easy to program and often even supported by user interface components that you make use of, themselves.)

Before, at DlhSoft

As you probably know if you follow our account, we at DlhSoft are building Gantt chart user interface components that you, developers, can easily integrate into your project management or resource scheduling apps. They are pretty much generic, and many of them do support built-in copy-pasting of tab delimited value content (and/or comma separated too), so your end users can directly copy-paste data rows to and from their Excel sheets into your app.

Gantt Charts - DlhSoft

If you want your users to be able to generate new Excel files out of your app or to read existing files instead, i.e. exporting and importing data to and from specifically crafted Excel templates, things were mostly up to you.

Introducing DlhSoft Excel adapter

They were; until May 2021. Here comes DlhSoft’s project management Excel adapter to the rescue! Simple tool, important features:

Available as NuGet package for .NET Framework 4+, .NET Core 2+, and .NET 5 (through .NET Standard 2.0), named DlhSoft.ProjectManagementFramework.Xlsx and offered as well as a MIT license based open source project on GitHub so you can modify everything if needed, the library can help you with both exporting and importing task data (names, start and finish times, completion, predecessors, assignments, and even costs) to and from Excel sheet files — with .xlsx extension — directly from your .NET app or Web based server.

DlhSoft.ProjectManagementFramework.Xlsx 1.0.3

DlhSoftTeam/ProjectManagementXlsx

The end user doesn’t even need to have Excel installed — we’re not going interop!

Leveraging Microsoft Project XML format

Because our Gantt chart and project management components and tools already offer Project XML schema based serialization as their main data port feature, i.e. supporting importing and exporting Microsoft Project XML files, we’ve built the new adapter to just handle creation of Excel sheets out of XML content, and vice-versa too.

Project Elements and XML Structure

This means you can use this tool with most of the DlhSoft Gantt chart components, be them WPF based, ASP .NET based, or even pure JavaScript client side interface that you manage in conjunction with a .NET based Web server.

That is, with all components that offer Microsoft Project XML import-export support.

Reusing Project Management Framework

Internally, the library leverages yet another DlhSoft product: Project Management Framework. That already offered what we needed to convert Project XML into task objects and the other way around.

Project Management Framework

You will therefore need a license for this tool as well, but do not worry: if you already have a development license for Gantt Chart Light Library (WPF), Gantt Chart Web Library (ASP .NET), or Gantt Chart Hyper Library (JavaScript), you’re entitled to obtain a free Project Management Framework license as well, directly from our Web site, now!

Converting Project XML to/from Excel sheets: the core

So, how you’d use this, you will ask. It’s very easy, once you’ve set the package up. Here is a full converter sample too.

DlhSoftTeam/ProjectManagementXlsx

To read specifically templated Excel files (see below) into Project XML content:

var excelBytes = File.ReadAllBytes(sourceXlsxFilePath);
var projectXml = ProjectManagementXlsx.Adapter.GetProjectXml(excelBytes);

And to create Excel files out of Project XML content:

var excelBytes = ProjectManagementXlsx.Adapter.GetExcelBytes(projectXml);                File.WriteAllBytes(targetXlsxFilePath, excelBytes);

WPF sample app — Gantt chart to/from Excel

Here is a full WPF sample app with C# source code, too.

It’s targeting .NET 5, but we have a .NET Framework 4.7 version if that’s what you prefer.

Don’t expect it to be very complex, though. It’s really just a small Get started.

It loads a bunch of tasks, and allows end users to load and save Excel files from data managed by a GanttChartDataGrid component instance from DlhSoft Project Data Light Library.

DlhSoftTeam/XlsxGanttChart

Gantt Chart Light Library

You can see in MainWindow.xaml.cs file how simple the code you need is. This is to save (for loading data back, the code is similarly short):

private void SaveButton_Click(object sender, RoutedEventArgs e)
{
  var dialog = new SaveFileDialog 
  { 
    Filter = "Excel files|*.xlsx", DefaultExt = ".xlsx", 
    Title = "Save Excel file"
  };
  if (dialog.ShowDialog() == true)
  {
    var projectXml = GanttChartDataGrid.GetProjectXml();
    var excelBytes = ProjectManagementXlsx.Adapter.GetExcelBytes(projectXml);
    using (var stream = dialog.OpenFile())
    {
      stream.Write(excelBytes, 0, excelBytes.Length);
    }
  }
}

Built-in Excel sheet template details

Excel sheets generated (and supported at import time) by the tool need to respect some conditions, of course.

Specifically, task data is assumed to always be in the first sheet (if more exists within the file), and the first row of the sheet should always contain header values (i.e. it is skipped). Then, task fields must map to columns exactly as indicated here:

Note: only indented values are actually used upon importing.

Then further customizing everything

If you want to customize all the above, don’t worry, you absolutely can! Clone the git repo, and then you’d just need to:

1. Update the Excel template file (ProjectTemplate.xlsx) in the project — if you plan to export Excel sheets;
2. Modify C# source code in Adapter.cs file there (AddTasksToExcel method for exporting Excel sheets, and ReadTasksFromExcel method for importing Project XML data back from them) to correctly handle differences.
3. And finally, enjoy!