java如何代码识别excel单元格 删除线_Excel导出长时间占用cpu优化

一、引言

1.1 案例背景

线上监控报警，cpu长时间占用高，后续有客户反映订单导出了很长时间都没导出来。

1.2 本文概述

本文主要讲述案例的分析过程、处理过程和最终成果。

二、分析处理

2.1 问题定位

基本思路，先找出长时间占用cpu的线程，获取线程的堆栈信息，分析相关代码块。

2.1.1 获取服务进程id

# jdk命令 获取所有java进程 jps -l# 或者 ps命令 查看java进程ps -ef | grep 启动的jar名，一般是项目名

2.1.2 找到cpu占用高的线程

# 查找进程中线程占用情况,获取到cpu占用高的线程idtop -Hp pid

呈现如下，按照cpu排序，拿到占用最高的PID.

2.1.3 查看线程堆栈信息

# 获得线程pid对应16进制的数printf %x 线程pid# 获取堆栈信息jstack 进程id ｜ grep -A 20 16进制线程pid# 堆栈信息如下"export-service-thread-1" #44e prio=5 os_prio=31 tid=0x00007fc37fd7d000 nid=0x1af03 runnable [0x000070001d09d000]   java.lang.Thread.State: RUNNABLE        at org.apache.xmlbeans.impl.store.Locale.count(Locale.java:2055)        at org.apache.xmlbeans.impl.store.Xobj.count_elements(Xobj.java:2050)        at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTMergeCellsImpl.sizeOfMergeCellArray(Unknown Source)        - locked <0x00000007a71b3080> (a org.apache.xmlbeans.impl.store.Locale)        at org.apache.poi.xssf.usermodel.XSSFSheet.addMergedRegion(XSSFSheet.java:279)        at org.apache.poi.xssf.streaming.SXSSFSheet.addMergedRegion(SXSSFSheet.java:342)        at com.xxServiceImpl.mergeRows(xxServiceImpl.java:747)        at com.xxServiceImpl.writeContent(xxServiceImpl.java:809)

2.1.4 分析堆栈

根据堆栈信息可以看到线程一直处于RUNNABLE状态，而且是一直在执行poi的addMergedRegion方法导致的，这个方法的作用是合并单元格。

源码如下：

public int addMergedRegion(CellRangeAddress region) {    // 校验    region.validate(SpreadsheetVersion.EXCEL2007);    validateArrayFormulas(region);    // 合并  worksheet为CTWorksheet    CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells();    CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();    ctMergeCell.setRef(region.formatAsString());    // 计算合并过的单元格总数    return ctMergeCells.sizeOfMergeCellArray();}

代码块2.1

通过堆栈我们可以看到线程一直在执行sizeOfMergeCellArray

源码如下：

public int sizeOfMergeCellArray() {    // 统计前需要先获取锁    synchronized(this.monitor()) {        this.check_orphaned();        // 统计合并单元类型的单元格个数        return this.get_store().count_elements(MERGECELL$0);    }}public int count_elements (QName name){    return _locale.count( this, name, null );}

代码块2.2

通过源码可以看到最终调用到count方法

源码如下：

int count(Xobj parent, QName name, QNameSet set){    int n = 0;    // 遍历所有单元格判断如果是合并单元格类型就+1    for (Xobj x = findNthChildElem(parent, name, set, 0);         x != null; x = x._nextSibling){        if (x.isElem()){            if (set == null){                if (x._name.equals(name))                    n++;            }            else if (set.contains(x._name))                n++;        }    }    return n;}

代码块2.3

分析到这我们得知每次合并单元格都会遍历所有单元格，去统计当前sheet合并的单元格数，如果10万的数据行，每个数据行有10条数据，那么每次需要循环100万次。如果有很多的单元格合并操作，那么这将会是一个庞大的性能损耗。并且代码块2.2可以看到还涉及到锁竞争问题。

我们的excel表因为有大量的单元格合并操作，因此就触发了这个问题。

2.2 合并单元格优化

基本思路：已经得知性能损耗主要是在统计合并单元格的地方，所以我们可以考虑参考源码自己封装一个合并单元格的方法。

通过addMergedRegion的源码我们得知，合并前需要先获取CTMergeCells，然后再进行合并操作，而CTMergeCells是由CTWorksheet生成的，那么就要考虑不同版本的Sheet如何去获取Sheet的CTWorksheet。

Poi包含三个版本的Sheet, 分别为 SXSSFSheet、XSSFSheet、HSSFSheet，简单提一下它们的区别。

HSSF	针对Excel2003版本的excel表进行数据处理的，导出的数据行数有限制，最多导出65535行。文件扩展名为xls。
XSSF	针对Excel2007版本之后的excel表数据，最多导出1048576行。数据量大可能导致内存溢出。文件扩展名为xlsx。
SXSSF	基于XSSFSheet的优化，限制内存中的数据，内存中的数据达到一定程度就写到临时文件，不过涉及到多次io，因此也是一种时间换空间的策略。

在了解三个版本的区别后，我们查看源码如何获取CTWorksheet。

XSSFSheet比较简单，本身就提供一个getCTWorksheet()方法，如下：

public CTWorksheet getCTWorksheet() {    return this.worksheet;}

代码块2.4

通过参看源码发现SXSSF是基于XSSFSheet进行封装的，所以我们可以先获取内部的XSSFSheet再去获取CTWorksheet。

获取方式可以通过如下方式：

SXSSFSheet sxssfSheet = (SXSSFSheet) sheet;SXSSFWorkbook sxssfWorkbook = (SXSSFWorkbook) sxssfSheet.getWorkbook();// 根据sheetName获取XSSFSheetXSSFSheet xssfSheet = sxssfWorkbook.getXSSFWorkbook().getSheet(sxssfSheet.getSheetName());ctWorksheet = xssfSheet.getCTWorksheet();

代码块2.5

剩下一个HSSF我从源码里并没有找到相关获取的方法，于是我又去看了HSSF合并单元格的实现，发现HSSF的实现方式不太一样，HSSF每次合并单元格，就会把单元格加入到集合中，这样每次只要获取集合的个数，就可以知道有多少个合并的单元格了。因为HSSF可以直接使用原本的实现。

public int addMergedRegion(int rowFrom, int colFrom, int rowTo, int colTo) {    if (rowTo < rowFrom) {        throw new IllegalArgumentException("The 'to' row (" + rowTo                                           + ") must not be less than the 'from' row (" + rowFrom + ")");    }    if (colTo < colFrom) {        throw new IllegalArgumentException("The 'to' col (" + colTo                                           + ") must not be less than the 'from' col (" + colFrom + ")");    }    // 合并    MergedCellsTable mrt = getMergedRecords();    mrt.addArea(rowFrom, colFrom, rowTo, colTo);    // 获取表中合并过的单元格数    return mrt.getNumberOfMergedRegions()-1;}// 获取表中合并过的单元格数public int getNumberOfMergedRegions() {    return _mergedRegions.size();}

代码块2.6

根据以上分析，我拆分出一个工具类如下：

public class ExportCommonUtils {    /**     * 合并单元格     * */    public static void addMergedRegion(Sheet sheet, CellRangeAddress region){        CTWorksheet ctWorksheet;        // 根据Sheet类型执行不同的单元格合并处理        if (sheet instanceof  SXSSFSheet){            SXSSFSheet sxssfSheet = (SXSSFSheet) sheet;            SXSSFWorkbook sxssfWorkbook = (SXSSFWorkbook) sxssfSheet.getWorkbook();            XSSFSheet xssfSheet = sxssfWorkbook.getXSSFWorkbook().getSheet(sxssfSheet.getSheetName());            ctWorksheet = xssfSheet.getCTWorksheet();        }else if (sheet instanceof XSSFSheet){            XSSFSheet xssfSheet = (XSSFSheet) sheet;            ctWorksheet = xssfSheet.getCTWorksheet();        }else{            sheet.addMergedRegion(region);            return;        }        // 合并单元格        CTMergeCells ctMergeCells = ctWorksheet.isSetMergeCells() ? ctWorksheet.getMergeCells() : ctWorksheet.addNewMergeCells();        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();        ctMergeCell.setRef(region.formatAsString());    }}

代码块2.7

ps:

因为我们业务并不需要统计这个excel表有多少个合并过的单元格，所以我直接把统计这一步去掉。当然如果需要这个返回值，也可以通过一个临时变量来统计，每次都合并都加1。

还有校验的步骤也去掉了，因为校验只是一些越界判断、合并区域是否合法等判断，在合并大量单元格的情况下，也是有部分性能损耗的，所以我把这块也删掉了，由业务方对这块区域合并的可靠性负责。

三、总结

优化效果：

优化前处理1w条需要合并的数据，需要耗时1个多小时，优化后只需要几秒，并且随着数据量的上升，提升的效果越明显。也不会长时间占用cpu了。

ps:

虽然这里把合并数据优化到秒级别，但是如果有大量导出事件同时触发，可能还是会短时间内cpu占用高。因此这块需要交给线程池处理，控制导出的线程数，导出本身不是一种实时性要求很高的业务，因此可以不用追求高实时性。