List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:xlsxtocsv.java
static void convertToXls(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellDData = new StringBuffer(); try {/* w w w. j a v a2s .c o m*/ FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Cell cell; Row row; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDData.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellDData.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellDData.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellDData.append("" + ","); break; default: cellDData.append(cell + ","); } } cellDData.append("\n"); } fos.write(cellDData.toString().getBytes()); fos.close(); } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } }
From source file:attandance.standalone.utils.ExcelUtils.java
public static List<AttandanceRecord> readDataFromExcel(String fileName) { List<AttandanceRecord> result = new ArrayList<>(); try {/* w ww . j a v a 2s. c o m*/ FileInputStream file = new FileInputStream(new File(fileName)); //Get the workbook instance for XLS file 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 lineIndex = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int columnIndex = 1; AttandanceRecord lineBean = new AttandanceRecord(); //skip header if (lineIndex == 1) { lineIndex += 1; continue; } while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: cellValue = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; default: cellValue = cell.getStringCellValue(); } lineBean.setAttribute(columnIndex, cellValue); columnIndex = columnIndex + 1; } result.add(lineBean); } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return result; }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;/*from www. java 2s . c om*/
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:bs.compra.proceso.ActualizarPreciosCostoBean.java
public void procesarArchivoExcel() { try {/*from w ww . jav a2 s. c o m*/ FileInputStream file = new FileInputStream(new File(pathArchivoExcel)); String[] split = pathArchivoExcel.split("\\."); String extension = split[split.length - 1].toLowerCase(); if (extension.equals("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); leerDatosExcel(rowIterator); } else if (extension.equals("xls")) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); leerDatosExcel(rowIterator); } else { JsfUtil.addErrorMessage( "Formato de archivo incorrecto. El archivo debe tener extensin xls o xlsx"); } } catch (IOException ex) { Logger.getLogger(ActualizarPreciosCostoBean.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:co.com.smartcode.bitcom.managedbeans.crud.utils.ExcelUtils.java
public static List<Producto> getProductosFromXls(byte[] bytes) { List<Producto> productos = new ArrayList<>(); try {/* w w w . j a v a2 s . com*/ HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes)); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Producto producto = new Producto(); Row row = rowIterator.next(); String nombre = row.getCell(0).getStringCellValue(); producto.setNombre(nombre); String referencia = getString(row, 1); producto.setReferencia(referencia); if (row.getCell(2) != null) { String nombreMarca = row.getCell(2).getStringCellValue(); if (nombreMarca != null && nombreMarca.trim().length() > 0) { Marca marca = new Marca(); marca.setNombre(nombreMarca); producto.setMarca(marca); } } if (row.getCell(3) != null) { String imagen = row.getCell(3).getStringCellValue(); producto.setImg(imagen); } BigDecimal precio = null; try { String precioString = getString(row, 4); precio = new BigDecimal(precioString); producto.setPrecio(precio); } catch (Exception e) { continue; } if (row.getCell(5) != null) { BigDecimal precioOferta = null; try { String precioString = getString(row, 5); precioOferta = new BigDecimal(precioString); producto.setPrecioOferta(precioOferta); } catch (Exception e) { } } if (producto.getPrecioOferta() == null) { producto.setPrecioOferta(new BigDecimal("0")); } if (row.getCell(6) != null) { String descripcion = row.getCell(6).getStringCellValue(); producto.setDescripcion(descripcion); } if (row.getCell(7) != null) { String especificaciones = row.getCell(7).getStringCellValue(); producto.setEspecificaciones(especificaciones); } if (row.getCell(8) != null) { String nombreSeccion = row.getCell(8).getStringCellValue(); if (nombreSeccion != null && nombreSeccion.trim().length() > 0) { String[] split = nombreSeccion.split("-"); if (split.length == 2) { Seccion seccion = new Seccion(); seccion.setNombre(split[0]); Subseccion subseccion = new Subseccion(); subseccion.setNombre(split[1]); subseccion.setSeccion(seccion); producto.setSubseccion(subseccion); } } } productos.add(producto); } } catch (Exception e) { e.printStackTrace(); } return productos; }
From source file:com.alphacell.controller.CargarDatosBean.java
public void handleFileUpload(FileUploadEvent event) { if (event.getFile().equals(null)) { FacesUtil.addInfoMessage("El archivo es null"); }// w w w. jav a 2 s. com InputStream file; HSSFWorkbook workbook = null; try { file = event.getFile().getInputstream(); workbook = new HSSFWorkbook(file); } catch (IOException e) { FacesUtil.addErrorMessage("Error Leyendo archivo : " + e); } HSSFSheet sheet = workbook.getSheetAt(1); Iterator<Row> rowIterator = sheet.iterator(); Calendar calendar = new GregorianCalendar(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); //Job job = new Job(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell) || HSSFDateUtil.isCellInternalDateFormatted(cell)) { calendar.setTime(cell.getDateCellValue()); } else { System.out.print(cell.getNumericCellValue() + "\t\t"); } break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t\t"); break; } } } }
From source file:com.anevis.jfreechartsamplespring.reader.FileReaderServiceImpl.java
@Override public List<PieChartData> readPieChartDataFromXls(String filepath) { InputStream stream;/* ww w .ja v a 2 s . co m*/ try { stream = new FileInputStream(filepath); List<PieChartData> pieChartDataList = new ArrayList<>(); HSSFWorkbook workbook = new HSSFWorkbook(stream); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); PieChartData data = new PieChartData(); Cell countryCell = row.getCell(0); Cell weightCell = row.getCell(1); if (countryCell != null && weightCell != null) { data.setCountry(countryCell.getStringCellValue()); data.setWeight(weightCell.getNumericCellValue()); pieChartDataList.add(data); } } return pieChartDataList; } catch (IOException ex) { Logger.getLogger(FileReaderServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } return null; }
From source file:com.appspot.backstreetfoodies.server.XLSParser.java
License:Apache License
public XLSParser(InputStream inputStream, int sheetIndex, int numColumnsExpected) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(sheetIndex); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator; numColumns = numColumnsExpected;// w ww. j a va 2 s. c o m while (rowIterator.hasNext()) { int numCellsInRow = 0; Row row = rowIterator.next(); cellIterator = row.cellIterator(); ArrayList<String> temp = new ArrayList<String>(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (!String.valueOf(cell.getNumericCellValue()).isEmpty()) { numCellsInRow++; temp.add(String.valueOf(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_STRING: if (!cell.getStringCellValue().isEmpty()) { numCellsInRow++; temp.add(cell.getStringCellValue().trim()); } break; default: break; } } if (numCellsInRow == numColumnsExpected) { xlsData.add(temp); } } }
From source file:com.binlist.binlistreader.BinlistReader.java
public static void main(String[] args) { String result[] = null;//from w ww . ja v a 2 s .com String folder = "/opt/"; String sourceFileName = "binlist.xls"; FileInputStream fis = null; try { fis = new FileInputStream(folder + sourceFileName); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator = null; Row row = null; Cell cell = null; int cellNo = 0; String binno = ""; String longUrl = ""; String shortUrl = ""; int rownum = 0; while (rowIterator.hasNext()) { rownum++; if (rownum == 1) continue; row = rowIterator.next(); cellIterator = row.cellIterator(); cellNo = 0; binno = ""; longUrl = ""; shortUrl = ""; cell = row.getCell(4); if (cell != null && cell.getStringCellValue() != null) { binno = cell.getStringCellValue(); } if (binno != null && binno.length() > 5) { result = postRequestV3(binno); System.out.println("rownum..:" + rownum + " binno..:" + binno + " result..:" + result.length); if (result != null) { row.getCell(5).setCellValue(checkNull(result[0], "")); row.getCell(6).setCellValue(checkNull(result[1], "")); row.getCell(7).setCellValue(checkNull(result[2], "")); } } } System.out.println("rownum..:" + rownum); fis.close(); FileOutputStream out = new FileOutputStream(folder + "newfile/" + sourceFileName); workbook.write(out); out.close(); workbook.close(); workbook = null; } catch (Exception e) { e.printStackTrace(); } finally { if (fis != null) try { fis.close(); } catch (IOException ex) { Logger.getLogger(BinlistReader.class.getName()).log(Level.SEVERE, null, ex); } fis = null; } }
From source file:com.cmcti.cmts.domain.service.impl.CustomerMacMappingLocalServiceImpl.java
License:Open Source License
public void importAddressFromXls(InputStream is, int sheetIdx, int startRow, ServiceContext serviceContext, boolean deleteAll) throws PortalException, SystemException { if (deleteAll) { customerMacMappingPersistence.removeAll(); counterLocalService.reset(CustomerMacMapping.class.getName()); }/*w w w. ja va 2 s.c om*/ Iterator<Row> rowIterator = null; try (HSSFWorkbook workbook = new HSSFWorkbook(is)) { HSSFSheet sheet = workbook.getSheetAt(sheetIdx); rowIterator = sheet.iterator(); } catch (Exception e) { logger.error(e); } List<CustomerMacMapping> mappings = getCustomerMacMappings(rowIterator, startRow, serviceContext, deleteAll); for (CustomerMacMapping mapping : mappings) { customerMacMappingPersistence.update(mapping); } }