java - Read empty cell (first column) using Apache POI Event model -

this enhancement old question: read empty cell using apache poi event model.

actually trying read empty cells , works when empty cells occur in middle or end columns. however, if first column has empty cells, doesn't treat blankrecord.sid in below code. , due value cell set empty string. want treat first column blankrecord set null.

here code xls:

public void processrecord(record record) {         int thisrow = -1;         string thisstr = null;          switch (record.getsid()) {             case boundsheetrecord.sid:                 boundsheetrecords.add(record);                 break;             case bofrecord.sid:                 bofrecord br = (bofrecord)record;                 if(br.gettype() == bofrecord.type_worksheet) {                     // works ordering bsrs location of bofrecords, , knowing                     // process bofrecords in byte offset order                     if(orderedbsrs == null) {                         orderedbsrs = boundsheetrecord.orderbybofposition(boundsheetrecords);                     }                      // check existence of sheets                     if(sheetindex == 0) {                         for(int i=0;i<excelsheetlist.length;i++) {                             boolean found = false;                             for(int j=0;j<orderedbsrs.length;j++) {                                 if(this.getexcelsheetspecification().equals(msexceladapter.use_worksheet_name)) {                                     string sheetname = ((boundsheetrecord) boundsheetrecords.get(j)).getsheetname();                                     if(excelsheetlist[i].equals(sheetname)) {                                         found = true;                                         break;                                     }                                 } else {                                     try {                                         if(integer.parseint(excelsheetlist[i]) == j) {                                             found = true;                                             break;                                         }                                     } catch (numberformatexception e) {                                     }                                 }                             }                             if(!found)                                 this.warning("processrecord()","sheet: " + excelsheetlist[i] + " not exist.");                         }                     }                      readcurrentsheet = true;                     sheetindex++;                     if(this.getexcelsheetspecification().equals(msexceladapter.use_worksheet_name)) {                         string sheetname = ((boundsheetrecord) boundsheetrecords.get(sheetindex-1)).getsheetname();                         if(!canread(sheetname)) {                             readcurrentsheet = false;                                                }                     } else {                         if(!canread(sheetindex + "")) {                             readcurrentsheet = false;                         }                     }                 }                 break;              case sstrecord.sid:                 sstrecord = (sstrecord) record;                 break;              case blankrecord.sid:                 blankrecord brec = (blankrecord) record;                  thisrow = brec.getrow();                 thisstr = null;                 values.add(thisstr);                 columncount++;                 break;              case formularecord.sid:                 formularecord frec = (formularecord) record;                  thisrow = frec.getrow();                 if(double.isnan( frec.getvalue() )) {                     // formula result string                     // stored in next record                     outputnextstringrecord = true;                     nextrow = frec.getrow();                 } else {                     thisstr = formatlistener.formatnumberdatecell(frec);                 }                 break;                case stringrecord.sid:                 if(outputnextstringrecord) {                     // string formula                     stringrecord srec = (stringrecord)record;                     thisstr = srec.getstring();                     thisrow = nextrow;                     outputnextstringrecord = false;                 }                 break;              case labelsstrecord.sid:                 if(readcurrentsheet) {                     labelsstrecord lsrec = (labelsstrecord) record;                     thisrow = lsrec.getrow() + 1;                     if(rownumberlist.contains(thisrow + "") ||                             (rownumberlist.contains(end_of_rows) && thisrow >= secondlastrow)) {                         if(sstrecord == null) {                             thisstr = "(no sst record, can't identify string)";                         } else {                             thisstr = sstrecord.getstring(lsrec.getsstindex()).tostring();                         }                     }                 }                 break;              case numberrecord.sid:                 if(readcurrentsheet) {                     numberrecord numrec = (numberrecord) record;                     thisrow = numrec.getrow() + 1;                     if(rownumberlist.contains(thisrow + "") ||                                 (rownumberlist.contains(end_of_rows) && thisrow >= secondlastrow)) {                             thisstr = formatlistener.formatnumberdatecell(numrec); // format                     }                 }                 break;             default:                 break;         }          // handle missing column         if(record instanceof missingcelldummyrecord) {             thisstr = "";         }          // if got print out,         if(thisstr != null) {             values.add(thisstr);             columncount++;         }          // handle end of row         if(record instanceof lastcellofrowdummyrecord) {                 .....         }         ... 

with xlsx if first column has empty cell being skipped. here code xlsx:

 /**       * default handler parsing excel sheet      * @see org.xml.sax.helpers.defaulthandler      */     private class sheethandler extends defaulthandler {         private sharedstringstable sst;         private string lastcontents;         private boolean nextisstring;         private msexcelreader reader;          private int thiscolumn = -1;         private int lastcolumnnumber = -1;  // last column printed output stream          private sheethandler(sharedstringstable sst, msexcelreader reader) {             this.sst = sst;             this.reader = reader;         }          public void startelement(string uri, string localname, string name, attributes attributes) throws saxexception {             // c => cell             if(name.equals("c")) {                 // figure out if value index in sst                 string celltype = attributes.getvalue("t");                 if(celltype != null && celltype.equals("s")) {                     nextisstring = true;                 } else {                     nextisstring = false;                 }                 // cell reference                 string r = attributes.getvalue("r");                 int firstdigit = -1;                 (int c = 0; c < r.length(); ++c) {                     if (character.isdigit(r.charat(c))) {                         firstdigit = c;                         break;                     }                 }                 thiscolumn = nametocolumn(r.substring(0, firstdigit));             }             // clear contents cache             lastcontents = "";         }          public void endelement(string uri, string localname, string name) throws saxexception {             // process last contents required.             // now, characters() may called more once             if(nextisstring) {                 try {                     int idx = integer.parseint(lastcontents);                     lastcontents = new xssfrichtextstring(sst.getentryat(idx)).tostring();                 } catch (numberformatexception e) {                 }             }              // v => contents of cell             // output after we've seen string contents             if(name.equals("v")) {                 (int = lastcolumnnumber; < thiscolumn - 1; ++i)                     values.add(null);  // add empty string missing columns                  values.add(lastcontents);                  // update column                 if (thiscolumn > -1)                     lastcolumnnumber = thiscolumn;             }              if(name.equals("row")) {             ... 

same old question things mention: not using usermodel (org.apache.poi.ss.usermodel) event api process xls , xlsx files.

i implementing hssflistener , overriding processrecord(record record) method xls files. xlsx files using javax.xml.parsers.saxparser , org.xml.sax.xmlreader.

i using jdk7 apache poi 3.7. can please help?

i have excel file following columns looks :-

column1 column2 column3 column4 column5 column6 column7         parag   joshi   pune                100              parag   joshi   pune    200          

the output generated code when print values in excel :-

;parag;joshi;pune;null;100;null ;parag;joshi;pune;200;null;null 

see above printed empty string first column other columnd printed value null. want first column print same value null.

if have cleaner way this, please advice, thanks!

create arraylist store columnindex;

    arraylist<integer> listallcolinrow = new arraylist<>();  

create arraylist store data;

    adddatatorow = new arraylist<>();     

add each columnindex listallcolinrow;

    while (cells.hasnext()) {            cell = (hssfcell) cells.next();             int col = cell.getcolumnindex();             listallcolinrow.add(col);         }    

get first column index in each row can check if the first column;

    integer = listallcolinrow.get(0);     

if not first column, add null first index of arraylist adddatatorow in example , on until reach first column;

    // while not 1st column         while( != 0){             //add null 1st index of arraylist             adddatatorow.add(0,null);             a--;         }