package lizikj.bigwheel.shop.util.excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.List;

import lizikj.bigwheel.common.vo.agent.Agent;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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 ReadExcelUtils {

    /** 总行数 */

    private int totalRows = 0;

    /** 总列数 */

    private int totalCells = 0;

    /** 错误信息 */

    private String errorInfo;

    /**

     * 验证excel文件

     *

     * @param filePath

     * @return

     */

    public boolean validateExcel(String filePath) {

        /** 检查文件名是否为空或者是否是Excel格式的文件 */

        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))) {

            errorInfo = "文件名不是excel格式";

            return false;

        }

        /** 检查文件是否存在 */

        File file = new File(filePath);

        if (file == null || !file.exists()) {

            errorInfo = "文件不存在";

            return false;

        }

        return true;

    }

    /**

     * 根据文件名读取excel文件

     *

     * @param filePath

     * @return

     */

    public List<Agent> read(String filePath) {

        List<Agent> dataLst = new ArrayList<Agent>();

        InputStream is = null;

        try {

            /** 验证文件是否合法 */

            if (!validateExcel(filePath)) {

                System.out.println(errorInfo);

                return null;

            }

            /** 判断文件的类型，是2003还是2007 */

            boolean isExcel2003 = true;

            if (WDWUtil.isExcel2007(filePath)) {

                isExcel2003 = false;

            }

            /** 调用本类提供的根据流读取的方法 */

            File file = new File(filePath);

            is = new FileInputStream(file);

            dataLst = read(is, isExcel2003);

            is.close();

        } catch (Exception ex) {

            ex.printStackTrace();

        } finally {

            if (is != null) {

                try {

                    is.close();

                } catch (IOException e) {

                    is = null;

                    e.printStackTrace();

                }

            }

        }

        /** 返回最后读取的结果 */

        return dataLst;

    }

    /**

     * 根据流读取Excel文件

     *

     * @param inputStream

     * @param isExcel2003

     * @return

     */

    public List<Agent> read(InputStream inputStream, boolean isExcel2003) {

        List<Agent> dataLst = null;

        try {

            /** 根据版本选择创建Workbook的方式 */

            Workbook wb = null;

            if (isExcel2003) {

                wb = new HSSFWorkbook(inputStream);

            } else {

                wb = new XSSFWorkbook(inputStream);

            }

            dataLst = read(wb);

        } catch (IOException e) {

            e.printStackTrace();

        }

        return dataLst;

    }

    /**

     * 读取数据

     *

     * @param wb

     * @return

     */

    private List<Agent> read(Workbook wb) {

        List<Agent> dataLst = new ArrayList<Agent>();

        /** 得到第一个shell */

        Sheet sheet = wb.getSheetAt(0);

        /** 得到Excel的行数 */

        this.totalRows = sheet.getPhysicalNumberOfRows();

        /** 得到Excel的列数 */

        if (this.totalRows >= 1 && sheet.getRow(0) != null) {

            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

        }

        Agent agent = null;

        /** 循环Excel的行 */

        for (int r = 1; r < this.totalRows; r++) {

            Row hssfRow = sheet.getRow(r);

            if (hssfRow == null) {

                continue;

            }

            agent = new Agent();

            Cell name = hssfRow.getCell(0);

            Cell pname = hssfRow.getCell(1);

            Cell cname = hssfRow.getCell(2);

            Cell rname = hssfRow.getCell(3);

            Cell address = hssfRow.getCell(4);

            Cell contact = hssfRow.getCell(5);

            Cell mobile = hssfRow.getCell(6);

            Cell companyName = hssfRow.getCell(7);

            agent.setParentAgentId(0);

            agent.setAgentName(getValue(name));

            agent.setAgentRole("K1");

            agent.setProvinceId(getValue(pname));

            agent.setCityId(getValue(cname));

            agent.setRegionId(getValue(rname));

            agent.setContact(getValue(contact));

            agent.setAddress(getValue(address));

            agent.setMobile(getValue(mobile));

            agent.setContactName(getValue(contact));

            agent.setCompanyName(getValue(companyName));

            dataLst.add(agent);

        }

        return dataLst;

    }

    @SuppressWarnings("static-access")

    private String getValue(Cell hssfCell) {

        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {

            // 返回布尔类型的值

            return String.valueOf(hssfCell.getBooleanCellValue());

        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {

            // 返回数值类型的值

            String result = "";

            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {// 处理日期格式、时间格式

                SimpleDateFormat sdf = null;

                if (hssfCell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {

                    sdf = new SimpleDateFormat("HH:mm");

                } else {// 日期

                    sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

                }

                Date date = hssfCell.getDateCellValue();

                result = sdf.format(date);

            } else if (hssfCell.getCellStyle().getDataFormat() == 58) {

                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)

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

                double value = hssfCell.getNumericCellValue();

                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);

                result = sdf.format(date);

            } else {

                double value = hssfCell.getNumericCellValue();

                CellStyle style = hssfCell.getCellStyle();

                DecimalFormat format = new DecimalFormat();

                String temp = style.getDataFormatString();

                // 单元格设置成常规

                if (temp.equals("General")) {

                    format.applyPattern("#");

                }

                result = format.format(value);

            }

            return result;

        } else {

            // 返回字符串类型的值

            return String.valueOf(hssfCell.getStringCellValue());

        }

    }

class WDWUtil { 

/**

* 是否是2003的excel，返回true是2003

* @param filePath

* @return

*/

public static boolean isExcel2003(String filePath) {

return filePath.matches("^.+\\.(?i)(xls)$");

} 

/**

* 是否是2007的excel，返回true是2007

* @param filePath

* @return

*/

public static boolean isExcel2007(String filePath) {

return filePath.matches("^.+\\.(?i)(xlsx)$");

} 

}