List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet
Sheet getSheet(String name);
From source file:ApachePOIExcelWrite.java
@Override public void doInvokeMethod(String MethodName, SiebelPropertySet inputs, SiebelPropertySet outputs) throws SiebelBusinessServiceException { if (MethodName.equalsIgnoreCase("GenerateExcelDoc")) { try {// w w w . ja v a 2s . co m // //IProperties AP = new ApplicationProperties(); SiebelDataBean conn = ApplicationsConnection.connectSiebelServer(); //Get excel path System.out.println(HelperAP.getInvoiceTemplate()); inputFile = HelperAP.getInvoiceTemplate(); //Read Excel document first input_document = new FileInputStream(new File(inputFile)); // Convert it into a POI object Workbook my_xlsx_workbook = WorkbookFactory.create(input_document); // Read excel sheet that needs to be updated Sheet my_worksheet = my_xlsx_workbook.getSheet("Sheet1"); // Declare a Cell object this.order_id = inputs.getProperty("QuoteId"); this.quote_number = inputs.getProperty("QuoteNum"); CustomerRecord customerInfo = new CustomerRecord(my_xlsx_workbook, my_worksheet, 3); customerInfo.setQuoteId(this.order_id); customerInfo.createCellFromList(new QShippment(conn), new ContactKey()); customerInfo.setStartRow(8); customerInfo.createCellFromList(new QAddress(conn), new ContactKey()); InvoiceExcel parts; int startRowAt = 17; parts = new InvoiceExcel(my_xlsx_workbook, my_worksheet); // parts.setStartRow(startRowAt); parts.setQuoteId(order_id); parts.createCellFromList(new QParts(conn), new ContactKey()); my_xlsx_workbook.setForceFormulaRecalculation(true); input_document.close(); XGenerator.doCreateBook(my_xlsx_workbook, "weststar_" + this.quote_number.replace(" ", "_")); Attachment a = new Attachment(conn, "Quote", "Quote Attachment"); String filepath = XGenerator.getProperty("filepath"); String filename = XGenerator.getProperty("filename"); //Attach the file to siebel a.Attach(filepath, filename, Boolean.FALSE, order_id); boolean logoff = conn.logoff(); my_xlsx_workbook.close(); System.out.println("Done"); outputs.setProperty("status", "success"); } catch (FileNotFoundException ex) { ex.printStackTrace(new PrintWriter(error_txt)); MyLogging.log(Level.SEVERE, "Caught File Not Found Exception: " + ex.getMessage() + error_txt.toString()); outputs.setProperty("status", "failed"); outputs.setProperty("error_message", error_txt.toString()); } catch (IOException ex) { ex.printStackTrace(new PrintWriter(error_txt)); MyLogging.log(Level.SEVERE, "Caught IO Exception: " + ex.getMessage() + error_txt.toString()); outputs.setProperty("status", "failed"); outputs.setProperty("error_message", error_txt.toString()); } catch (InvalidFormatException ex) { ex.printStackTrace(new PrintWriter(error_txt)); MyLogging.log(Level.SEVERE, "Caught Invalid Format Exception: " + ex.getMessage() + error_txt.toString()); outputs.setProperty("status", "failed"); outputs.setProperty("error_message", error_txt.toString()); } catch (EncryptedDocumentException ex) { ex.printStackTrace(new PrintWriter(error_txt)); MyLogging.log(Level.SEVERE, "Caught Encrypted Document Exception: " + ex.getMessage() + error_txt.toString()); outputs.setProperty("status", "failed"); outputs.setProperty("error_message", error_txt.toString()); } catch (Exception ex) { ex.printStackTrace(new PrintWriter(error_txt)); MyLogging.log(Level.SEVERE, "Caught Exception: " + ex.getMessage() + error_txt.toString()); outputs.setProperty("status", "failed"); outputs.setProperty("error_message", error_txt.toString()); } } }
From source file:android_connector.ConfigWindowController.java
/** * Liest eine Excel-Datei ein und entnimmt dieser die Werte fr Startnummer, * Name und Kategorie. Fgt die Werte anschlieend der Tabelle hinzu. * * @param event Klick auf Button, der die Methode aufruft *///from ww w . ja v a2 s. co m @FXML private void readExcel(ActionEvent event) { //Rcksetzen der Tabelle tabelle.setItems(FXCollections.observableArrayList()); personData = tabelle.getItems(); //FileChooser einsetzen, um Starterplan einzulesen FileChooser fc = new FileChooser(); //standardmig im Home-Verzeichnis starten fc.setInitialDirectory(new File(System.getProperty("user.home"))); //Alle-Dateien-Filter entfernen fc.setSelectedExtensionFilter(null); //FileFilter fr Exceldateien hinzufgen //nur "alte" Excel-Dateien knnen gelesen werden! fc.getExtensionFilters() .addAll(new FileChooser.ExtensionFilter("Microsoft Excel 1997-2003 Dokument (.xls)", "*.xls")); //Dateien einlesen File returnVal = fc.showOpenDialog(primaryStage); //prfen, ob Datei zurckgegeben --> eine gewhlt; muss aber nicht existieren if (returnVal != null) { //ExcelReader anlegen, der Datei dann in den Speicher vergewaltigt. ExcelReader reader = null; /** * Speichert, ob es beim Laden Probleme gab. True, wenn alles gut * ist, sonst false. */ boolean geladen = true; //Versuch, Datei zu laden try { reader = new ExcelReader(returnVal.getAbsolutePath()); } catch (IOException ex) { //Exception fangen, die durch Ladefehler entsteht, und diese mit ExceptionDialog anzeigen new MySQLConnection(null).showExceptionDialog(ex, "Lesefehler", "Datei konnte nicht gelesen werden.", "Die Datei " + returnVal.getAbsolutePath() + " konnte nicht gelesen werden. Bitte geben Sie bei einer Beschwerde an den Entwickler folgende Fehlermeldung an: ", false); //Fehler aufgetreten geladen = false; } //prfen, ob Fehler if (geladen) { //Datei geladen --> ExcelReader nicht null, kann referiert werden Workbook wb = reader.getWorkBook(); //Datei hat nur 1 Tabelle --> direkt die nehmen if (wb.getNumberOfSheets() == 1) { reader.setSheet(0); } else { //mehrere Tabellen --> User muss whlen, welche er will List<String> items = new ArrayList<>(); //Nahmen aller Tabellen in Liste speichern for (int i = 0; i < wb.getNumberOfSheets(); i++) { items.add(wb.getSheetName(i)); } //ChoiceDialog anzeigen, der die Auswahl ermglicht ChoiceDialog<String> dialog = new ChoiceDialog<>(items.get(0), items); dialog.setTitle("Tabelle auswhlen"); dialog.setHeaderText("Bitte Tabelle auswhlen!"); dialog.setContentText("In der bergebenen Datei wurden " + wb.getNumberOfSheets() + " Tabellen gefunden. Bitte whlen Sie die gewnschte Tabelle aus!"); Optional<String> result = dialog.showAndWait(); // The Java 8 way to get the response value (with lambda expression). /** * Finale Kopie des readers, der von Lambda adressiert * werden kann. */ final ExcelReader temp = reader; result.ifPresent(letter -> { //Tabelle laden, die gewhlt wurde. temp.setSheet(wb.getSheet(letter)); }); //nderungen bernehmen reader = temp; } //erste drei Spalten durchgehen, erste Zeile berspringen (Beschriftung), danach erste Spalte als Startnummer, zweite als Nahme, dritte als Kategorie interpretieren for (int i = 1; i < reader.getSheet().getLastRowNum() + 1; i++) { //Person-Objekt bilden, das Werte der Person in den Zeilen enthlt //Startnummern werden als Doubles gelesen, Umwandlung in int Person e = new Person((new Double(reader.getCellValueAt(i, 0))).intValue() + "", reader.getCellValueAt(i, 1), reader.getCellValueAt(i, 2)); //Starter den Personen hinzufgen ... personData.add(e); //... und ihre Zahl erhhen starter++; } //genderte Starter in Tabelle bernehmen tabelle.setItems(personData); } } }
From source file:at.peppol.commons.tools.MainCreateCodelistsFilesFromExcel.java
License:Mozilla Public License
public static void main(final String[] args) throws IOException, URISyntaxException { // Where is the Excel? final IReadableResource aXls = new FileSystemResource(EXCEL_FILE); if (!aXls.exists()) throw new IllegalStateException("The Excel file could not be found!"); // Interprete as Excel final Workbook aWB = new HSSFWorkbook(aXls.getInputStream()); // Check whether all required sheets are present final Sheet aParticipantSheet = aWB.getSheet(SHEET_PARTICIPANT); if (aParticipantSheet == null) throw new IllegalStateException("The " + SHEET_PARTICIPANT + " sheet could not be found!"); final Sheet aDocumentSheet = aWB.getSheet(SHEET_DOCUMENT); if (aDocumentSheet == null) throw new IllegalStateException("The " + SHEET_DOCUMENT + " sheet could not be found!"); final Sheet aProcessSheet = aWB.getSheet(SHEET_PROCESS); if (aProcessSheet == null) throw new IllegalStateException("The " + SHEET_PROCESS + " sheet could not be found!"); // Convert participants _emitIdentifierIssuingAgency(aParticipantSheet); // Convert document identifiers _emitDocumentIdentifiers(aDocumentSheet); // Convert processes identifiers _emitProcessIdentifier(aProcessSheet); // Write all Java source files final CodeWriter aWriter = new FileCodeWriter(new File("src/main/java"), CCharset.CHARSET_UTF_8); s_aCodeModel.build(aWriter);/*from w w w . ja v a2 s.co m*/ s_aLogger.info("Done creating code"); }
From source file:bad.robot.excel.matchers.SheetNameMatcher.java
License:Apache License
@Override protected boolean matchesSafely(Workbook actual, Description mismatch) { List<String> missingSheets = new ArrayList<String>(); for (Sheet sheet : sheetsOf(expected)) { if (actual.getSheet(sheet.getSheetName()) == null) missingSheets.add(sheet.getSheetName()); }/*from w ww .ja va2 s. c o m*/ mismatch.appendValueList("sheet(s) ", ", ", notFound(missingSheets), missingSheets); return missingSheets.isEmpty(); }
From source file:bad.robot.excel.matchers.SheetsMatcher.java
License:Apache License
@Override protected boolean matchesSafely(Workbook actual, Description mismatch) { for (Sheet expectedSheet : sheetsOf(expected)) { Sheet actualSheet = actual.getSheet(expectedSheet.getSheetName()); if (!hasSameNumberOfRowAs(expectedSheet).matchesSafely(actualSheet, mismatch)) return false; if (!hasSameRowsAs(expectedSheet).matchesSafely(actualSheet, mismatch)) return false; }//from w w w . j av a 2 s. c o m return true; }
From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java
License:Apache License
private void processSpreadSheet(Spreadsheet spCurrent, Workbook wbCurrent, FacesContext context) throws POIException { // Checking for Replacement Values Logger logCurrent = LoggerFactory.getLogger(this.getClass().getCanonicalName()); logCurrent.finer("Proccess Spread Sheet"); String strName = spCurrent.getName(); Sheet shProcess = wbCurrent.getSheet(strName); if (shProcess == null && !spCurrent.isCreate()) { return;//from w ww. ja v a 2s. c o m } if (shProcess == null) { shProcess = wbCurrent.createSheet(strName); } logCurrent.finer("Proccess Cell Values"); if (spCurrent.getCellValues() != null) { for (ICellValue iCV : spCurrent.getCellValues()) { if (iCV instanceof CellBookmark) { CellBookmark cb = (CellBookmark) iCV; if (StringUtil.isNotEmpty(cb.getName())) { findAndReplaceAll(shProcess, "<<" + cb.getName() + ">>", cb.getValue()); } } if (iCV instanceof CellValue) { CellValue cv = (CellValue) iCV; setCellValue(shProcess, cv.getRowNumber(), cv.getColumnNumber(), cv.getValue(), cv.isCellFormula(), cv.getPoiCellStyle()); } } } logCurrent.finer("Proccess ExportDefinition"); if (spCurrent.getExportDefinitions() != null) { for (IListDataExporter lstExport : spCurrent.getExportDefinitions()) { if (lstExport instanceof Data2ColumnExporter) { if (lstExport.getDataSource() != null) { EmbeddedDataSourceExportProcessor.getInstance().processExportCol( (Data2ColumnExporter) lstExport, shProcess, context, ((Data2ColumnExporter) lstExport).getVar(), ((Data2ColumnExporter) lstExport).getIndex()); } else { XPagesDataSourceExportProcessor.getInstances().processExportCol( (Data2ColumnExporter) lstExport, shProcess, context, ((Data2ColumnExporter) lstExport).getVar(), ((Data2ColumnExporter) lstExport).getIndex()); } } else if (lstExport instanceof Data2RowExporter) { if (lstExport.getDataSource() != null) { EmbeddedDataSourceExportProcessor.getInstance().processExportRow( (Data2RowExporter) lstExport, shProcess, context, ((Data2RowExporter) lstExport).getVar(), ((Data2RowExporter) lstExport).getIndex()); } else { XPagesDataSourceExportProcessor.getInstances().processExportRow( (Data2RowExporter) lstExport, shProcess, context, ((Data2RowExporter) lstExport).getVar(), ((Data2RowExporter) lstExport).getIndex()); } } } } }
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook; if (!outputFile.exists()) { outputFile.createNewFile();/* w w w .j a v a 2 s . com*/ outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private Hashtable<String, String> readNodeAbbrMapping(Workbook wb) { Sheet sheet = wb .getSheet(getPropertyValue(tpaProps.TRAINPATHSECTION_NODES_TO_SYSTEM_NODES_MAPPING_WS_NAME)); Hashtable<String, String> hashtable = new Hashtable<String, String>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i);//from www.ja va 2 s . c o m if (row == null) { continue; } Cell cellAbbr = row.getCell(0); if (cellAbbr == null || cellAbbr.getCellType() == HSSFCell.CELL_TYPE_BLANK) { continue; } Cell cellUnique = row.getCell(1); if (cellUnique == null || cellUnique.getCellType() == HSSFCell.CELL_TYPE_BLANK) { continue; } hashtable.put(formatter.formatCellValue(cellAbbr), formatter.formatCellValue(cellUnique)); } return hashtable; }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * ??ExcelTitle//from ww w .j a v a 2 s .c om * * @param excelFile * @param sheetName sheet??? * @return */ public static List<String> scanExcelTitles(File excelFile, String... sheetName) { List<String> titles = new ArrayList<>(); try { Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile)); Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } Row row = sheet.getRow(0); if (row != null) { int i = 0; while (true) { Cell cell = row.getCell(i); if (cell == null) { break; } titles.add(cell.getStringCellValue()); i++; } } } catch (Exception e) { logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error"); throw new RuntimeException(e); } return titles; }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * @param workbook /* w ww.j ava2s . c o m*/ * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; }