Example usage for org.apache.poi.ss.usermodel Workbook 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.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//from  ww  w.j a  v a2s. c  om
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, List<String> titleList, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);

    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(0);
    int colNum = titleRow.getLastCellNum();

    for (int i = 2; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();

        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titleList.get(j));
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

---

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//ww w .  j  av a 2  s.c o  m
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, int titlerow, int datarow, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);
    PropertieDescription rowidField = classInfo.getPropertyDescriptor("rowid");
    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(titlerow);

    int colNum = titleRow.getLastCellNum();
    String[] titles = new String[colNum];
    for (int i = 0; i < colNum; i++) {
        titles[i] = titleRow.getCell(i).getStringCellValue().trim();
    }

    for (int i = datarow; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();
        if (rowidField != null)
            rowidField.setValue(retObject, i);
        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titles[j]);
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

---

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to update the embedded Excel workbook. As the format and structire
 * of the workbook are known in advance, all this code attempts to do is
 * write a new value into the first cell on the first row of the first
 * worksheet. Prior to executing this method, that cell will contain the
 * value 1./*from   w w  w  .j  a va  2s.  c  o m*/
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

                // Get an InputStream from the pacage part and pass that
                // to the create method of the WorkbookFactory class. Update
                // the resulting Workbook and then stream that out again
                // using an OutputStream obtained from the same PackagePart.
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                cell.setCellValue(NEW_VALUE);
                workbook.write(pPart.getOutputStream());
            }
        }

        // Finally, write the newly modified Word document out to file.
        this.doc.write(new FileOutputStream(this.docFile));
    }
}

---

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to test whether or not the embedded workbook was correctly
 * updated. This method simply recovers the first cell from the first row
 * of the first workbook and tests the value it contains.
 * <p/>/*from   ww  w .j  a  v  a  2s .  co  m*/
 * Note that execution will not continue up to the assertion as the
 * embedded workbook is now corrupted and causes an IllegalArgumentException
 * with the following message
 * <p/>
 * <em>java.lang.IllegalArgumentException: Your InputStream was neither an
 * OLE2 stream, nor an OOXML stream</em>
 * <p/>
 * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
 * is executed.
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void checkUpdatedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                //assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001);
            }
        }
    }
}

---

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine,
        int sheetIndex) throws Excel4JException {

    Sheet sheet = workbook.getSheetAt(sheetIndex);
    Row row = sheet.getRow(offsetLine);//from  www .j  av  a 2s  .  c  o m
    List<T> list = new ArrayList<>();
    Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
    if (maps == null || maps.size() <= 0)
        throw new Excel4jReadException(
                "The Excel format to read is not correct, and check to see if the appropriate rows are set");
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();

    for (int i = offsetLine + 1; i <= maxLine; i++) {
        row = sheet.getRow(i);
        if (null == row)
            continue;
        T obj;
        try {
            obj = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new Excel4JException(e);
        }
        for (Cell cell : row) {
            int ci = cell.getColumnIndex();
            ExcelHeader header = maps.get(ci);
            if (null == header)
                continue;
            String val = Utils.getCellValue(cell);
            Object value;
            String filed = header.getFiled();
            // ??
            if (null != header.getReadConverter()
                    && header.getReadConverter().getClass() != DefaultConvertible.class) {
                value = header.getReadConverter().execRead(val);
            } else {
                // ?
                value = Utils.str2TargetClass(val, header.getFiledClazz());
            }
            Utils.copyProperty(obj, filed, value);
        }
        list.add(obj);
    }
    return list;
}

---

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private List<List<String>> readExcel2ObjectsHandler(Workbook workbook, int offsetLine, int limitLine,
        int sheetIndex) {

    List<List<String>> list = new ArrayList<>();
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();/*ww  w.j a  v a  2s .  c o  m*/
    for (int i = offsetLine; i <= maxLine; i++) {
        List<String> rows = new ArrayList<>();
        Row row = sheet.getRow(i);
        if (null == row)
            continue;
        for (Cell cell : row) {
            String val = Utils.getCellValue(cell);
            rows.add(val);
        }
        list.add(rows);
    }
    return list;
}

---

From source file:com.github.cutstock.utils.ProfileUtils.java

License:Apache License

public static Profiles parseFile(String filePath) {
    Workbook workbook = null;
    Sheet sheet = null;//w ww.  j  a  v a2 s .  c o m
    // CutStockPlugin.getLogger().log(new Status(0,
    // CutStockPlugin.PLUGIN_ID, filePath));
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        if (filePath.toLowerCase().endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }

    } catch (IOException e) {
        MessageDialog.openError(null, "ERROR",
                "?EXCEL,?????Excel");
    } finally {
        try {
            if (is != null) {
                is.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    sheet = workbook.getSheetAt(0);
    int sheetRowNum = sheet.getLastRowNum();
    Profiles proflies = new Profiles();
    for (int i = 1; i <= sheetRowNum; i++) {
        Row currentRow = sheet.getRow(i);
        if (currentRow != null) {
            Cell cell = currentRow.getCell(ColumnType.PROFILE_NAME);
            String name = (String) getCellValue(cell);
            if (StringUtil.Empty(name)) {
                break;
            }
            cell = currentRow.getCell(ColumnType.PROFILE_CODE_DATA);
            String codeData = (String) getCellValue(cell);
            cell = currentRow.getCell(ColumnType.PROFILE_COLOR);
            String color = (String) getCellValue(cell);

            cell = currentRow.getCell(ColumnType.PROFILE_WIDTH);
            double width = (Double) getCellValue(cell);
            cell = currentRow.getCell(ColumnType.PROFILE_AMOUNT);
            int amount = ((Double) getCellValue(cell)).intValue();

            proflies.add(name, codeData, color, new BigDecimal(width), amount);
        }
    }
    return proflies;
}

---

From source file:com.github.drbookings.ical.XlsxBookingFactory.java

License:Open Source License

@Override
public Collection<BookingBeanSer> build() {
    final List<BookingBeanSer> bookings = new ArrayList<>();
    FileInputStream stream = null;
    Workbook workbook = null;
    try {//  w  w  w .  ja v a  2s  .c o  m
        stream = new FileInputStream(file);
        workbook = new HSSFWorkbook(stream);
        final Sheet sheet = workbook.getSheetAt(0);
        if (logger.isInfoEnabled()) {
            logger.info("Processing sheet " + sheet.getSheetName());
        }
        final int indexBookingNumber = FileFormatBookingXLS.getColumnIndexBookingNumber(sheet.getRow(0));
        final int indexClientName = FileFormatBookingXLS.getColumnIndexClientName(sheet.getRow(0));
        final int indexBookingCheckIn = FileFormatBookingXLS.getColumnIndexCheckIn(sheet.getRow(0));
        final int indexBookingCheckOut = FileFormatBookingXLS.getColumnIndexCheckOut(sheet.getRow(0));
        final int indexStatus = FileFormatBookingXLS.getColumnIndexStatus(sheet.getRow(0));
        final List<Integer> bookingNumbers = new ArrayList<>();
        final List<String> guestNames = new ArrayList<>();
        final List<String> stati = new ArrayList<>();
        final List<LocalDate> bookingCheckIn = new ArrayList<>();
        final List<LocalDate> bookingCheckOut = new ArrayList<>();
        for (final Row r : sheet) {
            // skip first row
            if (r.getRowNum() == 0) {
                continue;
            }
            bookingNumbers.add(FileFormatBookingXLS.getBookingNumber(r.getCell(indexBookingNumber)));
            guestNames.add(FileFormatBookingXLS.getString(r.getCell(indexClientName)));
            bookingCheckIn.add(FileFormatBookingXLS.getDate(r.getCell(indexBookingCheckIn)));
            bookingCheckOut.add(FileFormatBookingXLS.getDate(r.getCell(indexBookingCheckOut)));
            stati.add(FileFormatBookingXLS.getString(r.getCell(indexStatus)));
        }
        if (logger.isDebugEnabled()) {
            logger.debug("BookingBean numbers: " + bookingNumbers);
            logger.debug("Guest names: " + guestNames);
            logger.debug("Check-in dates: " + bookingCheckIn);
            logger.debug("Check-out dates: " + bookingCheckOut);
        }
        if (logger.isInfoEnabled()) {
            logger.info("Building bookings.. ");
        }

        for (int i = 0; i < bookingNumbers.size(); i++) {
            final int number = bookingNumbers.get(i);
            final LocalDate checkIn = bookingCheckIn.get(i);
            final LocalDate checkOut = bookingCheckOut.get(i);
            final String names = guestNames.get(i);
            final String status = stati.get(i);
            if (status.equals("ok")) {
                final BookingBeanSer bb = new BookingBeanSer();
                bb.checkInDate = checkIn;
                bb.checkOutDate = checkOut;
                bb.guestName = names;
                bb.externalId = Integer.toString(number);
                bookings.add(bb);
            } else {
                if (logger.isDebugEnabled()) {
                    logger.debug("Skipping status " + status);
                }
            }
        }
    } catch (final Exception e) {
        if (logger.isErrorEnabled()) {
            logger.error(e.getLocalizedMessage(), e);
        }
    } finally {
        if (workbook != null) {
            IOUtils.closeQuietly(workbook);
        }
        if (stream != null) {
            IOUtils.closeQuietly(stream);
        }
    }
    return bookings;
}

---

From source file:com.github.wnameless.workbookaccessor.WorkbookReader.java

License:Apache License

/**
 * Creates a {@link WorkbookReader} by given {@link Workbook}. Assumes there
 * is a header included in the spreadsheet.
 * //from   w  ww .j  a  v  a2 s  .  c  o m
 * @param workbook
 *          a {@link Workbook}
 */
public WorkbookReader(Workbook workbook) {
    this.workbook = workbook;
    if (workbook.getNumberOfSheets() == 0)
        workbook.createSheet();
    sheet = workbook.getSheetAt(0);
    setHeader();
}

---

From source file:com.github.wnameless.workbookaccessor.WorkbookWriter.java

License:Apache License

/**
 * Creates a {@link WorkbookWriter} by given {@link Workbook}.
 * //  w  w  w  .  ja  va  2s.  c  o  m
 * @param workbook
 *          a {@link Workbook}
 */
public WorkbookWriter(Workbook workbook) {
    this.workbook = workbook;
    if (workbook.getNumberOfSheets() == 0)
        workbook.createSheet();
    sheet = workbook.getSheetAt(0);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »