Question 335127 in Html Question posted in
Some basic documentation can be found here.

Html – How do I change my code to fill an excel spreadsheet with colour?

I have some code that imports an image and converts it to an excel spreadsheet pixel by pixel. However, I’ve only been able to write the RGB into the cell and want to colour the cell instead.

I’m using html and javascript. My code is copied below. I haven’t found much in the way of googling, but if I’ve missed something obvious please let me know!

    function createExcelWorkbook(imageData, width, height) {
        const wb = XLSX.utils.book_new();
        const wsData = [];

        for (let y = 0; y < height; y++) {
            const rowData = [];
            for (let x = 0; x < width; x++) {
                const pixelIndex = (y * width + x) * 4; // Each pixel has 4 values (R, G, B, A)
                const red = imageData[pixelIndex];
                const green = imageData[pixelIndex + 1];
                const blue = imageData[pixelIndex + 2];
                rowData.push(`RGB(${red}, ${green}, ${blue})`);
            }
            wsData.push(rowData);
        }
Tags:

3

Answers


  1. 0

    You can use the fill property in the style of each cell. You’ll need to modify the code to include the style information for each cell.

    function createExcelWorkbook(imageData, width, height) {
    const wb = XLSX.utils.book_new();
    const wsData = [];

    for (let y = 0; y < height; y++) {
        const rowData = [];
        for (let x = 0; x < width; x++) {
            const pixelIndex = (y * width + x) * 4;
            const red = imageData[pixelIndex];
            const green = imageData[pixelIndex + 1];
            const blue = imageData[pixelIndex + 2];

            // Create a style object with the desired fill color
           const style = {
               fill: {
                   fgColor: { rgb: `FF${red.toString(16).padStart(2, '0')}${green.toString(16).padStart(2, '0')}${blue.toString(16).padStart(2, '0')}` }
               }
           };

            // Push an object representing both the cell value and style into rowData
            rowData.push({ v: '', s: style });
        }
        wsData.push(rowData);
    }

}

    If you need to download you can create blob after converting the data a worksheet like this

    // Convert the data to a worksheet and add it to the workbook
const ws = XLSX.utils.aoa_to_sheet(wsData);
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// Create a blob and download the workbook
const blob = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'blob' });
saveAs(blob, 'imageData.xlsx');
    Login or Signup to reply.
  2. 0

    Unfortunately, the XLSX.js library does not support cell background color or any other cell styling. This is because the library focuses on data, and not on styling or formatting.

    If you want to color cells based on their RGB values, you would need to use a library that supports Excel cell styling. One such library is ExcelJS.

    Here’s an example of how you could modify your code to use ExcelJS and set the fill of each cell to its corresponding RGB color:

    const ExcelJS = require('exceljs');

function createExcelWorkbook(imageData, width, height) {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Image Data');

    for (let y = 0; y < height; y++) {
        for (let x = 0; x < width; x++) {
            const pixelIndex = (y * width + x) * 4; // Each pixel has 4 values (R, G, B, A)
            const red = imageData[pixelIndex];
            const green = imageData[pixelIndex + 1];
            const blue = imageData[pixelIndex + 2];
            const cell = worksheet.getCell(y + 1, x + 1);
            cell.fill = {
                type: 'pattern',
                pattern: 'solid',
                fgColor: { argb: rgbToArgb(red, green, blue) }
            };
        }
    }

    return workbook;
}

function rgbToArgb(red, green, blue) {
    return ((255 << 24) | (red << 16) | (green << 8) | blue).toString(16).padStart(8, '0');
}

    In this code, rgbToArgb is a helper function that converts RGB values to ARGB, which is the format ExcelJS uses for colors. The fill property of a cell is used to set the background color of the cell. The type is set to ‘pattern’ and the pattern is set to ‘solid’ to create a solid color fill. The fgColor is set to the ARGB color.

    Please note that you’ll need to install the exceljs package to use this code. You can do this by running npm install exceljs in your project directory.

    Login or Signup to reply.
  3. 0

    To fill an Excel spreadsheet with color using the xlsx library in JavaScript, you can modify your code to include style information along with the cell data. Here’s how you can modify your createExcelWorkbook function to fill the cells with color:

    const XLSX = require('xlsx');

function createExcelWorkbook(imageData, width, height) {
    const wb = XLSX.utils.book_new();
    const wsData = [];

    // Create a new worksheet
    const ws = XLSX.utils.aoa_to_sheet([]);

    for (let y = 0; y < height; y++) {
        const rowData = [];
        for (let x = 0; x < width; x++) {
            const pixelIndex = (y * width + x) * 4; // Each pixel has 4 values (R, G, B, A)
            const red = imageData[pixelIndex];
            const green = imageData[pixelIndex + 1];
            const blue = imageData[pixelIndex + 2];
            const color = { rgb: `rgb(${red},${green},${blue})` };

            // Add cell with color and value
            const cell = { v: `RGB(${red}, ${green}, ${blue})`, s: { fill: color } };
            const cellRef = XLSX.utils.encode_cell({ r: y, c: x });
            ws[cellRef] = cell;
        }
        wsData.push(rowData);
    }

    // Add the worksheet to the workbook
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

    // Write the workbook to a file or return it
    return wb;
}

// Example usage:
const width = 3;
const height = 3;
const imageData = [
    255, 0, 0,  // Red pixel
    0, 255, 0,  // Green pixel
    0, 0, 255,  // Blue pixel
    255, 255, 0,  // Yellow pixel
    0, 255, 255,  // Cyan pixel
    255, 0, 255,  // Magenta pixel
    128, 128, 128,  // Gray pixel
    255, 128, 0,  // Orange pixel
    0, 128, 255   // Light blue pixel
];

const workbook = createExcelWorkbook(imageData, width, height);
XLSX.writeFile(workbook, "colored_cells.xlsx");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search