Read excel cell embed image

ExcelPic.kt

package cn.crowddigital.pic.pic.service

import org.apache.commons.compress.archivers.zip.ZipFile
import org.apache.poi.ooxml.util.PackageHelper
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.junit.jupiter.api.Test
import java.io.ByteArrayInputStream
import java.io.File
import java.io.InputStream
import java.nio.file.Files
import javax.xml.parsers.DocumentBuilderFactory


/**
 *
 * @author hzhou
 * @created 2024/7/8 11:36
 **/
class ExcelPicTest {
    @Test
    fun readExcel() {
        val filename = "C:\\Users\\Administrator\\Desktop\\要读取的文件.xlsx"
        val bytes = Files.readAllBytes(File(filename).toPath())
        val data = mutableListOf<MutableMap<String, Any?>>()
        XSSFWorkbook(ByteArrayInputStream(bytes)).use { wb ->
            val sheet = wb.getSheetAt(0)
            // 读取标题行
            val titleRow = sheet.getRow(0)
            val titles = mutableListOf<String>()
            titleRow.forEach { cell ->
                titles.add(cell.stringCellValue)
            }

            // 读取数据行
            sheet.forEachIndexed { index, row ->
                if (index == 0) {
                    return@forEachIndexed
                }
                val map = mutableMapOf<String, Any?>()
                row.forEachIndexed { cellIndex, cell ->
                    val title = titles[cellIndex]
                    val value = when (cell.cellType) {
                        CellType.STRING -> {
                            cell.stringCellValue
                        }

                        CellType.NUMERIC -> {
                            cell.numericCellValue
                        }

                        CellType.BOOLEAN -> {
                            cell.booleanCellValue
                        }

                        CellType.FORMULA -> {
                            cell.cellFormula
                        }

                        CellType.ERROR, CellType.BLANK, CellType._NONE -> {
                            null
                        }

                        else -> {
                            null
                        }
                    }
                    map[title] = value
                }
                data.add(map)
            }
        }

        // 处理所有的图片记录
        val images = mutableMapOf<String, ByteArray>()
        val imageInfos = extractExcelImages(ByteArrayInputStream(bytes))
        val zipFile = ZipFile(filename)
        val entries = zipFile.entries
        while (entries.hasMoreElements()) {
            val entry = entries.nextElement()
            if (entry.name.startsWith("xl/media/")) {
                imageInfos.find { "xl/" + it.target == entry.name }?.let { image ->
                    val pictureBytes = zipFile.getInputStream(entry).readBytes()
                    images[image.name] = pictureBytes
                }
            }
        }

        data.forEach { record ->
            record.forEach { (k, v) ->
                if (v is String && v.startsWith("_xlfn.DISPIMG")) {
                    val id = v.substringAfter("\"").substringBefore("\"")
                    record[k] = images[id]
                    saveImageToFile(images[id]!!, k)
                }
            }
        }

        println(data)
    }

    private fun extractExcelImages(stream: InputStream): List<EmbedImage> {
        val result = mutableListOf<EmbedImage>()
        PackageHelper.open(stream).use { opc ->
            // 获取每一张图片的信息
            opc.parts.find { it.partName.name == "/xl/cellimages.xml" }?.let { part ->
                // 解析xml
                val factory = DocumentBuilderFactory.newInstance()
                val builder = factory.newDocumentBuilder()

                // 获取图片id
                val cNvPrElements = builder.parse(part.inputStream).getElementsByTagName("xdr:cNvPr")
                // 获取embed的id
                val blipElements = builder.parse(part.inputStream).getElementsByTagName("a:blip")

                assert(cNvPrElements.length == blipElements.length) { "图片数量不一致" }
                for (i in 0 until cNvPrElements.length) {
                    val cNvPrElement = cNvPrElements.item(i)
                    val blipElement = blipElements.item(i)
                    result.add(
                        EmbedImage(
                            id = cNvPrElement.attributes.getNamedItem("id").nodeValue,
                            name = cNvPrElement.attributes.getNamedItem("name").nodeValue,
                            embedId = blipElement.attributes.getNamedItem("r:embed").nodeValue,
                            target = ""
                        )
                    )
                }
            }

            // 获取图片的路径
            opc.parts.find { it.partName.name == "/xl/_rels/cellimages.xml.rels" }?.let { part ->
                val factory = DocumentBuilderFactory.newInstance()
                val builder = factory.newDocumentBuilder()

                val relations = builder.parse(part.inputStream).getElementsByTagName("Relationship")
                for (i in 0 until relations.length) {
                    val relation = relations.item(i)
                    val id = relation.attributes.getNamedItem("Id").nodeValue
                    val target = relation.attributes.getNamedItem("Target").nodeValue
                    result.find { it.embedId == id }?.target = target
                }
            }
        }

        return result
    }

    private fun saveImageToFile(pictureBytes: ByteArray, fileName: String) {
        val file = File("d:\\${fileName}.jpg")
        file.writeBytes(pictureBytes)
    }

    data class EmbedImage(val id: String, val name: String, val embedId: String, var target: String)
}