Example usage for org.apache.poi.xssf.usermodel XSSFTable getEndCellReference

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFTable getEndCellReference.

Prototype

public CellReference getEndCellReference() 

Source Link

Usage

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public int[] getReferenceCoordinatesForTable(int sheetIndex, String tableName) {
    if (!isXSSF()) {
        throw new IllegalArgumentException("Tables are not supported with this file format");
    }/*from   w w w . j a va2 s.c o  m*/
    XSSFSheet s = (XSSFSheet) getSheet(sheetIndex);
    for (XSSFTable t : s.getTables()) {
        if (tableName.equals(t.getName())) {
            CellReference start = t.getStartCellReference();
            CellReference end = t.getEndCellReference();
            int top = start.getRow();
            int bottom = end.getRow();
            int left = start.getCol();
            int right = end.getCol();
            return new int[] { top, left, bottom, right };
        }
    }
    throw new IllegalArgumentException("Could not find table '" + tableName + "'!");
}

---

From source file:opn.greenwebs.FXMLDocumentController.java

private File createStockFile(List<ItemDB> list) {
    int nSize = list.size();
    XSSFWorkbook wbs = createStockWorkbook();

    XSSFSheet sheetStock = wbs.getSheet("Digital Version");
    List<XSSFTable> lTables = sheetStock.getTables();
    // Create a FormulaEvaluator to use
    FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper()
            .createFormulaEvaluator();/*from w w  w.j a  va  2  s. co  m*/
    File fStock = createFilename("STK", "");
    Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault()));
    Row rowed = sheetStock.getRow(6);
    Cell celled = rowed.getCell(10);
    CellStyle cellStyle = celled.getCellStyle();
    XSSFFont font = sheetStock.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    celled.setCellValue(Date.from(instant));
    celled.setCellStyle(cellStyle);
    rowed = sheetStock.getRow(10);
    celled = rowed.getCell(2);
    celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5));
    if (!lTables.isEmpty()) {
        XSSFTable table = lTables.get(0);
        table.getCTTable()
                .setRef(new CellRangeAddress(table.getStartCellReference().getRow(),
                        table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(),
                        table.getEndCellReference().getCol()).formatAsString());
        XSSFRow row;
        XSSFCell cell;
        font = sheetStock.getWorkbook().createFont();
        font.setFontHeight(14);
        int nCellRef = table.getStartCellReference().getRow() + 1;
        for (ItemDB itemdb : list) {
            row = sheetStock.createRow(nCellRef++);
            cell = row.createCell(0);
            cellStyle = cell.getCellStyle();
            cell.setCellValue(itemdb.getDblQty());
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(1);
            cell.setCellValue(itemdb.getStrMfr());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(itemdb.getStrSKU());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(itemdb.getStrDescrip());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(itemdb.getStrSupplier());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(5);
            cell.setCellValue(itemdb.getStrSupPart());
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellStyle(cellStyle);
            cell = row.createCell(6);
            cell.setCellValue(itemdb.getDblSalePrice());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(7);
            cell.setCellValue(itemdb.getDblCost());
            cell.setCellStyle(cellStyle);
            /*cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(9);
            cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            CellStyle style = wbs.createCellStyle();
            style.setDataFormat(wbs.createDataFormat().getFormat("0%"));
            cell.setCellStyle(style);*/
            mainWorkbookEvaluator.evaluateAll();
        }

        try {
            try (FileOutputStream fileOut = new FileOutputStream(fStock)) {
                wbs.write(fileOut);
                return fStock;
            }
        } catch (FileNotFoundException ex) {
            logger.info(ex.getLocalizedMessage());
        } catch (IOException ex) {
            logger.info(ex.getLocalizedMessage());
        }
    }
    return null;
}

---

From source file:opn.greenwebs.FXMLDocumentController.java

private void shootTables(XSSFSheet sheet, List<XSSFTable> list) {

    for (XSSFTable xTable : list) {
        for (int j = xTable.getStartCellReference().getRow(); j < xTable.getEndCellReference().getRow(); j++) {
            Row row = sheet.getRow(j + 1);
            List lstItem = new ArrayList();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    String strCell = cell.getStringCellValue();
                    lstItem.add(strCell);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    lstItem.add(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    lstItem.add(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    lstItem.add("");
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    lstItem.add(cell.getCellFormula());
                    break;
                default:
                }//from   ww  w.  ja va 2  s.co m
            }
            insertData(lstItem, true);
        }
    }
}

---