List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getTables
public List<XSSFTable> getTables()
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Prototype function. Not yet functional. * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template. * @param language Language of the label in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException//from w ww . ja v a 2 s.co m * @throws IOException * @throws InvalidFormatException */ public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException { //open template URL url = Translation.class.getClassLoader() .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx"); //Workbook wb = WorkbookFactory.create(new File(url.getPath())); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath()))); Sheet sheet = wb.getSheetAt(0); //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook Row row; Cell cell; for (int i = 0; i < 20; i++) { row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < 3; j++) { cell = row.getCell(j); if (cell == null) { cell = row.createCell(j); } switch (j) { case 0: cell.setCellValue(i); break; case 1: cell.setCellValue(i * j * -1); break; case 2: cell.setCellValue(i * j); break; } } } //redefine data range //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html XSSFSheet sheet1 = wb.getSheetAt(0); XSSFTable table = sheet1.getTables().get(0); CTTable cttable = table.getCTTable(); AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2)); /* Set Range to the Table */ cttable.setRef(my_data_range.formatAsString()); // cttable.setDisplayName("DATEN"); /* this is the display name of the table */ //cttable.setName("test"); /* This maps to "displayName" attribute in <table>, OOXML */ //cttable.setId(1L); //id attribute against table as long value /* //redefine data range Name rangeCell = wb.getName("DATEN"); //Set new range for named range //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface); String reference = sheet.getSheetName() + "!$A$2:$C$20"; //Assigns range value to named range rangeCell.setRefersToFormula(reference); */ //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public String[] getTables(int sheetIndex) { if (isXSSF()) { XSSFSheet s = (XSSFSheet) getSheet(sheetIndex); String[] tables = new String[s.getTables().size()]; int i = 0; Iterator<XSSFTable> it = s.getTables().iterator(); while (it.hasNext()) { tables[i++] = it.next().getName(); }/*from www .j a v a 2s .c om*/ return tables; } else { return new String[0]; } }
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 . jav a2s . co 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 ww. ja v a 2 s . c o 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 getData() { try {/*from w w w .j a v a2 s .c o m*/ fStockDir = new File(fUserDir, "Stock"); if (!fStockDir.exists()) { fStockDir.mkdirs(); } FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return lowercaseName.endsWith(".xlsx"); }; File[] fStock = fStockDir.listFiles(xlsxFilter); for (File excel : fStock) { try (FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); List<XSSFTable> lstTables = sheet.getTables(); if (!lstTables.isEmpty()) { shootTables(sheet, lstTables); } else { //System.out.println("we have one without a table"); Iterator<Row> itr = sheet.iterator(); boolean bData = false; int nRow = 0; while (itr.hasNext()) { Row row = itr.next(); int nCou = 0; Iterator<Cell> cellIterator = row.cellIterator(); List lstItem = new ArrayList(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); if (strCell.equalsIgnoreCase("Qty")) { nCou = nRow + 1; } if (bData) { lstItem.add(strCell); } break; case Cell.CELL_TYPE_NUMERIC: if (bData) { lstItem.add(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: if (bData) { lstItem.add(cell.getBooleanCellValue()); } break; case Cell.CELL_TYPE_BLANK: if (bData) { lstItem.add(""); } break; case Cell.CELL_TYPE_FORMULA: if (bData) { lstItem.add(cell.getCellFormula()); } break; default: } } if (bData) { System.out.print(lstItem); insertData(lstItem, true); } nRow++; if (nCou == nRow) { bData = true; } } //bIsGood = false; } } } } catch (FileNotFoundException fe) { //System.out.println("File not found exception " + fe.getMessage()); } catch (IOException ie) { //System.out.println("IO Exception " + ie.getMessage()); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private List<ItemDB> createListFromTable(File fStock) { List<ItemDB> lstMain = new ArrayList<>(); logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath()); try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName()); FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator(); List lstItem;//from w ww. j a va 2 s . c o m List<XSSFTable> lstTables = sheet.getTables(); logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size()); if (!lstTables.isEmpty()) { logger.log(Level.INFO, "the table is not empty"); for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0) .getEndCellReference().getRow(); j++) { ItemDB itmLine = new ItemDB(); lstItem = new ArrayList(); logger.log(Level.INFO, "we have some rows"); if (j > lstTables.get(0).getStartCellReference().getRow()) { Row row = sheet.getRow(j); if (row != null) { Iterator<Cell> cellIt = row.cellIterator(); logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext()); while (cellIt.hasNext()) { Cell cell = cellIt.next(); CellValue cellvalue = evaluator2.evaluate(cell); if (cellvalue == null) { lstItem.add(""); } else { switch (cellvalue.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cellvalue.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cellvalue.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cellvalue.getBooleanValue()); case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add("You found a formula"); break; default: } } } logger.log(Level.ALL, "lstItem is {0} and {1}", new Object[] { lstItem.size(), lstItem.toString() }); itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString())); logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString()); itmLine.setStrMfr(lstItem.get(1).toString()); itmLine.setStrSKU(lstItem.get(2).toString()); itmLine.setStrDescrip(lstItem.get(3).toString()); itmLine.setStrSupplier(lstItem.get(4).toString()); itmLine.setStrSupPart(lstItem.get(5).toString()); logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5)); if (lstItem.size() > 6) { if (!lstItem.get(6).toString().isEmpty()) { itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString())); } } //System.out.println("Added item " + itmLine.getStrDescrip()); lstMain.add(itmLine); } } } } } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } logger.log(Level.INFO, "returning {0}", lstMain.size()); return lstMain; }