Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook(PackagePart part) throws IOException

Source Link

Document

Constructs a XSSFWorkbook object using Package Part.

Usage

From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java

License:Apache License

public void parseFile(InputStream inStream) {
    try {/* w  w w. ja v a  2 s.  co m*/
        XSSFWorkbook workbook = new XSSFWorkbook(inStream);
        if (useFirstSheet) {
            XSSFSheet sheet = workbook.getSheetAt(0);
            processSheet(sheet, listeners.get(DEFAULT_RULESHEET_NAME));
        } else {
            for (String sheetName : listeners.keySet()) {
                XSSFSheet sheet = workbook.getSheet(sheetName);
                processSheet(sheet, listeners.get(sheetName));
            }
        }
    } catch (IOException ex) {
        throw new DecisionTableParseException(ex.getMessage(), ex);
    }
}

From source file:com.cloudera.sa.ExcelRecordReader.java

License:Apache License

@Override
public void initialize(InputSplit split, TaskAttemptContext context) throws IOException, InterruptedException {
    FileSplit fileSplit = (FileSplit) split;
    Configuration conf = context.getConfiguration();
    Path file = fileSplit.getPath();
    FileSystem fs = file.getFileSystem(conf);
    this.in = fs.open(file);
    XSSFWorkbook workbook = new XSSFWorkbook(this.in);
    XSSFSheet sheet = workbook.getSheetAt(0);
    this.totalRows = sheet.getPhysicalNumberOfRows();
    this.processedRows = 0;
    this.rowIterator = sheet.rowIterator();
}

From source file:com.clri.servlet.ExcelUpload.java

private int processExcelFile(File file, String uploadType, int type, int category) {

    int count = 0;
    Connection connection = null;
    MajorProductionsDAO majorProductionsDAO = new MajorProductionsDAO();
    MajorCustomersDAO majorCustomersDAO = new MajorCustomersDAO();
    DataBaseConnection dbcon = new DataBaseConnection();
    MajorProductions majorProductions = new MajorProductions();
    MajorCustomers majorCustomers = new MajorCustomers();
    majorCustomers.setType(type);//from  w  w w  . j av  a2  s  . c  om
    majorProductions.setType(type);
    majorProductions.setCategory(category);
    majorCustomers.setCategory(category);
    try {
        connection = dbcon.openConnection();
        // Creating Input Stream 
        FileInputStream myInput = new FileInputStream(file);

        // Create a workbook using the File System 
        XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);

        // Get the first sheet from workbook 
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        /**
         * We now need something to iterate through the cells.*
         */
        Iterator<Row> rowIter = mySheet.rowIterator();
        int rowCount = 0;
        while (rowIter.hasNext()) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (rowCount != 0) {
                if (CommonConstants.CUSTOMER_UPLOAD.equalsIgnoreCase(uploadType)) {
                    count += majorCustomersDAO.insertMajorCustomers(connection,
                            getCustomers(row, majorCustomers));
                } else {
                    count += majorProductionsDAO.insertMajorProductions(connection,
                            getProductions(row, majorProductions));
                }

            }
            rowCount++;
        }
    } catch (IOException e) {
    } finally {
        DBUtils.closeConnection(connection);
    }
    return count;

}

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

/**
 * ?/* www  .j  a  v a 2 s.  c o  m*/
 *
 * @param beanPackage
 * @param className
 * @param fileName
 * @return
 * @throws Exception
 */
public ArrayList<Object> importData(String beanPackage, String className, String fileName) throws Exception {
    //???
    Class objClass = Class.forName(beanPackage + className);
    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 = null;
    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();
        if (fieldDes.indexOf(fieldName) != -1) {
            templateDataIndex[fieldDes.indexOf(fieldName)] = i;
        } else {
            return null;
        }
    }

    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();
        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue());
                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue());
                    }
                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, "");
                } else {
                    field.set(object, Units.getStringCellValue(cell));
                }

            }
        }

        result.add(object);
    }

    return result;
}

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

/**
 * Excel?//from ww  w  .  jav  a2 s. c o  m
 * @param beanPackage
 * @param tableName
 * @param fileName
 * @param conn
 * @return
 * @throws Exception 
 */
public String importData(String beanPackage, String tableName, String fileName, String conn) throws Exception {
    String json;
    CommonController commonController = new CommonController();
    //???
    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 = null;
    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 json;
    }

    //?????
    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();
        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.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue());
                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue());
                    }
                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(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.getStringCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, null);
                } else {
                    field.set(object, Units.getStringCellValue(cell));
                }
            }
        }

        result.add(object);
    }

    ArrayList<Integer> addResult = commonController.dataBaseOperate(
            JSONObject.toJSONString(result, Units.features), beanPackage, tableName, "add", conn);
    //        System.out.println("import res:" + Arrays.toString(addResult.toArray()));
    if (addResult.get(0) == 0) {
        json = Units.objectToJson(0, "?" + (addResult.size() - 1) + "??!", null);
    } else if (addResult.get(0) == 2) {
        json = Units.objectToJson(2, "?!", null);
    } else {
        int successNum = 0, failedNum = 0;
        String failedMsg = ":";
        for (int i = 1; i < addResult.size(); i++) {
            int res = addResult.get(i);
            if (res == 1) {
                successNum++;
            } else {
                failedNum++;
                failedMsg += (i + 1) + ",";
            }
        }
        json = Units.objectToJson(-1,
                "?" + successNum + "??, " + failedNum + "??!" + failedMsg,
                null);
    }
    return json;
}

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

/**
 * ?//  ww w  .j  a  va 2s  .  co 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.servlet.InInterface.java

private ArrayList<Object> importDetailData(String detail, String beanPackage, String tableName, String fileName)
        throws Exception {
    //???/*www.j  a v a  2s  . co m*/
    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

/**
* ?2007excel//from  w  ww . j a v a  2 s . c o m
* 
* @param file
* @return
*/
private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell 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 XSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case XSSFCell.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 XSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

From source file:com.codellect.util.ExcelReader.java

private void read() {
    try (FileInputStream inputStream = new FileInputStream(excel);
            Workbook workbook = new XSSFWorkbook(inputStream)) {
        Sheet firstSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();
        Row nextRow = iterator.next();//from w w w. jav a2s .c o m
        while (iterator.hasNext()) {
            nextRow = iterator.next();
            short count = nextRow.getLastCellNum();
            if (count == 4 & notEmpty(nextRow.getCell(ColumnType.STATEMENT.value()))) {
                QbankBean qbankBean = new QbankBean();
                qbankBean.setId((int) nextRow.getCell(ColumnType.ID.value()).getNumericCellValue());
                qbankBean.setLevel((int) nextRow.getCell(ColumnType.LEVEL.value()).getNumericCellValue());
                qbankBean.setTag(nextRow.getCell(ColumnType.TAG.value()).getStringCellValue());
                qbankBean.setQuestion(nextRow.getCell(ColumnType.STATEMENT.value()).getStringCellValue());
                push(qbankBean);
            }
        }
    } catch (IOException ex) {
        System.out.println("\033[0;1m" + ex.getMessage());
        System.out.println(
                "Please place the data.xlsx file here or provide path of data file as in command line argument"
                        + "\033[0;0m");
    }
}

From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java

License:Apache License

private void readXmlBasedExcel(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data)
        throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Cell cell = null;/*from   w w  w  .j  a v  a2s  . c  o m*/

        List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            Map<String, String> columnData = new HashMap<String, String>();

            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);

                columnData.put("column" + (cellIndex + 1), cell.toString());
            }

            sheetData.add(columnData);
        }

        data.put("sheet" + (sheetIndex + 1), sheetData);
    }

}