Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook.

Prototype

public HSSFWorkbook(InputStream s) throws IOException 

Source Link

Document

Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your InputStream , including all nodes.

Usage

From source file:com.cn.controller.MaterialBaseInfoController.java

/**
 * ?//from  w  w  w. jav  a 2  s.c  o m
 *
 * @param fileName
 * @param carrierName
 * @return
 */
public int importData(String fileName, String carrierName) {
    InputStream inputStream = null;
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<MaterialBaseInfo> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            //                logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i);
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }

            int cellNum = row.getPhysicalNumberOfCells();
            //                logger.info("count cell num is:" + cellNum);
            if (cellNum >= 4) {
                MaterialBaseInfo info = new MaterialBaseInfo();
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                info.setPinMing(row.getCell(0).getStringCellValue());
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                info.setJianHao(row.getCell(1).getStringCellValue());
                if (Units.strIsEmpty(info.getJianHao()))
                    continue;
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setCarModel(row.getCell(2).getStringCellValue());
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                info.setProductJianHao(row.getCell(3).getStringCellValue());
                row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setCarNum((int) row.getCell(4).getNumericCellValue());

                imports.add(info);
            }
        }
        DatabaseOpt opt;
        Connection conn = null;
        CallableStatement statement = null;
        opt = new DatabaseOpt();
        try {
            conn = opt.getConnect();
            conn.setAutoCommit(false);
            statement = conn.prepareCall(
                    "insert into tbMaterialBaseInfo(materialBaseId, pinMing, jianHao, carModel, carNum, carrierName, productJianHao)"
                            + "values(MATERIALBASEID.NEXTVAL, ?, ?, ?, ?, ?, ?)");
            for (MaterialBaseInfo infoImport : imports) {
                statement.setString(1, infoImport.getPinMing());
                statement.setString(2, infoImport.getJianHao());
                statement.setString(3, infoImport.getCarModel());
                statement.setInt(4, infoImport.getCarNum());
                statement.setString(5, carrierName);
                statement.setString(6, infoImport.getProductJianHao());
                statement.addBatch();
            }
            statement.executeBatch();
            conn.commit();
            return 0;
        } catch (SQLException ex) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException ex1) {
                logger.error("?", ex1);
            }
            logger.error("?", ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                logger.error("?", ex);
            }
        }

    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.controller.MaterialInController.java

public int importData(String fileName, String carrierName) {
    InputStream inputStream = null;
    try {/*from w w  w .j  a v  a 2  s.c om*/
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<MaterialIn> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            //                logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i);
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }

            int cellNum = row.getPhysicalNumberOfCells();
            //                logger.info("count cell num is:" + cellNum);
            if (cellNum >= 5) {
                MaterialIn info = new MaterialIn();

                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                info.setPinMing(row.getCell(0).getStringCellValue());
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                info.setJianHao(row.getCell(1).getStringCellValue());
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setCarModel(row.getCell(2).getStringCellValue());
                row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setCarCount((int) row.getCell(3).getNumericCellValue());
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                info.setRemark(row.getCell(4).getStringCellValue());

                imports.add(info);
            }
        }
        return batchAddMaterial(carrierName, imports);

    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.controller.MaterialInitController.java

/**
 * ????/*ww  w  . jav a2s  . com*/
 *
 * @param fileName
 * @param carrierName
 * @return
 */
public int importData(String fileName, String carrierName) {
    InputStream inputStream = null;
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<MaterialInit> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            //                logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i);
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }

            int cellNum = row.getPhysicalNumberOfCells();
            //                logger.info("count cell num is:" + cellNum);
            if (cellNum >= 4) {
                MaterialInit info = new MaterialInit();
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                info.setJianHao(row.getCell(0).getStringCellValue());
                if (Units.strIsEmpty(info.getJianHao())) {
                    continue;
                }
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                info.setPinMing(row.getCell(1).getStringCellValue());
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setCarModel(row.getCell(2).getStringCellValue());
                row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setInitNum((int) row.getCell(3).getNumericCellValue());

                if (row.getCell(4).getCellType() == Cell.CELL_TYPE_STRING) {
                    info.setInitDate(row.getCell(4).getStringCellValue());
                } else {
                    if (HSSFDateUtil.isCellDateFormatted(row.getCell(4))) {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = HSSFDateUtil.getJavaDate(row.getCell(4).getNumericCellValue());
                        info.setInitDate(dateFormat.format(date));
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        info.setInitDate(df.format(row.getCell(4).getNumericCellValue()));
                    }
                }
                info.setCarrierName(carrierName);
                imports.add(info);
            }
        }

        return batchAddData(carrierName, imports);
    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.controller.MaterialOutController.java

public int importData(String fileName, String carrierName) {
    InputStream inputStream = null;
    try {// w ww . jav  a  2 s  . c om
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<MaterialOut> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            //                logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i);
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }

            int cellNum = row.getPhysicalNumberOfCells();
            //                logger.info("count cell num is:" + cellNum);
            if (cellNum >= 5) {
                MaterialOut info = new MaterialOut();

                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                info.setPinMing(row.getCell(0).getStringCellValue());
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                info.setJianHao(row.getCell(1).getStringCellValue());
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setCarModel(row.getCell(2).getStringCellValue());
                row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setCarCount((int) row.getCell(3).getNumericCellValue());
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                info.setRemark(row.getCell(4).getStringCellValue());
                imports.add(info);
            }
        }
        return batchAddMaterial(carrierName, imports);
    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.controller.OrderController.java

/**
 * ?(???, ?)//from w w w. ja v  a 2  s  .c o  m
 *
 * @param fileName
 * @param productLineId
 * @param isEmergency
 * @param isGuanShu
 * @return
 * @throws IllegalStateException
 */
public int importOrder(String fileName, int productLineId, int isEmergency, int isGuanShu)
        throws IllegalStateException {
    InputStream inputStream = null;
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<OrderInfo> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            //                logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i);
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }

            int cellNum = row.getPhysicalNumberOfCells();
            //                logger.info("count cell num is:" + cellNum);
            if (cellNum >= 8) {
                OrderInfo info = new OrderInfo();
                info.setProductLineId(productLineId);

                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                info.setProductCommand(row.getCell(0).getStringCellValue());
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                info.setProductCode(row.getCell(1).getStringCellValue());
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setProductName(row.getCell(2).getStringCellValue());
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                info.setProductUnit(row.getCell(3).getStringCellValue());
                row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setPlanNum((int) row.getCell(4).getNumericCellValue());
                row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                info.setProductStandard(row.getCell(5).getStringCellValue());

                if (row.getCell(6).getCellType() == Cell.CELL_TYPE_STRING) {
                    info.setPlanFinishTime(row.getCell(6).getStringCellValue());
                } else {
                    if (HSSFDateUtil.isCellDateFormatted(row.getCell(6))) {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = HSSFDateUtil.getJavaDate(row.getCell(6).getNumericCellValue());
                        info.setPlanFinishTime(dateFormat.format(date));
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        info.setPlanFinishTime(df.format(row.getCell(6).getNumericCellValue()));
                    }
                }
                row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                info.setCustomerName(row.getCell(7).getStringCellValue());
                imports.add(info);
            }
        }

        DatabaseOpt opt;
        Connection conn = null;
        CallableStatement statement = null;
        opt = new DatabaseOpt();
        try {
            conn = opt.getConnect();
            conn.setAutoCommit(false);
            statement = conn.prepareCall("{call [tbAddOrderInfo](?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            for (OrderInfo infoImport : imports) {
                statement.setInt("productLineId", infoImport.getProductLineId());
                statement.setInt("planNum", infoImport.getPlanNum());
                statement.setString("productCommand", infoImport.getProductCommand());
                statement.setString("productCode", infoImport.getProductCode());
                statement.setString("productName", infoImport.getProductName());
                statement.setString("productUnit", infoImport.getProductUnit());
                statement.setString("productStandard", infoImport.getProductStandard());
                statement.setString("planFinishedTime", infoImport.getPlanFinishTime());
                statement.setInt("isEmergency", isEmergency);
                statement.setInt("isGuanShu", isGuanShu);
                statement.setString("customerName", infoImport.getCustomerName());
                statement.addBatch();
            }
            statement.executeBatch();
            conn.commit();
            return 0;
        } catch (SQLException ex) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException ex1) {
                logger.error("?", ex1);
            }
            logger.error("?", ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                logger.error("?", ex);
            }
        }

    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.controller.OrderPlanController.java

/**
 * ?/*from ww  w . ja  v  a2s  . c o  m*/
 *
 * @param fileName
 * @param planType
 * @return -1 -- ?, 0 -- ??, -2 -- ?, -3 -- ??
 */
public int importData(String fileName, int planType) {
    InputStream inputStream = null;
    int templateType;//?: 0 -- ??? | 1 -- ??? | 2 -- ?SAP?
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        Sheet sheet;
        if (fileName.endsWith(".xls")) {
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            sheet = workbook.getSheetAt(0);
        } else if (fileName.endsWith(".xlsx")) {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            sheet = workbook.getSheetAt(0);
        } else {
            logger.info("?Excel!");
            return -2;
        }

        Row headRow = sheet.getRow(0);
        if (headRow.getPhysicalNumberOfCells() == 38) {
            templateType = 0;
        } else if (headRow.getPhysicalNumberOfCells() == 8) {
            templateType = 1;
        } else if (headRow.getPhysicalNumberOfCells() == 34) {
            templateType = 2;
        } else {
            logger.info("???!");
            return -3;
        }

        ArrayList<OrderPlan> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            int cellNum = row.getPhysicalNumberOfCells();
            //logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i + ",count cell num is:" + cellNum);
            OrderPlan info = new OrderPlan();
            //
            if (row.getCell(templateDataIndex[templateType][0]).getCellType() == Cell.CELL_TYPE_STRING) {
                info.setFinishTime(row.getCell(templateDataIndex[templateType][0]).getStringCellValue());
            } else {
                if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][0]))) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = DateUtil
                            .getJavaDate(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue());
                    info.setFinishTime(dateFormat.format(date));
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    info.setFinishTime(
                            df.format(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue()));
                }
            }
            //
            if (row.getCell(templateDataIndex[templateType][1]).getCellType() == Cell.CELL_TYPE_STRING) {
                info.setSendTime(row.getCell(templateDataIndex[templateType][1]).getStringCellValue());
            } else {
                if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][1]))) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = DateUtil
                            .getJavaDate(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue());
                    info.setSendTime(dateFormat.format(date));
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    info.setSendTime(
                            df.format(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue()));
                }
            }
            //?
            row.getCell(templateDataIndex[templateType][2]).setCellType(Cell.CELL_TYPE_STRING);
            info.setCarrierCode(row.getCell(templateDataIndex[templateType][2]).getStringCellValue());
            //??
            row.getCell(templateDataIndex[templateType][3]).setCellType(Cell.CELL_TYPE_STRING);
            info.setCarrierName(row.getCell(templateDataIndex[templateType][3]).getStringCellValue());
            //???
            row.getCell(templateDataIndex[templateType][4]).setCellType(Cell.CELL_TYPE_STRING);
            info.setPinMing(row.getCell(templateDataIndex[templateType][4]).getStringCellValue());
            //?
            row.getCell(templateDataIndex[templateType][5]).setCellType(Cell.CELL_TYPE_STRING);
            info.setJianHao(row.getCell(templateDataIndex[templateType][5]).getStringCellValue());
            //???
            row.getCell(templateDataIndex[templateType][6]).setCellType(Cell.CELL_TYPE_STRING);
            info.setUnit(row.getCell(templateDataIndex[templateType][6]).getStringCellValue());
            //?
            row.getCell(templateDataIndex[templateType][7]).setCellType(Cell.CELL_TYPE_NUMERIC);
            info.setCarCount((int) row.getCell(templateDataIndex[templateType][7]).getNumericCellValue());

            imports.add(info);
            /*
             if (templateType == 0) {
             OrderPlan info = new OrderPlan();
             //
             if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setFinishTime(row.getCell(0).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(0))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue());
             info.setFinishTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setFinishTime(df.format(row.getCell(0).getNumericCellValue()));
             }
             }
             //
             if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setSendTime(row.getCell(1).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(1))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue());
             info.setSendTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setSendTime(df.format(row.getCell(1).getNumericCellValue()));
             }
             }
             //?
             row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierCode(row.getCell(13).getStringCellValue());
             //??
             row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierName(row.getCell(14).getStringCellValue());
             //???
             row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
             info.setPinMing(row.getCell(16).getStringCellValue());
             //?
             row.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
             info.setJianHao(row.getCell(17).getStringCellValue());
             //???
             row.getCell(21).setCellType(Cell.CELL_TYPE_STRING);
             info.setUnit(row.getCell(21).getStringCellValue());
             //?
             row.getCell(22).setCellType(Cell.CELL_TYPE_NUMERIC);
             info.setCarCount((int) row.getCell(22).getNumericCellValue());
                    
             imports.add(info);
             } else if (templateType == 1) {
             OrderPlan info = new OrderPlan();
             //
             if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setFinishTime(row.getCell(0).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(0))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue());
             info.setFinishTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setFinishTime(df.format(row.getCell(0).getNumericCellValue()));
             }
             }
             //
             if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setSendTime(row.getCell(1).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(1))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue());
             info.setSendTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setSendTime(df.format(row.getCell(1).getNumericCellValue()));
             }
             }
             //?
             row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierCode(row.getCell(2).getStringCellValue());
             //??
             row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierName(row.getCell(3).getStringCellValue());
             //???
             row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
             info.setPinMing(row.getCell(4).getStringCellValue());
             //?
             row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
             info.setJianHao(row.getCell(5).getStringCellValue());
             //???
             row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
             info.setUnit(row.getCell(6).getStringCellValue());
             //?
             row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
             info.setCarCount((int) row.getCell(7).getNumericCellValue());
                    
             imports.add(info);
             } else if (templateType == 2) {
             OrderPlan info = new OrderPlan();
             //
             if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setFinishTime(row.getCell(0).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(0))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue());
             info.setFinishTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setFinishTime(df.format(row.getCell(0).getNumericCellValue()));
             }
             }
             //
             if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setSendTime(row.getCell(1).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(1))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue());
             info.setSendTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setSendTime(df.format(row.getCell(1).getNumericCellValue()));
             }
             }
             //?
             row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierCode(row.getCell(2).getStringCellValue());
             //??
             row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierName(row.getCell(3).getStringCellValue());
             //???
             row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
             info.setPinMing(row.getCell(4).getStringCellValue());
             //?
             row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
             info.setJianHao(row.getCell(5).getStringCellValue());
             //???
             row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
             info.setUnit(row.getCell(6).getStringCellValue());
             //?
             row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
             info.setCarCount((int) row.getCell(7).getNumericCellValue());
                    
             imports.add(info);
             }
             */
        }
        return batchAddData(planType, imports);
    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.controller.ProductInitController.java

/**
 * ????/*from   w ww.  j ava  2  s. c  om*/
 *
 * @param fileName
 * @param carrierName
 * @return
 */
public int importData(String fileName, String carrierName) {
    InputStream inputStream = null;
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<ProductInit> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            //                logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i);
            HSSFRow row = sheet.getRow(i);
            if (null == row) {
                continue;
            }

            int cellNum = row.getPhysicalNumberOfCells();
            //                logger.info("count cell num is:" + cellNum);
            if (cellNum >= 4) {
                ProductInit info = new ProductInit();
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                info.setJianHao(row.getCell(0).getStringCellValue());
                if (Units.strIsEmpty(info.getJianHao()))
                    continue;
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                info.setPinMing(row.getCell(1).getStringCellValue());
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setCarModel(row.getCell(2).getStringCellValue());
                row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setInitNum((int) row.getCell(3).getNumericCellValue());

                if (row.getCell(4).getCellType() == Cell.CELL_TYPE_STRING) {
                    info.setInitDate(row.getCell(4).getStringCellValue());
                } else {
                    if (HSSFDateUtil.isCellDateFormatted(row.getCell(4))) {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = HSSFDateUtil.getJavaDate(row.getCell(4).getNumericCellValue());
                        info.setInitDate(dateFormat.format(date));
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        info.setInitDate(df.format(row.getCell(4).getNumericCellValue()));
                    }
                }
                info.setCarrierName(carrierName);
                imports.add(info);
            }
        }
        DatabaseOpt opt;
        Connection conn = null;
        CallableStatement statement = null;
        opt = new DatabaseOpt();
        try {
            conn = opt.getConnect();
            conn.setAutoCommit(false);
            statement = conn.prepareCall(
                    "insert into tbProductInit(productInitId, pinMing, jianHao, carModel, initNum, initDate, carrierName)"
                            + "values(productInitId.NEXTVAL, ?, ?, ?, ?, ?, ?)");
            for (ProductInit infoImport : imports) {
                statement.setString(1, infoImport.getPinMing());
                statement.setString(2, infoImport.getJianHao());
                statement.setString(3, infoImport.getCarModel());
                statement.setInt(4, infoImport.getInitNum());
                statement.setString(5, infoImport.getInitDate());
                statement.setString(6, infoImport.getCarrierName());
                statement.addBatch();
            }
            statement.executeBatch();
            conn.commit();
            return 0;
        } catch (SQLException ex) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex1) {
                logger.error("?", ex1);
            }
            logger.error("?", ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                logger.error("?", ex);
            }
        }

    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

---

From source file:com.cn.servlet.InInterface.java

private ArrayList<Object> importDetailData(String detail, String beanPackage, String tableName, String fileName)
        throws Exception {
    //???//from  w  ww  . j a  v a  2 s.  c om
    Class objClass = Class.forName(beanPackage + tableName);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        //            json = Units.objectToJson(-1, "???, ?, ???", null);
        return null;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        templateDataIndex[fieldDes.indexOf(fieldName)] = i;
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        //??object(, ??  )
        JSONObject jSONObject = JSONObject.parseObject(detail);
        Iterator<String> keysIterator = jSONObject.keySet().iterator();
        while (keysIterator.hasNext()) {
            String key = keysIterator.next();
            for (Field field : fields) {
                //                    System.out.println("fieldName:" + field.getName() + ",key:" + key);
                if (field.getName().compareToIgnoreCase(key) == 0) {
                    //                        System.out.println("fieldName:" + field.getName() + ",key:" + key + ",key value:" + jSONObject.getString(key));
                    field.setAccessible(true);
                    if (!Units.strIsEmpty(jSONObject.getString(key))) {
                        field.set(object, jSONObject.getString(key));
                    }
                }
            }
        }

        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            //                String fieldType = field.getGenericType().toString();

            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Integer.valueOf(Units.subZeroAndDot(Units.getCellValue(cell))));

                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    field.set(object, Float.valueOf(Units.getCellValue(cell)));

                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getCellValue(cell)));

                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, null);
                } else {
                    field.set(object, Units.getCellValue(cell));
                }
            }
        }

        result.add(object);
    }
    return result;
}

---

From source file:com.cn.util.ExcelImport.java

/**
* ?2003excel/*from   w w  w  .  j a  v a 2s.  c  o  m*/
* @param file
* @return
*/
private static List<List<Object>> read2003Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = null;
    HSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

---

From source file:com.cn.util.ExcelImport.java

/**
 * ?excel//from   w w  w  .ja  va2  s.c  o m
 * @param file
 * @return
 * @throws IOException
 */
public static String[] readExcelHead(File file) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = null;
    HSSFCell cell = null;
    row = sheet.getRow(0);
    String[] buff = new String[row.getLastCellNum()];
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        cell = row.getCell(i);
        buff[i] = cell.getStringCellValue();
    }
    return buff;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• »