Java XSSFSheet.rowIterator方法代碼示例

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Exports a single sheet to a file
 *
 * @param sheet
 * @throws FactoryConfigurationError
 * @throws XMLStreamException
 * @throws UnsupportedEncodingException
 * @throws FileNotFoundException
 */
private void export(final XSSFSheet sheet, final XMLStreamWriter out)
		throws UnsupportedEncodingException, XMLStreamException, FactoryConfigurationError, FileNotFoundException {
	boolean isFirst = true;
	final Map<String, String> columns = new HashMap<String, String>();
	final String sheetName = sheet.getSheetName();
	System.out.print(sheetName);
	out.writeStartElement("sheet");
	out.writeAttribute("name", sheetName);
	Iterator<Row> rowIterator = sheet.rowIterator();
	while (rowIterator.hasNext()) {
		Row row = rowIterator.next();
		if (isFirst) {
			isFirst = false;
			this.writeFirstRow(row, out, columns);
		} else {
			this.writeRow(row, out, columns);
		}
	}
	out.writeEndElement();
	System.out.println("..");
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
private static String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();

    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
            excelExtractor.close();
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }

    return sb.toString();
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of patients from the workbook.
 *
 * @return A list of {@link Patient} objects.
 */
private List<Patient> readPatients() throws DataProviderException {
	XSSFSheet sheet = readRequiredSheet("patient");
	String sheetName = sheet.getSheetName();
	List<Patient> result = new ArrayList<>();
	Iterator<Row> rows = sheet.rowIterator();
	rows.next(); // skip header row
	while (rows.hasNext()) {
		Row row = rows.next();
		Patient patient = new Patient();
		patient.setId(readLongValue(sheetName, row.getCell(0)));
		patient.setFirstName(readStringValue(sheetName, row.getCell(1)));
		patient.setLastName(readStringValue(sheetName, row.getCell(2)));
		patient.setDateOfBirth(readDateValue(sheetName, row.getCell(3)));
		patient.setLanguage(readStringValue(sheetName, row.getCell(4)));
		patient.setMaritalStatus(readStringValue(sheetName, row.getCell(5)));
		patient.setRace(readStringValue(sheetName, row.getCell(6)));
		patient.setGender(readStringValue(sheetName, row.getCell(7)));
		result.add(patient);
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of providers in the workbook.
 *
 * @return A list of {@link Provider} objects.
 */
private List<Provider> readProviders() throws DataProviderException {
	XSSFSheet sheet = readRequiredSheet("provider");
	String sheetName = sheet.getSheetName();
	List<Provider> result = new ArrayList<>();
	Iterator<Row> rows = sheet.rowIterator();
	rows.next(); // skip header row
	while (rows.hasNext()) {
		Row row = rows.next();
		Provider provider = new Provider();
		provider.setId(readLongValue(sheetName, row.getCell(0)));
		provider.setFirstName(readStringValue(sheetName, row.getCell(1)));
		provider.setLastName(readStringValue(sheetName, row.getCell(2)));
		result.add(provider);
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of encounters in the workbook.
 *
 * @return A list of {@link Encounter} objects.
 */
private List<Encounter> readEncounters() throws DataProviderException {
	XSSFSheet sheet = readRequiredSheet("encounter");
	String sheetName = sheet.getSheetName();
	List<Encounter> result = new ArrayList<>();
	Iterator<Row> rows = sheet.rowIterator();
	LOGGER.debug("Encounter iterator: {}", Integer.valueOf(rows.hashCode()));
	rows.next(); // skip header row
	while (rows.hasNext()) {
		Row row = rows.next();
		Encounter encounter = new Encounter();
		encounter.setId(readLongValue(sheetName, row.getCell(0)));
		encounter.setPatientId(readLongValue(sheetName, row.getCell(1)));
		encounter.setProviderId(readLongValue(sheetName, row.getCell(2)));
		encounter.setStart(readDateValue(sheetName, row.getCell(3)));
		encounter.setEnd(readDateValue(sheetName, row.getCell(4)));
		encounter.setType(readStringValue(sheetName, row.getCell(5)));
		encounter.setDischargeDisposition(readStringValue(sheetName, row.getCell(6)));
		result.add(encounter);
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of CPT codes in the workbook.
 *
 * @return A list of {@link CPT} objects.
 */
private List<CPT> readCpts() throws DataProviderException {
	XSSFSheet sheet = this.workbook.getSheet("eCPT");
	List<CPT> result = new ArrayList<>();
	if (sheet != null) {
		String sheetName = sheet.getSheetName();
		Iterator<Row> rows = sheet.rowIterator();
		rows.next(); // skip header row
		while (rows.hasNext()) {
			Row row = rows.next();
			CPT cpt = new CPT();
			cpt.setId(readStringValue(sheetName, row.getCell(0)));
			cpt.setEncounterId(readLongValue(sheetName, row.getCell(1)));
			cpt.setTimestamp(readDateValue(sheetName, row.getCell(2)));
			cpt.setEntityId(readStringValue(sheetName, row.getCell(3)));
			result.add(cpt);
		}
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of ICD9 Procedure codes present in the workbook.
 *
 * @return A list of {@link Icd9Procedure} objects.
 */
private List<Icd9Procedure> readIcd9Procedures() throws DataProviderException {
	XSSFSheet sheet = this.workbook.getSheet("eICD9P");
	List<Icd9Procedure> result = new ArrayList<>();
	if (sheet != null) {
		String sheetName = sheet.getSheetName();
		Iterator<Row> rows = sheet.rowIterator();
		rows.next(); // skip header row
		while (rows.hasNext()) {
			Row row = rows.next();
			Icd9Procedure procedure = new Icd9Procedure();
			procedure.setId(readStringValue(sheetName, row.getCell(0)));
			procedure.setEncounterId(readLongValue(sheetName, row.getCell(1)));
			procedure.setTimestamp(readDateValue(sheetName, row
					.getCell(2)));
			procedure.setEntityId(readStringValue(sheetName, row
					.getCell(3)));
			result.add(procedure);
		}
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of medications present in the workbook.
 *
 * @return A list of {@link Medication} objects.
 */
private List<Medication> readMedications() throws DataProviderException {
	XSSFSheet sheet = this.workbook.getSheet("eMEDS");
	List<Medication> result = new ArrayList<>();
	if (sheet != null) {
		String sheetName = sheet.getSheetName();
		Iterator<Row> rows = sheet.rowIterator();
		rows.next(); // skip header row
		while (rows.hasNext()) {
			Row row = rows.next();
			Medication medication = new Medication();
			medication.setId(readStringValue(sheetName, row.getCell(0)));
			medication.setEncounterId(readLongValue(sheetName, row
					.getCell(1)));
			medication.setTimestamp(readDateValue(sheetName, row
					.getCell(2)));
			medication.setEntityId(readStringValue(sheetName, row
					.getCell(3)));
			result.add(medication);
		}
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of labs present in the workbook's "eLABS" worksheet.
 *
 * @return A list of {@link Lab} objects.
 */
private List<Lab> readLabs() throws DataProviderException {
	XSSFSheet sheet = this.workbook.getSheet("eLABS");
	List<Lab> result = new ArrayList<>();
	if (sheet != null) {
		String sheetName = sheet.getSheetName();
		Iterator<Row> rows = sheet.rowIterator();
		rows.next(); // skip header row
		while (rows.hasNext()) {
			Row row = rows.next();
			Lab lab = new Lab();
			lab.setId(readStringValue(sheetName, row.getCell(0)));
			lab.setEncounterId(readLongValue(sheetName, row.getCell(1)));
			lab.setTimestamp(readDateValue(sheetName, row.getCell(2)));
			lab.setEntityId(readStringValue(sheetName, row.getCell(3)));
			lab.setResultAsStr(readStringValue(sheetName, row.getCell(4)));
			lab.setResultAsNum(readDoubleValue(sheetName, row.getCell(5)));
			lab.setUnits(readStringValue(sheetName, row.getCell(6)));
			lab.setFlag(readStringValue(sheetName, row.getCell(7)));
			result.add(lab);
		}
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of vitals present in the workbook's "eVITALS" worksheet.
 *
 * @return A list of {@link Vital} objects.
 */
private List<Vital> readVitals() throws DataProviderException {
	XSSFSheet sheet = this.workbook.getSheet("eVITALS");
	List<Vital> result = new ArrayList<>();
	if (sheet != null) {
		String sheetName = sheet.getSheetName();
		Iterator<Row> rows = sheet.rowIterator();
		rows.next(); // skip header row
		while (rows.hasNext()) {
			Row row = rows.next();
			Vital vital = new Vital();
			vital.setId(readStringValue(sheetName, row.getCell(0)));
			vital.setEncounterId(readLongValue(sheetName, row.getCell(1)));
			vital.setTimestamp(readDateValue(sheetName, row.getCell(2)));
			vital.setEntityId(readStringValue(sheetName, row.getCell(3)));
			vital.setResultAsStr(readStringValue(sheetName, row.getCell(4)));
			vital.setResultAsNum(readDoubleValue(sheetName, row.getCell(5)));
			vital.setUnits(readStringValue(sheetName, row.getCell(6)));
			vital.setFlag(readStringValue(sheetName, row.getCell(7)));
			result.add(vital);
		}
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse newer OOXML Excel Spreadsheets
 * @param inputStreams
 * @return
 * @throws IOException
 */
private List<String> excelOOXMLToArray(InputStream inputStreams) throws IOException {
	XSSFWorkbook wb  = new XSSFWorkbook(inputStreams);

    //Convert an Excel OOXML (.xslx) file to csv
	XSSFSheet sheet = wb.getSheetAt(0);
    List<String> array = new ArrayList<String>();
    Iterator it = sheet.rowIterator();
    while (it.hasNext()){
    	XSSFRow row = (XSSFRow) it.next();
        String rowAsString = fromXSSFRowtoCSV(row);
        if (rowAsString.replaceAll(",", "").replaceAll("\"", "").equals("")) {
            continue;
        }
        array.add(fromXSSFRowtoCSV(row));
    }
    return array;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Parse the list of ICD9 Diagnostic codes present in the workbook.
 *
 * @return A list of {@link Icd9Diagnosis} objects.
 */
private List<Icd9Diagnosis> readIcd9Diagnoses() throws DataProviderException {
	XSSFSheet sheet = this.workbook.getSheet("eICD9D");
	List<Icd9Diagnosis> result = new ArrayList<>();
	if (sheet != null) {
		String sheetName = sheet.getSheetName();
		Iterator<Row> rows = sheet.rowIterator();
		rows.next(); // skip header row
		Map<Long, Integer> ranksByEncounter = new HashMap<>();
		while (rows.hasNext()) {
			Row row = rows.next();
			Icd9Diagnosis diagnosis = new Icd9Diagnosis();
			diagnosis.setId(readStringValue(sheetName, row.getCell(0)));
			Long encId = readLongValue(sheetName, row.getCell(1));
			diagnosis.setEncounterId(encId);
			diagnosis.setTimestamp(readDateValue(sheetName, row
					.getCell(2)));
			diagnosis.setEntityId(readStringValue(sheetName, row
					.getCell(3)));
			Integer rank = ranksByEncounter.get(encId);
			if (rank == null) {
				diagnosis.setRank(1);
				ranksByEncounter.put(encId, 1);
			} else {
				int newRank = rank + 1;
				diagnosis.setRank(newRank);
				ranksByEncounter.put(encId, newRank);
			}
			result.add(diagnosis);
		}
	}
	return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public static void main(String[] args) throws IOException {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
    sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
    FileInputStream fileInputStream = new FileInputStream("superstore.xlsx");

    XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.rowIterator();

    List<String> headerNames = new ArrayList<>();

    Client client = TransportClient.builder().build()
            .addTransportAddress(new InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));


    boolean firstRow = true;

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        Iterator<Cell> cellIterator = row.cellIterator();

        int columnIndex = 0;
        Map<String, Object> document = new HashMap<>();


        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (firstRow) {
                headerNames.add(cell.getStringCellValue());
            } else {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        document.put(headerNames.get(columnIndex), cell.getNumericCellValue());
                        break;
                    }
                    case Cell.CELL_TYPE_STRING: {
                        document.put(headerNames.get(columnIndex), cell.getStringCellValue());
                        break;
                    }
                    case Cell.CELL_TYPE_FORMULA: {
                        document.put(headerNames.get(columnIndex), sdf.format(cell.getDateCellValue()));
                        break;
                    }
                    case Cell.CELL_TYPE_BOOLEAN: {
                        document.put(headerNames.get(columnIndex), cell.getBooleanCellValue());
                        break;
                    }
                    default: {

                    }
                }
            }
            columnIndex++;
        }

        IndexResponse response = client.prepareIndex("twitter", "tweet")
                .setSource(document)
                .get();
        firstRow = false;
    }
}