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

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. 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.claim.controller.VajiraRFController.java

public ProgrameStatus hcRfSummarySpliteWithHcode(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 17;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraRfSummary> listData = new ArrayList<ObjRptVajiraRfSummary>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {/*  ww  w  .  j ava2 s  .  com*/
        connection = new DBManage().open();

        VajiraREDao vajiraREDao = new VajiraREDao();
        vajiraREDao.setConnection(connection);

        //readTemplate 
        file = new FileInputStream(
                new File("." + File.separator + "xls" + File.separator + "HC_RF_SUM_SPLITE.xls"));

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        sheet.setColumnWidth((col_last + 1), WIDTH_TXID);

        // row 0 HEADER0
        row = sheet.createRow(0);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HEADER1);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last));
        cell.setCellStyle(csHead);

        // row 1 HEADER2
        row = sheet.createRow(1);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HEADER2);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last));
        cell.setCellStyle(csHead);

        // row 2 HOSPITAL
        row = sheet.createRow(2);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HOSPITAL);
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last));
        cell.setCellStyle(csHead);

        int curRow = row_start;
        int autoNumber = 1;
        listData = vajiraREDao.getListVajiraSumGroupHmain(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraRfSummary objData = listData.get(i);

            row = sheet.createRow(curRow);
            row.setHeight((short) 400);

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getHmain());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHmain_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(objData.getCount_pid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(4);
            cell.setCellValue(objData.getCount_txid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(5);
            cell.setCellValue(objData.getSum_chrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(6);
            cell.setCellValue(objData.getSum_chrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(7);
            cell.setCellValue(objData.getSum_chrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(8);
            cell.setCellValue(objData.getSum_chrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getSum_chrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getSum_paid_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getSum_paid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getSum_paid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getSum_paid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getSum_paid_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getSum_point());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getSum_reimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getSum_totalreimburse());
            cell.setCellStyle(csDouble2R);

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csString2Center);
        row.createCell(2).setCellStyle(csString2Center);

        cell = row.createCell(3);
        cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(4);
        cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(5);
        cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(6);
        cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(7);
        cell.setCellFormula(builderFormulaSumRound(7, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(8);
        cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(9);
        cell.setCellFormula(builderFormulaSumRound(9, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(10);
        cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(11);
        cell.setCellFormula(builderFormulaSumRound(11, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(12);
        cell.setCellFormula(builderFormulaSumRound(12, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(13);
        cell.setCellFormula(builderFormulaSumRound(13, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(14);
        cell.setCellFormula(builderFormulaSumRound(14, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(15);
        cell.setCellFormula(builderFormulaSumRound(15, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(16);
        cell.setCellFormula(builderFormulaSumRound(16, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(17);
        cell.setCellFormula(builderFormulaSumRound(17, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_SUM_REPORT
                + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls");
        workbookBase.write(out);

        out.close();
        file.close();
        Console.LOG("? " + StringOpUtil.removeNull(report.getServiceName())
                + " : " + report.getYearMonth() + "-" + report.getNo()
                + " ?", 1);
        programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS);
        programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE);
        programeStatus.setProcessStatus(true);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}

From source file:com.cmcti.cmts.domain.service.impl.CustomerMacMappingLocalServiceImpl.java

License:Open Source License

public void importAddressFromXls(InputStream is, int sheetIdx, int startRow, ServiceContext serviceContext,
        boolean deleteAll) throws PortalException, SystemException {
    if (deleteAll) {
        customerMacMappingPersistence.removeAll();
        counterLocalService.reset(CustomerMacMapping.class.getName());
    }/*from w w  w .ja  va2s. com*/

    Iterator<Row> rowIterator = null;
    try (HSSFWorkbook workbook = new HSSFWorkbook(is)) {

        HSSFSheet sheet = workbook.getSheetAt(sheetIdx);
        rowIterator = sheet.iterator();
    } catch (Exception e) {
        logger.error(e);
    }

    List<CustomerMacMapping> mappings = getCustomerMacMappings(rowIterator, startRow, serviceContext,
            deleteAll);

    for (CustomerMacMapping mapping : mappings) {
        customerMacMappingPersistence.update(mapping);
    }
}

From source file:com.cmcti.cmts.domain.service.impl.MerchantLocalServiceImpl.java

License:Open Source License

public void importMerchant(InputStream is, int sheetIdx, int startRowIdx, ServiceContext serviceContext,
        boolean deleteAll) throws PortalException, SystemException {

    if (deleteAll) {
        merchantPersistence.removeAll();
        merchantScopePersistence.removeAll();
        counterLocalService.reset(Merchant.class.getName());
        counterLocalService.reset(MerchantScope.class.getName());
    }/* w w w . ja v  a2s .co  m*/

    Iterator<Row> rowIterator = null;
    try (HSSFWorkbook workbook = new HSSFWorkbook(is)) {
        HSSFSheet sheet = workbook.getSheetAt(sheetIdx);
        rowIterator = sheet.iterator();
    } catch (Exception e) {
        logger.error(e);
    }

    List<Merchant> merchants = getMerchants(rowIterator, startRowIdx, serviceContext, deleteAll);
    for (Merchant merchant : merchants) {
        merchantPersistence.update(merchant);
    }
}

From source file:com.cmcti.cmts.domain.service.impl.MerchantScopeLocalServiceImpl.java

License:Open Source License

public void importMerchant(InputStream is, int sheetIdx, int startRowIdx, ServiceContext serviceContext)
        throws PortalException, SystemException {

    merchantScopePersistence.removeAll();
    counterLocalService.reset(MerchantScope.class.getName());

    Iterator<Row> rowIterator = null;
    try (HSSFWorkbook workbook = new HSSFWorkbook(is)) {
        HSSFSheet sheet = workbook.getSheetAt(sheetIdx);
        rowIterator = sheet.iterator();//from   w w w .  j  a v a  2s .  c o  m
    } catch (Exception e) {
        log.error(e);
    }

    List<MerchantScope> merchants = getMerchants(rowIterator, startRowIdx, serviceContext);
    for (MerchantScope merchant : merchants) {
        merchantScopePersistence.update(merchant);
    }
}

From source file:com.cmcti.cmts.domain.service.impl.UpChannelMetadataLocalServiceImpl.java

License:Open Source License

public void importAddressFromXls(InputStream is, int sheetIdx, int startRow, ServiceContext serviceContext,
        boolean deleteAll) throws PortalException, SystemException {
    // Remove all data
    if (deleteAll) {
        upChannelMetadataPersistence.removeAll();
        counterLocalService.reset(UpChannelMetadata.class.getName());
    }// w  w  w.j a  v  a2 s . c om

    Iterator<Row> rowIterator = null;
    try (HSSFWorkbook workbook = new HSSFWorkbook(is)) {

        HSSFSheet sheet = workbook.getSheetAt(sheetIdx);
        rowIterator = sheet.iterator();
    } catch (Exception e) {
        logger.error(e);
    }

    List<UpChannelMetadata> mappings = getUpstreamMetas(rowIterator, startRow, serviceContext, deleteAll);

    for (UpChannelMetadata mapping : mappings) {
        merchantScopePersistence.removeByUpstreamChannel(mapping.getCmtsId(), mapping.getIfIndex());
        if (mapping.getMerchantCodes() != null && mapping.getMerchantCodes().size() > 0) {
            for (String merchantCode : mapping.getMerchantCodes()) {
                MerchantScope scope = merchantScopePersistence.create(0);
                scope.setCmtsId(mapping.getCmtsId());
                scope.setIfIndex(mapping.getIfIndex());
                scope.setMerchantCode(merchantCode);
                merchantScopeLocalService.updateMerchantScope(scope, serviceContext);
            }
        }

        upChannelMetadataPersistence.update(mapping);
    }
}

From source file:com.cms.utils.DataUtil.java

License:Open Source License

/**
 *
 * @param file//w  w w . j  a va2  s.c o m
 * @param iSheet
 * @param iBeginRow
 * @param iFromCol
 * @param iToCol
 * @param rowBack
 * @param lstValidateCells
 * @return
 */
public static List isValidExcells(String mimeType, File file, int iSheet, int iBeginRow, int iFromCol,
        int iToCol, int rowBack, List<ValidateCells> lstValidateCells) {
    String fileName = file.getName();
    fileName = removeDotInteger(fileName);
    boolean isCopySheet = true;
    File fileError = null;
    Map<String, String> mapsNameError = new HashMap<>();
    List lst = null;
    try {
        if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx
            lst = ExcelReaderXLSX.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack);
        } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
            lst = ExcelReader.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack);
        }
        //If lst null return
        if (lst == null) {
            return lst;
        }
        String error = "";
        ValidateCells validateCells = null;
        int index = 0;
        if (iBeginRow == 0) {
            index = 1;
        } else {
            index = 0;
        }
        int rowErr = 0;
        Object[] temp;
        List<String> lstReturn = Lists.newArrayList();
        for (int i = index, size = lst.size(); i < size; i++) {
            temp = (Object[]) lst.get(i);
            if (checkObjectNull(temp)) {
                //                    lst.remove(temp);
                //                    i--;
                //                    continue;
                if (i == 0) {
                    lst = Lists.newArrayList();
                }
                break;
            }
            error = "";
            for (int j = 0; j < lstValidateCells.size(); j++) {
                validateCells = lstValidateCells.get(j);
                if (validateCells.getPattern() != null) {
                    error += DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(),
                            validateCells.isIsNotNull(), validateCells.getPattern());
                } else {
                    lstReturn = DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(),
                            validateCells.isIsNotNull(), validateCells.getLength());
                    error += lstReturn.get(0);
                    temp[j] = lstReturn.get(1);
                }
            }
            if (!isStringNullOrEmpty(error)) {
                rowErr = i + iBeginRow;
                mapsNameError.put(rowErr + "", error);
            }
        }

        if (!mapsNameError.isEmpty()) {
            //
            FileInputStream flieInput = new FileInputStream(file);
            XSSFWorkbook workbookIp = null;
            String fileCreate = fileName + "_Error.xlsx";
            FileOutputStream fileOut = new FileOutputStream(fileCreate);
            XSSFWorkbook workbookEp = new XSSFWorkbook();
            XSSFSheet worksheetEp = workbookEp.createSheet("Thong_Tin_Loi");
            XSSFCellStyle cellStyle = null;
            //
            if (isCopySheet) {
                if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx
                    workbookIp = new XSSFWorkbook(flieInput);
                } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
                    HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(flieInput);
                    workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook);
                }
                XSSFSheet worksheetIp = workbookIp.getSheetAt(iSheet);
                ExcelReaderXLSX.copySheets(worksheetEp, worksheetIp, iToCol);
                //
                cellStyle = CommonUtils.styleCell(workbookEp);
                isCopySheet = false;
            }
            for (Map.Entry<String, String> entrySet : mapsNameError.entrySet()) {
                String key = entrySet.getKey();
                String value = entrySet.getValue();
                int row = Integer.valueOf(key);
                XSSFRow row5 = worksheetEp.getRow(row);
                if (row5 != null) {
                    XSSFCell cellB1 = row5.createCell(iToCol + 1);
                    cellB1.setCellValue(value);
                    cellB1.setCellStyle(cellStyle);
                }
            }
            workbookEp.write(fileOut);
            fileOut.flush();
            fileOut.close();
            fileError = new File(fileCreate);
            Resource resource = new FileResource(fileError);
            Page.getCurrent().open(resource, null, false);
            lst = null;
            fileError.deleteOnExit();
        }

    } catch (Exception e) {
        e.printStackTrace();
        ;
        lst = null;
    }
    return lst;
}

From source file:com.cms.utils.ExcelReader.java

public static List importExcel(File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack)
        throws FileNotFoundException {
    List lst = new ArrayList();
    FileInputStream flieInput = new FileInputStream(file);
    HSSFWorkbook workbook;//  w w w.  j av  a2s. c o  m
    try {
        workbook = new HSSFWorkbook(flieInput);
        HSSFSheet worksheet = workbook.getSheetAt(iSheet);
        int irowBack = 0;
        for (int i = iBeginRow; i <= worksheet.getLastRowNum(); i++) {
            Object[] obj = new Object[iToCol - iFromCol + 1];
            Row row = worksheet.getRow(i);
            if (row != null) {
                int iCount = 0;
                int check = 0;
                for (int j = iFromCol; j <= iToCol; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            obj[iCount] = cell.getStringCellValue().trim();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                obj[iCount] = DateTimeUtils.convertDateToString(date, "dd/MM/yyyy");
                            } else {
                                Double doubleValue = (Double) cell.getNumericCellValue();
                                //String.format("%.0f", doubleValue);
                                List<String> lstValue = DataUtil.splitDot(String.valueOf(doubleValue));
                                if (lstValue.get(1).matches("[0]+")) {
                                    obj[iCount] = lstValue.get(0);
                                } else {
                                    obj[iCount] = String.format("%.2f", doubleValue).trim();
                                }
                            }

                            break;
                        case Cell.CELL_TYPE_BLANK:
                            check++;
                            break;
                        }
                    } else {
                        obj[iCount] = null;
                    }
                    iCount += 1;
                }
                if (check != (iToCol - iFromCol + 1)) {
                    lst.add(obj);
                }

            } else {
                irowBack += 1;
            }
            if (irowBack == rowBack) {
                break;
            }
        }
    } catch (IOException ex) {
        lst = null;
    }
    return lst;
}

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

/**
 * ?//from  www . j av a 2 s  . c  om
 *
 * @param fileName
 * @return
 */
public int importData(String fileName) {
    InputStream inputStream = null;
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        ArrayList<BaseInfo> 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) {
                BaseInfo info = new BaseInfo();
                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(3).setCellType(Cell.CELL_TYPE_NUMERIC);
                info.setCarNum((int) row.getCell(3).getNumericCellValue());
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                info.setCarModel(row.getCell(2).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("insert into tbBaseInfo(baseId, pinMing, jianHao, carModel, carNum)"
                    + "values(BASEID.NEXTVAL, ?, ?, ?, ?)");
            for (BaseInfo infoImport : imports) {
                statement.setString(1, infoImport.getPinMing());
                statement.setString(2, infoImport.getJianHao());
                statement.setString(3, infoImport.getCarModel());
                statement.setInt(4, infoImport.getCarNum());
                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.CommonController.java

/**
 * ?/*from ww w .  j  a  v a2  s.co 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  w  w  w. j a  v a  2s.  com*/
 * @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;
}