How to locate a picture？

[yongjun925]

How do I tell which row and column the picture belongs to？

const Excel = require('exceljs')
var workbook = new Excel.Workbook();
workbook.xlsx.readFile('test.xlsx')
    .then(function () {
        // fs.writeFileSync('1.png', workbook.getImage(0).buffer)
        var worksheet = workbook.getWorksheet('sheet1');
        console.log(worksheet.getCell('AY2').style.border)
        console.log(worksheet.getImages()[1])
        console.log(worksheet.getImages()[0])
    });

{ left: { style: 'thin', color: { indexed: 8 } },
  right: { style: 'thin', color: { indexed: 8 } },
  top: { style: 'thin', color: { indexed: 8 } },
  bottom: { style: 'thin', color: { indexed: 8 } } }
{ type: 'image',
  imageId: 0,
  range:
   { tl: { col: 49.5854015625, row: 2.8599277777777776 },
     br: { col: 52.926940625, row: 10.136783333333334 } } }
{ type: 'image',
  imageId: 1,
  range:
   { tl: { col: 50.283765625, row: 1.4515555555555555 },
     br: { col: 53.239728125, row: 6.94735 } } }

[yongjun925]

I think if I can get the data, I can probably judge the picture in the row and column

drawing1.xml

 <xdr:twoCellAnchor>
        <xdr:from>
            <xdr:col>50</xdr:col>
            <xdr:colOff>181610</xdr:colOff>
            <xdr:row>1</xdr:row>
            <xdr:rowOff>81280</xdr:rowOff>
        </xdr:from>
        <xdr:to>
            <xdr:col>50</xdr:col>
            <xdr:colOff>2073426</xdr:colOff>
            <xdr:row>1</xdr:row>
            <xdr:rowOff>1070523</xdr:rowOff>
        </xdr:to>
        <xdr:pic>
...
<xdr:twoCellAnchor>
        <xdr:from>
            <xdr:col>49</xdr:col>
            <xdr:colOff>374657</xdr:colOff>
            <xdr:row>2</xdr:row>
            <xdr:rowOff>154787</xdr:rowOff>
        </xdr:from>
        <xdr:to>
            <xdr:col>50</xdr:col>
            <xdr:colOff>1873242</xdr:colOff>
            <xdr:row>2</xdr:row>
            <xdr:rowOff>1464621</xdr:rowOff>
        </xdr:to>
        <xdr:pic>
...

[hankolsen]

Similar to #526 ?

[Siemienik]

@yongjun925
is it works for You?

worksheet.getRow(Math.floor(image.range.tl.row)+1)
worksheet.getCol(Math.floor(image.range.tl.col)+1)

[arimus]

I was trying to match things up, but in my case I had images bound to the cell. This means I didn't need to fuzzy find the row/col because they were exact. But in any case I still had the issue of correlating images with the proper cells. Here is what appears to work. It may not be correct for all cases, but functionally appears to be working on first pass and I wanted to share:

  const ExcelJS = require('exceljs');
  const workbook = new ExcelJS.Workbook();
  const data = await workbook.xlsx.readFile('./test.xlsx');

  const worksheet = workbook.worksheets[0];
  for (const image of worksheet.getImages()) {
    console.log('processing image row', image.range.tl.nativeRow, 'col', image.range.tl.nativeCol, 'imageId', image.imageId);
    // fetch the media item with the data (it seems the imageId matches up with m.index?)
    const img = workbook.model.media.find(m => m.index === image.imageId);
    fs.writeFileSync(`${image.range.tl.nativeRow}.${image.range.tl.nativeCol}.${img.name}.${img.extension}`, img.buffer);
  }

This will write all the files out in row / col order.

[huongtran84]

I have tried this code and until row 20 it seem infinite loop :

[RiggedToBlow]

@arimus
I tried your code
it does write the images
but I tried matching it with the rows and cols it seems to be off and not matching the rows and cols,
anyone can help please

[jhking17]

I was trying to match things up, but in my case I had images bound to the cell. This means I didn't need to fuzzy find the row/col because they were exact. But in any case I still had the issue of correlating images with the proper cells. Here is what appears to work. It may not be correct for all cases, but functionally appears to be working on first pass and I wanted to share:

  const ExcelJS = require('exceljs');
  const workbook = new ExcelJS.Workbook();
  const data = await workbook.xlsx.readFile('./test.xlsx');

  const worksheet = workbook.worksheets[0];
  for (const image of worksheet.getImages()) {
    console.log('processing image row', image.range.tl.nativeRow, 'col', image.range.tl.nativeCol, 'imageId', image.imageId);
    // fetch the media item with the data (it seems the imageId matches up with m.index?)
    const img = workbook.model.media.find(m => m.index === image.imageId);
    fs.writeFileSync(`${image.range.tl.nativeRow}.${image.range.tl.nativeCol}.${img.name}.${img.extension}`, img.buffer);
  }

This will write all the files out in row / col order.

Very well. Thank you for your help! 👍

[lasthead]

I was trying to match things up, but in my case I had images bound to the cell. This means I didn't need to fuzzy find the row/col because they were exact. But in any case I still had the issue of correlating images with the proper cells. Here is what appears to work. It may not be correct for all cases, but functionally appears to be working on first pass and I wanted to share:

  const ExcelJS = require('exceljs');
  const workbook = new ExcelJS.Workbook();
  const data = await workbook.xlsx.readFile('./test.xlsx');

  const worksheet = workbook.worksheets[0];
  for (const image of worksheet.getImages()) {
    console.log('processing image row', image.range.tl.nativeRow, 'col', image.range.tl.nativeCol, 'imageId', image.imageId);
    // fetch the media item with the data (it seems the imageId matches up with m.index?)
    const img = workbook.model.media.find(m => m.index === image.imageId);
    fs.writeFileSync(`${image.range.tl.nativeRow}.${image.range.tl.nativeCol}.${img.name}.${img.extension}`, img.buffer);
  }

This will write all the files out in row / col order.

Thank's a lot!

[SimeonRolev]

I work on a viewer that reads .xlsx files and I try to position the image properly. I have this image in my table, viewed with LibreOffice:

When I load the worksheet with exceljs and print out the image's image.range.tl and image.range.br I get those:

// Tl:
let tl = {
    nativeCol: 0,  // Correct
    nativeColOff: 424440,  // ?
    nativeRow: 2,  // Correct
    nativeRowOff: 455040,  // ?
    col: 0.9999950470529966, // NOT correct. I'd expect something like 0.33
    colWidth: 201900,   // ?
    row: 2.632,  // Looks close to correct
    rowHeight: 720000  // ?
}

// Br:
let br = {
    nativeCol: 4,  // Correct
    nativeColOff: 483480,  // ?
    nativeRow: 5, // Correct
    nativeRowOff: 933840,  // ?
    col: 4.999991922455574,  // NOT correct. I'd expect something like 4.5
    colWidth: 123800,  // ?
    row: 5.948060913705584,  // NOT correct. I'd expect something like 5.7
    rowHeight: 985000  // ?
}

nativeCol and nativeRow seem to be working fine and can be used to locate the cells that contain the image corners. Everything else is unclear to me.

• col and row are not correct - especially if I place a corner of the picture past 50% of the width of a column, the col value is close to .999; I feel like it always displays higher value than the actual position.
• Also, I don't understand what values do nativeColOff, nativeRowOff, colWidth, colHeight represent, in what units and can I use them to calculate the location in pixels?

Possibly related to #1640