List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:br.com.tecsinapse.dataio.importer.parser.SpreadsheetParser.java
License:LGPL
private List<List<String>> parseCurrentSheetAsStringList() { workbook = getWorkbook();//from ww w.ja v a2 s . c o m Sheet sheet = workbook.getSheetAt(getSheetNumber()); final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Iterator<Row> rowIterator = sheet.iterator(); List<List<String>> list = new ArrayList<>(); int i = 0; while (rowIterator.hasNext()) { List<String> rowList = new ArrayList<>(); Row row = rowIterator.next(); i++; if (i <= headersRows) { continue; } Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = cells.next(); rowList.add(ImporterUtils.getValueOrEmpty(evaluator, cell, exporterFormatter)); } list.add(rowList); } return list; }
From source file:br.com.tecsinapse.exporter.importer.parser.SpreadsheetParser.java
License:LGPL
private List<List<String>> parseCurrentSheetAsStringList() throws IllegalAccessException, InstantiationException, InvocationTargetException, IOException, InvalidFormatException, NoSuchMethodException { workbook = getWorkbook();/*w w w . j a va2 s. c o m*/ Sheet sheet = workbook.getSheetAt(getSheetNumber()); final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Iterator<Row> rowIterator = sheet.iterator(); List<List<String>> list = new ArrayList<>(); int i = 0; while (rowIterator.hasNext()) { List<String> rowList = new ArrayList<>(); Row row = rowIterator.next(); i++; if (i <= headersRows) { continue; } Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = cells.next(); rowList.add(ImporterUtils.getValueOrEmpty(evaluator, cell, exporterFormatter)); } list.add(rowList); } return list; }
From source file:br.sp.telesul.service.ExportServiceImpl.java
@Override public List<Funcionario> readExcelDocument() { try {//from w ww . jav a 2 s . c o m List<Funcionario> funcionariosExcel = new ArrayList<>(); ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); // FileInputStream fl = new FileInputStream(new File("C:\\Matriz1.xlsx")); Workbook wb = new XSSFWorkbook(classLoader.getResourceAsStream("Matriz1.xlsx")); Sheet firstSheet = wb.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); int row = nextRow.getRowNum(); // System.out.println("Row start" + row); Iterator<Cell> cellIterator = nextRow.cellIterator(); Funcionario f = new Funcionario(); Formacao fm = new Formacao(); Idioma id = new Idioma(); int column = 0; while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); column = columnIndex; // System.out.println("Valor" + getCellValue(nextCell)); // System.out.println("Index: " + columnIndex); if (row > 0) { switch (columnIndex) { case 1: f.setArea((String) getCellValue(nextCell)); break; case 2: Date dt = new Date(); if (!getCellValue(nextCell).toString().isEmpty()) { try { dt = DateUtil.getJavaDate((Double) getCellValue(nextCell)); } catch (ClassCastException cce) { SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy"); dt = formatter.parse((String) getCellValue((nextCell))); } ; } f.setDtAdmissao(dt); break; case 3: f.setCargo((String) getCellValue(nextCell)); break; case 4: f.setNome((String) getCellValue(nextCell)); break; case 5: f.setGestor((String) getCellValue(nextCell)); break; case 9: fm.setNivel((String) getCellValue(nextCell)); break; case 10: fm.setCurso((String) getCellValue(nextCell)); break; case 11: fm.setInstituicaoo((String) getCellValue(nextCell)); break; case 12: String typeEnum = (String) getCellValue(nextCell); if (!typeEnum.isEmpty()) { id.setNome(Language.valueOf(typeEnum.trim())); } break; case 13: String typeEnumNivel = (String) getCellValue(nextCell); if (!typeEnumNivel.isEmpty()) { id.setNivel(Nivel.valueOf(typeEnumNivel.trim())); } break; } } } List<Formacao> listFm = new ArrayList<>(); listFm.add(fm); f.setFormacoes(listFm); List<Idioma> listId = new ArrayList<>(); listId.add(id); f.setIdiomas(listId); if (row > 0) { funcionariosExcel.add(f); } } wb.close(); // fl.close(); // for (Funcionario fc : funcionariosExcel) { // System.out.println(fc.getNome()); // } return funcionariosExcel; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;//from www. ja v a 2s .c o m
DataTable = new JTable();
myEntries = new ArrayList<>();
File filecheck = new File(csvFile);
if (filecheck.isAbsolute()) {
String[] left_right_side_of_dot = csvFile.split("\\.");
String file_extension = left_right_side_of_dot[left_right_side_of_dot.length - 1];
switch (file_extension) {
case "xls":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception e) {
System.out.println("Error occurred while reading XLS file: " + e.toString());
}
break;
case "xlsx":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception ex) {
System.out.print("Exception during XLSX import: " + ex.toString());
}
break;
case "csv":
try {
CSVFileReader = new CSVReader(new FileReader(DataFile), ',', '"', '\0');
myEntries = CSVFileReader.readAll();
} catch (Exception e) {
}
}
columnnames = (String[]) myEntries.get(0);
DefaultTableModel tableModel = new DefaultTableModel(columnnames, myEntries.size() - 1);
rowcount = tableModel.getRowCount();
this.number_of_records = rowcount;
for (int x = 0; x < rowcount + 1; x++) {
int columnnumber = 0;
if (x > 0) {
for (String thiscellvalue : (String[]) myEntries.get(x)) {
tableModel.setValueAt(thiscellvalue, x - 1, columnnumber);
columnnumber++;
}
}
}
DataTable = new JTable(tableModel);
int number_of_rows = DataTable.getRowCount();
if (number_of_rows < 20) {
DataTable.setPreferredScrollableViewportSize(
new Dimension(1200, number_of_rows * DataTable.getRowHeight()));
}
} else {
columnnames[0] = "Stored URL List:" + csvFile;
DefaultTableModel tableModel = new DefaultTableModel(columnnames, 0);
DataTable = new JTable(tableModel);
DataTable.getColumnModel().getColumn(0).setPreferredWidth(200);
DataTable.setPreferredScrollableViewportSize(new Dimension(20, 0));
}
}
From source file:clases.excel.EXCELreader.java
private void prepareList(String path) { excelString = ""; listaEXCEL = new ArrayList<>(); int sw = 0;//0 no,1 si, 2 ready XSSFWorkbook workbook;//from w ww. jav a 2s . co m try { //Create Workbook instance holding reference to .xlsx file workbook = new XSSFWorkbook(new FileInputStream(new File(path))); //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[] linea = new String[parametros]; Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); // if (rowcont2 ==16) { // excelString += ++rowcont + "***"; int rowcont = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //JOptionPane.showMessageDialog(null, "Tipo *" + cell.getCellType() + "*"); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //leyendo tabla if (sw == 2) { Double d = cell.getNumericCellValue();//String.format("%d", d.intValue()) excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t"; linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString()); } break; case Cell.CELL_TYPE_STRING: // JOptionPane.showMessageDialog(null, "String *" + cell.getStringCellValue().trim() + "*"); if (sw == 0) { if (cell.getStringCellValue().trim().equals(init_detalle)) { sw = 1; } } //leyendo tabla if (sw == 2) { excelString += cell.getStringCellValue() + "\t"; linea[rowcont++] = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BLANK: //si estaba leyendo tabla, pero encuentra vacio // termina de leer la tabla if (rowcont == 0 && sw == 2) { sw = 4; } break; case Cell.CELL_TYPE_FORMULA: //leyendo tabla if (sw == 2) { Double d = cell.getNumericCellValue(); excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t"; linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString()); } break; } //si ya es 1 termina el ciclo, para iniciar el detalle // en la siguiente linea if (sw == 1) { sw = 2; break; } //si ya no leera mas tabla, termina de leer la row if (sw == 4) { break; } } //guarda si esta leyendo tabla if (sw == 2 && linea[0] != null) { listaEXCEL.add(linea); excelString += "\n"; } //termino de leer la tabla, cierra todo if (sw == 4) { break; } // } } int as = 0; } catch (Exception e) { e.printStackTrace(); } }
From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
/** * ??,??//from w w w .j av a 2 s .c o m * * @param sheet * @return */ private Map<String, Integer> getTitleMap(Sheet sheet) { Row row = null; Iterator<Cell> cellTitle; Map<String, Integer> titlemap = new HashMap<String, Integer>(); for (int j = 0; j < teplateParams.getHeadingRows(); j++) { row = sheet.getRow(j + teplateParams.getHeadingStartRow()); cellTitle = row.cellIterator(); int i = row.getFirstCellNum(); while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); if (!StringUtils.isEmpty(value)) { titlemap.put(value, i); } i = i + 1; } } return titlemap; }
From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java
License:Apache License
/** * ????/*from w w w . j av a 2s . c o m*/ */ private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params, List<ExcelCollectionParams> excelCollection, Map<String, ExcelImportEntity> excelParams) { Map<Integer, String> titlemap = new LinkedHashMap<Integer, String>(); Iterator<Cell> cellTitle; String collectionName = null; ExcelCollectionParams collectionParams = null; Row row = null; for (int j = 0; j < params.getHeadRows(); j++) { row = rows.next(); if (row == null) { continue; } cellTitle = row.cellIterator(); while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = getKeyValue(cell); value = value.replace("\n", ""); int i = cell.getColumnIndex(); //???? if (StringUtils.isNotEmpty(value)) { if (titlemap.containsKey(i)) { collectionName = titlemap.get(i); collectionParams = getCollectionParams(excelCollection, collectionName); titlemap.put(i, collectionName + "_" + value); } else if (StringUtils.isNotEmpty(collectionName) && collectionParams != null && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) { titlemap.put(i, collectionName + "_" + value); } else { collectionName = null; collectionParams = null; } if (StringUtils.isEmpty(collectionName)) { titlemap.put(i, value); } } } } // ? Set<String> keys = excelParams.keySet(); for (String key : keys) { if (key.startsWith("FIXED_")) { String[] arr = key.split("_"); titlemap.put(Integer.parseInt(arr[1]), key); } } return titlemap; }
From source file:cn.bzvs.excel.imports.ExcelImportServer.java
License:Apache License
/** * ????//from www .j a v a 2 s . co m * @param rows * @param params * @param excelCollection * @return */ private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params, List<ExcelCollectionParams> excelCollection) { Map<Integer, String> titlemap = new HashMap<Integer, String>(); Iterator<Cell> cellTitle; String collectionName = null; ExcelCollectionParams collectionParams = null; Row row = null; for (int j = 0; j < params.getHeadRows(); j++) { row = rows.next(); if (row == null) { continue; } cellTitle = row.cellIterator(); while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = getKeyValue(cell); int i = cell.getColumnIndex(); //???? if (StringUtils.isNotEmpty(value)) { if (titlemap.containsKey(i)) { collectionName = titlemap.get(i); collectionParams = getCollectionParams(excelCollection, collectionName); titlemap.put(i, collectionName + "_" + value); } else if (StringUtils.isNotEmpty(collectionName) && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) { titlemap.put(i, collectionName + "_" + value); } else { collectionName = null; collectionParams = null; } if (StringUtils.isEmpty(collectionName)) { titlemap.put(i, value); } } } } return titlemap; }
From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java
License:Apache License
private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, Locale locale) { MetaInfo metaInfo = MetaInfo.forType(type, locale); Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Iterator<Row> rows = sheet.rowIterator(); if (!rows.hasNext()) { return Collections.emptyList(); }//from w ww. j a va 2s. c o m Row firstRow = rows.next(); Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metaInfo.size()); for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) { Cell cell = it.next(); JsonElement jsonElement = parseAsJsonElement(cell, evaluator); if (jsonElement != null) { Field field = metaInfo.getField(jsonElement.getAsString()); if (field != null) { String name = field.getName(); int index = cell.getColumnIndex(); columnIndexToFieldName.put(index, name); } } } if (columnIndexToFieldName.isEmpty()) { return Collections.emptyList(); } List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum()); while (rows.hasNext()) { result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type)); } return result; }
From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java
License:Apache License
private static <T> T parseRow(FormulaEvaluator evaluator, Row row, Map<Integer, String> fields, Class<T> type) { JsonObject jsonObject = new JsonObject(); for (Iterator<Cell> it = row.cellIterator(); it.hasNext();) { Cell cell = it.next();//from www. j av a 2 s .com String name = fields.get(cell.getColumnIndex()); if (name != null) { JsonElement cellValue = parseAsJsonElement(cell, evaluator); if (cellValue != null) { jsonObject.add(name, cellValue); } } } return GsonHolder.GSON.fromJson(jsonObject, type); }