Poi resolution excel, poiexcel

Poi resolution excel, poiexcel

I. Problems encountered:

When the cell is set to the date type, the value returned by cell. getCellStyle (). getDataFormat () is 176.

More than 3.14 of the poi jar package does not support cell. getCellType () method to determine the type.

To use the poi parsing technology, you need to import the poi and poi-ooxml jar packages.

2. cell. getCellType ():

HSSFCell. CELL_TYPE_NUMERIC: Number type (including date)

CELL_TYPE_STRING: String

CELL_TYPE_BOOLEAN: Boolean Type

CELL_TYPE_BLANK: no value

CELL_TYPE_FORMULA: formula Value

3. cell. getCellStyle (). getDataFormat () value when cells are of the custom type:

Yyyy-MM-dd ----> 14

Yyyy-m-D ---> 31

Yyyy-mmonth ------> 57

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

HH: mm ---------> 20

Mm = h ------> 32

Iv. Final code:

Package cn. trashman. excel; import java. io. fileInputStream; import java. io. IOException; import java. io. inputStream; import java. text. simpleDateFormat; import java. util. date; import org. apache. poi. hssf. usermodel. HSSFCell; import org. apache. poi. hssf. usermodel. HSSFWorkbook; import org. apache. poi. ss. usermodel. cell; import org. apache. poi. ss. usermodel. dateUtil; import org. apache. poi. ss. usermodel. row; import org. apac He. poi. ss. usermodel. sheet; import org. apache. poi. ss. usermodel. workbook; import org. apache. poi. xssf. usermodel. XSSFWorkbook; public class PoiExcel {public static void main (String [] args) {poiExcel ("D:/1.xlsx") ;}@ SuppressWarnings ("resource ") public static void poiExcel (String fileName) {boolean is2007 = true; // you can determine whether it is in excel2007 format if (fileName. endsWith ("xlsx") is2007 = false; try {InputStream input = New FileInputStream (fileName); // create the input stream Workbook wb = null; // initialize if (is2007) wb = new HSSFWorkbook (input) according to the file format (2003 or 2007 ); else wb = new XSSFWorkbook (input); Sheet sheet = wb. getSheetAt (0); int lastRowNum = sheet. getLastRowNum (); for (int I = 0; I <= lastRowNum; I ++) {// specific to a Row row Row = sheet. getRow (I); // number of cells int lastCellNum = row. getLastCellNum (); for (int j = 0; j <lastCellNum; j ++) {Ce Ll cell = row. getCell (j); // output data switch (cell. getCellType () {case HSSFCell. CELL_TYPE_NUMERIC: Object result = cell. getNumericCellValue (); // determines whether a cell belongs to the date format. // if (HSSFDateUtil. isCellDateFormatted (cell) {/// java. util. date type // result = cell. getDateCellValue (); //} short format = cell. getCellStyle (). getDataFormat (); SimpleDateFormat sdf = null; if (format! = 0) {// format = 176 when the specified cell format is 176 if (format = 14 | format = 31 | format = 57 | format = = 58 | format = 176) {// Date sdf = new SimpleDateFormat ("yyyy-MM-dd");} else if (format = 20 | format = 32) {// time sdf = new SimpleDateFormat ("HH: mm");} double value = cell. getNumericCellValue (); Date date = DateUtil. getJavaDate (value); result = sdf. format (date);} System. out. println (result); break; case HSSFCell. CELL_TYPE_STRING: System. out. println (cell. getStringCellValue (); break; case HSSFCell. CELL_TYPE_BOOLEAN: System. out. println (cell. getBooleanCellValue (); break; case HSSFCell. CELL_TYPE_FORMULA: System. out. println (cell. getCellFormula (); break; case HSSFCell. CELL_TYPE_BLANK: System. out. println ("null"); break; default: System. out. println ("unsuported publish type"); break ;}} System. out. println () ;}} catch (IOException e) {// TODO Auto-generated catch block e. printStackTrace ();}}}