Example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.poifs.filesystem.*
  5. POIFSFileSystem
  6. POIFSFileSystem

Introduction

In this page you can find the example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem.

Prototype


public POIFSFileSystem(InputStream stream) throws IOException

Source Link

Document

Create a POIFSFileSystem from an InputStream.

Usage

From source file:com.krawler.esp.servlets.XLSDataExtractor.java

License:Open Source License

public JSONObject parseXLS1(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    ArrayList<String> arr = new ArrayList<String>();
    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;

    JSONArray jArr = new JSONArray();
    try {//from   ww w  . j a  v a  2s  . c om
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                jArr.put(obj);
                continue;
            }
            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    arr.add(val);
                    continue;
                }
                ;
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    val = Double.toString(cell.getNumericCellValue());
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        String df_full = "yyyy-MM-dd";
                        DateFormat sdf = new SimpleDateFormat(df_full);
                        val = sdf.format(df);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    val = cell.getRichStringCellValue().getString();
                    break;
                }
                if (i == 0) { // List of Headers (Consider first row as Headers)
                    jtemp1 = new JSONObject();
                    jtemp1.put("header", val);
                    jtemp1.put("index", j);
                    jobj.append("Header", jtemp1);
                    obj.put(colHeader, val);
                    arr.add(val);
                } else {
                    if (arr.get(j) != null)
                        obj.put(arr.get(j), val);
                }

            }
            jArr.put(obj);
        }
    } catch (Exception ex) {
        Logger.getLogger(XLSDataExtractor.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportController.java

License:Open Source License

public ModelAndView fileUploadXLS(HttpServletRequest request, HttpServletResponse response) {
    String View = "jsonView-ex";
    JSONObject jobj = new JSONObject();
    try {/*from  www  .  j av  a 2 s .  co  m*/
        System.out.println("A(( Upload XLS start : " + new Date());
        jobj.put("success", true);
        FileItemFactory factory = new DiskFileItemFactory(4096,
                new File(ConfigReader.getinstance().get("UploadTempDir", "/tmp")));
        ServletFileUpload upload = new ServletFileUpload(factory);
        upload.setSizeMax(10485760);
        List fileItems = upload.parseRequest(request);
        Iterator i = fileItems.iterator();
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        String fileName = null;
        String fileid = UUID.randomUUID().toString();
        fileid = fileid.replaceAll("-", ""); // To append UUID without "-" [SK]
        String Ext = "";
        while (i.hasNext()) {
            java.io.File destDir = new java.io.File(destinationDirectory);
            if (!destDir.exists()) { //Create xls file's folder if not present
                destDir.mkdirs();
            }

            FileItem fi = (FileItem) i.next();
            if (fi.isFormField())
                continue;
            fileName = fi.getName();
            if (fileName.contains(".")) {
                Ext = fileName.substring(fileName.lastIndexOf("."));
                int startIndex = fileName.contains("\\") ? (fileName.lastIndexOf("\\") + 1) : 0;
                fileName = fileName.substring(startIndex, fileName.lastIndexOf("."));
            }

            if (fileName.length() > 28) { // To fixed Mysql ERROR 1103 (42000): Incorrect table name
                throw new DataInvalidateException("Filename is too long, use upto 28 characters.");
            }
            fi.write(new File(destinationDirectory, fileName + "_" + fileid + Ext));
        }

        POIFSFileSystem fs = new POIFSFileSystem(
                new FileInputStream(destinationDirectory + "/" + fileName + "_" + fileid + Ext));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        int count = wb.getNumberOfSheets();
        JSONArray jArr = new JSONArray();
        for (int x = 0; x < count; x++) {
            JSONObject obj = new JSONObject();
            obj.put("name", wb.getSheetName(x));
            obj.put("index", x);
            jArr.put(obj);
        }
        jobj.put("file", destinationDirectory + "/" + fileName + "_" + fileid + Ext);
        jobj.put("filename", fileName + "_" + fileid + Ext);
        jobj.put("data", jArr);
        jobj.put("msg", "Image has been successfully uploaded");
        jobj.put("lsuccess", true);
        jobj.put("valid", true);
    } catch (FileUploadBase.SizeLimitExceededException ex) {
        Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, ex);
        jobj.put("msg", "File exceeds max size limit i.e 10MB.");
        jobj.put("lsuccess", false);
        jobj.put("valid", true);
    } catch (Exception e) {
        Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, e);
        try {
            String msg = e.getMessage().contains("Invalid header signature;")
                    ? "Not a real xls file. File contents are not XLS content/corrupted content."
                    : e.getMessage();
            jobj.put("msg", msg);
            jobj.put("lsuccess", false);
            jobj.put("valid", true);
        } catch (Exception ex) {
        }
    } finally {
        System.out.println("A(( Upload XLS end : " + new Date());
        return new ModelAndView(View, "model", jobj.toString());
    }
}

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }/*w ww  .ja va2s. c  o m*/

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                HSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = Double.toString(cell.getNumericCellValue());
                            java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                            DateFormat sdf = new SimpleDateFormat(df);
                            val = sdf.format(date1);
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        val = cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public JSONObject parseXLS1(String filename, int sheetNo, int startindex)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    ArrayList<String> arr = new ArrayList<String>();
    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;

    JSONArray jArr = new JSONArray();
    try {/*from  w ww  .j  av a  2s  .  com*/
        for (int i = startindex; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum();
            }
            for (int j = 0; j < maxCol; j++) {
                HSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    arr.add(val);
                    continue;
                }
                ;
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = Double.toString(cell.getNumericCellValue());
                        java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        DateFormat sdf = new SimpleDateFormat(df);
                        val = sdf.format(date1);
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    val = cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                if (i == startindex) { // List of Headers (consider startindex row as a headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val);
                        jtemp1.put("index", j);
                        jobj.append("Header", jtemp1);
                        obj.put(colHeader, val);
                    }
                    arr.add(val);
                } else {
                    if (arr.get(j) != null)
                        obj.put(arr.get(j), val);
                }

            }
            if (obj.length() > 0) {
                jArr.put(obj);
            }

        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public int dumpXLSFileData(String filename, int sheetNo, int startindex) throws ServiceException {
    int dumpedRows = 0;
    try {/*from w  w  w. j  a v  a2s .  c o m*/
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        for (int i = startindex; i <= maxRow; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                HSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = Double.toString(cell.getNumericCellValue());
                        java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        DateFormat sdf = new SimpleDateFormat(df);//(df_full); //BUG:16085
                        val = sdf.format(date1);
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    val = cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                dumpedRows += importDao.dumpFileRow(tableName, dataArray.toArray());
            }
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    }
    return dumpedRows;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * Generate the preview of the xls grid//from  w  w w .j a v  a 2s  .  c  o m
 * @param filename
 * @param sheetNo
 * @return
 * @throws FileNotFoundException
 * @throws IOException
 * @throws JSONException
 */
public static JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    //DateFormat sdf = new SimpleDateFormat(df);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int maxSheetColCnt = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }
    int firstValidRec = 0;
    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header
                firstValidRec = i;

            //                    if(i==0) {
            maxCol = row.getLastCellNum();
            if (maxSheetColCnt < maxCol) // get max column count
                maxSheetColCnt = maxCol;
            //                    }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                HSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;

                if (cell != null) {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = cell.toString();//Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == firstValidRec) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxSheetColCnt);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename//from  w ww .j a  v a 2 s.c o m
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                HSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    }
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
@Transactional/*from w  w  w  . ja v a2 s  .c  o m*/
public void uploadSelfCheckData(InputStream is, String hcrwId, String fileName, Integer nd) throws Exception {
    Hcrw hcrw = hcrwMapper.selectByPrimaryKey(hcrwId);
    Workbook workbook = null;
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }

    //?
    validateExcel(hcrwId, workbook);
    if (importFlag.equals("1")) {
        //?
        nianbaoWangzhiwangdian(hcrw, workbook.getSheet("??"), nd);
        //?
        nianbao(hcrw, workbook.getSheet(""), workbook.getSheet("??"),
                workbook.getSheet(""), nd);

        //
        gudongchuzi(hcrw, workbook.getSheet("??"), nd);

        //??
        guquanbiangeng(hcrw, workbook.getSheet("????"), nd);

        //
        duiwaitouzi(hcrw, workbook.getSheet("?????"), nd);

        //?
        duiwandanbao(hcrw, workbook.getSheet("??"), nd);

        //?
        xingzhengxuke(hcrw, workbook.getSheet("??????"), nd);

        jsGudongchuzhi(hcrw, workbook.getSheet("??"));
        jsGuquanbiangeng(hcrw, workbook.getSheet("????"));
        jsXingzhengxuke(hcrw, workbook.getSheet("??????"));
        jsZhishichanquan(hcrw, workbook.getSheet("??"));
        jsXingzhengchufa(hcrw, workbook.getSheet("??"));
    }
    workbook.close();
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
public void judgeRepeatExcle(InputStream is, int firstRowNum, int colNum, String fileName) throws Exception {
    Map<String, Object> sheetValues = new HashedMap();
    Workbook workbook = null;/*from w  w  w .  j ava  2s  .c o m*/
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }
    Sheet sheet = workbook.getSheetAt(0);
    for (int i = firstRowNum; i < sheet.getLastRowNum() + 1; i++) {
        Row row = sheet.getRow(i - 1);
        System.out.println(i);
        Cell cell = row.getCell(colNum - 1);
        if (null != cell && null != POIUtils.getStringCellValue(cell)
                && !POIUtils.getStringCellValue(cell).equals("")) {
            if (sheetValues.containsKey(POIUtils.getStringCellValue(cell))) {
                throw new RuntimeException(
                        POIUtils.getStringCellValue(cell) + "??????");
            } else {
                sheetValues.put(POIUtils.getStringCellValue(cell), i);
            }
        }
    }
}

From source file:com.lacreacion.remates.FrameMiembros.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    try {/*w w  w .j a va2 s .co  m*/
        JFileChooser fc = new JFileChooser();
        int returnVal = fc.showOpenDialog(this);

        if (returnVal == JFileChooser.APPROVE_OPTION) {
            getDatabaseIP();
            File file = fc.getSelectedFile();
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            HSSFCell cell;

            int rows; // No of rows
            rows = sheet.getPhysicalNumberOfRows();

            int cols = 0; // No of columns
            int tmp = 0;

            // This trick ensures that we get the data properly even if it doesn't start from first few rows
            for (int i = 0; i < 10 || i < rows; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                    if (tmp > cols) {
                        cols = tmp;
                    }
                }
            }

            for (int r = 1; r < rows; r++) {
                row = sheet.getRow(r);
                if (row != null) {
                    entityManager.getTransaction().commit();
                    entityManager.getTransaction().begin();
                    TblMiembros miembro = new TblMiembros();
                    miembro.setNombre(row.getCell(0).getStringCellValue());
                    miembro.setCtacte(
                            Integer.valueOf(row.getCell(1).getStringCellValue().replaceAll("[^\\d.]", "")));
                    if (row.getCell(2) != null) {
                        miembro.setDomicilio(row.getCell(2).getStringCellValue());
                    }
                    if (row.getCell(3) != null) {
                        miembro.setBox((int) row.getCell(3).getNumericCellValue());
                    }
                    entityManager.persist(miembro);
                    entityManager.flush();
                    java.util.Collection data = query.getResultList();
                    list.clear();
                    list.addAll(data);
                }
            }

        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null,
                Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        ex.printStackTrace();
    }
}