Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheetAt.

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.runwaysdk.facade.InvokeMethodTest.java

License:Open Source License

public void testInvokeMethodWithByteArrayReturnType() throws Exception {
    BusinessDTO collectionObj1 = clientRequest.newBusiness(collectionType);
    collectionObj1.setValue("aCharacter", "some value");
    clientRequest.createBusiness(collectionObj1);

    BusinessDTO collectionObj2 = clientRequest.newBusiness(collectionType);
    collectionObj2.setValue("aCharacter", "some other value");
    clientRequest.createBusiness(collectionObj2);

    try {//from   w  w  w  .j  a  va 2  s . co  m
        Class<?> collectionClass = WebTestGeneratedClassLoader.load(collectionDTO);

        Method getCount = collectionClass.getMethod("getCollectionObjectCount", ClientRequestIF.class);
        Integer recordCount = (Integer) getCount.invoke(null, clientRequest);

        Method getExcelBytes = collectionClass.getMethod("getExcelFile", ClientRequestIF.class);
        Byte[] excelBytes = (Byte[]) getExcelBytes.invoke(null, clientRequest);

        // FileOutputStream fileBytes = new FileOutputStream(new
        // File(ExcelTest.path+"/ValueQueryTest.xls"));

        byte[] bytes = new byte[excelBytes.length];

        for (int i = 0; i < bytes.length; i++) {
            bytes[i] = excelBytes[i];
        }

        // fileBytes.write(bytes);
        //
        // fileBytes.flush();
        // fileBytes.close();

        InputStream stream = new ByteArrayInputStream(bytes);

        POIFSFileSystem fileSystem = new POIFSFileSystem(stream);
        Workbook workbook = new HSSFWorkbook(fileSystem);
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();

        Integer rowCount = 0;
        while (rowIterator.hasNext()) {
            rowIterator.next();
            rowCount++;
        }

        // Minus 1 for the header row
        rowCount--;

        assertEquals(recordCount, rowCount);
    } finally {
        clientRequest.delete(collectionObj1.getId());
        clientRequest.delete(collectionObj2.getId());
    }
}

From source file:com.runwaysdk.facade.InvokeMethodTest.java

License:Open Source License

public void testInvokeMethodWithInputStreamReturnType() throws Exception {
    BusinessDTO collectionObj1 = clientRequest.newBusiness(collectionType);
    collectionObj1.setValue("aCharacter", "some value");
    clientRequest.createBusiness(collectionObj1);

    BusinessDTO collectionObj2 = clientRequest.newBusiness(collectionType);
    collectionObj2.setValue("aCharacter", "some other value");
    clientRequest.createBusiness(collectionObj2);

    try {// ww  w .  ja v a  2s  .c  om
        Class<?> collectionClass = WebTestGeneratedClassLoader.load(collectionDTO);

        Method getCount = collectionClass.getMethod("getCollectionObjectCount", ClientRequestIF.class);
        Integer recordCount = (Integer) getCount.invoke(null, clientRequest);

        Method getExcelBytes = collectionClass.getMethod("getFileStream", ClientRequestIF.class);
        InputStream stream = (InputStream) getExcelBytes.invoke(null, clientRequest);

        POIFSFileSystem fileSystem = new POIFSFileSystem(stream);
        Workbook workbook = new HSSFWorkbook(fileSystem);
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();

        Integer rowCount = 0;
        while (rowIterator.hasNext()) {
            rowIterator.next();
            rowCount++;
        }

        // Minus 1 for the header row
        rowCount--;

        assertEquals(recordCount, rowCount);
    } finally {
        clientRequest.delete(collectionObj1.getId());
        clientRequest.delete(collectionObj2.getId());
    }
}

From source file:com.salahatwa.randomme.ReadXLS.java

/**
 * @param filePath/*w w w  . j a va 2s  .  c  o m*/
 * @return  list of Readed cells from xlsx
 */
public List<ReadedBean> readXLSFromFile(String filePath) {
    List<ReadedBean> data = new ArrayList();
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(filePath);
        // Using XSSF for xlsx format, for xls use HSSF

        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        //looping over each workbook sheet
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            //iterating over each row
            while (rowIterator.hasNext()) {

                ReadedBean readedBean = new ReadedBean();
                Row row = (Row) rowIterator.next();
                Iterator cellIterator = row.cellIterator();
                //Iterating over each cell (column wise)  in a particular row.
                while (cellIterator.hasNext()) {
                    Cell cell = (Cell) cellIterator.next();

                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(cell.getStringCellValue());
                        }

                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(String.valueOf((int) cell.getNumericCellValue()));
                        }
                        //                           
                    }
                }
                System.out.println(readedBean.getCell());
                data.add(readedBean);
            }
        }
        fis.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return data;
}

From source file:com.sany.appbom.service.AppBomServiceImpl.java

License:Open Source License

public void setExcelData(Workbook workbook, boolean is2003, List<AppBom> beans) {
    try {/*from w w w.j a va 2 s.  c om*/
        Sheet sheet = null;
        CellStyle cellStyle = null;
        Font font = null;
        if (is2003) {
            sheet = (HSSFSheet) workbook.getSheetAt(0);

        } else {
            sheet = (XSSFSheet) workbook.getSheetAt(0);
            cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        }
        for (int i = 0; i < beans.size(); i++) {
            AppBom bom = beans.get(i);
            Row row = null;
            row = sheet.createRow(i + 2);
            row.setHeight((short) 450);
            row.createCell(0).setCellValue(bom.getBm());
            row.createCell(1).setCellValue(bom.getApp_name_en());
            row.createCell(2).setCellValue(bom.getApply_domain());
            row.createCell(3).setCellValue(bom.getApp_name());
            row.createCell(4).setCellValue(bom.getDescription());
            row.createCell(5).setCellValue(getSoftLevel(bom.getSoft_level()));
            row.createCell(6).setCellValue(bom.getSupplier());
            row.createCell(7).setCellValue(bom.getStart_year());
            row.createCell(8).setCellValue(getState(bom.getState()));
            row.createCell(9).setCellValue(getRdType(bom.getRd_type()));
            row.createCell(10).setCellValue(bom.getVersion_no());
            row.createCell(11).setCellValue(bom.getDomain_url());
            row.createCell(12).setCellValue(bom.getStruct_mode());
            row.createCell(13).setCellValue(bom.getSoft_language());
            row.createCell(14).setCellValue(bom.getDevelop_tool());
            row.createCell(15).setCellValue(bom.getDb_type());
            row.createCell(16).setCellValue(bom.getDepartment_develop());
            row.createCell(17).setCellValue(bom.getProduct_manager());
            row.createCell(18).setCellValue(bom.getDepartment_maintain());
            row.createCell(19).setCellValue(bom.getSys_manager());
            row.createCell(20).setCellValue(getPlanType(bom.getPlan_type()));
            row.createCell(21).setCellValue(bom.getEvolve_strategy());
            row.createCell(22).setCellValue(bom.getEvolve_plan());
            row.createCell(23).setCellValue(bom.getEvolve_depart());
            row.createCell(24).setCellValue(getManageScope(bom.getManage_scope()));
            row.createCell(25).setCellValue(bom.getMain_description());
        }
    } catch (Exception e) {
        e.printStackTrace();

    } finally {

    }
}

From source file:com.sdfl.compiler.util.inputfile.impl.ImportInputFileLoaderFileSystemImpl.java

License:Open Source License

@Override
public ImportInputFile load(File pDataFile) {
    ImportInputFile lImportInputFile = new ImportInputFile();

    if (!pDataFile.exists()) {
        throw new FileNotFoundException(pDataFile);
    }/*from w w  w.  j  a v a  2 s .c o m*/

    try (FileInputStream lWorkbookFIS = new FileInputStream(pDataFile)) {
        Workbook lWB = null;

        if (pDataFile.getName().toLowerCase().endsWith("xls")) {
            lWB = new HSSFWorkbook(lWorkbookFIS);
        } else if (pDataFile.getName().toLowerCase().endsWith("xlsx")) {
            lWB = new XSSFWorkbook(lWorkbookFIS);
        } else {
            throw new UnrecongnizedFileFormatException(pDataFile);
        }

        Sheet lFirstSheet = lWB.getSheetAt(FIRST_SHEET);
        for (Row lCurRow : lFirstSheet) {
            ImportInputFileRow lCurInputFileRow = this.loadRow(lCurRow);
            lImportInputFile.add(lCurInputFileRow);
        }
    } catch (IOException e) {
        throw new FileNotFoundException(pDataFile);
    }

    return lImportInputFile;
}

From source file:com.sfs.ucm.controller.HelpContentAction.java

License:Open Source License

/**
 * Extract help content// w  w w  .  j  a v  a 2 s  .  c o m
 * 
 * @param buf
 *            byte array representing help content file
 * @throws UCMException
 */
private void extractHelpContent(byte[] buf) throws UCMException {

    try {
        InputStream inp = new ByteArrayInputStream(buf);

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> iter = sheet.iterator();
        Cell cell = null;
        Row row = null;

        // header rows
        if (iter.hasNext()) {
            row = iter.next(); // table title
            row = iter.next(); // column headers
        }

        if (iter.hasNext()) {
            while (iter.hasNext()) {

                // process records
                row = iter.next();

                // help key
                cell = row.getCell(0);

                if (cell != null) {
                    String key = cell.getStringCellValue();

                    // help content
                    cell = row.getCell(1);
                    String contents = cell.getStringCellValue();

                    // log it
                    Object[] values = new Object[3];
                    values[0] = row.getRowNum() + 1; // display as one-based
                    values[1] = key;
                    values[2] = StringUtils.abbreviate(contents, 20);
                    logger.info("Processing row {}; contents: {};{}", values);

                    // construct the help content object
                    Help theHelpItem = new Help(key, contents);

                    // if help item already exists then just update its contents otherwise add record
                    int ndx = this.helpItems.indexOf(theHelpItem);
                    if (ndx == -1) {
                        this.helpItems.add(theHelpItem);
                        logger.info("Added Help Item {}", theHelpItem.getKeyword());

                        // persist the object
                        em.persist(theHelpItem);

                    } else {
                        Help tmp = this.helpItems.get(ndx);
                        tmp.setContent(contents);

                        // persist the object
                        em.persist(tmp);
                        logger.info("Updated Help Item {}", tmp.getKeyword());

                    }
                }
            }
        }

        // done
        inp.close();
    } catch (InvalidFormatException e) {
        logger.error(e.getMessage());
        throw new UCMException(e);
    } catch (IOException e) {
        logger.error(e.getMessage());
        throw new UCMException(e);
    }
}

From source file:com.simopuve.helper.ExcelWrapperHelper.java

public static void WritePDVToExcell(PDVSurvey survey) {
    String pointOfSaleName = survey.getHeader().getPointOfSaleName().replace(" ", "");
    String folderName = new StringBuilder()
            .append(new DecimalFormat("00").format(Calendar.getInstance().get(Calendar.DAY_OF_MONTH)))
            .append("-").append(new DecimalFormat("00").format(Calendar.getInstance().get(Calendar.MONTH) + 1))
            .append("-").append(Calendar.getInstance().get(Calendar.YEAR)).toString();
    String filePath = new StringBuilder(System.getProperty("jboss.server.data.dir")).append("/PDV/")
            .append(folderName).append("/").append(survey.getHeader().isMall() ? "Mall/" : "Oficina/")
            .toString();//  w  w  w .j a  va2 s . co m
    String fileName = new StringBuilder(pointOfSaleName).append(".xls").toString();
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("pseudo-platilla.xlsx");
    try {
        Workbook book1 = new XSSFWorkbook(is);
        is.close();
        Sheet sheet = book1.getSheetAt(0);
        ExcelFiller.fillHeader(survey.getHeader(), sheet);
        ExcelFiller.fillRows(sheet, survey.getRows());
        File directory = new File(filePath);
        directory.mkdirs();
        FileOutputStream fileOut = new FileOutputStream(filePath + fileName);
        book1.write(fileOut);
        fileOut.close();
    } catch (IOException ex) {
        Logger.getLogger(ExcelWrapperHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.simopuve.helper.ReadPVDFromFile.java

public static PDVSurvey getPDVSurveyFromFile(String path, Boolean isMall) throws IOException {
    PDVSurvey PDVSurvey = new PDVSurvey();
    PDVHeader PDVHeader = new PDVHeader();
    PDVRow PDVRow = new PDVRow();
    PDVSurvey.setHeader(PDVHeader);//from  ww  w .j a v  a  2s  .c om

    Workbook book1 = getWorkBookFromPath(path);
    fillHeader(PDVHeader, book1.getSheetAt(0), isMall);
    PDVSurvey.setRows(getRowsFromWorkbook(book1.getSheetAt(0)));
    PDVSurvey.getRows().size();

    return PDVSurvey;
}

From source file:com.simopuve.helper.WriteAndReadExcel.java

public void writePoll() {
    FileInputStream isr = null;/*from ww  w  . j ava 2s. c o m*/
    try {
        File filename = new File("/home/neseiza/proyecto-chile/docs/pseudo-platilla.xlsx");
        isr = new FileInputStream(filename);
        Workbook book1 = new XSSFWorkbook(isr);
        isr.close();
        Sheet sheet = book1.getSheetAt(0); //leer una hoja del libro template

        ExcelFiller.fillHeader(sheet);
        List<PDVRow> PDVRows = new ArrayList();
        PDVRows.add(ExcelFiller.getFilledRow());
        PDVRows.add(ExcelFiller.getFilledRow());
        ExcelFiller.fillRows(sheet, PDVRows);

        //crear nuevo excel             
        FileOutputStream fileOut = new FileOutputStream(
                "/home/neseiza/proyecto-chile/docs/" + System.currentTimeMillis() + "-modificado.xls");
        book1.write(fileOut);
        fileOut.close();

    } catch (FileNotFoundException ex) {
        Logger.getLogger(WriteAndReadExcel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(WriteAndReadExcel.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            isr.close();
        } catch (IOException ex) {
            Logger.getLogger(WriteAndReadExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.simopuve.rest.SimopuveRESTServices.java

@Path("/reports")
@GET//from w w w .j a  v  a 2s. c  om
@Produces("text/plain")
public String getReportByDateInterval(@QueryParam("from") String from, @QueryParam("to") String to) {
    Date start;
    Date end;
    if (from == null) {
        start = new Date();
    } else {
        try {
            start = new SimpleDateFormat("dd/MM/yyyy").parse(from);
        } catch (ParseException ex) {
            start = new Date();
            Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    if (to == null) {
        end = new Date();
    } else {
        try {
            end = new SimpleDateFormat("dd/MM/yyyy").parse(to);
        } catch (ParseException ex) {
            end = new Date();
            Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    org.joda.time.format.DateTimeFormatter fmt = DateTimeFormat.forPattern("dd-MM-yyy");
    DateTime startDate = new DateTime(start);
    DateTime endDate = new DateTime(end);
    Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "Received UA: " + start);
    Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "Received UA: " + from);
    Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "Received UA: " + startDate);
    MutableDateTime currentDate = new MutableDateTime(startDate);
    List<PDVSurvey> surveyList = new ArrayList<>();
    File currentFolder;
    File mallFolder;
    File officeFolder;
    File tmpFolder;
    String varPath;
    String tmpPath;
    PDVSurvey survey = null;
    Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "Received startdate: " + startDate);
    while (!currentDate.isAfter(endDate)) {
        varPath = new StringBuilder(System.getProperty("jboss.server.data.dir")).append("/PDV/")
                .append(currentDate.toString(fmt)).append("/").toString();
        currentFolder = new File(varPath);

        if (currentFolder.exists()) {
            mallFolder = new File(varPath + "/Mall");
            officeFolder = new File(varPath + "/Oficina");
            if (mallFolder.exists()) {
                for (File fileEntry : mallFolder.listFiles()) {
                    try {
                        Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "archivo {0} ",
                                fileEntry.getName());
                        tmpPath = varPath + "/Mall/" + fileEntry.getName();
                        survey = getPDVSurveyFromFile(tmpPath, true);
                        surveyList.add(survey);

                    } catch (IOException ex) {
                        Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
            if (officeFolder.exists()) {
                for (File fileEntry : officeFolder.listFiles()) {
                    try {
                        Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "archivo {0} ",
                                fileEntry.getName());
                        tmpPath = varPath + "/Oficina/" + fileEntry.getName();
                        survey = getPDVSurveyFromFile(tmpPath, false);
                        surveyList.add(survey);
                    } catch (IOException ex) {
                        Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        }
        Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.INFO, "-+-+-+-tamao de lista {0} ",
                surveyList.size());
        currentDate.addDays(1);
    }

    String filePath = new StringBuilder(System.getProperty("jboss.server.data.dir"))
            .append("/PDV/testFlow.xlsx").toString();

    Workbook flowWorkbook = POIHelper.getWorkbookFromLocalReource("plantilla-base-flujo.xlsx");
    FillFlowBaseSheet(surveyList, flowWorkbook.getSheetAt(0));
    FillDetailBaseSheet(surveyList, flowWorkbook.getSheetAt(1));
    POIHelper.writeWorkbookInPath(flowWorkbook, filePath);

    return "Listo";
}