Hi Devs! Adding graphs in an Excel sheet seemed to be an easy task until we started searching for a way to do it in React. We wanted a functionality where we get the report along with the other data in an Excel sheet. We asked the backend dev to give us the generated Excel file directly but his response was “It’s easy to do that on the client side, so enjoy”.
We went through many blogs trying for an implementation, but the most popular library xlsx, which is based on sheet.js, provides the chart functionality only in the PRO version, and what a pity!
So, if you are a never-give-up coder like us, you would have tried to find work around that, and voila! A simple trick: why not create a graph chart, turn it into an image and add the image instead, and then, everything is dynamic!
Hence, we have explained the solution below.
1. Setting Up the React App
Before we dive into the integration of Chart.js and ExcelJS, let’s ensure our React app is set up properly. Follow these steps:
-
- Create a new React App:
$ npx create-react-app react-chart-excel
$ cd react-chart-excel
-
- Install dependencies:
$ npm install chart.js exceljs
2. Adding the Following Code in src/App.js
Replace the content of src/App.js with the following code:
const generateExcelFile = async (data,jsonData) => {
const workbook = new ExcelJS.Workbook();
workbook.views = [
{
x: 0, y: 0, width: 10000, height: 20000,
firstSheet: 0, activeTab: 1, visibility: 'visible'
}
]
const worksheet = workbook.addWorksheet('My Sheet');
const headers = Object.keys(jsonData[0]);
worksheet.addRow(headers);
jsonData.forEach((data) => {
const row = worksheet.addRow(Object.values(data));
row.alignment = { horizontal: 'center' };
});
const columnB = worksheet.getColumn('B');
columnB.alignment = { horizontal: 'right' };
const cellA2 = worksheet.getCell('A2');
cellA2.alignment = { horizontal: 'left', vertical: 'middle' };
const numRows = worksheet.rowCount;
const myBase64Image = data;
let imageID = workbook.addImage({
base64: myBase64Image,
extension: 'png',
});
worksheet.addImage(imageID, {
tl: { col: 1, row: numRows + 1 }, // top-left corner in column D, next row after the last data row
br: { col: 4, row: numRows + 6 },
});
const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer]);
return blob;
};
The “generate ExcelFile” function takes care of creating an Excel workbook. It dynamically generates a worksheet and populates it with data. The real magic happens when we introduce an image of our bar chart into the Excel sheet. This is achieved by converting the chart, essentially a canvas, to a base64 image using the toDataURL method on the canvas context.
Handle File Download Functionality:
Note: Use the above code in the following handleDownload function:
import React, { useState, useEffect, useRef } from 'react';
import BarChart from './BarChart';
import * as ExcelJS from 'exceljs';
import Chart from 'chart.js/auto';
const App = () => {
const data = {
labels: ['Category 1', 'Category 2', 'Category 3'],
values: [10, 20, 15],
};
const chartRef = useRef(null);
const chartInstance = useRef(null);
useEffect(() => {
if (chartInstance.current) {
chartInstance.current.destroy();
}
const ctx = chartRef.current.getContext('2d');
chartInstance.current = new Chart(ctx, {
type: 'bar',
data: {
labels: data.labels,
datasets: [{
label: 'Bar Chart',
data: data.values,
backgroundColor: 'rgba(75, 192, 192, 0.2)',
borderColor: 'rgba(75, 192, 192, 1)',
borderWidth: 1,
}],
},
options: {
scales: {
y: { beginAtZero: true },
},
},
});
return () => {
if (chartInstance.current) {
chartInstance.current.destroy();
}
};
}, [data]);
const handleDownload = async () => {
const chartData = chartRef.current.toDataURL();
const excelBlob = await generateExcelFile(chartData, [
{ Name: 'John Doe', Age: 30, City: 'New York' },
{ Name: 'Jane Smith', Age: 25, City: 'Los Angeles' },
{ Name: 'Bob Johnson', Age: 35, City: 'Chicago' },
]);
const url = URL.createObjectURL(excelBlob);
const a = document.createElement('a');
a.href = url;
a.download = 'bar_chart_data.xlsx';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
};
return (
<div>
<canvas ref={chartRef} />
<button onClick={handleDownload}>Download Excel</button>
</div>
);
};export default App;
For the React Js Excel export, when done in the “handleDownload” function, we extract the chart data as a base64 image and seamlessly integrate it into the Excel sheet using ExcelJS. The resulting Excel file contains the raw data and a visual representation of that data through the embedded bar chart. Now, you can read Excel files in React JS.
Conclusion
It doesn’t seem like a lot, but to do it perfectly, it is important to learn the right way, and this is the way to add graph charts in Excel sheets using chartJS and excelJS. We hope that the learning journey in this blog has been fruitful for you.
You can read more such blogs at CloudZenia; we also provide practical solutions and consultations that can be time-saving and effective for you.
Leave a Reply