Android Apache POI Excel开发：散点图Y轴添加字符串分类值及自定义数据标签实现咨询

阿华AIGC实验室

2026-4-2

最近在做Android端用Apache POI生成Excel散点图的需求，需要把Y轴的字符串分类转换成对应数值lane，同时给每个系列的第一个点自定义从指定单元格读取的标签，还得隐藏掉不需要的图表元素。整理了实现代码和关键细节，跟大家分享下：

一、核心功能说明

将Y轴的字符串分类（如事件类型）映射为连续数值lane，实现时间线式散点图效果
给每个系列的第一个点添加自定义标签，标签内容从指定辅助单元格读取
隐藏系列连线，仅保留圆形标记点
关闭所有默认数据标签，仅显示自定义单个点标签
清除图表级数据标签，避免干扰自定义设置

二、完整实现代码

fun eventsTimelineScatter(
    drawSheet: XSSFSheet, // ΠΟΥ ζωγραφίζει (π.χ. "Charts")
    dataSheet: XSSFSheet, // ΑΠΟ ΠΟΥ διαβάζει (π.χ. "PlotData")
    lastRow: Int,
    title: String,
    anchorCols: IntRange,
    anchorRows: IntRange,
    xCol: Int, // στήλη χρόνου (Excel serial, numeric)
    typeCol: Int // στήλη κατηγορίας (String)
) {
    data class Cat(val key: String, val display: String)

    fun norm(raw: String?): Cat {
        val d = (raw ?: "Unknown").trim().replace(Regex("\\s+"), " ")
        return Cat(d.lowercase().ifEmpty { "unknown" }, d.ifEmpty { "Unknown" })
    }

    val byType = mutableMapOf<String, MutableList<Pair<Double,Double>>>()
    val keyToDisplay = mutableMapOf<String,String>()
    val laneOf = mutableMapOf<String,Int>()

    fun lane(k: String) = laneOf.getOrPut(k) { laneOf.size + 1 }.toDouble()

    var xMin = Double.POSITIVE_INFINITY
    var xMax = Double.NEGATIVE_INFINITY

    for (r in 1..lastRow) {
        val row = dataSheet.getRow(r) ?: continue
        val cat = norm(row.getCell(typeCol)?.toString())
        val x = row.getCell(xCol)?.numericCellValue ?: continue
        byType.getOrPut(cat.key) { mutableListOf() }.add(x to lane(cat.key))
        keyToDisplay.putIfAbsent(cat.key, cat.display)
        if (x < xMin) xMin = x
        if (x > xMax) xMax = x
    }

    if (byType.isEmpty()) return

    // 创建辅助单元格存储系列名称（供标签引用）
    val legendCol = (anchorCols.last + 2).coerceAtLeast(24)
    val legendRow0 = anchorRows.first
    val keysInOrder = keyToDisplay.keys.sortedBy { keyToDisplay[it]!!.lowercase() }

    keysInOrder.forEachIndexed { i, k ->
        val row = drawSheet.getRow(legendRow0 + i) ?: drawSheet.createRow(legendRow0 + i)
        row.createCell(legendCol).setCellValue(keyToDisplay[k])
    }

    // 初始化图表容器与坐标轴
    val drawing = drawSheet.createDrawingPatriarch() as XSSFDrawing
    val anchor = drawing.createAnchor(0,0,0,0, anchorCols.first, anchorRows.first, anchorCols.last+1, anchorRows.last+1)
    val chart = drawing.createChart(anchor) as XSSFChart

    val xAxis = chart.createValueAxis(AxisPosition.BOTTOM).apply {
        crosses = AxisCrosses.MIN
        majorTickMark = AxisTickMark.OUT
        minorTickMark = AxisTickMark.NONE
        tickLabelPosition = AxisTickLabelPosition.LOW
    }

    val yAxis = chart.createValueAxis(AxisPosition.LEFT).apply {
        majorTickMark = AxisTickMark.NONE
        minorTickMark = AxisTickMark.NONE
        tickLabelPosition = AxisTickLabelPosition.NONE
    }

    // 创建散点图数据系列
    val data = chart.createData(ChartTypes.SCATTER, xAxis, yAxis) as XDDFScatterChartData
    data.setVaryColors(false)
    try {
        data.style = ScatterStyle.MARKER
    } catch (_: Throwable) {}

    keysInOrder.forEachIndexed { i, k ->
        val pts = byType[k]!!.sortedBy { it.first }
        val xs = XDDFDataSourcesFactory.fromArray(pts.map { it.first }.toTypedArray())
        val ys = XDDFDataSourcesFactory.fromArray(pts.map { it.second }.toTypedArray())
        val s = data.addSeries(xs, ys) as XDDFScatterChartData.Series
        val ref = CellReference(drawSheet.sheetName, legendRow0 + i, legendCol, true, true)
        s.setTitle(null, ref)
        s.setMarkerStyle(MarkerStyle.CIRCLE)
        s.setMarkerSize(7)
        s.isSmooth = false
    }

    chart.plot(data)
    chart.setTitleText(title)
    chart.setTitleOverlay(false)

    // --- CT层级深度自定义（XDDF API未覆盖的细节）---
    chart.ctChart.plotArea.scatterChartList.lastOrNull()?.let { sc ->
        // 全局设置：仅显示标记点，隐藏连线
        val st = sc.scatterStyle ?: sc.addNewScatterStyle()
        st.`val` = STScatterStyle.MARKER

        sc.serList.forEachIndexed { i, ser ->
            // 隐藏当前系列的连线
            val sp = if (ser.isSetSpPr) ser.spPr else ser.addNewSpPr()
            val ln = if (sp.isSetLn) sp.ln else sp.addNewLn()
            if (!ln.isSetNoFill) ln.addNewNoFill()

            // 重置系列默认数据标签，创建自定义标签组
            if (ser.isSetDLbls) ser.unsetDLbls()
            val dLbls = ser.addNewDLbls()

            // 仅给第0个点添加标签，内容引用辅助单元格
            val dl = dLbls.addNewDLbl()
            dl.addNewIdx().`val` = 0L
            val ref = CellReference(drawSheet.sheetName, legendRow0 + i, legendCol, true, true)
            dl.addNewTx().addNewStrRef().f = ref.formatAsString()

            // 关闭所有默认标签显示项
            dl.addNewShowSerName().`val` = false
            dl.addNewShowVal().`val` = false
            dl.addNewShowCatName().`val` = false
            dl.addNewShowLegendKey().`val` = false
            dl.addNewShowPercent().`val` = false
            dl.addNewShowBubbleSize().`val` = false

            // 设置标签位置
            dl.addNewDLblPos().`val` = STDLblPos.L
        }

        // 清除图表级数据标签，避免干扰自定义设置
        if (sc.isSetDLbls) sc.unsetDLbls()
    }
}