Migrating from an Excel Gantt chart to a Bryntum Gantt chart

Microsoft Excel is a common tool used in project management. It's affordable and simple to use, and if you need to create a Gantt chart to schedule project tasks, Excel has many template Gantt charts to get you started.

However, you may need more advanced functionality from your Gantt chart than Microsoft Excel provides, for example:

• Scheduling tasks using dependencies and constraints.
• Using recurrent and fixed-time intervals.
• Handling large datasets.
• Customizing the rendering and styling.

If your project would benefit from these capabilities, then the Bryntum Gantt web component is a better choice.

In this tutorial, we'll show you how to get Gantt chart data from an Excel file into a Bryntum Gantt chart. We'll do the following:

• Write a migration script to convert data from an Excel Gantt chart to a JSON file that can be read by a Bryntum Gantt chart.
• Set up an Express.js API endpoint to return the JSON data.
• Create a Bryntum Gantt chart that gets data from the API endpoint.

First we'll get data from a simple Excel Gantt chart. Then we'll modify the code to get data from a more complex Gantt chart. You can adjust the code to get data from different Excel Gantt charts.

Writing a migration script to convert data from an Excel Gantt chart to a JSON file

Start by cloning the Excel to Bryntum Server starter GitHub repository. This repository includes a server.js file, which contains an Express.js server API endpoint at the "/download" route for GET requests. It also has CORS set up for when we make requests to the API endpoint from our client-side Bryntum Gantt chart.

You can find the completed code for this tutorial in the completed-server branch of the starter repository.

There are two Excel files in the starter's root directory:

• simple-gantt-chart.xlsx
• agile-gantt-chart.xlsx

These are Excel Gantt chart templates taken from Microsoft 365 Excel templates.

Once you've cloned the repository, install the dependencies by running the following command:

npm install

In the root folder, create a file called migration-simple-gantt.js. This is where we'll add the script to read the Excel data from simple-gantt-chart.xlsx, convert it to a JSON structure that can be read by a Bryntum Gantt chart, and save the JSON data to a file.

Converting Excel file data to a JavaScript object

To read the Excel file and convert the data to JSON format, we'll use the xlsx library. Import the library in your migration-simple-gantt.js file:

import xlsx from "xlsx";

Add the following lines of code:

// read the Excel file
const workbook = xlsx.readFile("./simple-gantt-chart.xlsx");
const sheetName = workbook.SheetNames[0]; // select the sheet you want
const worksheet = workbook.Sheets[sheetName];

const jsonData = xlsx.utils.sheet_to_json(worksheet);

The readFile method reads the data from the Excel file and parses it into a workbook object. The Excel file has two sheets, we only want the data from the first sheet. The workbook.SheetNames array is an ordered list of the sheets in the Excel workbook. We get the name of the first sheet from the ordered lists of sheets. We then get the worksheet data from the Sheets object.

We use the sheet_to_json utility method to convert the worksheet object into an array of JavaScript objects.

If you console.log the jsonData variable, you'll see a large array of objects returned:

[
  {
    'Create a Project Schedule in this worksheet.\r\nEnter title of this project in cell B1. \r\nInformation about how to use this worksheet, 
including instructions for screen readers and the author of this workbook is in the About worksheet.\r\nContinue navigating down column A to h
ear further instructions.': 'Enter Company Name in cell B2.',
    'PROJECT TITLE': 'Company Name',

    ...

Each object in the array is a row of Excel data, for most of the data. You'll notice that a lot of the text returned is not visible in the Excel Gantt chart. This is the text from cells with hidden values for explaining how to use the template worksheet. For example, if you click on cell A1 in the Excel file, you'll see the hidden text in the formula bar.

The tasks data that we need to extract from the array objects is in the "PROJECT TITLE", __EMPTY", "__EMPTY_1", "__EMPTY_2", "__EMPTY_3", and "__EMPTY_5" property values.

Let's create a function to extract the tasks data and convert it to the format required by Bryntum Gantt.

Converting the Excel data to a JSON file compatible with Bryntum Gantt

Later in this tutorial, we'll use the Bryntum Gantt chart Crud Manager to simplify fetching data from our API. The Crud Manager expects the data it receives from an API endpoint to have a specific structure. We need to extract the data we need from the jsonData variable and format it appropriately.

The start date and end date values of the "__EMPTY_2" and "__EMPTY_3" properties are Excel date numbers. Excel stores dates as a number format, with the number representing the offset from an epoch. We need a function to convert Excel numbers to JavaScript Date objects. Add the following function to the top of the migration-simple-gantt.js file to convert an Excel date number to a JavaScript Date object:

function excelToJSDate(excelDate) {
  const dateObj = xlsx.SSF.parse_date_code(excelDate);
  return new Date(
    dateObj.y,
    dateObj.m - 1,
    dateObj.d,
    dateObj.H,
    dateObj.M,
    dateObj.S
  );
}

The xlsx SSF (SpreadSheet Format) object has a parse_date_code method that parses the Excel number to an SFF date object. We use this Date object to create a JavaScript Date object. To construct the JavaScript Date object, we subtract one from the SFF date object, as months are zero-indexed in JavaScript.

Now we need a function to create an array of tasks data that can be read by a Bryntum Gantt chart. Add the following function below the excelToJSDate function:

function createBryntumTasksRows(data) {
  let taskId = 0;
  let taskStore = [];
  let currentParentId;

  // name of first task is "Phase 1 Title"
  // only start adding tasks after it's found
  const firstTaskName = "Phase 1 Title";
  const exampleTaskName = "Insert new rows ABOVE this one";
  let isFirstTaskFound = false;
  for (let i = 0; i < data.length; i++) {
    if (
      data[i].hasOwnProperty("PROJECT TITLE") &&
      data[i]["PROJECT TITLE"].startsWith("Phase")
    ) {
      if (data[i]["PROJECT TITLE"] === firstTaskName) {
        isFirstTaskFound = true;
      }
      currentParentId = taskId;
      // parent tasks
      taskStore.push({
        id: taskId++,
        name: data[i]["PROJECT TITLE"],
        expanded: true,
      });
    } else if (data[i]["PROJECT TITLE"]) {
      if (!isFirstTaskFound) {
        continue;
      }
      // last task has been added
      if (data[i]["PROJECT TITLE"] === exampleTaskName) {
        break;
      }
      // child tasks
      taskStore.push({
        id: taskId++,
        name: data[i]["PROJECT TITLE"],
        parentId: currentParentId,
        resourceAssignment: data[i]["__EMPTY"],
        percentDone: data[i]["__EMPTY_1"] * 100,
        startDate: excelToJSDate(data[i]["__EMPTY_2"]),
        endDate: excelToJSDate(data[i]["__EMPTY_3"]),
        manuallyScheduled: true,
      });
    }
  }

  return taskStore;
}

This function loops through the objects of the Excel data that we pass in as arguments. These objects mostly represent Excel rows. We construct a taskStore array to store the new task objects. We only start adding to the taskStore once the firstTaskName is found.

For this specific Gantt data, we determine which tasks are parent tasks by checking if the "PROJECT TITLE" starts with the text "Phase". We create a unique id for each task using the taskId variable, which increments on each loop.

If the "PROJECT TITLE" does not start with the text "Phase", the task is made a child of the last parent task using the parentId property. Once the exampleTaskName is reached, we break out of the loop.

Now add the following variable below the jsonData variable to create the task rows:

const tasksRows = createBryntumTasksRows(jsonData);

If you console.log the tasksRows, you'll see the following array:

[
  { id: 0, name: 'Phase 1 Title', expanded: true },
  {
    id: 1,
    name: 'Task 1',
    parentId: 0,
    resourceAssignment: 'Name',
    percentDone: 50,
    startDate: 'Mon May 15 2023 00:00:00 GMT+0200 (South Africa Standard Time)',
    endDate: 'Thu May 18 2023 00:00:00 GMT+0200 (South Africa Standard Time)',
    manuallyScheduled: true
  },
  {
    id: 2,
    ...

If the startDate and endDate values are behind what they should be, it may be due to the Excel TODAY() function that's used to determine the dates in the Excel file. The TODAY() function is a volatile function, which means that it recalculates every time the spreadsheet is opened. When you read the Excel file using a library like xlsx, it won't trigger these formulas to recalculate. It will read the last calculated value from when the file was last opened and saved in Excel. If the dates are behind what they should be, make a small change to the Excel file, save the change, and try again.

Now, we need to add a resources property and an assignments property for the JSON object that we'll create. The resources property will populate the ResourceStore of the Bryntum Gantt, which holds the resources that can be assigned to tasks. The simple Gantt chart only has a single resource, "Name", that's assigned to the first child task. The assignments property will show which tasks resources are assigned to.

Add the following lines of code below the tasksRows variable:

// create resources
const resourceNames = new Set();
const resourcesRows = tasksRows
  .filter(
    (item) =>
      item.resourceAssignment && !resourceNames.has(item.resourceAssignment)
  )
  .map((item, i) => {
    const name = item.resourceAssignment;
    return {
      id: i,
      name,
    };
  });

We determine the resources of the data using the resourceAssignment property of the tasksRows data. We then create a resourcesRows array, where each object in the array is a resource.

If you console.log the resourcesRows variable, you'll see the following array:

[ { id: 0, name: 'Name' } ]

Now let's create the rows for assignments. Add the following lines of code below the resourcesRows variable:

// create assignments
const resourcesWithAssignments = tasksRows.filter(
  (item) => item.resourceAssignment
);

const assignmentsRows = resourcesWithAssignments.map((item, i) => {
  const resource = resourcesRows.find(
    (resource) => resource.name === item.resourceAssignment
  );
  return {
    id: i,
    event: item.id,
    resource: resource.id,
  };
});

To determine the tasks (events) that are assigned to a resource, we first find tasks that have a resource assignment. We then use the tasks to get the resource id and construct the assignmentsRows array.

If you console.log the assignmentsRows variable, you'll see the following array:

[ { id: 0, event: 1, resource: 0 } ]

Now create an object for the data that will be sent to the Bryntum Gantt chart:

// convert JSON data to the expected load response structure
const ganttLoadResponse = {
  success: true,
  tasks: {
    rows: tasksRows,
  },
  resources: {
    rows: resourcesRows,
  },
  assignments: {
    rows: assignmentsRows,
  },
};

Next we need to convert this object to JSON format and save it in a JSON file.

Saving the data to a JSON file

Add the following lines of code below the ganttLoadResponse variable:

const dataJson = JSON.stringify(ganttLoadResponse, null, 2); // convert the data to JSON, indented with 2 spaces

// define the path to the data folder
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
const dataPath = path.join(__dirname, "data");

// ensure the data directory exists
if (!fs.existsSync(dataPath)) {
  fs.mkdirSync(dataPath);
}

// define the path to the JSON file in the data folder
const filePath = path.join(dataPath, "simple-gantt-chart.json");

// write the JSON string to a file in the data directory
fs.writeFile(filePath, dataJson, (err) => {
  if (err) throw err;
  console.log("JSON data written to file");
});

Import the Node modules required:

import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import { dirname } from "path";

We convert the JavaScript object to a JSON string. We then create a JSON file called simple-gantt-chart.json in the data folder using the Node File System module and add the Excel JSON data to it.

To run this script, use the following command:

node migration-simple-gantt.js

This will create a simple-gantt-chart.json file in the data folder.

For this tutorial, we simply save Excel Gantt data to a JSON file on the server. If you want to persist the data to a database, you can check out the saving data section of our docs

Next, we'll set up an API endpoint that will send the JSON data to the Bryntum Gantt chart.

Setting up an Express server with an API endpoint

In the server.js file, there is an API endpoint at the "/download" route for GET requests. Replace the code in the try block with the following lines of code:

const data = await fs.readFile("./data/simple-gantt-chart.json", "utf-8");
const jsonData = JSON.parse(data);
res.json(jsonData);

Import the Node File System module with promise-based functions:

import fs from "fs/promises";

We read the JSON data from the file system, parse it, and then return it from the API as a JSON response.

Now start the server using the following command:

npm run start

If you make a GET request to your API endpoint at http://localhost:3000/download, you should see the following response:

{
    "success": true,
    "tasks": {
        "rows": [
            {
                "id": 0,
                "name": "Phase 1 Title",
                "expanded": true
            },
            {
                "id": 1,
                "name": "Task 1",
                "parentId": 0,
                "resourceAssignment": "Name",
                "percentDone": 50,
                "startDate": "Mon May 15 2023 00:00:00 GMT+0200 (South Africa Standard Time)",
                "endDate": "Thu May 18 2023 00:00:00 GMT+0200 (South Africa Standard Time)",
                "manuallyScheduled": true
            },
      ...

Now we can create a Bryntum Gantt chart that will get data from this API endpoint.

Creating a client-side Bryntum Gantt chart

To get started, clone the following Excel to Bryntum Client starter GitHub repository. It has a "completed-gantt" branch that shows the code for the completed tutorial. It uses Vite, which is a development server and JavaScript bundler. Install the Vite dev dependency by running the following command:

npm install

Next, install the Bryntum Gantt component by following our guide to installing the Gantt chart component using npm.

The main.js file contains code for a basic Bryntum Gantt chart. The project config property is used to configure the data stores the Gantt chart uses. The tasksData property contains some example inline data. To simplify the data structure, the taskStore is configured to consume a flat dataset, where the task parents are defined using the parentId property. Flat data is converted to tree data with autotree and transformFlatData so that the Bryntum Gantt can parse it.

Start the development server by running the following command:

npm run dev

You should see a Gantt chart with one parent task and one child task.

Next, we'll use the Bryntum Gantt Crud Manager to get the Excel data from our API.

Getting the data from the API endpoint

First, remove the tasksData property from the gantt config in the main.js file.

Add the following properties to the gantt config, below the taskStore property:

  transport: {
    load: {
      url: "http://localhost:3000/download",
    },
  },
  autoLoad: true,
  validateResponse: true,

The transport property is used to populate the Gantt chart data stores. We configured the load property to get data from our server API endpoint. We set the autoLoad and validateResponse properties to true to initiate a data load when the store is instantiated and to check the response structure of requests made by the Crud Manager, respectively.

You can also configure transport to sync data changes to a specific URL. To learn more, you can read the saving data section of the Crud Manager guide in our docs.

We need to add columns to our Gantt chart to match the headers of the Excel Gantt chart. Add the following objects to the columns property of the gantt config:

    {
      type: "resourceassignment",
      text: "Assigned Resources",
      showAvatars: false,
      width: 160,
    },
    {
      type: "percent",
      text: "PROGRESS",
      field: "renderedPercentDone",
      showValue: true,
      width: 160,
    },
    { type: "date", field: "startDate", text: "START", width: 110 },
    { type: "date", field: "endDate", text: "END", width: 110 },

Now run the client and server development servers and you'll see the Bryntum Gantt chart with the data from the Excel file:

The Bryntum Gantt chart has a lot of functionality out of the box, such as:

• Configurable and sortable task columns. Right-click on a column header to see the options.
• Collapsable task groups.
• Drag-and-drop reordering of tasks.
• Add, edit, copy, and delete tasks. Right-click on a task to see a pop-up menu with these actions and more, including editing dependencies.
• Draggable task durations.
• Resizeable task durations. Hover over the left or right side of a task duration until the cursor changes to a resize icon. Then click and drag left or right.
• Create task dependencies. Hover over the left or right side of a task duration until you see a circle icon. Then click and drag to connect the task to another task.
• Change date ranges or filter tasks by name by right-clicking on one of the date headers.

Now, let's get Excel data from a more complex Excel Gantt chart.

Getting data from a more complex Excel Gantt chart

The data source we'll use for this part of the tutorial is the agile-gantt-chart.xlsx Excel file, which will require changes to our server and client code.

Changes to the server: Writing a new migration script

Make a copy of the migration-simple-gantt.js file and rename it migration-agile-gantt.js.

The data we need from the agile-gantt-chart.xlsx Excel file is in sheet two. Change the workbook and sheetName variables in the migration-agile-gantt.js file to get data from the second workbook sheet of the agile Gantt chart:

    const workbook = xlsx.readFile("./agile-gantt-chart.xlsx");
    const sheetName = workbook.SheetNames[1];

Remove the jsonData variable:

    const jsonData = xlsx.utils.sheet_to_json(worksheet);

Replace it with the following lines of code:

    const csv = xlsx.utils.sheet_to_csv(worksheet);
    const jsonData = await csvToJson().fromString(csv);

Instead of converting the Excel data directly to JSON, we first convert it to CSV values. We then use the csvtojson library to convert the CSV values to a JSON string.

Make sure that you import the csvToJson function from the csvtojson library:

import csvToJson from "csvtojson";

We use this method to convert the Excel data to a JSON data object as the JSON object output is easier to parse than the output we used for the simple-gantt-chart.xlsx Excel file. You might like to try each method to see which works best for you.

If you console.log the jsonData object, you'll see a large array of objects:

[
    {
        field1: "",
        field2: "PROJECT: Light",
        field3: "",
        ...
        field68: "",
    },
    {
        field1: "",
        field2: "",
        ...
        field68: "",
    },
      ...

Each object in the "data" array represents a row in the Excel file. The columns are named incrementally from "field1" to "field68".

The data we need for the Gantt chart is in columns two to seven of the Excel chart, which correspond to the properties field2 to field7 of the "data" array objects. Unlike the previous Gantt chart we imported data from, there is no end date for each task in this chart. Instead, each task has a start date and a duration in days. This Gantt chart also has a "Categories" column.

The start date values of the field6 keys are not in Excel date number format, so we don't need the excelToJSDate function. Replace it with the following function:

function convertToDate(dateStr) {
  let [month, day, year] = dateStr.split("/");
  year = year.length === 2 ? "20" + year : year;
  month = month - 1;
  return new Date(year, month, day);
}

This function converts the date string to a JavaScript Date object.

Now, replace the existing createBryntumTasksRows with the one below:

function createBryntumTasksRows(data) {
  let taskId = 0;
  let taskStore = [];
  let currentParentId = null;

  // name of first task is "Phase 1 Title"
  // only start adding tasks after it's found
  const firstTaskName = "Project development";
  const exampleTaskName = "Task 4";
  let isFirstTaskFound = false;
  for (let i = 0; i < data.length; i++) {
    // check for first task
    if (data[i]["field2"] === firstTaskName) {
      isFirstTaskFound = true;
    }
    // check for example task
    if (data[i]["field2"] === exampleTaskName) {
      break;
    }

    if (isFirstTaskFound) {
      // parent task
      if (data[i]["field6"] === "") {
        currentParentId = taskId;
        taskStore.push({
          id: taskId++,
          name: data[i]["field2"],
          expanded: true,
        });
      } else {
        // child tasks
        taskStore.push({
          id: taskId++,
          name: data[i]["field2"],
          parentId: currentParentId,
          category: data[i]["field3"],
          resourceAssignment: data[i]["field4"] ? data[i]["field4"] : undefined,
          percentDone: data[i]["field5"].slice(0, -1) * 1,
          startDate: convertToDate(data[i]["field6"]),
          duration: data[i]["field3"] === "Milestone" ? 0 : data[i]["field7"],
          manuallyScheduled: true,
        });
      }
    }
  }

  return taskStore;
}

This function is similar to the one we used for the simple Gantt chart. We check if a task is a parent task if the value of the "field6" property is an empty string. The child tasks have no endDate property and there's an additional category property. The duration property is set to 0 if the category is "Milestone". This will indicate to the Bryntum Gantt that the task is a milestone and the task bar will be styled like a milestone, diamond-shaped. The resources and assignments properties will be created in the same way as previously.

Change the name of the filePath that the JSON file will be written to:

const filePath = path.join(dataPath, "agile-gantt-chart.json");

In the server.js file, change the JSON file that the fs.readFile method reads from:

    const data = await fs.readFile("./data/agile-gantt-chart.json", "utf-8");

Run the migration script to create the agile-gantt-chart.json file:

node migration-agile-gantt.js

We'll now change our client-side Bryntum Gantt chart to resemble the structure of the Excel Gantt chart.

Changes to the client

First, in the main.js file, we'll add extra gantt columns and adjust the width of the columns. Change the columns configuration to the following:

  columns: [
    { type: "name", width: 190, text: "Milestone description" },
    { width: 80, text: "Category", field: "category" },
    {
      type: "resourceassignment",
      text: "Assigned to",
      showAvatars: false,
      width: 100,
    },

    {
      type: "percent",
      text: "PROGRESS",
      field: "renderedPercentDone",
      showValue: true,
      width: 100,
    },
    { type: "date", field: "startDate", text: "START", width: 110 },
    { type: "date", field: "endDate", text: "END", width: 110 },
  ],

You'll now see the following Bryntum Gantt chart when you run the development server:

Notice that the "Category" values are not displayed. This is because "Category" is not one of the predefined fields of the Bryntum Gantt task store Model. We can add this custom field to the model by adding the following lines of code to the bottom of the main.js file:

gantt.taskStore.modelClass.addField({
  name: "category",
  type: "string",
});

The "Category" values will now be displayed in your Gantt chart:

If you look at the agile-gantt-chart.xlsx Gantt chart, you'll see that the task bars are colored depending on the task category, and the milestones are shown as a flag. Let's color the child task bars based on the task category. Add the following method in the gantt config, below the columns property:

  // custom taskbar content
  taskRenderer({ taskRecord, renderData }) {
    const { category, parentId } = taskRecord;
    if (parentId !== null) {
      switch (category) {
        case "Goal":
          renderData.style = "background-color:" + "#d65532";
          break;
        case "Milestone":
          renderData.style = "background-color:" + "none";
          break;
        case "Low Risk":
          renderData.style = "background-color:" + "#00b0f0";
          break;
        case "Med Risk":
          renderData.style = "background-color:" + "#4868e5";
          break;
        case "High Risk":
          renderData.style = "background-color:" + "#852c98";
          break;
        case "On Track":
          renderData.style = "background-color:" + "#4ca472";
          break;
        default:
          renderData.style = "background-color:" + "#e6e6e6";
      }
    }

    return "";
  },

The taskRenderer is called each time a task is rendered. You can use it to change what's displayed in the task bars. It returns a string that's displayed in the task bar. The string can be text or HTML. You can also return a DOM config object.

We can get information about the task being rendered from the taskRecord parameter. If the task has a parentId, it's a child task. For the child tasks, we use a switch statement to determine the category of the task. We then style the task bars based on their category using the renderData parameter. We style the task bars by changing their "background-color" CSS property.

To make the "Milestone" category task bars look like a flag, add the following lines of code directly above the return statement:

    if (category === "Milestone")
      return `<span style="position:absolute; top: -10px"><i class="b-fa b-fa-flag b-fa-2xs" style="color: orange;"></i></span>`;

If the task is a Milestone, we'll render a Font Awesome flag icon on top of the task bar. The version of Font Awesome used by Bryntum components has a b-fa prefix instead of fa to prevent conflicts with other versions of Font Awesome on the page. If you are rendering user-provided data, you can use the xss function to sanitize the rendered string or HTML.

The Milestone task bars will have a flag icon rendered on top of them:

We can use CSS to hide the underlying task bar diamond shape. Add the following lines to the style.css file:

.b-gantt-task-wrap.b-milestone-wrap
  .b-gantt-task:not(.b-gantt-task-withicon)
  .b-gantt-task-content::before {
  height: 0;
  width: 0;
}

It's often easiest to use your browser dev tools to determine which HTML elements you want to apply the CSS styling to.

You'll now only see the flag icon for the Milestone task bars:

Next steps

Using the code in this tutorial as a guide, try to migrate your own Excel Gantt charts to a Bryntum Gantt chart to take your project management to the next level.

You can also persist the data to a database. Check out the saving data section of our docs or take a look at one of these tutorials from our blog:

• Migrating from DHTMLX Gantt to Bryntum Gantt: Learn how to set up a MySQL database for a Bryntum Gantt chart.
• Creating a Bryntum Gantt chart with React, TypeScript, Prisma, and SQLite: Remix vs. Next.js: Learn how to set up a SQLite database for a Bryntum Gantt chart.