Importing and exporting JavaScript Gantt chart data from and to Excel®

We have recently published an article about loading and saving Excel® files using our new ProjectManagementFramework.Xlsx package (on top of DlhSoft Project Management Framework, of course) from a Windows desktop app.

But what if you want to import/export data sheets within a JavaScript-enabled page (or app) instead? Such as within an ASP .NET based Web site, be it based on .NET Core 2.x, .NET 5 or even .NET Framework 4+.

Don’t worry, we’ve got you covered. Our open source library has been developed as a .NET Standard 2.0 library, so it absolutely works there too.

Why Excel sheets you may ask. Why not, we would reply. CSV or tab delimited data is indeed easy to export/import, but — for so many users — it’s a lot better if you can directly go Office files too. And clearly, Microsoft Office is the most common business suite that virtually every other platform integrates to and supports.

You want the details, right? So here we go:

NuGet package

If you just want to get started, here is the place to go:

DlhSoft.ProjectManagementFramework.Xlsx 1.0.3

GitHub project

But if you want to customize things further, just clone our repo and have fun instead!

DlhSoftTeam/ProjectManagementXlsx

Project XML format

As mentioned above, our new tool is leveraging DlhSoft Project Management Framework, which — besides inline adding and removing items — works with Microsoft Project XML formatted data.( And all our Gantt chart components do so, too.)

Project Elements and XML Structure

Component licensing

But don’t worry: although you do need a development license for Project Management Framework, indeed, you can grab one for free, directly from our Web site, as long as you’re a DlhSoft Gantt Chart Hyper (or Web, or Light) Library licensee.

Converting Project XML to/from Excel sheets

And here you go, this is the way you can convert content between Microsoft Project XML and Excel files.

To read Excel files as Project XML content:

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

To create Excel files out of Project XML strings:

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

Built-in Excel template

Excel sheets generated by the tool need to respect some conditions, of course.

Specifically, task data is assumed to always be in the first sheet of the file, and the first row of the sheet should always contain header values. And actual task fields must map to columns exactly as indicated here below:

Sample app for ASP .NET and JavaScript

Let’s now bring everything together and load and save Excel sheets directly from Gantt chart data showing in the browser.

Here is a small sample app developed using ASP .NET Core (MVC) and a bit of JavaScript, leveraging GanttChartView component from DlhSoft Gantt Chart Hyper Library as the UI.

Feel free to extract relevant import-export code (see site.js and HomeController.cs) and extend things as you need!

(And again, don’t hesitate to also update the Adapter that performs the actual XML-Excel conversions, if you really need further customization!)

DlhSoftTeam/XlsxGanttChartWeb

To save Gantt chart data as a Excel sheet, you’d need to get Project XML content from the client side, convert it to Excel bytes on the server, and download the bytes back. And to convert an Excel sheet uploaded to the server to Project XML, it’s similarly easy, too. Just see the code below:

Exporting data

Extract from site.js file (client side):

function saveXlsx() {
  let data = new FormData();
  var projectSerializer = DlhSoft.Controls.GanttChartView.ProjectSerializer.initialize(ganttChartView);
  var projectXml = projectSerializer.getXml();
  data.append('ProjectXml', projectXml);
  fetch('/Home/SaveXlsx', { method: 'POST', body: data }).then(response => response.json()).then(response => {
    var excelBase64 = response.data;
    var dataUrl = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + excelBase64;
    const a = document.createElement('a');
    a.href = dataUrl;
    a.download = 'GanttChart.xlsx';
    a.click();
  });
}

Extract from HomeController.cs file (server side):

[HttpPost]
public IActionResult SaveXlsx(ProjectXmlForm form)
{
  var excelBytes = ProjectManagementXlsx.Adapter.GetExcelBytes(form.ProjectXml);
  return Ok(new { data = Convert.ToBase64String(excelBytes) });
}

Importing data

Extract from site.js file (client side):

function loadXlsx() {
  let data = new FormData();
  var fileInput = document.getElementById('xlsxFileInput');
  let file = fileInput.files[0];
  if (!file) {
    alert('Select a file first.');
    return;
  }
  data.append('file', file);
  fetch('/Home/LoadXlsx', { method: 'POST', body: data }).then(response => response.json()).then(response => {
    var projectXml = response.data;
    var projectSerializer = DlhSoft.Controls.GanttChartView.ProjectSerializer.initialize(ganttChartView);
    projectSerializer.loadXml(projectXml);
  });
}

Extract from HomeController.cs file (server side):

[HttpPost]
public IActionResult LoadXlsx(IFormFile file)
{
  using (var stream = file.OpenReadStream()) {
    byte[] excelBytes = new byte[file.Length];
    stream.Read(excelBytes, 0, excelBytes.Length);
    var projectXml = ProjectManagementXlsx.Adapter.GetProjectXml(excelBytes);
    return Ok(new { data = projectXml });
  }
}