java - Blank not detecting in XSSFWorksheet code -


i converting xlsx csv , uploading data db using below code,

import java.io.*; import java.sql.connection; import java.sql.sqlexception; import java.sql.statement; import java.util.iterator;  import org.apache.poi.ss.usermodel.cell; import org.apache.poi.ss.usermodel.row; import org.apache.poi.xssf.usermodel.xssfsheet; import org.apache.poi.xssf.usermodel.xssfworkbook;  public class xlstocsvconvert {     static connectivity conv;     static connection con = null;     static statement st = null;     static string query=null;      static void xlsx(file inputfile, file outputfile)  {             // storing data csv files             stringbuffer data = new stringbuffer();              try {                 fileoutputstream fos = new fileoutputstream(outputfile);                 // workbook object xlsx file                 system.out.println("working......");                 xssfworkbook wbook = new xssfworkbook(new fileinputstream(inputfile));                 // first sheet workbook                 system.out.println("working......");                 xssfsheet sheet = wbook.getsheetat(0);                 row row;                 cell cell;                 // iterate through each rows first sheet                 iterator<row> rowiterator = sheet.iterator();                  while (rowiterator.hasnext()) {                     row = rowiterator.next();                      // each row, iterate through each columns                     iterator<cell> celliterator = row.celliterator();                     while (celliterator.hasnext()) {                          cell = celliterator.next();                          switch (cell.getcelltype()) {                             case cell.cell_type_boolean:                                 data.append(cell.getbooleancellvalue() + ",");                                  break;                             case cell.cell_type_numeric:                                 data.append(cell.getnumericcellvalue() + ",");                                  break;                             case cell.cell_type_string:                                 data.append(cell.getstringcellvalue() + ",");                                 break;                              case cell.cell_type_blank:                                 data.append("" + ",");                                 break;                             default:                                 data.append(cell + ",");                          }                      }                     data.append("\n");                 }                  fos.write(data.tostring().getbytes());                 fos.close();                   conv = new connectivity();                 con = conv.setconnection();                 st = con.createstatement();                 query = "load data local infile \"" + outputfile + "\" table xlsxupload fields terminated ',' ignore 1 lines";                  st.executeupdate(query);                } catch (exception ioe) {                 ioe.printstacktrace();             }         }      public static void main(string[] args) {         try         {             //fileoutputstream fin = new fileoutputstream("/home/raptorjd4/desktop/raptortrackingsystem/mani.csv");         }         catch(exception e)         {             e.printstacktrace();         }         file inputfile = new file("/home/raptorjd4/desktop/xls files/toconsult.xlsx");         //writing excel data csv          file outputfile = new file("/home/raptorjd4/desktop/raptortrackingsystem/toconsult.csv");         xlsx(inputfile, outputfile);      }  } 

above code uploading data table blank values not detecting code , null values stores in table blank values , date format stores in 2033-01-02 instead of 1990-05-19.

where doing wrong?

excel doesn't know date cell. value stored number , format make date.

if cell contains value 33012 , specify date format yyyy-mm-dd shown in excel 1990-05-19. when extract data numeric value cell.

you need differentiate numeric values should interpreted date normal numeric values. find below snippet example.

import org.apache.poi.ss.usermodel.dateutil; ... case cell.cell_type_numeric:     if (dateutil.iscelldateformatted(cell)) {         // append string in format specified in excel         // system.out.println("format: " + cell.getcellstyle().getdataformatstring());         data.append(cell);     } else {         data.append(cell.getnumericcellvalue());     } 

Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -