In this article, I will be taking you through building an application that demonstrates OpenAI GPT Excel Integration using Node.js in the backend and React.js in the front end. You need to have already installed NodeJS on your PC which can be downloaded here
This is Part 1 of the series of 2 tutorials where I cover the following
1. Part 1: You are here. Read excel using OpenAI and process results.
2. Part 2. Future tutorial. This covers manipulating the results and displaying the formatted results.
The demo application will showcase the following:
- Select an Excel file
- Preview of the file to verify if it’s the correct one
- An input field that allows the user to ask questions pertaining to the file
- A response field to display the output.
If you want to read how to use ChatGPT with excel, read this blog
Creating the backend
Navigate to the directory you want the project to be in, open the terminal, and run the following command
npm initEnter a package name if you want to or press Enter key on your keyboard.
Also set the version, description and entry point, test command, git repository, keywords, author, and license or you can skip these steps by pressing Enter key from your keyboard.
(It’s advisable you press Enter key so as not to encounter any issues)
Then it’s done. You have created the backend file.
We will need to install some dependencies that will aid our project
They are:
body-parser: To process post requests from the React.js applicationcors: Handle cross-origin requests generated from external domainsopenai: OpenAI library to process our NLP queries for excel
Install them using the following command
npm i cors body-parser openaiMake sure you run the code in this same format. You can copy the code directly from here.
After you have installed them, you will need the create a file named index.js, that’s where all the codes will be written. I prefer to use Visual Studio Code (VSC) which gives me a lot of tools to write my code efficiently. To create the index.js file using VSC, run the following command on your terminal
code index.jsWe will need to write some codes, you can directly copy and paste the code directly from this code block:
const express = require("express");
const cors = require("cors");
const bodyParser = require("body-parser");
const { Configuration, OpenAIApi } = require("openai");
const configuration = new Configuration({
apiKey: "<YOUR_OPENAI_KEY>",
});
const openai = new OpenAIApi(configuration);
openai.listModels().then(models => {
console.log(models);
console.log('successfully connected');
}).catch(error => {
console.log(error);
});
// Set up the server
const app = express();
app.use(bodyParser.json());
app.use(cors())
// Start the server
const port = 8080;
app.listen(port, () => {
console.log(`Server listening on port ${port}`);
});The dependencies we installed earlier are important, here is what they do:
Express
This is a layer built on top of the Node js that helps manage servers and routes.
body-parser: This is an npm module used to process data sent in an HTTP request body. It provides four express middleware for parsing JSON, Text, URL-encoded, and raw data sets over an HTTP request body.
Cors
This defines a way in which a browser and server can interact to determine whether it is safe to allow the cross-origin request.
The above code does the following:
The first 13 lines of code create Configuration and OpenAI API classes from the OpenAI package. With the help of a specified API key, this produces an object that can communicate with the OpenAI API.
There is also the openai.listModels() method, which returns a list of every model that is accessible via the OpenAI API. The function will record a success message and the list of models if it is successful. It will record the error message if one happens.
By starting a fresh instance of the app and adding middleware, the next few lines configure the express server and also cors. The app.use() method also applies Cors and body-parser to the server.
The app.listen() method starts the server on a specified port (in this example, port 8080). A message stating that the server is listening on the given port shows up in the console when the server starts up successfully.
You should replace the “YOUR_API_KEY” with your personal OpenAI API key.
The connection will return “successfully connected” in the console or an error if the connection is not successful.
To generate your individual OpenAI key if you haven’t, on your web browser, type in https://openi.com, click on login by the right-hand side of the tab, and then log in using whatever method you prefer.
If successfully logged in to the site, you should see something like this:
Click on personal and you will also see something like this:
Click on it View API keys and you will see this:
You will see a Generate Key button if you haven’t generated one. Generate it and copy it. Then head back to your IDE. Now in our backend folder, I assume everything is set. We will now need to start our server, head to your package.json file and then edit the following:
Edit the test line to the start and then type the code you see here and save.
Start the server by running this in the terminal:
npm startYou will see something like this at the end:
That shows our server is successfully running on the specified port and is also connected to the OpenAI server.
Creating our Frontend
In the same backend folder, in the terminal also, run the following command and hit Enter:
npm create react-app frontendThen you will have to wait a bit as it takes some seconds or minutes to create depending on the connectivity of your PC.
After you have created the frontend folder, navigate to the folder using the terminal. Now in the frontend folder, we need some dependencies, they are Xlsx and Axios.
Run the following to install them:
npm i axios xlsxThis is what they do:
Xlsx
This allows us to read any excel file and also be able to create one.
Axios
This makes it easy for us to send asynchronous HTTP requests to our endpoints.
For simplicity, I have written all our codes into one file, App.js. In the App.js file, delete the existing codes, paste this and save:
import axios from "axios";
import { React, useState } from "react";
import * as XLSX from 'xlsx';
import "./App.css"
function App() {
const [data, setData] = useState([]);
const [prompt, setPrompt] = useState("");
let jsonData = null;
const handleFile = (file) => {
const reader = new FileReader();
const rABS = !!reader.readAsBinaryString;
reader.onload = async (e) => {
const bstr = e.target.result;
const wb = XLSX.read(bstr, { type: rABS ? "binary" : "array" });
const wsname = wb.SheetNames[0];
const ws = wb.Sheets[wsname];
jsonData = XLSX.utils.sheet_to_json(ws, { header: 1 });
setData(jsonData.slice(0, 11));
};
if (rABS) reader.readAsBinaryString(file);
else reader.readAsArrayBuffer(file);
};
const handleButtonClick = () => {
document.getElementById("input-file").click();
}
const handleInputChange = (e) => {
const file = e.target.files[0];
handleFile(file);
};
const handleSubmit = (e) => {
e.preventDefault();
};
return (
<div className="container">
<h1>Upload Excel File</h1>
<input type="file" id="input-file" accept=".xlsx, .xls" onChange={handleInputChange} style={{ display: "none" }} />
<button onClick={handleButtonClick}>Choose File</button>
{data.length > 0 &&
<table className="table">
<thead>
<tr>
{data[0] &&
data[0].map((header, index) => <th key={index}>{header}</th>)}
</tr>
</thead>
<tbody>
{data.slice(1).map((row, index) => (
<tr key={index}>
{row.map((cell, index) => (
<td key={index}>{cell}</td>
))}
</tr>
))}
</tbody>
</table>
}
{data.length > 0 &&
<form onSubmit={handleSubmit}>
<label htmlFor="input">Enter your related questions:</label>
<input
type="text"
value={prompt}
onChange={(e) => setPrompt(e.target.value)}
/>
<button type="submit">Submit</button>
</form>
}
</div>
);
}
export default App;Also in the App.css file, delete the existing codes, paste this and save:
form {
margin-top: 20px;
}
label {
display: block;
font-weight: bold;
margin-bottom: 5px;
}
input[type="text"] {
padding: 10px;
font-size: 16px;
border-radius: 4px;
border: 1px solid #ccc;
width: 100%;
box-sizing: border-box;
margin-bottom: 10px;
}
button[type="submit"] {
padding:20px;
background-color: #4CAF50;
color: white;
border: none;
border-radius: 4px;
cursor: pointer;
font-size: 16px;
}
button[type="submit"]:hover {
background-color: #45a049;
}
button[type="submit"]:focus {
outline: none;
}
.container {
max-width: 800px;
margin: 0 auto;
padding: 20px;
background-color: #f7f7f7;
border-radius: 4px;
box-shadow: 0 0 5px rgba(0, 0, 0, 0.1);
}
h1 {
font-size: 24px;
text-align: center;
margin-bottom: 20px;
}
table {
border-collapse: collapse;
width: 100%;
}
th,
td {
border: 1px solid #ddd;
padding: 8px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
.header{
margin-top: 5px;
}
.cart{
margin-top: 5px;
}This is a list of what the code does:
The first four lines import necessary CSS files and packages. The useState hook defines the state variables data and prompt. The data and prompt state then record the user’s inquiries and the contents of the Excel file.
The handleFile method selects and uploads the excel file. This function reads the contents of the file using the FileReader API and then converts the Excel data into a JSON object using the XLSX library. The data state variable is then initialized with the first 11 rows of the data.
A user triggers the handleInputChange method by selecting a different file to upload. This method calls the handleFile function, which parses the file contents.
A user calls the handleSubmit function when he/she submits a form. This function also prevents recurrent form submission default behavior.
The render method returns JSX that displays the program’s user interface. It contains a title, a button that opens the file input box, and a table that lists the information included in the Excel file. If the data state variable has data, the table is shown.
Moreover, if the data state variable has information, a form allowing the user to ask questions shows up.
Assuming you have taken following all the following steps, we will need to run our app. To do that, run this in the terminal:
npm startMake sure that you are in the frontend folder.
In the end, you should see this:
Their app also previews the first 10 rows of our excel file so I implore you to select a sample excel file and you will see this:
Also note that the “choose file” button only sees excel files so if there is none, any file won’t be seen on the PC.
The next step is to connect our front end to our backend and also allow the model to be able to read data from our table and also answer related questions pertaining to it. That will be available in Part 2 of this article.
Thanks.
