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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:at.htlpinkafeld.beans.AlleAbwesenheitenBean.java

/**
 * xls post processing// ww  w .  j a  v  a 2 s. c  om
 *
 * @param document xls document
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java

/**
 * post processes the XLS for creating/*from w w w  . ja  va  2  s.com*/
 *
 * @param document xls-doc
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    sheet.shiftRows(0, sheet.getLastRowNum(), 2);

    HSSFRow topRow = sheet.createRow(0);

    topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear());
    topRow.createCell(3).setCellValue("von " + selectedUser.getPersName());
    sheet.createRow(1).createCell(0).setCellValue(" ");

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    HSSFRow header = sheet.getRow(2);
    HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        cell = footer.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)//from  ww  w . j a va  2  s. c o  m
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Reads a spreadsheet from an execl file using POI and transfers the data into the <code>IGridWMServer</code>.
 * Only the first sheet in the file is copied.
 *
 * @param grid the XMA model where to copy the data
 * @param in an InputStream containing the excel file
 * @throws SysException if the file could not be read by poi
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 * @throws SysException if the file could not be read and parsed by poi.
 *///www  .  j  av a2s.c  o  m
public static List poi2xma(IGridWM grid, InputStream in) {
    try {
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook book = new HSSFWorkbook(fs);
        List errors = poi2xma(grid, book, 0);
        calcAlignements(grid, book.getSheetAt(0));
        return errors;
    } catch (IOException exc) { // problem dedected by POI and reported in an IOException
        throw new SysException(exc, ((GridWM) grid).getMessage("poiReadError", exc.getMessage()))
                .setCode(GridWM.CODE_poiReadError);
    } catch (Exception exc) { // problem not dedected by POI -> need original exception type
        throw new SysException(exc, ((GridWM) grid).getMessage("poiReadError", exc.toString()))
                .setCode(GridWM.CODE_poiReadError);
    }
}

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the first sheet in the workbook is copied.
        /*www. j a  v  a 2 s. co m*/
 * @param grid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM grid, HSSFWorkbook book) {
    List errors = poi2xma(grid, book, 0);
    calcAlignements(grid, book.getSheetAt(0));
    return errors;
}

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the sheet on the given sheetIndex is copied.
        /*from  w ww.j  av a 2  s  . c o  m*/
 * @param igrid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @param sheetIndex the index of the sheet to copy
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) {
    GridWM grid = (GridWM) igrid;
    try {
        List errorList = new ArrayList();
        grid.setSheetName(book.getSheetName(sheetIndex));

        grid.colors.clear();
        grid.initBuildInColors();
        short ic = GridWM.HSSF_FIRST_COLOR_INDEX;
        HSSFPalette palette = book.getCustomPalette();
        for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) {
            grid.colors.add(ic, new GridColor(color.getTriplet()));
        }

        grid.fonts.clear();
        int numFonts = book.getNumberOfFonts();
        if (numFonts > 4) {
            // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt()
            numFonts += 1;
        }
        for (short i = 0; i < numFonts; i++) {
            HSSFFont font = book.getFontAt(i);
            byte fontstyle = GridFont.FONT_NORML;
            if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD)
                fontstyle |= GridFont.FONT_BOLD;
            if (font.getItalic())
                fontstyle |= GridFont.FONT_ITALIC;
            grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor()));
        }

        grid.styles.clear();
        for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) {
            HSSFCellStyle style = book.getCellStyleAt(i);
            grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor()));
        }

        grid.namedRanges.clear();
        for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) {
            HSSFName name = book.getNameAt(i);
            String rangeName = name.getNameName();
            String rangeRef = null;
            try { // ranges not defined but referenced by formulas have a name but no reference in HSSF
                rangeRef = name.getReference();
            } catch (Exception exc) {
                errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName))
                        .setCode(GridWM.CODE_inconsistentRange));
            }
            if (rangeRef != null) {
                try {
                    GridRange range = grid.getJeksDelegate().toRange(rangeRef);
                    range.setKey(rangeName);
                    grid.namedRanges.put(rangeName, range);
                } catch (Exception exc) {
                    errorList.add(new SysException(exc,
                            ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef))
                                    .setCode(GridWM.CODE_unsupportedReference));
                }
            }
        }

        grid.rows.clear();
        grid.cols.clear();
        grid.cells.clear();
        grid.delegate = new GridJeksDelegate(grid);
        HSSFSheet sheet = book.getSheetAt(sheetIndex);
        int firstColNum = Integer.MAX_VALUE;
        int lastColNum = Integer.MIN_VALUE;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null)
                continue;
            if (row.getFirstCellNum() >= 0)
                firstColNum = Math.min(firstColNum, row.getFirstCellNum());
            lastColNum = Math.max(lastColNum, row.getLastCellNum());
            if (lastColNum > 255)
                lastColNum = 255;
            for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                HSSFCell hssfcell = row.getCell(j);
                if (hssfcell == null)
                    continue;
                GridCell gridcell = grid.getOrCreateCell(i, j);
                switch (hssfcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridcell.setValue(hssfcell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    // TODO: recherche error text
                    byte errorCode = hssfcell.getErrorCellValue();
                    //                    gridcell.setValue(errorCode);
                    gridcell.setValue("#ERROR");
                    errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord",
                            grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode)))
                                    .setCode(GridWM.CODE_errorRecord));
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formula = null;
                    try {
                        formula = hssfcell.getCellFormula();
                        gridcell.setFormula(formula);
                    } catch (SysException e) {
                        if (formula != null)
                            gridcell.setValue("=" + formula); //set it as text without interpretation
                        errorList.add(e);
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (isDateCell(book, hssfcell)) {
                        gridcell.setValue(hssfcell.getDateCellValue());
                    } else {
                        gridcell.setValue(hssfcell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridcell.setValue(hssfcell.getStringCellValue());
                    break;
                default:
                    throw new SysException("unknown cell type " + hssfcell.getCellType());
                }
                gridcell.setEditable(!hssfcell.getCellStyle().getLocked());
                gridcell.setStyle(hssfcell.getCellStyle().getIndex());
            }
        }

        final int scalefactor = 256 / 7; // empirically testet
        //        int width = sheet.getDefaultColumnWidth();  // returns nonsense
        //        width = width/scalefactor;
        //        grid.setDefaultColumnWidth(width);
        for (short i = (short) firstColNum; i <= lastColNum; i++) {
            int width = sheet.getColumnWidth(i);
            width = width / scalefactor;
            grid.getOrCreateColumn(i).setWidth(width);
        }

        if (firstColNum == Integer.MAX_VALUE)
            firstColNum = 0;
        if (lastColNum == Integer.MIN_VALUE)
            lastColNum = 0;
        grid.setMaxRange(
                new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum));
        grid.setVisibleRange(grid.getMaxRange());
        return errorList;
    } finally {
        grid.handle(grid.new GridReloadEvent());
    }
}

From source file:attandance.standalone.utils.ExcelUtils.java

public static List<AttandanceRecord> readDataFromExcel(String fileName) {
    List<AttandanceRecord> result = new ArrayList<>();
    try {/*from w  ww  . ja  va 2s .com*/
        FileInputStream file = new FileInputStream(new File(fileName));
        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int lineIndex = 1;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            int columnIndex = 1;
            AttandanceRecord lineBean = new AttandanceRecord();
            //skip header
            if (lineIndex == 1) {
                lineIndex += 1;
                continue;
            }
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellValue = "";
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = "" + cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    cellValue = "" + cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                default:
                    cellValue = cell.getStringCellValue();
                }
                lineBean.setAttribute(columnIndex, cellValue);
                columnIndex = columnIndex + 1;
            }
            result.add(lineBean);
        }
        file.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return result;
}

From source file:axiom.util.TextExtractor.java

License:Open Source License

public static String msExcelExtractor(InputStream is) throws Exception {
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    StringBuffer sb = new StringBuffer();

    final int numSheets = wb.getNumberOfSheets();
    for (int k = 0; k < numSheets; k++) {
        HSSFSheet sheet = wb.getSheetAt(k);
        Iterator rIt = sheet.rowIterator();
        while (rIt.hasNext()) {
            HSSFRow row = (HSSFRow) rIt.next();
            Iterator cIt = row.cellIterator();
            while (cIt.hasNext()) {
                HSSFCell cell = (HSSFCell) cIt.next();
                sb.append(cell.toString()).append(" ");
            }/*  ww  w .  ja  v a  2 s.com*/
        }
    }

    return sb.toString();
}

From source file:be.vds.jtbdive.client.view.core.dive.profile.DiveProfileExcelParser.java

License:Open Source License

public DiveProfile read(File file) throws IOException {
    DiveProfile dp = new DiveProfile();
    Map<Double, Double> depthEntries = new HashMap<Double, Double>();
    Set<Double> ascentWarnings = new HashSet<Double>();
    Set<Double> decoWarnings = new HashSet<Double>();
    Set<Double> remainingBottomTimeWarnings = new HashSet<Double>();
    Set<Double> decoEntries = new HashSet<Double>();

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet sheet = wb.getSheetAt(0);

    int rowMax = sheet.getLastRowNum();
    for (int i = 1; i <= rowMax; i++) {
        HSSFRow row = sheet.getRow(i);//from   ww w  . ja  va 2  s .co m
        double second = row.getCell(0).getNumericCellValue();
        depthEntries.put(second, row.getCell(1).getNumericCellValue());

        boolean b = row.getCell(2).getBooleanCellValue();
        if (b)
            ascentWarnings.add(second);

        b = row.getCell(3).getBooleanCellValue();
        if (b)
            remainingBottomTimeWarnings.add(second);

        b = row.getCell(4).getBooleanCellValue();
        if (b)
            decoWarnings.add(second);

        b = row.getCell(5).getBooleanCellValue();
        if (b)
            decoEntries.add(second);
    }

    dp.setDepthEntries(depthEntries);

    if (ascentWarnings.size() > 0)
        dp.setAscentWarnings(ascentWarnings);

    if (decoWarnings.size() > 0)
        dp.setDecoCeilingWarnings(decoWarnings);

    if (remainingBottomTimeWarnings.size() > 0)
        dp.setRemainingBottomTimeWarnings(remainingBottomTimeWarnings);

    if (decoEntries.size() > 0)
        dp.setDecoEntries(decoEntries);

    return dp;
}

From source file:Beans.GeminusCompen.java

public void postProcessXLS(Object document) throws IOException {
    HSSFWorkbook wb = (HSSFWorkbook) document;

    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);//from   www  .  ja  va 2s .co  m

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }
}