POI read the Excel custom date format (instance code) _java

Reading Excel data with POI: (version number: POI3.7)

1. Read Excel

Private list<string[]> Rosolvefile (InputStream is, String suffix, int startrow) throws IOException, FILENOTF 
    oundexception {Workbook xssfworkbook = null; 
    if ("xls". Equals (suffix)) {Xssfworkbook = new Hssfworkbook (IS); 
    else if ("xlsx". Equals (suffix)) {Xssfworkbook = new Xssfworkbook (IS); 
    } Sheet xssfsheet = Xssfworkbook.getsheetat (0); 
    if (Xssfsheet = = null) {return null; 
    } arraylist<string[]> list = new arraylist<string[]> (); 
    int lastrownum = Xssfsheet.getlastrownum (); for (int rownum = StartRow; rownum <= lastrownum; rownum++) {if (Xssfsheet.getrow (rownum)!= null) {R 
        ow xssfrow = Xssfsheet.getrow (rownum); 
        Short firstcellnum = Xssfrow.getfirstcellnum (); 
        Short lastcellnum = Xssfrow.getlastcellnum (); 
          if (firstcellnum!= lastcellnum) {string[] values = new String[lastcellnum]; for (int cellnum = firstcellnum; Cellnum &lT Lastcellnum; 
            cellnum++) {Cell Xssfcell = Xssfrow.getcell (cellnum); 
            if (Xssfcell = = null) {Values[cellnum] = ""; 
            else {Values[cellnum] = Parseexcel (Xssfcell); 
        } list.add (values); 
  }} return list;  }

2, Excel data processing:

The Excel store date and time are stored by numeric type, when reading POI first judge whether it is a numeric type, then make a judgment transformation

1, numerical format (cell_type_numeric):

1. Pure numerical format: Getnumericcellvalue () direct access to data

2. Date format: Deal with YYYY-MM-DD, d/m/yyyy h:mm, hh:mm date format without text

1). Determine whether the date format: hssfdateutil.iscelldateformatted (cell)

2). Judgment is the date or time

Cell.getcellstyle (). getDataFormat () = = Hssfdataformat.getbuiltinformat ("h:mm")

OR:cell.getCellStyle (). getDataFormat () = = Hssfdataformat.getbuiltinformat ("Yyyy-mm-dd")

3. Custom date format: processing yyyy years M-month D-day, h mm minutes, yyyy years, such as the date format containing the text

Determine Cell.getcellstyle (). getDataFormat () value, resolving numeric formats

YYYY M-month D-day----->31

M-month D-day---->58

H-time mm--->32

2, character format (cell_type_string): Direct access to content

 

private string Parseexcel (cell cell) {string result = new String (); Switch (Cell.getcelltype ()) {case hssfcell.cell_type_numeric://number type if (hssfdateutil.iscelldateformatted cel 
        L)) {//Processing date format, time format simpledateformat SDF = null; if (Cell.getcellstyle (). getDataFormat () = = Hssfdataformat. Getbuiltinformat ("h:mm")) {SDF = new S 
        Impledateformat ("hh:mm"); 
        else {//date SDF = new SimpleDateFormat ("Yyyy-mm-dd"); 
        Date date = Cell.getdatecellvalue (); 
      result = Sdf.format (date); else if (Cell.getcellstyle (). getDataFormat () = = 58) {//Process custom date format: M-month D-day (by determining the cell's format ID, the value of the ID is) simple 
        DateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd"); 
        Double value = Cell.getnumericcellvalue (); 
        Date date = Org.apache.poi.ss.usermodel.DateUtil. Getjavadate (value); 
      result = Sdf.format (date); } else {Double value = Cell.getnumeRiccellvalue (); 
        CellStyle style = Cell.getcellstyle (); 
        DecimalFormat format = new DecimalFormat (); 
        String temp = style.getdataformatstring (); 
        The cell is set to regular if (Temp.equals ("General")) {Format.applypattern ("#"); 
      result = Format.format (value); 
    } break; 
      Case hssfcell.cell_type_string://STRING Type result = Cell.getrichstringcellvalue (). toString (); 
    Break 
    Case HSSFCell.CELL_TYPE_BLANK:result = ""; 
      Default:result = ""; 
    Break 
  return result;  }

* Universal Treatment Scheme:

All date formats can be judged by the getDataFormat () value.

YYYY-MM-DD-----14

YYYY M-month D-Day---31

YYYY year m month-------57

M-month D-Day----------58

hh:mm-----------20

H time mm minute-------32

1, determine whether the numeric format 
if (cell.getcelltype () = = hssfcell.cell_type_numeric) {short 
  format = Cell.getcellstyle (). getDataFormat (); 
  SimpleDateFormat SDF = null; 
  if (format = = | | format = = | | | format = = = = = = = =/ 
    date 
    sdf = new SimpleDateFormat ("Yyyy-mm-dd"); 
   }else if (format = = | | | format = =) { 
    //time 
    SDF = new SimpleDateFormat ("hh:mm"); 
  } 
  Double value = Cell.getnumericcellvalue (); 
  Date date = Org.apache.poi.ss.usermodel.DateUtil.getJavaDate (value); 
  result = Sdf.format (date); 
} 

Above this POI to Excel custom date format reads (instance code) is the small series to share to everybody's content, hoped can give everybody a reference, also hoped that everybody supports the cloud habitat community.