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.apache.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;    //判断是否是excel2007格式

        if(fileName.endsWith("xlsx"))

            is2007 = false;

        try {

            InputStream input = new FileInputStream(fileName);  //建立输入流

            Workbook wb  = null;

            //根据文件格式(2003或者2007)来初始化

            if(is2007)

                wb = new HSSFWorkbook(input);

            else

                wb = new XSSFWorkbook(input);

            Sheet sheet = wb.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();

            for (int i = 0; i <= lastRowNum; i++) {

                //具体到某一行

                Row row = sheet.getRow(i);

                //单元格的数量

                int lastCellNum = row.getLastCellNum();

                for (int j = 0; j < lastCellNum; j++) {

                    Cell cell = row.getCell(j);

                    //根据cell中的类型来输出数据

                    switch (cell.getCellType()) {

                    case HSSFCell.CELL_TYPE_NUMERIC:

                        Object result = cell.getNumericCellValue();

//                        // 判断单元格是否属于日期格式

//                        if(HSSFDateUtil.isCellDateFormatted(cell)){

//                            //java.util.Date类型

//                            result = cell.getDateCellValue();

//                        }

                        short format = cell.getCellStyle().getDataFormat();

                        SimpleDateFormat sdf = null;

                        if(format != 0){

//                            format == 176时是当指定单元格格式为日期是都是176

                            if(format == 14 || format == 31 || format == 57 || format == 58||format == 176){

                                //日期

                                sdf = new SimpleDateFormat("yyyy-MM-dd");

                            }else if (format == 20 || format == 32) {

                                //时间

                                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("为空");

                        break;

                    default:

                        System.out.println("unsuported sell type");

                    break;

                    }

                }

                System.out.println();

            }

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

}