POI read for Excel custom date format

Post Source: http://yl-fighting.iteye.com/blog/1726285

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

1. Read Excel

Java code

2. Private list<string[]> Rosolvefile (InputStream is, String suffix,
4. int StartRow) throws IOException, FileNotFoundException {
6. Workbook Xssfworkbook = null;
8. if ("xls". Equals (suffix)) {
10. Xssfworkbook = New Hssfworkbook (IS);
12. } Else if ("xlsx". Equals (suffix)) {
14. Xssfworkbook = New Xssfworkbook (IS);
16. }
18. Sheet Xssfsheet = Xssfworkbook.getsheetat (0);
20. if (Xssfsheet = = null) {
22. return null;
24. }
26. arraylist<string[]> list = new arraylist<string[]> ();
28. int lastrownum = Xssfsheet.getlastrownum ();
30. For (int rowNum = startrow; rowNum <= lastrownum; rownum++) {
32. if (xssfsheet.getrow (rowNum) = null) {
34. Row Xssfrow = Xssfsheet.getrow (RowNum);
36. Short firstcellnum = Xssfrow.getfirstcellnum ();
38. Short lastcellnum = Xssfrow.getlastcellnum ();
40. if (firstcellnum! = lastcellnum) {
42. String[] values = new String[lastcellnum];
44. For (int cellnum = firstcellnum; cellnum < lastcellnum; cellnum++) {
46. Cell Xssfcell = Xssfrow.getcell (cellnum);
48. if (Xssfcell = = null) {
50. Values[cellnum] = "";
52. } Else {
54. Values[cellnum] = Parseexcel (Xssfcell);
56. }
58. }
60. List.add (values);
62. }
64. }
66. }
68. return list;
70. }

2. Excel Data Processing:

Excel stores date and time are stored as numeric types, and when read, POI first determines whether it is a numeric type and then makes a judgment conversion.

1. Numerical format (cell_type_numeric):

1. Pure Numeric format : Getnumericcellvalue () get data directly

2. date format : deal with Yyyy-mm-dd, d/m/yyyy h:mm, hh:mm , etc. no text-containing date format

1). Determine if it is a date format: hssfdateutil.iscelldateformatted (cell)

2). Judgment is 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 year M D Day, h time mm, yyyy year m month and other date formats with text

Determine Cell.getcellstyle (). getDataFormat () value, parse numeric format

YYYY year M D Day----->31

M D Day---->58

H mm Sub--->32

2. Character format (cell_type_string): Get content directly

Java code

2. Private String Parseexcel (cell cell) {
4. string result = new string ();
6. switch (Cell.getcelltype ()) {
8. Case Hssfcell.cell_type_numeric://numeric type
10. if (hssfdateutil.iscelldateformatted (cell)) {//Process date format, time format
12. SimpleDateFormat SDF = null;
14. if (Cell.getcellstyle (). getDataFormat () = = Hssfdataformat
16. . Getbuiltinformat ("h:mm")) {
18. SDF = new SimpleDateFormat ("hh:mm");
20. } else {//date
22. SDF = new SimpleDateFormat ("Yyyy-mm-dd");
24. }
26. Date date = Cell.getdatecellvalue ();
28. result = Sdf.format (date);
30. } Else if (Cell.getcellstyle (). getDataFormat () = = + ) {
32. //Process Custom date format: M month D Day (resolved by judging the format ID of the cell, the value of the ID is ()
34. SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd");
36. Double value = Cell.getnumericcellvalue ();
38. Date date = Org.apache.poi.ss.usermodel.DateUtil
40. . getjavadate (value);
42. result = Sdf.format (date);
44. } Else {
46. Double value = Cell.getnumericcellvalue ();
48. CellStyle style = Cell.getcellstyle ();
50. DecimalFormat format = new DecimalFormat ();
52. String temp = style.getdataformatstring ();
54. //cell set to normal
56. if (Temp.equals ("General")) {
58. Format.applypattern ("#");
60. }
62. result = Format.format (value);
64. }
66. Break ;
68. Case hssfcell.cell_type_string://STRING type
70. result = Cell.getrichstringcellvalue (). toString ();
72. Break ;
74. Case Hssfcell.cell_type_blank:
76. result = "";
78. Default:
80. result = "";
82. Break ;
84. }
86. return result;
88. }

* Universal Treatment Solution :

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

YYYY-MM-DD-----14

YYYY year M D Day---31

yyyy m month-------57

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

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

h mm min-------32

Java code

2. 1. Determine if it is a numeric format
4. if (cell.getcelltype () = = Hssfcell.cell_type_numeric) {
6. Short format = Cell.getcellstyle (). getDataFormat ();
8. SimpleDateFormat SDF = null;
10. if (format = = | | | format = = | | format = = | | | format = = ) {
12. //Date
14. SDF = new SimpleDateFormat ("Yyyy-mm-dd");
16. }Else if (format = = | | format = = ) {
18. //Time
20. SDF = new SimpleDateFormat ("hh:mm");
22. }
24. Double value = Cell.getnumericcellvalue ();
26. Date date = Org.apache.poi.ss.usermodel.DateUtil.getJavaDate (value);
28. result = Sdf.format (date);
30. }

POI read for Excel custom date format