Docs
Implementations
Export Data

Export Data

There are two main technical difficulties related to querying a database and collecting all the necessary data for export:

  • Querying and aggregating data from the database
  • Storing the processed data to an Excel file and streaming the file to the frontend

For example, the Prisma API is designed to make it easy to retrieve all data for a given course from the database using just one function call. This can save a significant amount of time and effort, and can help ensure that the data is accurate and up-to-date. Here is the example implementation of using Prisma.

async function getCourseData(prisma: PrismaClient, courseCode: string) {
  // get all projects, students, tas and assessments for the course
  return prisma.course.findUnique({
		// search course by course code
    where: {
      code: courseCode
    },
    select: {
			// select all project data within that course
      projects: {
        select: {
					// select essential project data for that team
          teamNumber: true,
          lab: true,
          supervisorName: true,
          title: true,
          abstract: true,
          type: true,
          client: true,
 
					// select corresponding ta assigned to that tean
          ta: { ... },
 
					// select all team members assigned to that team
          teamMembers: { ... },
 
					// select all assessment data for that team
          teamAssessments: { ... }
        }
      },
			// export all student enrolled in that course
      enrollments: { ... }
    }
  });
}

The above code takes a Prisma client and course code as input and returns an object containing all essential data for that specific course, which is created by the Prisma library. For the sake of simplicity, we omit some detailed nested queries as they share similar semantics in terms of querying the data.

We then use the Excel processing library ExcelJS (https://github.com/exceljs/exceljs (opens in a new tab)) to write the queried data into an Excel file. It provides a simple and flexible API for creating and modifying spreadsheets, and supports a wide range of Excel features and formats. ExcelJS can be used to create complex spreadsheets with rich formatting, formulas, and charts. It can also be used to read and modify existing Excel files, making it a useful tool for data processing and analysis. ExcelJS supports a wide range of file formats, including XLSX, XLS, CSV, and TXT, and can be used in both client-side and server-side applications.

In the next section, we will only demonstrate how to export the project's data because the other sections are similar.

First of all, we create a new workbook.

// create workbook
const workbook = new Workbook();

Then we create a worksheet with the name "projects"

// define project worksheet
const projectWorksheets = workbook.addWorksheet('projects');

Next, we define columns for the projects worksheet. The header property defines the display name of each column, key is used for later reference when inserting the data, and width is the visual width for each Excel column.

// define columns for project worksheet
  projectWorksheets.columns = [
    { header: 'Team Number', key: 'teamNumber', width: 12 },
    ...
    { header: 'Leader', key: 'leader', width: 20 },
    { header: 'Leader Email', key: 'leaderEmail', width: 30 },
    { header: 'Leader ID', key: 'leaderId', width: 15 },
		...
    { header: 'Second Member', key: 'secondMember', width: 20 },
    { header: 'Second Member Email', key: 'secondMemberEmail', width: 30 },
    { header: 'Second Member ID', key: 'secondMemberId', width: 15 },
    ...
  ];

After that, we write an loop to insert rows into our new Excel worksheet.

for (const project of projects) {
    // add a new row for the project worksheet
		projectWorksheets.addRow({
      teamNumber: project.teamNumber,
      lab: project.lab,
      ...
      client: project.client
    });
}

Finally we define the file name and send the file back to frontend.

const today = new Date();
// get today's date format in dd_mm_yyyy
const date_str = `${today.getDate()}_${today.getMonth() + 1}_${today.getFullYear()}`;
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader('Content-Disposition', 'attachment; filename=' + `${courseCode}_Export_${date_str}.xlsx`);
 
return workbook.xlsx.write(res).then(function () {
  res.end();
});

This concludes our implementation of the data export function.

Email NotificationsStudent Portal