What is the process for extracting images from an Excel file and converting them into JSON format?

Question

What is the process for extracting images from an Excel file and converting them into JSON format?

I have a spreadsheet file with columns labeled as image and name. My goal is to extract the image, convert it to ByteArray, and save the data as a JSON object structured like this:

{ image: ByteArrya, name: string }

.

Here is the JavaScript form code I am working with:

<label className="flex items-center gap-3 px-3 py-2 bg-[#1D6F42] text-white text-blue rounded-md cursor-pointer">
    <RiFileExcel2Line size={22} />
    <span className="text-base leading-normal">Upload From Excel</span>
    <input type="file" className="hidden" disabled={formLoading} onChange={(e) => handleFormSubmit("file", e)} accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>
</label>

I am looking for a JavaScript function that can take an xlsx file input and then convert it into JSON data.

javascript typescript

Answer 1

Answer №1

To read an xlsx file using SheetJS and load image data from a URL into Base64 String, the following code can be used:

<!DOCTYPE html>
<html lang="en">
<head>
    <title>XLSX Frontend Reading</title>
    <script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
</head>
<body>
<input type="file" id="xlsxFileInput"/>
<script>
    const fileInput = document.getElementById('xlsxFileInput');

    fileInput.addEventListener('change', (e) => {
        const file = e.target.files[0];

        // use FileReader to read xlsx file
        const reader = new FileReader();
        reader.onload = function (e) {
            const data = new Uint8Array(e.target.result);

            const workbook = XLSX.read(data, {type: 'array'});

            const worksheet = workbook.Sheets[workbook.SheetNames[0]];

            const jsonData = XLSX.utils.sheet_to_json(worksheet, {header: 1});

            const headerRow = jsonData[0];
            let imageIndex = -1;
            let nameIndex = -1;
            // Find the image index and name index
            for (let i = 0; i < headerRow.length; i++) {
                if (headerRow[i] === "image") {
                    imageIndex = i;
                }
                if (headerRow[i] === "name") {
                    nameIndex = i;
                }
                if (imageIndex >= 0 && nameIndex >= 0) {
                    break;
                }

            }

            console.log(imageIndex);
            console.log(nameIndex);

            let result = [];
            for (let i = 1; i < jsonData.length; i++) {
                const row = jsonData[i];
                const resultJsonData = {};
                for (let j = 0; j < row.length; j++) {
                    const cellValue = row[j];

                    if (j === imageIndex) {
                        console.log(cellValue);
                        //
                        const img = new Image();

                        // Set the src attribute of the Image object to the URL of the image, it may be a read URL on your operating machine
                        img.src = cellValue;

                        img.onload = function () {
                            const canvas = document.createElement('canvas');
                            const ctx = canvas.getContext('2d');
                            canvas.width = img.width;
                            canvas.height = img.height;
                            ctx.drawImage(img, 0, 0);
                            // Transfer the image to base64
                            const base64String = canvas.toDataURL('image/jpeg');

                            resultJsonData.image = base64String;

                            console.log(base64String);
                        };
                    }


                    if (j === nameIndex) {
                        resultJsonData.name = cellValue;
                    }
                }
                result.push(resultJsonData);
            }
            console.log(result);

        };

    });
</script>

</body>
</html>

Answer 2

To read an xlsx file using SheetJS and load image data from a URL into Base64 String, the following code can be used:

<!DOCTYPE html>
<html lang="en">
<head>
    <title>XLSX Frontend Reading</title>
    <script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
</head>
<body>
<input type="file" id="xlsxFileInput"/>
<script>
    const fileInput = document.getElementById('xlsxFileInput');

    fileInput.addEventListener('change', (e) => {
        const file = e.target.files[0];

        // use FileReader to read xlsx file
        const reader = new FileReader();
        reader.onload = function (e) {
            const data = new Uint8Array(e.target.result);

            const workbook = XLSX.read(data, {type: 'array'});

            const worksheet = workbook.Sheets[workbook.SheetNames[0]];

            const jsonData = XLSX.utils.sheet_to_json(worksheet, {header: 1});

            const headerRow = jsonData[0];
            let imageIndex = -1;
            let nameIndex = -1;
            // Find the image index and name index
            for (let i = 0; i < headerRow.length; i++) {
                if (headerRow[i] === "image") {
                    imageIndex = i;
                }
                if (headerRow[i] === "name") {
                    nameIndex = i;
                }
                if (imageIndex >= 0 && nameIndex >= 0) {
                    break;
                }

            }

            console.log(imageIndex);
            console.log(nameIndex);

            let result = [];
            for (let i = 1; i < jsonData.length; i++) {
                const row = jsonData[i];
                const resultJsonData = {};
                for (let j = 0; j < row.length; j++) {
                    const cellValue = row[j];

                    if (j === imageIndex) {
                        console.log(cellValue);
                        //
                        const img = new Image();

                        // Set the src attribute of the Image object to the URL of the image, it may be a read URL on your operating machine
                        img.src = cellValue;

                        img.onload = function () {
                            const canvas = document.createElement('canvas');
                            const ctx = canvas.getContext('2d');
                            canvas.width = img.width;
                            canvas.height = img.height;
                            ctx.drawImage(img, 0, 0);
                            // Transfer the image to base64
                            const base64String = canvas.toDataURL('image/jpeg');

                            resultJsonData.image = base64String;

                            console.log(base64String);
                        };
                    }


                    if (j === nameIndex) {
                        resultJsonData.name = cellValue;
                    }
                }
                result.push(resultJsonData);
            }
            console.log(result);

        };

    });
</script>

</body>
</html>

Answer 3

Answer №2

Reading images from an xlsx file is not as straightforward as reading string values. An XLSX file actually consists of a zipped archive containing multiple XML files. One effective solution could involve utilizing a ZIP library to extract the necessary information, such as the image's anchor location (similar to how LibreOffice allows you to anchor images when opening an xlsx file). By parsing this anchor value, you can determine the corresponding row and column for the image.

Answer 4

Reading images from an xlsx file is not as straightforward as reading string values. An XLSX file actually consists of a zipped archive containing multiple XML files. One effective solution could involve utilizing a ZIP library to extract the necessary information, such as the image's anchor location (similar to how LibreOffice allows you to anchor images when opening an xlsx file). By parsing this anchor value, you can determine the corresponding row and column for the image.

What is the process for extracting images from an Excel file and converting them into JSON format?

Answer №1

Answer №2

Similar questions

Applying reduce method for accessing object information within an array and transforming its structure

Issue with Backbone: Unable to access property 'fn' as it is undefined

jQuery compatible JavaScript plugin

Having trouble with VueJS method not getting called after an asynchronous function callback?

*ngFor is not rendering the array data and no error is being shown

When working with DNN, I attempted to redirect using JavaScript code, but found that I continue to see the same page after the form submission code

I am looking to showcase a series of icons linked together by connecting lines

Tips for establishing a connection with an MQTT mosquito broker in a React application

Creating an interactive amchart that is connected to a database - a step-by-step guide

Unresponsive JavaScript on specific element IDs

Can a href from a "<Link>" component be passed through a Higher Order Component (HOC) into an "<a>" tag?

To ensure responsiveness in JavaScript, adjust the width to 100%

JavaScript click event to open a URL in a new window

Configuring Protractor to customize Safari settings such as adjusting window size

Combining React with the power of Express

Converting a JSON array stored in a local file to a TypeScript array within an Angular 5 project

javascript code to fetch the full Date object using user input for both date and time

Ensuring security against cross site scripting attacks on window.location.href

Vue paired with Rainyday.js

Issue with Vue.js v-for not displaying component