List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:org.powertools.engine.sources.ExcelTestSource.java
License:Open Source License
protected ExcelTestSource(String fileName, String sheetName, Scope scope, TestRunResultPublisher publisher) { super(scope, publisher); mFileName = fileName;/*www. j a v a 2s . c o m*/ Workbook workbook = createWorkbook(fileName); mSheetName = sheetName.isEmpty() ? workbook.getSheetName(0) : sheetName; Sheet sheet = workbook.getSheet(mSheetName); if (sheet == null) { throw new ExecutionException("sheet '" + mSheetName + "' does not exist"); } mRowIter = sheet.rowIterator(); }
From source file:org.talend.mdm.webapp.browserecords.server.service.UploadService.java
License:Open Source License
private List<WSPutItemWithReport> readExcelFile(FileInputStream fileInputStream) throws ServiceException, Exception { List<WSPutItemWithReport> wSPutItemWithReportList = new LinkedList<WSPutItemWithReport>(); String[] importHeader = null; Workbook workBook = null;/*from w ww. j av a 2 s.com*/ if (FILE_TYPE_EXCEL_SUFFIX.equals(fileType.toLowerCase())) { POIFSFileSystem poiFSFile = new POIFSFileSystem(fileInputStream); workBook = new HSSFWorkbook(poiFSFile); } else { workBook = new XSSFWorkbook(fileInputStream); } Sheet sheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); int rowNumber = 0; boolean dataLine; while (rowIterator.hasNext()) { dataLine = false; rowNumber++; if ((rowNumber - 1) > defaultMaxImportCount) { break; } Row row = rowIterator.next(); if (rowNumber == 1) { importHeader = readHeader(row, null); if (importHeader != null && importHeader.length > 0 && entityModel != null) { validateKeyFieldExist(importHeader); } if (headersOnFirstLine) { continue; } } multiNodeMap = new HashMap<String, List<Element>>(); if (importHeader != null) { Document document; if (isPartialUpdate) { Boolean keyContainsEmpty = false; String[] keys = new String[entityModel.getKeys().length]; for (int k = 0; k < entityModel.getKeys().length; k++) { for (String header : importHeader) { if (header.equals(entityModel.getKeys()[k]) && row.getCell(k) != null) { keys[k] = getExcelFieldValue(row.getCell(k)); if (keys[k].isEmpty()) { keyContainsEmpty = true; } } else if (header.equals(entityModel.getKeys()[k]) && row.getCell(k) == null) { keyContainsEmpty = true; } } } if (keyContainsEmpty) { if (isEmptyRecordInExcel(row, importHeader)) { rowNumber--; continue; } throw new UploadException(MESSAGES.getMessage(new Locale(language), "save_error") + " " //$NON-NLS-1$ //$NON-NLS-2$ + MESSAGES.getMessage(new Locale(language), "save_row_count", rowNumber) //$NON-NLS-1$ + MESSAGES.getMessage(new Locale(language), "error_missing_key_field")); //$NON-NLS-1$ } document = getItemForPartialUpdate(entityModel, keys, rowNumber); } else { if (isEmptyRecordInExcel(row, importHeader)) { rowNumber--; continue; } document = XmlUtil.parseDocument(org.talend.mdm.webapp.browserecords.server.util.CommonUtil .getSubXML(typeModel, null, null, language)); } Element currentElement = document.getRootElement(); for (int i = 0; i < importHeader.length; i++) { if (row.getCell(i) != null) { String fieldValue = getExcelFieldValue(row.getCell(i)); if (fieldValue != null && !fieldValue.isEmpty()) { dataLine = true; fillFieldValue(currentElement, importHeader[i], fieldValue, row, null); } else { if (isPartialUpdate) { dataLine = true; fillFieldValue(currentElement, importHeader[i], "", row, null); //$NON-NLS-1$ } } } else { if (isPartialUpdate) { dataLine = true; fillFieldValue(currentElement, importHeader[i], "", row, null); //$NON-NLS-1$ } } } if (dataLine) { wSPutItemWithReportList.add(buildWSPutItemWithReport(document)); } } } return wSPutItemWithReportList; }
From source file:org.talend.mdm.webapp.browserecords.server.service.UploadServiceTest.java
License:Open Source License
public void testGetExcelFieldValue() throws Exception { boolean partialUpdateFlag = true; String[] keys = { "EntityA/EntityAId" }; //$NON-NLS-1$ headerVisibleMap = new HashMap<String, Boolean>(); headerVisibleMap.put("EntityA/EntityAId", true); //$NON-NLS-1$ headerVisibleMap.put("EntityA/Name", true); //$NON-NLS-1$ headerVisibleMap.put("EntityA/Age", true); //$NON-NLS-1$ multipleValueSeparator = "|"; //$NON-NLS-1$ EntityModel em = getEntityModel("PartialUpdateModel.xsd", "PartialUpdateModel", "EntityA", keys); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ UploadService service = new TestUploadService(em, fileType, partialUpdateFlag, headersOnFirstLine, headerVisibleMap, inheritanceNodePathList, multipleValueSeparator, seperator, encoding, textDelimiter, language);//from ww w .j a va 2 s . c o m String[] importHeader = new String[] { "EntityA/EntityAId" }; //$NON-NLS-1$ try { service.validateKeyFieldExist(importHeader); } catch (Exception e) { fail("Key Field is not Exist"); //$NON-NLS-1$ } file = new File(this.getClass().getResource("UploadTestModel_PartialUpdate.xls").getFile()); //$NON-NLS-1$ try { FileInputStream fileInputStream = new FileInputStream(file); POIFSFileSystem poiFSFile = new POIFSFileSystem(fileInputStream); Workbook workBook = new HSSFWorkbook(poiFSFile); Sheet sheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); int rowNumber = 0; while (rowIterator.hasNext()) { rowNumber++; Row row = rowIterator.next(); if (rowNumber == 1) { continue; } else if (rowNumber == 2) { assertEquals("22", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("updatedName2", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("2222", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ } else if (rowNumber == 3) { assertEquals("33", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("updatedName3", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("3333", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ } else if (rowNumber == 4) { assertEquals("44", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("updatedName4", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("4444", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ } } } catch (Exception exception) { fail("get excel file field value failed."); //$NON-NLS-1$ } }
From source file:org.talend.mdm.webapp.browserecords.server.service.UploadServiceTest.java
License:Open Source License
public void testGetExcelForeignKeyFieldValue() throws Exception { String[] keys = { "Product/Id" }; //$NON-NLS-1$ headerVisibleMap = new HashMap<String, Boolean>(); headerVisibleMap.put("Product/Id", true); //$NON-NLS-1$ headerVisibleMap.put("Product/Name", true); //$NON-NLS-1$ headerVisibleMap.put("Product/Price", true); //$NON-NLS-1$ headerVisibleMap.put("Product/Description", true); //$NON-NLS-1$ headerVisibleMap.put("Product/Availability", true); //$NON-NLS-1$ headerVisibleMap.put("Product/Features/Sizes/Size", true); //$NON-NLS-1$ headerVisibleMap.put("Product/Features/Colors/Color", true); //$NON-NLS-1$" headerVisibleMap.put("Product/Family", true); //$NON-NLS-1$" multipleValueSeparator = "|"; //$NON-NLS-1$ EntityModel entityModel = getEntityModel("Product.xsd", "Product", "Product", keys); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ UploadService service = new TestUploadService(entityModel, fileType, isPartialUpdate, headersOnFirstLine, headerVisibleMap, inheritanceNodePathList, multipleValueSeparator, seperator, encoding, textDelimiter, language);// w w w. ja v a 2 s. co m file = new File(this.getClass().getResource("Product_FK.xls").getFile()); //$NON-NLS-1$ try { FileInputStream fileInputStream = new FileInputStream(file); POIFSFileSystem poiFSFile = new POIFSFileSystem(fileInputStream); Workbook workBook = new HSSFWorkbook(poiFSFile); Sheet sheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); int rowNumber = 0; while (rowIterator.hasNext()) { rowNumber++; Row row = rowIterator.next(); if (rowNumber == 1) { continue; } else if (rowNumber == 2) { assertEquals("Id1", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("Name1", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("111.0", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ assertEquals("Description1", service.getExcelFieldValue(row.getCell(3))); assertEquals("[FK1]", service.transferFieldValue("Product/Family", (service.getExcelFieldValue(row.getCell(7))), "|")); } else if (rowNumber == 3) { assertEquals("Id2", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("Name2", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("222.0", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ assertEquals("Description2", service.getExcelFieldValue(row.getCell(3))); assertEquals("[FK2]", service.transferFieldValue("Product/Family", (service.getExcelFieldValue(row.getCell(7))), "|")); } else if (rowNumber == 4) { assertEquals("Id3", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("Name3", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("333.0", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ assertEquals("Description3", service.getExcelFieldValue(row.getCell(3))); assertEquals("[FK3]", service.transferFieldValue("Product/Family", (service.getExcelFieldValue(row.getCell(7))), "|")); } else if (rowNumber == 5) { assertEquals("Id4", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("Name4", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("444.0", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ assertEquals("Description4", service.getExcelFieldValue(row.getCell(3))); assertEquals("[FK4]", service.transferFieldValue("Product/Family", (service.getExcelFieldValue(row.getCell(7))), "|")); } else if (rowNumber == 6) { assertEquals("Id5", service.getExcelFieldValue(row.getCell(0))); //$NON-NLS-1$ assertEquals("Name5", service.getExcelFieldValue(row.getCell(1))); //$NON-NLS-1$ assertEquals("555.0", service.getExcelFieldValue(row.getCell(2))); //$NON-NLS-1$ assertEquals("Description5", service.getExcelFieldValue(row.getCell(3))); assertEquals("[FK5]", service.transferFieldValue("Product/Family", (service.getExcelFieldValue(row.getCell(7))), "|")); } } } catch (Exception exception) { fail("get excel file field value failed."); //$NON-NLS-1$ } }
From source file:org.ujmp.poi.AbstractMatrixExcelImporter.java
License:Open Source License
public DenseObjectMatrix2D importFromSheet(final Sheet sheet) throws InvalidFormatException, IOException { final int rowCount = sheet.getLastRowNum(); int columnCount = 0; Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/* www.ja va 2 s.c o m*/ if (row.getLastCellNum() > columnCount) { columnCount = row.getLastCellNum(); } } final DefaultDenseObjectMatrix2D matrix = new DefaultDenseObjectMatrix2D(rowCount, columnCount); matrix.setLabel(sheet.getSheetName()); for (int r = 0; r < rowCount; r++) { Row row = sheet.getRow(r); if (row != null) { for (int c = 0; c < columnCount; c++) { Cell cell = row.getCell(c); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: matrix.setAsBoolean(cell.getBooleanCellValue(), r, c); break; case Cell.CELL_TYPE_ERROR: break; case Cell.CELL_TYPE_FORMULA: matrix.setAsString(cell.getCellFormula(), r, c); break; case Cell.CELL_TYPE_NUMERIC: matrix.setAsDouble(cell.getNumericCellValue(), r, c); break; case Cell.CELL_TYPE_STRING: matrix.setAsString(cell.getStringCellValue(), r, c); break; default: break; } } } } } return matrix; }
From source file:org.wso2.carbon.dataservices.sql.driver.processor.reader.ExcelDataReader.java
License:Open Source License
public void populateData() throws SQLException { Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook(); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); String sheetName = sheet.getSheetName(); ColumnInfo[] headers = this.extractColumnHeaders(sheet); DataTable dataTable = new FixedDataTable(sheetName, headers); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { Row row = rowItr.next();/*ww w.j a va 2 s . c o m*/ if (row.getRowNum() != 0) { DataRow dataRow = new DataRow(row.getRowNum() - 1); Iterator<Cell> cellItr = row.cellIterator(); int cellIndex = 0; while (cellItr.hasNext()) { Cell cell = cellItr.next(); DataCell dataCell = new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell)); dataRow.addCell(dataCell.getColumnId(), dataCell); cellIndex++; } dataTable.addRow(dataRow); } } this.getData().put(dataTable.getTableName(), dataTable); } }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
public void printStyles() { ensureOut();//from ww w . j a va 2 s. c om // First, copy the base css BufferedReader in = null; try { in = new BufferedReader(new InputStreamReader(new FileInputStream(rootPath + "/excelStyle.css"))); String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { //noinspection ThrowFromFinallyBlock throw new IllegalStateException("Reading standard css", e); } } } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style, out, false, false); seen.add(style); } } } } }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();// ww w . j av a 2 s.c o m out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); int lastNum = -1; while (rows.hasNext()) { Row row = rows.next(); int curNum = row.getRowNum(); if (curNum - lastNum > 1) { for (int i = lastNum + 2; i <= curNum; i++) { out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, i); out.format(" <td colspan=%d style=\"%s\"> </td>%n", (endColumn - firstColumn + 1), styleSimpleContents(null, false)); out.format(" </tr>%n"); } } lastNum = curNum; out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; boolean isNumeric = false; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content != null && !content.equals("") && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) { // Date if ("mmm-yy".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("MMM.yy"); content = sdfRus.format(cell.getDateCellValue()); } else if ("h:mm".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("HH:mm"); content = sdfRus.format(cell.getDateCellValue()); } else if (style.getDataFormatString() != null && style.getDataFormatString().contains("mm")) { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); content = sdfRus.format(cell.getDateCellValue()); } else { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy"); content = sdfRus.format(cell.getDateCellValue()); } } else { // Number if ("- 0".equals(content.trim())) content = " "; else content = "<nobr>" + content.replace(",", " ").replace(".", ",") + "</nobr>"; isNumeric = true; } } if (content == null || content.equals("")) content = " "; } } boolean isInRangeNotFirst = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress rangeAddress = sheet.getMergedRegion(j); if (row.getRowNum() == rangeAddress.getFirstRow() && i == rangeAddress.getFirstColumn()) { if (rangeAddress.getLastRow() - row.getRowNum() > 0) attrs += " rowspan=" + (rangeAddress.getLastRow() - row.getRowNum() + 1); if (rangeAddress.getLastColumn() - i > 0) attrs += " colspan=" + (rangeAddress.getLastColumn() - i + 1); break; } else if (row.getRowNum() >= rangeAddress.getFirstRow() && row.getRowNum() <= rangeAddress.getLastRow() && i >= rangeAddress.getFirstColumn() && i <= rangeAddress.getLastColumn()) { isInRangeNotFirst = true; break; } } if (!isInRangeNotFirst) { out.format(" <td style=\"%s\" %s>%s</td>%n", styleSimpleContents(style, isNumeric), attrs, content); } } // columns out.format(" </tr>%n"); } // rows out.format("</tbody>%n"); }
From source file:simbad.reporteUnificado.java
public void MostrarReporte() throws IOException { Workbook workbook = new XSSFWorkbook(new FileInputStream( "C:\\Users\\Miguel\\My Documents\\NetBeansprojects\\SIMBAD\\reporteUnificado.xlsx")); Sheet sheet = workbook.getSheet("Sheet1"); for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { Row row = rit.next();/* ww w .j av a 2 s .c om*/ for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { Cell cell = cit.next(); cell.setCellType(Cell.CELL_TYPE_STRING); System.out.print(cell.getStringCellValue() + "\t"); } System.out.println(); } }
From source file:sol.neptune.elisaboard.service.VPlanToHtml.java
License:Apache License
public void printStyles() { ensureOut();/* ww w.j a v a 2 s .c o m*/ // First, copy the base css BufferedReader in = null; try { in = new BufferedReader(new InputStreamReader(ToHtml.class.getResourceAsStream("excelStyle.css"))); String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { //noinspection ThrowFromFinallyBlock throw new IllegalStateException("Reading standard css", e); } } } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } } }