使用ExcelJS向XLSX单元格居中插入图片
本文记录了使用ExcelJS库向XLSX单元格里居中写入图片的方法。本文内容包括：xlsx 文件格式；xlsx 图片定位的长度单位；使用nativeColOff和nativeRowOff进行定位，不要使用官网给的定位方式；添加图片到数据表的总体流程和样例代码；注意事项等。
使用ExcelJS向XLSX单元格居中插入图片
摘要：
本文记录了使用ExcelJS库，向XLSX单元格里居中写入图片的方法。
技术要点包括：
-
xlsx 文件本质是一个 zip 文件，其内容是一组相互关联的 xml 文件，了解图片定位相关 xml 语法有助于解决问题；
-
xlsx 的图片定位的长度单位是 EMU (English Metric Unit)，单元格高度的单位是pt，列宽单位是0.1英寸，在写入图片时需要注意px、pt、emu、英寸之间的转换；
-
！！！使用nativeColOff和nativeRowOff进行定位，不要使用官网给的定位方式！！！；
-
写入 并保存 xlsx 后，用 excel 打开，可能会发现 excel 中显示的列宽与写入时给定的列宽不一致，二者有固定大小的偏差，因此在居中定位图片时需要考虑列宽偏差；
-
图片加载完毕后才能将图片写入到单元格，否则单元格内的图片将是空白的；
-
在浏览器端，ExcelJS只支持写入base64字符串格式的图片。可以将图片绘制到canvas元素然后将canvas转为base64字符串。
ExcelJS介绍
ExcelJS是前端开发者常用的读写xlsx文件的npm包。SheetJS也支持相同功能，但是想实现写入图片、自定义样式等非基础功能，需要购买Pro版。有xlsx-style库可以帮助实现上述功能，但是需要修改源码，比较麻烦，而且xlsx-style库已不再维护。详情可见两个包的官方文档：
ExcelJS:
GitHub：https://github.com/exceljs/exceljs
中文版：https://www.cnblogs.com/full-stack-linux-new/p/18249002 (版本更新信息不全)
SheetJS：
GitHub: https://sheetjs.com/
中文版：https://xlsx.nodejs.cn/docs/getting-started/examples/import/
XLSX文件格式
一个XLSX文件是以xlsx为后缀的zip文件。修改xlsx文件后缀为zip，然后解压，就会发现，xlsx文件本质上就是一堆相互关联的xml文件。
下图是修改文件扩展名前后：
下图是解压后的文件夹结构：
下图为xl文件夹的结构，这个文件夹是xlsx的核心：
下面为/xl/worksheets/sheet1.xml的代码，可以看见其标签是语义化的，比较好理解：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac"
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<dimension ref="A1:C7" />
<sheetFormatPr defaultRowHeight="15" outlineLevelRow="0" outlineLevelCol="0"
x14ac:dyDescent="55" />
<cols>
<col min="1" max="1" width="40" customWidth="1" />
<col min="2" max="2" width="5" customWidth="1" />
<col min="3" max="3" width="40" customWidth="1" />
</cols>
<sheetData>
<row r="1" spans="1:3" x14ac:dyDescent="0.25">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
<c r="C1" t="s">
<v>2</v>
</c>
</row>
<row r="2" spans="1:3" x14ac:dyDescent="0.25">
<c r="A2" t="s">
<v>3</v>
</c>
<c r="B2" t="s">
<v>4</v>
</c>
<c r="C2" t="s">
<v>5</v>
</c>
</row>
<row r="3" ht="100" customHeight="1" spans="1:3" x14ac:dyDescent="0.25">
<c r="A3" t="s">
<v>3</v>
</c>
<c r="B3" s="1" t="s">
<v>6</v>
</c>
<c r="C3" t="s">
<v>7</v>
</c>
</row>
<row r="4" spans="1:3" x14ac:dyDescent="0.25">
<c r="A4" t="s">
<v>3</v>
</c>
<c r="B4" t="s">
<v>8</v>
</c>
<c r="C4" t="s">
<v>7</v>
</c>
</row>
<row r="5" spans="1:3" x14ac:dyDescent="0.25">
<c r="A5" t="s">
<v>3</v>
</c>
<c r="B5" t="s">
<v>4</v>
</c>
<c r="C5" t="s">
<v>9</v>
</c>
</row>
<row r="6" spans="1:3" x14ac:dyDescent="0.25">
<c r="A6" t="s">
<v>3</v>
</c>
<c r="B6" t="s">
<v>10</v>
</c>
<c r="C6" t="s">
<v>11</v>
</c>
</row>
<row r="7" spans="1:3" x14ac:dyDescent="0.25">
<c r="A7" t="s">
<v>3</v>
</c>
<c r="B7" t="s">
<v>10</v>
</c>
<c r="C7" t="s">
<v>12</v>
</c>
</row>
</sheetData>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
<pageSetup orientation="portrait" horizontalDpi="4294967295" verticalDpi="4294967295"
scale="100" fitToWidth="1" fitToHeight="1" />
<drawing r:id="rId1" />
</worksheet>
系统的xlsx文件格式解析请见：
http://officeopenxml.com/anatomyofOOXML-xlsx.php
应着重了解 sheet、workbook、rels、drawing、media 等组成部分。
XLSX文件格式的规范基于XML格式规范，XML规范可自行搜索。
XLSX的图片定位
XLSX的图片定位方式遵循DrawML规范（http://officeopenxml.com/drwOverview.php）。
图片插入与定位的核心是在drawing.xml指定图片来源、定位起始点，和 终止点，如下方代码和注释所示：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<!--起锚定方式-->
<xdr:twoCellAnchor editAs="twoCell">
<!--起始点-->
<xdr:from>
<!--起始行列以及偏离值-->
<xdr:col>1</xdr:col>
<xdr:colOff>0</xdr:colOff>
<xdr:row>2</xdr:row>
<xdr:rowOff>0</xdr:rowOff>
</xdr:from>
<!--终点-->
<xdr:to>
<!--终止行列以及偏离值-->
<xdr:col>2</xdr:col>
<xdr:colOff>0</xdr:colOff>
<xdr:row>2</xdr:row>
<xdr:rowOff>999899</xdr:rowOff>
</xdr:to>
<!--图片信息-->
<xdr:pic>
<xdr:nvPicPr>
<xdr:cNvPr id="1" name="Picture 1">
<a:extLst>
<a:ext uri="{FF2B5EF4-FFF2-40B4-BE49-F238E27FC236}">
<a16:creationId
xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main"
id="{00000000-0008-0000-0000-000002000000}" />
</a:ext>
</a:extLst>
</xdr:cNvPr>
<xdr:cNvPicPr>
<a:picLocks noChangeAspect="1" />
</xdr:cNvPicPr>
</xdr:nvPicPr>
<!--图片来源-->
<xdr:blipFill>
<!-- r:embed="rId1"是drawings文件夹下_rels子文件夹中rel文件里图片的ID -->
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
r:embed="rId1" cstate="print" />
<a:stretch>
<a:fillRect />
</a:stretch>
</xdr:blipFill>
<xdr:spPr>
<a:xfrm>
<a:off x="0" y="0" />
<a:ext cx="0" cy="0" />
</a:xfrm>
<a:prstGeom prst="rect">
<a:avLst />
</a:prstGeom>
</xdr:spPr>
</xdr:pic>
<xdr:clientData />
</xdr:twoCellAnchor>
</xdr:wsDr>
定位细节请见：http://officeopenxml.com/drwOverview.php
长度单位
定位偏移量（rowOff，colOff）的单位是EMU(English Metric Unit)，其值必须是整数，否则会导致xlsx无法打开。
1 cm = 360000 EMUs
为了确定定位偏移量，需要知道图片的物理尺寸，以及单元格的物理尺寸。可通过html api获取客户端1px对应的物理长度。xlsx 单元格高度的单位是pt，列宽单位是0.1英寸，很容易与EMU换算。
我是通过阅读网络博客知道上述长度单位的，没有在ISO标准中确认过。
两个行为怪异的定位方式：tl/Ext，tl/br
ExcelJS Readme里记录了两种图片定位方式，包括：
-
使用 tl（topLeft的缩写） 和 ext（extent 的缩写）
ext的长宽是96dpi下，以px为单位的图片长宽。这个API需要进行繁琐的长度换算，而且行为怪异，无法正确地精细控制图片形状和位置，建议不要使用。 -
使用 tl 和 br（bottomRight 的缩写）
这API看着没啥问题，实际用起来也是一团糟，感兴趣的可以试试。建议不要使用。
ColOff & RowOff
在具备了上述知识后，阅读exceljs 的github上的readme文档的release（版本更新历史）部分，发现release4.3.0最下面一项说修改了定位bug。
查看此PR（pull request），发现修改了定位相关代码，增加了一个Anchor类
这个类的成员的语义与xml原生定位标签语义很相似。根据ExcelJS源码中对Anchor类的使用情况以及TS类型导出情况，我判断 release4.3.0后已经可以使用nativeCol/Row和nativeColOff/RowOff进行图片定位，但是Readme还没更新。
于是，我尝试使用这一方法。
果然，定位结果是准确的，效果如下所示：
定位技术细节
说完解决定位bug的过程，现在通过一个案例讲下ExcelJS图片定位的技术细节。
下面是一个封装好的写入图片的函数。参数workbook和worksheet都是ExcelJS库构造的对象，在此不赘述，详情请见ExcelJS官网。img是一个img元素，cellName是一个类似’B3’这样的字符串。这个函数是用来写入正方形图片的，长方形图片其实大同小异
此函数做了几件事情：
- 获取目标单元格的物理尺寸
- 获取图片尺寸，构造相同大小的canvas，将图片绘制到canvas上，然后转为base64字符串
- 根据目标单元格尺寸和图片尺寸，计算col/row和colOff/rowOff的值
- 将图片添加到workbook，获取imageID
- 将图片imageID添加到worksheet
function writeImgToCell(workbook: Workbook, worksheet: Worksheet, img: HTMLImageElement, cellName: string) {
const canvas = document.createElement('canvas')
const context = canvas.getContext('2d')
const rowNumber = parseInt(cellName.slice(1))
const columnNumber = cellName.charCodeAt(0) - 64
const row = worksheet.getRow(rowNumber)
const column = worksheet.getColumn(columnNumber)
const columnWidthInEMU = Math.floor((column.width! - 0.62) * WUM_MM * MM_EMU_RATIO)
const columnHeightInEMU = Math.floor(row.height * PT_MM_RATIO * MM_EMU_RATIO)
const imageSizeInEMU = columnWidthInEMU < columnHeightInEMU ? columnWidthInEMU : columnHeightInEMU // 0.1inch -> mm -> px
const rowSpaceTop = Math.floor((columnHeightInEMU - imageSizeInEMU) / 2)
canvas.height = img.naturalWidth
canvas.width = img.naturalWidth
context?.drawImage(img, 0, 0)
const myBase64Image = canvas.toDataURL()
const imageId = workbook.addImage({
base64: myBase64Image,
extension: 'png'
})
worksheet.addImage(imageId, {
tl: { nativeRow: 2, nativeCol: 1, nativeRowOff: rowSpaceTop, nativeColOff: 0 } as Anchor,
br: { nativeRow: 2, nativeCol: 2, nativeRowOff: rowSpaceTop + imageSizeInEMU, nativeColOff: 0 } as Anchor,
editAs: 'twoCell'
})
}
需要注意的是，必须在图片异步加载完成后再将图片添加到单元格，如下方代码所示：
const cellName = 'B3'
const img = new Image()
img.src = imageUrl //这里是图片url
img.crossOrigin = 'anonymous'
img.onload = () => {
writeImgToCell(workbook, worksheet, img, cellName)
}
同步添加图片会导致添加图片为空
另外，我发现，用excel打开导出的xlsx后，列宽总是比给定列宽小0.62。比如下图中，显示列宽为14.38，但是我给定的列宽是15。目前还不知道是什么原因。
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
27
0- 0
已为社区贡献1条内容
所有评论(0)