Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet#iterator()
The following examples show how to use
org.apache.poi.xssf.usermodel.XSSFSheet#iterator() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: TestReadExcelDemo.java From poi with Apache License 2.0 | 6 votes |
|
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream(new File(
TestUtil.DOC_PATH + File.separator
+ TestWriteExcelDemo.EXCEL_NAME
+ Globals.SUFFIX_XLSX));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// Check the cell type and format accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Example 2
| Source File: AccessConfigImportServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0 | 6 votes |
|
private void importObjectAccess(XSSFSheet sheet) {
App app = appService.getApp(sheet.getSheetName());
if (app == null) {
return;
}
Iterator<Row> rowIter = sheet.iterator();
Map<Integer, AccessConfig> accessMap = null;
while (rowIter.hasNext()) {
if (accessMap == null) {
accessMap = getAccessConfig(rowIter.next(), app);
continue;
}
createObjectRoles(accessMap, rowIter.next());
}
}
Example 3
| Source File: AccessConfigImportServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0 | 6 votes |
|
private void importMenuAccess(XSSFSheet sheet) {
App app = appService.getApp(sheet.getSheetName().split("-")[0]);
if (app == null) {
return;
}
Iterator<Row> rowIter = sheet.iterator();
Map<Integer, AccessConfig> accessMap = null;
while (rowIter.hasNext()) {
if (accessMap == null) {
accessMap = getAccessConfig(rowIter.next(), app);
continue;
}
createMenuRoles(accessMap, rowIter.next());
}
}
Example 4
| Source File: TableXLS.java From Rel with Apache License 2.0 | 6 votes |
|
private TupleIterator iteratorRawXLSX() throws IOException {
FileInputStream reader = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(reader);
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
return new SpreadsheetTupleIterator(sheet.iterator()) {
@Override
public void close() {
try {
workbook.close();
} catch (IOException e1) {
}
try {
reader.close();
} catch (IOException e) {
}
}
};
}
Example 5
| Source File: ExcelAdjacencyMatrixExtractor.java From wandora with GNU General Public License v3.0 | 6 votes |
|
public void processSheet(XSSFSheet sheet, TopicMap tm) {
Iterator<Row> rowIterator = sheet.iterator();
boolean isFirst = true;
columnLabels = new HashMap();
rowLabels = new HashMap();
while(rowIterator.hasNext() && !forceStop()) {
Row row = rowIterator.next();
if(isFirst) {
processAsLabels(row, tm);
isFirst = false;
}
else {
processRow(row, tm);
}
}
}
Example 6
| Source File: TestExcelFormulaDemo.java From poi with Apache License 2.0 | 5 votes |
|
public static void readSheetWithFormula() {
try {
FileInputStream file = new FileInputStream(new File(
TestUtil.DOC_PATH + File.separator + EXCEL_NAME
+ Globals.SUFFIX_XLSX));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
FormulaEvaluator evaluator = workbook.getCreationHelper()
.createFormulaEvaluator();
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// If it is formula cell, it will be evaluated otherwise no
// change will happen
switch (evaluator.evaluateInCell(cell).getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_FORMULA:
// Not again
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Example 7
| Source File: ReadXlsxFileWithHeader.java From WhiteRabbit with Apache License 2.0 | 5 votes |
|
public RowIterator() {
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
XSSFSheet sheet = workbook.getSheetAt(0);
iterator = sheet.iterator();
fieldName2ColumnIndex = new HashMap<String, Integer>();
for (Cell header : iterator.next())
fieldName2ColumnIndex.put(header.toString(), fieldName2ColumnIndex.size());
} catch (IOException e) {
e.printStackTrace();
}
}
Example 8
| Source File: ExcelTopicTreeExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
public void processSheet(XSSFSheet sheet, TopicMap tm) {
Iterator<Row> rowIterator = sheet.iterator();
hierarchy = new Topic[1000];
while(rowIterator.hasNext() && !forceStop()) {
Row row = rowIterator.next();
processRow(row, tm);
}
}
Example 9
| Source File: ExcelTopicOccurrenceExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
public void processSheet(XSSFSheet sheet, TopicMap tm) {
Iterator<Row> rowIterator = sheet.iterator();
boolean isFirst = true;
occurrenceTypes = new HashMap();
while(rowIterator.hasNext() && !forceStop()) {
Row row = rowIterator.next();
if(isFirst && FIRST_ROW_CONTAINS_OCCURRENCE_TYPES) {
processRowAsOccurrenceTypes(row, tm);
isFirst = false;
}
else {
processRow(row, tm);
}
}
}
Example 10
| Source File: ExcelAdjacencyListExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
public void processSheet(XSSFSheet sheet, TopicMap tm) {
Iterator<Row> rowIterator = sheet.iterator();
boolean isFirst = true;
rolesPerColumn = new HashMap();
while(rowIterator.hasNext() && !forceStop()) {
Row row = rowIterator.next();
if(isFirst && FIRST_ROW_CONTAINS_ROLES) {
processRowAsRoles(row, tm);
isFirst = false;
}
else {
processRow(row, tm);
}
}
}
Example 11
| Source File: ExcelTopicExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
public void processSheet(XSSFSheet sheet, TopicMap tm) {
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext() && !forceStop()) {
Row row = rowIterator.next();
processRow(row, tm);
}
}
Example 12
| Source File: ExcelTopicNameExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
public void processSheet(XSSFSheet sheet, TopicMap tm) {
Iterator<Row> rowIterator = sheet.iterator();
boolean isFirst = true;
languagesPerColumn = new HashMap();
while(rowIterator.hasNext() && !forceStop()) {
Row row = rowIterator.next();
if(isFirst && FIRST_ROW_CONTAINS_LANGUAGES) {
processRowAsLanguages(row, tm);
isFirst = false;
}
else {
processRow(row, tm);
}
}
}
Example 13
| Source File: ReadExcelFile.java From journaldev with MIT License | 4 votes |
|
public static void main(String[] args) throws IOException {
Map<String,String> countryMap = new HashMap<String, String>();
List<String> countryShortCodes = new ArrayList<String>();
List<String> countryNames = new ArrayList<String>();
FileInputStream file = new FileInputStream(new File(
"ISOCountryCodes.xlsx"));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
String key="";
String value="";
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_STRING:
if(key.equals("")){
key = cell.getStringCellValue().trim();
}else{
value = cell.getStringCellValue().trim();
}
break;
}
}
countryMap.put(value, key);
countryNames.add(value);
countryShortCodes.add(key);
//System.out.println("");
}
file.close();
//System.out.println(countryMap);
//System.out.println(countryNames);
//System.out.println(countryShortCodes);
Collections.sort(countryNames);
for(String name : countryNames){
System.out.print(name.trim()+":"+countryMap.get(name)+"|");
}
}
Example 14
| Source File: OptionsFileConverterUtil.java From sakai with Educational Community License v2.0 | 4 votes |
|
public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
List<String> optionsList = new ArrayList<String>();
try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
Iterator<Row> iterator;
switch(FileMagic.valueOf(bufferedInputStream)) {
case OOXML:
log.debug("Input file detected as OOXML.");
XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
XSSFSheet datatypeSheet = workbook.getSheetAt(0);
iterator = datatypeSheet.iterator();
break;
case OLE2:
log.debug("Input file detected as OLE2.");
HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
iterator = legacyDatatypeSheet.iterator();
break;
default:
log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
while(scanner.hasNext()){
String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
if(StringUtils.isNotBlank(inputString)){
optionsList.add(inputString);
}
}
} catch(Exception ex){
throw new IOException("Error processing the file as text type.", ex);
}
return optionsList;
}
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
if(cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
switch(currentCell.getCellType()) {
case STRING:
if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
}
break;
case NUMERIC:
optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
break;
case BOOLEAN:
optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
break;
case FORMULA:
case BLANK:
case _NONE:
case ERROR:
default:
break;
}
}
}
} catch (Exception e) {
throw new IOException("Error converting the file to options list.");
}
return optionsList;
}
Example 15
| Source File: OptionsFileConverterUtil.java From sakai with Educational Community License v2.0 | 4 votes |
|
public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
List<String> optionsList = new ArrayList<String>();
try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
Iterator<Row> iterator;
switch(FileMagic.valueOf(bufferedInputStream)) {
case OOXML:
log.debug("Input file detected as OOXML.");
XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
XSSFSheet datatypeSheet = workbook.getSheetAt(0);
iterator = datatypeSheet.iterator();
break;
case OLE2:
log.debug("Input file detected as OLE2.");
HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
iterator = legacyDatatypeSheet.iterator();
break;
default:
log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
while(scanner.hasNext()){
String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
if(StringUtils.isNotBlank(inputString)){
optionsList.add(inputString);
}
}
} catch(Exception ex){
throw new IOException("Error processing the file as text type.", ex);
}
return optionsList;
}
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
if(cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
switch(currentCell.getCellType()) {
case STRING:
if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
}
break;
case NUMERIC:
optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
break;
case BOOLEAN:
optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
break;
case FORMULA:
case BLANK:
case _NONE:
case ERROR:
default:
break;
}
}
}
} catch (Exception e) {
throw new IOException("Error converting the file to options list.");
}
return optionsList;
}
