This article has been authorized by the author Ye Fuhong netease cloud community.

Welcome to visit netease Cloud Community to learn more about Netease’s technical product operation experience.


Yesterday, a business feedback said that the quotation information failed to be exported. After checking the log, I found that NullpointException was reported when the exported record was exported to Excel. The error is the code in red (dataRow is null) in the following method.

private void MergedRowRegion(Sheet sheet, int endRowNum, int rowNum){        if(endRowNum > rowNum){
            Row dataRow = sheet.getRow(rowNum);            for(int i=0; i< 8; i++){                Cell cell = dataRow.getCell(i);
                if(cell ! = null){ sheet.addMergedRegion(new CellRangeAddress(rowNum, endRowNum, i, i)); }}}}Copy the code


This method merges the cells from rowNum rows to endRowNum columns 1 through 7. After checking the code and data repeatedly for several times, I did not find anything that would cause the dataRow to be null except the large amount of data. I finally found the reason through debugging, and now I will record the problem.

A brief description of the export business: The exported information contains the product, supplier, and supplier quote. An item can contain multiple supplier information, and each supplier can have multiple quotes. Excel format is as follows:

Therefore, when a commodity corresponds to multiple suppliers and offers, the information of the commodity and supplier needs to be combined into cells. The steps for writing data to Excel look like this: First, write the commodity information in rowNum row, then supplier information and quotation information, and merge the supplier information into cells. Then return the last row number endRowNum. Finally, pass endRowNum and rowNum into the above method to merge the cell of commodity attributes. There are altogether 1739 quotations for the item with abnormal price, the quantity is a little too much.

_rows is a TreeMap<Integer, SXSSFRow>, and Key is the index of the Row, starting from 0. 1,2,3… Incrementing, Value is the corresponding row data. The key in _rows is 739, 740,741,742….. All the way up to 1739 there are 1,000 records. Put in 1,739 pieces of data, only the last 1,000 pieces of data. When _rows is greater than or equal to 1000 rows, insert another row and the size is still 1000, but the first row is squeezed out (first in, first out).

Check SXSSFSheet’s createRow method to find the reason, code is as follows:


public SXSSFRow createRow(int rownum) {
  ......

   SXSSFRow newRow = new SXSSFRow(this, initialAllocationSize);   this._rows.put(Integer.valueOf(rownum), newRow);   this.allFlushed = false;   if(this._randomAccessWindowSize >= 0 && this._rows.size() > this._randomAccessWindowSize) {   try {        this.flushRows(this._randomAccessWindowSize);
     } catch (IOException var7) {        throw new RuntimeException(var7);
     }  returnnewRow; . }Copy the code
The argument rownum is the subscript of the row and the key of _rows.Copy the code
Invokes the enclosing flushRows (enclosing _randomAccessWindowSize); Method,Copy the code
FlushRows: flushRows flushRows: flushRows: flushRows: flushRows: flushRows: flushRows: flushRows: flushRows: flushRows: flushRows: flushRows: flushRowsCopy the code
public void flushRows(int remaining) throws IOException {        while(this._rows.size() > remaining) {            this.flushOneRow();
        }        if(remaining == 0) {            this.allFlushed = true; }}flushOneRow private void flushOneRow() throws IOException { Integer firstRowNum = (Integer)this._rows.firstKey(); // Retrieve the smallest keyif(firstRowNum ! = null) { int rowIndex = firstRowNum.intValue(); SXSSFRow row = (SXSSFRow)this._rows.get(firstRowNum); this._writer.writeRow(rowIndex, row); // this._rows.remove(firstRowNum); this.lastFlushedRowNumber = rowIndex; }}Copy the code

The flushOneRow method takes the smallest piece of data in the key, writes it to the file, and then deletes it from _rows, keeping _rows at 1000.

This explains why there are 1739 pieces of data in there, only 1000 in _rows, and all that exists are later ones.

So when sheet.getRow(rowNum) is called to retrieve the data deleted from _rows, null is returned.

As you can see from the source code, the _rows size is 100 by default, not 1000.

 public static final int DEFAULT_WINDOW_SIZE = 100; 
 public SXSSFWorkbook(XSSFWorkbook workbook, int rowAccessWindowSize, boolean compressTmpFiles,Copy the code
boolean useSharedStringsTable) {        this._sxFromXHash = new HashMap();        this._xFromSxHash = new HashMap();        this._randomAccessWindowSize = 100;        this._compressTmpFiles = false; this._sharedStringSource = null; this.setRandomAccessWindowSize(rowAccessWindowSize); this.setCompressTempFiles(compressTmpFiles); .Copy the code

This can be modified via constructors:


public SXSSFWorkbook(int rowAccessWindowSize) {        this((XSSFWorkbook)null, rowAccessWindowSize);
    }Copy the code


Free experience cloud security (EASY Shield) content security, verification code and other services

For more information about netease’s technology, products and operating experience, please click here.


Distributed storage System Reliability Series 5: Copy placement Algorithm & CopySet Replication