List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet
@Override
public XSSFSheet getSheet(String name)
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
public ArrayList<ErrorModel> startReporting_QtrVerification(int SORQ_tokenLocation, int EORQ_tokenLocation, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<String> financial_standard_workingSectionList, XSSFWorkbook workbook) { ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>(); boolean operationCheck; boolean financialCheck; int first_Occurance_Of_Financial_Comparision = 0; XSSFSheet Sheet = workbook.getSheet("Reporting_Qtr"); Iterator<Row> rowIterator = Sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from w ww . j a v a 2s. c om Iterator<Cell> cellIterator = row.iterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1) { String key = cell.getStringCellValue(); if ((key.equalsIgnoreCase("Financial Comparison")) && (first_Occurance_Of_Financial_Comparision == 0)) { first_Occurance_Of_Financial_Comparision = row.getRowNum() + 1; } } } } operationCheck = operation_Standard_Workingsection_Verification(SORQ_tokenLocation, first_Occurance_Of_Financial_Comparision, Sheet, opeartion_standard_workingSectionList, errorModelList, workbook); //financialCheck=financial_Standard_Workingsection_Verification(first_Occurance_Of_Financial_Comparision,EORQ_tokenLocation,Sheet,financial_standard_workingSectionList,errorModelList,workbook); //if return false that means no error. if (!operationCheck) { reporting_Qtr_operation_Standard_C_To_I_Column_Verification((SORQ_tokenLocation + 3), first_Occurance_Of_Financial_Comparision, errorModelList, Sheet, workbook); } /*if(!financialCheck) { }*/ return errorModelList; }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
public ArrayList<ErrorModel> startReporting_YearVerification(int SORY_tokenLocation, int EORY_tokenLocation, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<String> financial_standard_workingSectionList, XSSFWorkbook workbook) { ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>(); boolean operationCheck; boolean financialCheck; int first_Occurance_Of_Financial_Comparision = 0; XSSFSheet Sheet = workbook.getSheet("Reporting_Year"); Iterator<Row> rowIterator = Sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/*ww w .j a v a2s. c o m*/ Iterator<Cell> cellIterator = row.iterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1) { String key = cell.getStringCellValue(); if ((key.equalsIgnoreCase("Financial Comparison")) && (first_Occurance_Of_Financial_Comparision == 0)) { first_Occurance_Of_Financial_Comparision = row.getRowNum() + 1; } } } } operationCheck = operation_Standard_Workingsection_Verification(SORY_tokenLocation, first_Occurance_Of_Financial_Comparision, Sheet, opeartion_standard_workingSectionList, errorModelList, workbook); //financialCheck=financial_Standard_Workingsection_Verification(first_Occurance_Of_Financial_Comparision,EORY_tokenLocation,Sheet,financial_standard_workingSectionList,errorModelList,workbook); //if return false that means no error. if (!operationCheck) { reporting_Year_operation_Standard_C_To_I_Column_Verification((SORY_tokenLocation + 3), first_Occurance_Of_Financial_Comparision, errorModelList, Sheet, workbook); } /*if(!financialCheck) { }*/ return errorModelList; }
From source file:io.unravellingtechnologies.excalibur.Sheet.java
License:Open Source License
/** * Default constructor. Loads the sheet into the object, with all the data in it. * /* w w w.j a v a 2s . c o m*/ * @param excelBook Excel book object where the sheet will be read from. * @param sheetName Name of the sheet to read. */ Sheet(final XSSFWorkbook excelBook, final String sheetName) { final XSSFSheet sheet = excelBook.getSheet(sheetName); /* * If it has rows proceed. Note: to be considered valid at least one row must exist (header). */ if (sheet.getPhysicalNumberOfRows() > 1) { sheetHeader = new HashMap<Integer, String>(); rows = new ArrayList<Row>(); setSheetHeader(sheet); loadRows(sheet); } }
From source file:jp.ac.tohoku.ecei.sb.metabolome.lims.progenesis.ProgenesisLoader.java
License:Open Source License
public static XSSFWorkbook createTemplateXlsxFile(InputStream is) throws IOException, InvalidSampleInfoFormatException { XSSFWorkbook xlsx = new XSSFWorkbook( ProgenesisLoader.class.getResourceAsStream("metabolome-dataset-template.xlsx")); Sheet injectionSheet = xlsx.getSheet("Injection"); int numberOfInjections = 0; try (Reader reader = new InputStreamReader(is)) { CSVReader csvReader = new CSVReader(reader); String[] header1 = csvReader.readNext(); String[] header2 = csvReader.readNext(); String[] header3 = csvReader.readNext(); int dataOffset1 = Arrays.asList(header1).indexOf("Raw abundance"); if (dataOffset1 < 0) throw new InvalidSampleInfoFormatException( "\"Raw abundance\" is not found in the first line. May be invalid progenesis file"); int dataOffset2 = Arrays.asList(header2).indexOf("Tags"); if (dataOffset2 < 0 || dataOffset1 >= dataOffset2) throw new InvalidSampleInfoFormatException( "\"Tags\" is not found in the second line. May be invalid progenesis file"); List<String> filenameList = Arrays.asList(header3).subList(dataOffset1, dataOffset2); numberOfInjections = filenameList.size(); for (int i = 0; i < numberOfInjections; i++) { Row row = injectionSheet.createRow(i + 1); row.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(i + 1); row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(filenameList.get(i)); }/*from w w w.j a v a 2 s .c o m*/ injectionSheet.autoSizeColumn(0); injectionSheet.autoSizeColumn(3); } { // Create plate template Sheet plateSheet = xlsx.getSheet("Plate"); for (int i = 0; i < Math.ceil(numberOfInjections / 117.); i++) { Row row = plateSheet.createRow(1 + i); row.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(1 + i); row.createCell(1, Cell.CELL_TYPE_NUMERIC).setCellValue("Sample plate " + (i + 1)); row.createCell(2, Cell.CELL_TYPE_STRING).setCellValue(DATE_FORMAT.format(new Date())); } for (int i = 0; i < 3; i++) plateSheet.autoSizeColumn(i); } { // Create sample template Sheet sampleSheet = xlsx.getSheet("Sample"); Row blankRow = sampleSheet.createRow(1); blankRow.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(1); blankRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("C7772A67-3345-4444-A3AF-14D8A0FB77F4"); blankRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("BLANK"); blankRow.createCell(3, Cell.CELL_TYPE_STRING).setCellValue("blank"); for (int i = 0; i < 2; i++) { Row qcRow = sampleSheet.createRow(2 + i); qcRow.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(2 + i); qcRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("QC"); qcRow.createCell(3, Cell.CELL_TYPE_STRING).setCellValue("Quality Control " + (i + 1)); } for (int i = 0; i < 4; i++) sampleSheet.autoSizeColumn(i); } return xlsx; }
From source file:jp.ac.tohoku.ecei.sb.metabolome.lims.progenesis.ProgenesisLoader.java
License:Open Source License
public static DataManager loadFromProgenesisAndSampleInfo(InputStream progenesisCSV, InputStream sampleInfoXlsx) throws IOException, SQLException, InvalidSampleInfoFormatException { XSSFWorkbook sampleInfo = new XSSFWorkbook(sampleInfoXlsx); DataManager dataManager = new DataManager(); OperationHistoryImpl operationHistory = new OperationHistoryImpl(ProgenesisLoader.class, "loadFromProgenesisAndSampleInfo"); dataManager.getOperationHistories().create(operationHistory); // Start loading int processingLine = 1; StudyImpl study;/*from w w w. j a v a 2s. c om*/ try { // Load study Sheet studySheet = sampleInfo.getSheet("Study"); ExcelSheetReader sheetReader = new ExcelSheetReader(studySheet); List<TableCell[]> data = sheetReader.readAll(); if (!data.get(6)[0].toString().equals("Study name")) throw new InvalidSampleInfoFormatException("Column A, Row 7 should be \"Study name\""); if (!data.get(7)[0].toString().equals("Comment")) throw new InvalidSampleInfoFormatException("Column A, Row 8 should be \"Comment\""); study = new StudyImpl(data.get(6)[1].toString()); String comment = data.get(7)[1].toString(); if (!comment.isEmpty()) { study.setAttribute("COMMENT", comment); } dataManager.getStudies().create(study); } catch (ArrayIndexOutOfBoundsException | NullPointerException e) { throw new InvalidSampleInfoFormatException("Cannot load study sheet", e); } try { // Load plate Sheet plateSheet = sampleInfo.getSheet("Plate"); ExcelSheetReader sheetReader = new ExcelSheetReader(plateSheet); sheetReader.setUseHeader(true); if (!Arrays.equals(sheetReader.getHeader(), new String[] { "Plate ID", "Name", "Run date", "Comment" })) throw new InvalidSampleInfoFormatException("Invalid header of plate sheet"); processingLine = 2; for (TableRecord record : sheetReader) { PlateImpl plate = new PlateImpl(); plate.setStudy(study); plate.setName(record.get("Name").toString()); if (record.get("Run date").toString().length() > 0) plate.setDateTime(CSVDataLoader.DATE_FORMAT.parse(record.get("Run date").toString())); plate.setId((int) record.get("Plate ID").toNumeric()); String comment = record.get("Comment").toString(); if (!comment.isEmpty()) { plate.setAttribute("COMMENT", comment); } dataManager.getPlates().create(plate); processingLine += 1; } } catch (ArrayIndexOutOfBoundsException | NullPointerException | ParseException | NumberFormatException e) { throw new InvalidSampleInfoFormatException( String.format("Cannot load plate sheet line:%d", processingLine), e); } try { processingLine = 2; // Load plate Sheet sampleSheet = sampleInfo.getSheet("Sample"); ExcelSheetReader sheetReader = new ExcelSheetReader(sampleSheet); sheetReader.setUseHeader(true); if (!Arrays.equals(sheetReader.getHeader(), new String[] { "Sample ID", "UUID", "Sample Type", "Name", "Comment" })) throw new InvalidSampleInfoFormatException("Invalid header of sample sheet"); for (TableRecord record : sheetReader) { SampleImpl sample = new SampleImpl(); sample.setName(record.get("Name").toString()); sample.setId((int) record.get("Sample ID").toNumeric()); sample.setSampleType(Sample.SampleType.valueOf(record.get("Sample Type").toString())); String uuidString = record.get("UUID").toString(); UUID uuid; if (uuidString.isEmpty()) uuid = UUID.randomUUID(); else uuid = UUID.fromString(uuidString); sample.setUuid(uuid); String comment = record.get("Comment").toString(); if (!comment.isEmpty()) { sample.setAttribute("COMMENT", comment); } dataManager.getSamples().create(sample); processingLine += 1; } } catch (ArrayIndexOutOfBoundsException | NullPointerException | IllegalArgumentException e) { throw new InvalidSampleInfoFormatException( String.format("Cannot load sample sheet line: %d", processingLine), e); } try { // Load injection Sheet injectionSheet = sampleInfo.getSheet("Injection"); ExcelSheetReader sheetReader = new ExcelSheetReader(injectionSheet); sheetReader.setUseHeader(true); if (!Arrays.equals(new String[] { "Run Index", "Plate ID", "Sample ID", "FileName", "Name", "QC Index", "Ignored", "Dilution Factor", "Comment" }, sheetReader.getHeader())) throw new InvalidSampleInfoFormatException("Invalid header of injection sheet"); processingLine = 2; for (TableRecord row : sheetReader) { log.info("Injection {} {}", processingLine, row.getContent()); InjectionImpl injection = new InjectionImpl(); int runIndex = (int) Double.parseDouble(row.get("Run Index").toString()); injection.setId(runIndex); injection.setRunIndex(runIndex); PlateImpl plate = dataManager.getPlates().queryForId((int) row.get("Plate ID").toNumeric()); if (plate == null) throw new InvalidSampleInfoFormatException( String.format("Invalid Plate ID; Injection sheet; line: %d", processingLine)); injection.setPlate(plate); try { SampleImpl sample = dataManager.getSamples().queryForId((int) row.get("Sample ID").toNumeric()); if (sample == null) throw new InvalidSampleInfoFormatException( String.format("Invalid Sample ID; Injection sheet; line: %s", processingLine)); injection.setSample(sample); if (sample.getSampleType() == Sample.SampleType.QC) injection.setQCIndex((int) row.get("QC Index").toNumeric()); } catch (NumberFormatException e) { SampleImpl sample = new SampleImpl(Sample.SampleType.NORMAL, UUID.randomUUID(), "Normal " + processingLine); dataManager.getSamples().create(sample); injection.setSample(sample); } injection.setName(row.get("Name").toString()); injection.setFileName(row.get("FileName").toString()); injection.setIgnored(row.get("Ignored").toBoolean()); String dilutionFactor = row.get("Dilution Factor").toString(); try { injection.setAttribute("DILUTION_FACTOR", Double.parseDouble(dilutionFactor)); } catch (NumberFormatException e) { if (injection.getSample().getSampleType() == Sample.SampleType.QC) { injection.setAttribute("DILUTION_FACTOR", 1.0); } } String comment = row.get("Comment").toString(); if (!comment.isEmpty()) { injection.setAttribute("COMMENT", comment); } dataManager.getInjections().create(injection); processingLine += 1; } } catch (ArrayIndexOutOfBoundsException | NullPointerException | NumberFormatException e) { throw new InvalidSampleInfoFormatException( String.format("Cannot load injection sheet line: %d", processingLine), e); } try { // Load Progenesis File processingLine = 1; TableReader reader = new TableCSVReader(new InputStreamReader(progenesisCSV)); List<TableCell[]> data = reader.readAll(); int dataOffset1 = Arrays.asList(TableCellHelper.convertFromTableCell(data.get(0))) .indexOf("Raw abundance"); if (dataOffset1 < 0) throw new InvalidSampleInfoFormatException( "\"Raw abundance\" is not found in the first line. May be invalid progenesis file"); int dataOffset2 = Arrays.asList(TableCellHelper.convertFromTableCell(data.get(1))).indexOf("Tags"); if (dataOffset2 < 0 || dataOffset1 >= dataOffset2) throw new InvalidSampleInfoFormatException( "\"Tags\" is not found in the second line. May be invalid progenesis file"); List<String> filenameList = Stream.of(data.get(2)).map(TableCell::toString).collect(Collectors.toList()) .subList(dataOffset1, dataOffset2); int numberOfFilename = filenameList.size(); List<String> filenamesInInjectionTable = dataManager.getInjections().queryForAll().stream() .map(InjectionImpl::getFileName).collect(Collectors.toList()); List<String> missingFilenameInProgenesis = filenamesInInjectionTable.stream() .filter(s -> !filenameList.contains(s)).collect(Collectors.toList()); List<String> missingFilenameInInjectionTable = filenameList.stream() .filter(s -> !filenamesInInjectionTable.contains(s)).collect(Collectors.toList()); if (missingFilenameInInjectionTable.size() > 0) { throw new InvalidSampleInfoFormatException( String.format("%d injections are missing in the injection table. They are %s", missingFilenameInInjectionTable.size(), Arrays.toString(missingFilenameInInjectionTable.toArray()))); } if (missingFilenameInProgenesis.size() > 0) { throw new InvalidSampleInfoFormatException( String.format("%d injections are missing in the progenesis CSV. They are %s", missingFilenameInProgenesis.size(), Arrays.toString(missingFilenameInProgenesis.toArray()))); } final int PROGENESIS_ROW_OFFSET = 3; IntensityMatrixImpl marix = new IntensityMatrixImpl(data.size() - PROGENESIS_ROW_OFFSET, filenameList.size()); if (!Arrays.equals( new String[] { "Compound", "Neutral mass (Da)", "m/z", "Charge", "Retention time (min)" }, Stream.of(data.get(2)).map(TableCell::toString).limit(5).toArray())) { throw new InvalidSampleInfoFormatException("Invalid Header of Compound Information"); } List<Compound> compounds = new ArrayList<>(); for (int i = PROGENESIS_ROW_OFFSET; i < data.size(); i++) { TableCell[] row = data.get(i); CompoundImpl oneCompound = new CompoundImpl(row[2].toNumeric(), row[4].toNumeric(), row[1].toString().isEmpty() ? null : row[1].toNumeric(), (int) row[3].toNumeric()); compounds.add(oneCompound); dataManager.getCompounds().create(oneCompound); } List<Injection> injections = filenameList.stream().map(f -> { try { List<InjectionImpl> oneInjection = dataManager.getInjections().queryForEq("FileName", f); if (oneInjection.size() == 1) return oneInjection.get(0); else throw new RuntimeException(String.format("Cannot find injection for %s", f)); } catch (SQLException e) { throw new RuntimeException(e); } }).collect(Collectors.toList()); Injection[] progenesisCSVInjection = injections.stream().toArray(Injection[]::new); injections.sort((x, y) -> Integer.compare(x.getRunIndex(), y.getRunIndex())); marix.setRowKeys(compounds); marix.setColumnKeys(injections); processingLine = PROGENESIS_ROW_OFFSET; for (int i = PROGENESIS_ROW_OFFSET; i < data.size(); i++) { TableCell[] row = data.get(i); for (int j = 0; j < numberOfFilename; j++) { marix.put(compounds.get(i - PROGENESIS_ROW_OFFSET), progenesisCSVInjection[j], row[j + dataOffset1].toNumeric()); } } dataManager.setIntensityMatrix(marix); } catch (RuntimeException e) { throw new InvalidSampleInfoFormatException( String.format("Cannot load intensity matrix line: %d", processingLine), e); } return dataManager; }
From source file:migrationextraction.MainWindowUI.java
private void processBasicInfoSheet(XSSFWorkbook wb) throws InvalidFormatException { try {//from w w w. j av a2s. c o m XSSFSheet sheet = wb.getSheet("1. Supplier Basic Info"); CTWorksheet ctws = sheet.getCTWorksheet(); /*String xml10pattern = "[^" + "\u0009\r\n" + "\u0020-\uD7FF" + "\uE000-\uFFFD" + "\ud800\udc00-\udbff\udfff" + "]"; String xml = ctws.toString().replaceAll(xml10pattern, "");*/ //Get embedded objects positions. SAXBuilder saxB = new SAXBuilder(); org.jdom2.Document doc = saxB.build(new StringReader(ctws.toString())); List<OleObject> oleObjects = new ArrayList<>(); List<Element> elements = doc.getRootElement().getChildren(); elements.stream().forEach((element) -> { if (element.getName().equals("oleObjects")) { element.getChildren(); getXMLUntilOle(element.getContent(), 0); //Get content System.out.println(""); //for(Content content:element.getContent()) } }); } catch (JDOMException | IOException ex) { Logger.getLogger(MainWindowUI.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
public static void loadData(String directoryName, String fileName) { try {/*from w ww . j ava 2 s . c o m*/ // Delete current dir contents FileUtils.delete(new File("./substances/")); FileUtils.delete(new File("./patients/")); FileUtils.delete(new File("./environments/")); FileUtils.delete(new File("./nutrition/")); FileUtils.delete(new File("./config/")); // Ok, let's make them again FileUtils.createDirectory("./substances/"); FileUtils.createDirectory("./patients/"); FileUtils.createDirectory("./environments/"); FileUtils.createDirectory("./nutrition/"); FileUtils.createDirectory("./config/"); } catch (Exception ex) { Log.error("Unable to clean directories"); return; } try { File xls = new File(directoryName + "/" + fileName); if (!xls.exists()) { Log.error("Could not find xls file " + directoryName + "/" + fileName); return; } FileInputStream xlFile = new FileInputStream(directoryName + "/" + fileName); XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); evaluator = xlWBook.getCreationHelper().createFormulaEvaluator(); List<SEPatient> patients = readPatients(xlWBook.getSheet("Patients")); for (SEPatient p : patients) { Log.info("Writing patient : " + p.getName()); CDMSerializer.writeFile("./patients/" + p.getName() + ".xml", p.unload()); } Map<String, SESubstance> substances = readSubstances(xlWBook.getSheet("Substances")); for (SESubstance s : substances.values()) { Log.info("Writing substance : " + s.getName()); CDMSerializer.writeFile("./substances/" + s.getName() + ".xml", s.unload()); } List<SESubstanceCompound> compounds = readCompounds(xlWBook.getSheet("Compounds"), substances); for (SESubstanceCompound c : compounds) { Log.info("Writing compound : " + c.getName()); CDMSerializer.writeFile("./substances/" + c.getName() + ".xml", c.unload()); } Map<String, SEEnvironmentalConditions> environments = readEnvironments(xlWBook.getSheet("Environment"), substances); for (String name : environments.keySet()) { Log.info("Writing environment : " + name); environments.get(name).trim();//Removes zero amount ambient substances CDMSerializer.writeFile("./environments/" + name + ".xml", environments.get(name).unload()); } Map<String, SENutrition> meals = readNutrition(xlWBook.getSheet("Nutrition")); for (String name : meals.keySet()) { Log.info("Writing nutrition : " + name); CDMSerializer.writeFile("./nutrition/" + name + ".xml", meals.get(name).unload()); } Map<String, PhysiologyEngineStabilization> stabilization = readStabilization( xlWBook.getSheet("Stabilization")); for (String name : stabilization.keySet()) { Log.info("Writing stabilization : " + name); CDMSerializer.writeFile("./config/" + name + ".xml", stabilization.get(name).unload()); } xlWBook.close(); } catch (Exception ex) { Log.error("Error reading XSSF : " + directoryName + "/" + fileName, ex); return; } }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporterImporterTest.java
License:Open Source License
@Test public void test() throws Exception { AppUser appUser = authHelper.getCurrentUser(); Service service = ajouterServiceToAppUser(appUser); List<ArticleStock> listeArticleStock = genererListeArticleStock(service); File exportFile = File.createTempFile("stock_test_export-", ".xlsx"); logger.info("Export pour inventaire : " + exportFile); // Export/*from www.ja v a2s . c om*/ StockSpreadsheetExporter.exportToXls(service, listeArticleStock, catalogueService, new FileOutputStream(exportFile)); XSSFWorkbook workbookExport = new XSSFWorkbook(exportFile); XSSFSheet worksheet = workbookExport.getSheet("Inventaire"); Assert.assertNotNull(worksheet); // Verif for (int i = 0; i < worksheet.getLastRowNum(); i++) { XSSFRow row = worksheet.getRow(i); int col = 0; if (i == 0) { Assert.assertEquals("Photo", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Rfrence", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Libell", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Stock\n Appock", row.getCell(col++).getStringCellValue()); Assert.assertEquals("Stock\n rel", row.getCell(col++).getStringCellValue()); } else { col++; Assert.assertEquals("REF_" + (i - 1), row.getCell(col++).getStringCellValue()); Assert.assertEquals("ARTICLE CATALOGUE " + (i - 1), row.getCell(col++).getStringCellValue()); Assert.assertEquals(i, (long) row.getCell(col++).getNumericCellValue()); } } // Import : toutes les quantits ont t augments de 1 File importFile1 = File.createTempFile("stock_test_import1-", ".xlsx"); logger.info("Import de l'inventaire : " + importFile1); OutputStream outputStream1 = new FileOutputStream(importFile1); XSSFWorkbook workbookImport = new XSSFWorkbook(exportFile); XSSFSheet worksheetImport = workbookImport.getSheet("Inventaire"); for (int i = 1; i < worksheetImport.getLastRowNum() + 1; i++) { XSSFRow row = worksheetImport.getRow(i); if (i == 5) { // Cellule en erreur row.getCell(4).setCellValue(" 05 "); } else { row.getCell(4).setCellValue(row.getCell(3).getNumericCellValue() + 1); } } workbookImport.write(outputStream1); outputStream1.flush(); outputStream1.close(); List<String> warnings = StockSpreadsheetImporter.importFromXls(service, stockService, new FileInputStream(importFile1)); // Verif Stock stock = stockService.findOne(service.getStock().getId()); Assert.assertEquals(10, stock.getListeArticleStock().size()); Assert.assertEquals(1, warnings.size()); logger.info("Warning message : " + warnings.get(0)); for (int i = 0; i < stock.getListeArticleStock().size(); i++) { ArticleStock articleStock = stock.getListeArticleStock().get(i); int oldQunatite = i + 1; if (i == 4) { // Quantit inchange Assert.assertEquals(oldQunatite, (int) articleStock.getQuantiteStock()); } else { Assert.assertEquals(oldQunatite + 1, (int) articleStock.getQuantiteStock()); } } }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetImporter.java
License:Open Source License
public static List<String> importFromXls(Service service, StockService stockService, InputStream in) throws Exception { List<String> warnings = new ArrayList<>(); try {/*from w w w .j a v a 2s.com*/ XSSFWorkbook workbook = new XSSFWorkbook(in); XSSFSheet worksheet = workbook.getSheet(NOM_ONGLET_CLASSEUR); if (worksheet == null) { throw new Exception("L'onglet '" + NOM_ONGLET_CLASSEUR + "' du classeur est introuvable"); } for (int i = 1; i < worksheet.getLastRowNum() + 1; i++) { try { traiterLigne(i, worksheet, service, stockService); } catch (ImportExcelException e) { warnings.add(e.getMessage()); } } } finally { in.close(); } return warnings; }
From source file:net.sf.mzmine.util.io.XSSFExcelWriterReader.java
License:Open Source License
/** * Returns an existing sheet. If there is no sheet with this name a new will be created * //w w w . j a v a 2 s . com * @param name name of sheet * @return an existing or new sheet * @see */ public XSSFSheet getSheet(XSSFWorkbook wb, String name) { // try to get row XSSFSheet sheet = wb.getSheet(name); // if not exist: create row if (sheet == null) sheet = wb.createSheet(name); // get cell return sheet; }