Java XSSFSheet.iterator方法代碼示例

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Método que se encarga de leer el libro de excel
 * cuya ruta recibimos en el constructor y devuelve una lista 
 * de ciudadanos
 * @return lista de Usuarios de la base de datos
 */
public List<CitizenDB> readExcelFile(){
	List<CitizenDB> citizens = new ArrayList<CitizenDB>(); 
	// para cada una de las hojas presentes en el documento de excel
	for(int i=0;i < workbook.getNumberOfSheets();i++){
		XSSFSheet sheet = this.workbook.getSheetAt(i);
		Iterator<Row> rowIterator = sheet.iterator();
		Row row;
		int counter = 0;
		//para cada fila de la hoja
		while(rowIterator.hasNext()){
			row = rowIterator.next();
			if (counter > 0) { //omitimos la cabecera (hay que mirar si hay un metodo de la API)
				Iterator<Cell> cellIterator = row.cellIterator();
				int j = 0;
				CitizenDB user = new CitizenDB();
				while (cellIterator.hasNext()) 	
					this.insertCitizenField(user, j++, cellIterator.next());	
				user.setPassword(new GenerationPassword().passwordGenerator());
				citizens.add(user);
			}
			counter++;
		}
	}
	return citizens;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
private void fillSheetAsArray(XSSFSheet sheet, ArraySheetLib arraySheet) {
	//
	Iterator<Row> rowIter = sheet.iterator();

	while (rowIter.hasNext()) {
		Row row = rowIter.next();
		Iterator<Cell> cellIter = row.iterator();
		ArrayList<String> rowCells = new ArrayList<String>(); 
		while (cellIter.hasNext()) {
			Cell cell = cellIter.next();
			String cellValue = getCellValueAsString(cell); 
			rowCells.add(cellValue);
		}
		arraySheet.addRow(rowCells.toArray(new String[rowCells.size()]));  
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public List<List<String>> readWorksheetWithName(String fileName, String worksheetName)
    //Read a specific worksheet within excel spreadsheet, based on the name of the worksheet
    {
    try {
        FileInputStream file = new FileInputStream(new File(fileName));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook based on worksheet name
        XSSFSheet sheet = workbook.getSheet(worksheetName);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row input_row = rowIterator.next();
            tableExcelFile.add(readWorksheetRow(input_row));
        }
        file.close();
        setReturnMessage(Constants.OK);
    } catch (Exception e) {
        e.printStackTrace();
        setReturnMessage(e.toString());
    }
    return tableExcelFile;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
@Override
public ArrayList<Ciudadano> leerCiudadanos(ArrayList<Ciudadano> ciudadanos, String ruta) {
	try {
		FileInputStream file = new FileInputStream(new File(ruta));
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		XSSFSheet sheet = workbook.getSheetAt(0);

		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();

			ArrayList<Object> aux = new ArrayList<Object>();
			for (int i = 0; i < 7; i++) {
				aux.add(row.getCell(i) != null ? row.getCell(i).toString() : null);
			}

			String nombre = row.getCell(0) != null ? row.getCell(0).toString() : null;
			if (nombre != null && nombre.equals("Nombre"))
				continue;
			String fecha = row.getCell(3) != null ? row.getCell(3).toString() : null;

			Date date = new SimpleDateFormat("dd-MMM-yyyy").parse(fecha);
			java.sql.Date nacimiento = new java.sql.Date(date.getTime());

			Ciudadano ciudadano = new Ciudadano(aux.get(0).toString(), aux.get(1).toString(), aux.get(2).toString(),
					aux.get(4).toString(), aux.get(5).toString(), aux.get(6).toString(), nacimiento);
			
			ciudadanos.add(ciudadano);
		}

		file.close();
		workbook.close();
	} catch (Exception e) {
		System.err.println("Error al leer del excel xlsx");
	}
	return ciudadanos;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
@Override
public ByteArrayOutputStream convert(InputStream stream) {
    ByteArrayOutputStream outStream;
    try {
        outStream = new ByteArrayOutputStream();
        // Read workbook into HSSFWorkbook
        XSSFWorkbook my_xls_workbook = new XSSFWorkbook(stream);
        // Read worksheet into HSSFSheet
        XSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
        // To iterate over the rows
        Iterator<Row> rowIterator = my_worksheet.iterator();
        // We will create output PDF document objects at this point
        Document pdf = new Document();
        PdfWriter.getInstance(pdf, outStream);
        pdf.open();
        // we have two columns in the Excel sheet, so we create a PDF table
        // with two columns
        // Note: There are ways to make this dynamic in nature, if you want
        // to.
        PdfPTable my_table = new PdfPTable(2);
        // We will use the object below to dynamically add new data to the
        // table
        PdfPCell table_cell;
        // Loop through rows.
        printPdf(rowIterator, my_table);
        // Finally add the table to PDF document
        pdf.add(my_table);
        pdf.close();
        // we created our pdf file..
        stream.close(); // close xls
        return outStream;
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return null;

}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public List<Person> readXLSX(InputStream input) {
        try {
            XSSFWorkbook wBook = new XSSFWorkbook(input);
            XSSFSheet sheet = wBook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            rowIterator.next();
            
            List<Person> persons = getPersonsList(rowIterator);
//            wBook.close();
            return persons;
        } catch (Exception ioe) {
            ioe.printStackTrace();
            throw new ApplicationContextException("Loadfile Error");
        }
    }
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * This method loads the database table with the information in the mapping file.
 * @throws IOException This exception is thrown if there is a problem accessing the spreadsheet.
 * @throws SQLException 
 */
@Override
public void loadDatabaseTable() throws IOException, SQLException {
	
	prepareDatabase();
	
	XSSFWorkbook oWorkbook = new XSSFWorkbook (this.fisMapFile);
	 
	XSSFSheet oMapSheet = oWorkbook.getSheet(PAGE_NAME);
	if (oMapSheet != null) {
		Iterator<Row> iterDrugRow = oMapSheet.iterator();
		long lRowIndex = 0;
		while (iterDrugRow.hasNext()) {
			Row oRow = iterDrugRow.next();
			
			if ((lRowIndex > 0) ||
				((lRowIndex == 0) && (!isTitleRow(oRow)))) {
				
				RowMapping oRowMapping = new RowMapping(oRow);
				RowProcessingStatus eStatus = null;
				try {
					addRowToTermDatabase(oRowMapping);
					eStatus = RowProcessingStatus.RECORD_WRITTEN;
				}
				catch (Exception e) {
					outputRowMessage("Exception", e.getMessage(), oRowMapping);
					eStatus = RowProcessingStatus.EXCEPTION_OCCURRED;
				}
				updateStatistics(eStatus);
			}
			
			lRowIndex++;
			
			if ((lRowIndex % NUM_ROWS_PER_SECTION) == 0) {
				System.out.println("Total rows processed so far: " + lRowIndex);
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
private static int getNumberOfRows(XSSFSheet sheet) {
  int numnberOfRows = 0;
  Iterator<Row> iterator = sheet.iterator();
  while (iterator.hasNext()) {
    Row row = iterator.next();
    numnberOfRows = Math.max(numnberOfRows, row.getRowNum());
  }
  return numnberOfRows + 1;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * This method reads the information in the JLV table that contains VA VUID to RxNORM mappings.   
 * 
 * @throws IOException This exception occurs if there is a problem reading the excel spreadsheet.
 */
private void loadJlvVaMedData() throws IOException {
	//Get the workbook instance for XLS file
	XSSFWorkbook workbook = new XSSFWorkbook (this.fisJlvVaMedFileName);
	 
	XSSFSheet oDrugsSheet = workbook.getSheet("DRUGS");
	if (oDrugsSheet != null) {
		Iterator<Row> iterDrugRow = oDrugsSheet.iterator();
		long lRowIndex = 0;
		while (iterDrugRow.hasNext()) {
			Row oRow = iterDrugRow.next();
			
			if ((lRowIndex > 0) ||
				((lRowIndex == 0) && (!isVaMapTitleRow(oRow)))) {
				
				VaDrugRowMapping oVaDrugRowMapping = new VaDrugRowMapping(oRow);
				RowProcessingStatus eStatus = null;
				try {
					eStatus = updateTermDatabase(oVaDrugRowMapping);
				}
				catch (TermLoadException e) {
					outputRowMessage("Exception", e.getMessage(), oVaDrugRowMapping);
					eStatus = RowProcessingStatus.VA_MAP_EXCEPTION_OCCURRED;
				}
				updateVaMapStatistics(eStatus);
			}
			
			lRowIndex++;
			
			if ((lRowIndex % NUM_PER_SECTION) == 0) {
				System.out.println("Total rows processed so far: " + lRowIndex);
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * This method reads the information in the JLV table that contains VA VUID to RxNORM mappings.   
 * 
 * @throws IOException This exception occurs if there is a problem reading the excel spreadsheet.
 */
private void loadJlvDodMedData() throws IOException {
	//Get the workbook instance for XLS file
	XSSFWorkbook workbook = new XSSFWorkbook (this.fisJlvDodMedFileName);
	 
	XSSFSheet oDrugsSheet = workbook.getSheet("DOD_MEDICATIONS");
	if (oDrugsSheet != null) {
		Iterator<Row> iterDrugRow = oDrugsSheet.iterator();
		long lRowIndex = 0;
		while (iterDrugRow.hasNext()) {
			Row oRow = iterDrugRow.next();
			
			if ((lRowIndex > 0) ||
				((lRowIndex == 0) && (!isDodMapTitleRow(oRow)))) {
				
				DodDrugRowMapping oDodDrugRowMapping = new DodDrugRowMapping(oRow);
				RowProcessingStatus eStatus = null;
				try {
					eStatus = updateTermDatabase(oDodDrugRowMapping);
				}
				catch (TermLoadException e) {
					outputRowMessage("Exception", e.getMessage(), oDodDrugRowMapping);
					eStatus = RowProcessingStatus.DOD_MAP_EXCEPTION_OCCURRED;
				}
				updateDodMapStatistics(eStatus);
			}
			
			lRowIndex++;
			
			if ((lRowIndex % NUM_PER_SECTION) == 0) {
				System.out.println("Total rows processed so far: " + lRowIndex);
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public ExcelReader(String fileName, String sheetName) {

		int rowNumber = 0;
		int colNumber = 0;

		try {
			FileInputStream file = new FileInputStream(new File(fileName));
			XSSFWorkbook workbook = new XSSFWorkbook(file);
			XSSFSheet sheet;
			Iterator<Row> rowIterator;
			
			if (sheetName != null)
				sheet = workbook.getSheet(sheetName);
			else
				sheet = workbook.getSheetAt(0);
			
			Row row;
			Cell cell;
			Iterator<Cell> cellIterator;
			
			if (sheet != null) {
				rowIterator = sheet.iterator();
				
				while (rowIterator.hasNext()) {
					row = rowIterator.next();
					cellIterator = row.cellIterator();
					colNumber = 0;
					
					while (cellIterator.hasNext()) {
						cell = cellIterator.next();
						
						if (rowNumber == 0)
							celNum.put(cellVal(cell), colNumber);
						else
							celValues.put("["+ rowNumber +","+ colNumber +"]", cellVal(cell));
						
						colNumber++;
					}
					
					rowNumber++;
				}
			}
			
			rowCount = rowNumber - 1;
			file.close();
		}
		catch (Exception x) {
			x.printStackTrace();
		}
	}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public List<ItemRateDescription> buildItemRateDescription(String saveDirectory,
                                                          MultipartFile multipartFile) throws IOException, BulkUploadException {
    List<ItemRateDescription> itemRateDescriptions = new ArrayList<ItemRateDescription>();
    FileInputStream fileInputStream = null;
    try {
        String path = saveDirectory + multipartFile.getOriginalFilename();
        File file = new File(path);
        fileInputStream = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        for (SheetNames sheetName : SheetNames.values()) {
            XSSFSheet sheet = workbook.getSheet(sheetName.getSheetName());
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                ItemRateDescription itemRateDescription = new ItemRateDescription();
                if (row.getRowNum() == 0) {
                    continue;
                }
                Iterator<Cell> cellIterator = row.cellIterator();
                if (sheetName == SheetNames.MATERIAL_RATES) {
                    while (cellIterator.hasNext()) {
                        itemRateDescription = getItemDescription(row, itemRateDescription, cellIterator, Constants.MATERIAL);
                    }

                }
                if (sheetName == SheetNames.LABOUR_RATES) {
                    while (cellIterator.hasNext()) {
                        itemRateDescription = getItemDescription(row, itemRateDescription, cellIterator, Constants.LABOUR);
                    }
                }
                if (sheetName == SheetNames.MACHINERY_RATES) {
                    while (cellIterator.hasNext()) {
                        itemRateDescription = getItemDescription(row, itemRateDescription, cellIterator, Constants.MACHINERY);
                    }
                }
                if (sheetName == SheetNames.OTHER_RATES) {
                    while (cellIterator.hasNext()) {
                        itemRateDescription = getItemDescription(row, itemRateDescription, cellIterator, Constants.OTHER);
                    }
                }
                itemRateDescriptions.add(itemRateDescription);
            }
        }


    } catch (Exception e) {
        LOGGER.error("Exception while reading excel", e);
        throw new BulkUploadException("Error in Uploading the file");
    } finally {
        if (fileInputStream != null) {
            fileInputStream.close();
        }
    }
    return itemRateDescriptions;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * This method loads the database table with the information in the mapping file.
 * @throws IOException This exception is thrown if there is a problem accessing the spreadsheet.
 * @throws SQLException 
 */
@Override
public void loadDatabaseTable() throws IOException, SQLException {
	
	prepareDatabase();
	
	XSSFWorkbook oWorkbook = new XSSFWorkbook (this.fisMapFile);
	 
	XSSFSheet oMapSheet = oWorkbook.getSheet(PAGE_NAME);
	if (oMapSheet != null) {
		Iterator<Row> iterDrugRow = oMapSheet.iterator();
		long lRowIndex = 0;
		while (iterDrugRow.hasNext()) {
			Row oRow = iterDrugRow.next();
			
			if ((lRowIndex > 0) ||
				((lRowIndex == 0) && (!isTitleRow(oRow)))) {
				
				RowMapping oRowMapping = new RowMapping(oRow);
				RowProcessingStatus eStatus = null;
				if (containsValidData(oRowMapping)) {
					try {
						addRowToTermDatabase(oRowMapping);
						eStatus = RowProcessingStatus.RECORD_WRITTEN;
					}
					catch (Exception e) {
						outputRowMessage("Exception", e.getMessage(), oRowMapping);
						eStatus = RowProcessingStatus.EXCEPTION_OCCURRED;
					}
				}
				else {
					outputRowMessage("Error", "The row contained invalid data.", oRowMapping);
					eStatus = RowProcessingStatus.INVALID_ROW_DATA;
				}
				updateStatistics(eStatus);
			}
			
			lRowIndex++;
			
			if ((lRowIndex % NUM_ROWS_PER_SECTION) == 0) {
				System.out.println("Total rows processed so far: " + lRowIndex);
			}
		}
	}
}