下面列出了org.apache.poi.ss.usermodel.Sheet#rowIterator ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
private void ensureColumnBounds(Sheet sheet) {
if (gotBounds)
return;
Iterator<Row> iter = sheet.rowIterator();
firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
endColumn = 0;
while (iter.hasNext()) {
Row row = iter.next();
short firstCell = row.getFirstCellNum();
if (firstCell >= 0) {
firstColumn = Math.min(firstColumn, firstCell);
endColumn = Math.max(endColumn, row.getLastCellNum());
}
}
gotBounds = true;
}
@Test
public void testStringFormattedFormulaCell() throws Exception {
try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
Cell B1 = getCellFromNextRow(rowIterator, 1);
nextRow(rowIterator);
Cell B3 = getCellFromNextRow(rowIterator, 1);
expectType(B3, FORMULA);
// expectCachedType(B3, STRING); // this can't return FUNCTION as cached type as per javadoc ! fix in future work
expectFormula(B3, "B1");
expectSameStringContent(B1, B3);
expectStringContent(B3, "a");
}
}
private void ensureColumnBounds(Sheet sheet) {
if (gotBounds)
return;
Iterator<Row> iter = sheet.rowIterator();
firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
endColumn = 0;
while (iter.hasNext()) {
Row row = iter.next();
short firstCell = row.getFirstCellNum();
if (firstCell >= 0) {
firstColumn = Math.min(firstColumn, firstCell);
endColumn = Math.max(endColumn, row.getLastCellNum());
}
}
gotBounds = true;
}
@Test
public void testNumericFormattedFormulaCell() throws Exception {
try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
Cell C1 = getCellFromNextRow(rowIterator, 2);
Cell C2 = getCellFromNextRow(rowIterator, 2);
expectType(C2, FORMULA);
expectCachedType(C2, NUMERIC);
expectFormula(C2, "C1");
expectSameStringContent(C2, C1);
expectStringContent(C2, "May 11 2018");
}
}
/**
* 获取指定页sheet的数据
* @param sheet
* @return
*/
private static List<List<String>> getSheet(Sheet sheet){
List<List<String>> list = new ArrayList<>();
// 获得表单的迭代器
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
// 获得行数据
Row row = rows.next();
// 获得行的迭代器
Iterator<Cell> cells = row.cellIterator();
List<String> rowList = new ArrayList<>();
while (cells.hasNext()) {
Cell cell = cells.next();
if(cell != null) {
String value = getCellValue(cell);
rowList.add(value);
}
}
list.add(rowList);
}
return list;
}
@Test
public void testQuotedStringFormattedFormulaCell() throws Exception {
try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
nextRow(rowIterator);
Cell B2 = getCellFromNextRow(rowIterator, 1);
nextRow(rowIterator);
Cell B4 = getCellFromNextRow(rowIterator, 1);
expectType(B4, FORMULA);
// expectCachedType(B4, STRING); // this can't return FUNCTION as cached type as per javadoc ! fix in future work
// expectFormula(B4, "B2"); // returning wrong forumla type? this needs to be fixed in future work
expectSameStringContent(B2, B4);
expectStringContent(B4, "\"a\"");
}
}
private void ensureColumnBounds(Sheet sheet) {
if (gotBounds)
return;
Iterator<Row> iter = sheet.rowIterator();
firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
endColumn = 0;
while (iter.hasNext()) {
Row row = iter.next();
short firstCell = row.getFirstCellNum();
if (firstCell >= 0) {
firstColumn = Math.min(firstColumn, firstCell);
endColumn = Math.max(endColumn, row.getLastCellNum());
}
}
gotBounds = true;
}
@Test
public void testFormulaCells() throws Exception {
try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
assertEquals(1, workbook.getNumberOfSheets());
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
Cell A1 = getCellFromNextRow(rowIterator, 0);
Cell A2 = getCellFromNextRow(rowIterator, 0);
Cell A3 = getCellFromNextRow(rowIterator, 0);
expectType(A3, FORMULA);
expectCachedType(A3, NUMERIC);
expectFormula(A3, "SUM(A1:A2)");
expectStringContent(A1, "1");
expectStringContent(A2, "2");
expectStringContent(A3, "3");
}
}
public void populateData() throws SQLException {
Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook();
int noOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < noOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
String sheetName = sheet.getSheetName();
ColumnInfo[] headers = this.extractColumnHeaders(sheet);
DataTable dataTable = new FixedDataTable(sheetName, headers);
Iterator<Row> rowItr = sheet.rowIterator();
while (rowItr.hasNext()) {
Row row = rowItr.next();
if (row.getRowNum() != 0) {
DataRow dataRow = new DataRow(row.getRowNum() - 1);
Iterator<Cell> cellItr = row.cellIterator();
int cellIndex = 0;
while (cellItr.hasNext()) {
Cell cell = cellItr.next();
DataCell dataCell =
new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell));
dataRow.addCell(dataCell.getColumnId(), dataCell);
cellIndex++;
}
dataTable.addRow(dataRow);
}
}
this.getData().put(dataTable.getTableName(), dataTable);
}
}
private void printSheetContent(Sheet sheet) {
// printColumnHeads(sheet);
MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
out.format("<tbody>%n");
Iterator<Row> rows = sheet.rowIterator();
int rowIndex = 1;
while (rows.hasNext()) {
Row row = rows.next();
out.format(" <tr style='height:%spx;'>%n", row.getHeight() / 15);
// out.format(" <td class='%s'>%d</td>%n", ROW_HEAD_CLASS,
// row.getRowNum() + 1);
for (int i = firstColumn; i < endColumn; i++) {
if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
String content = " ";
CellStyle style = null;
if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
Cell cell = row.getCell(i);
if (cell != null) {
style = cell.getCellStyle();
content = cellValueHelper.getHtmlValue(cell);
}
}
if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
out.format(" <td rowspan='%s' colspan='%s' class='%s' >%s</td>%n", rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
} else {
out.format(" <td class='%s'>%s</td>%n", styleName(style), content);
}
}
}
out.format(" </tr>%n");
rowIndex++;
}
out.format("</tbody>%n");
}
public static List<String[]> readExcel(InputStream inputStream, String extension) throws IOException {
List<String[]> ret = new ArrayList<>();
Workbook excelBook = extension.equalsIgnoreCase(XLS.name()) ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
Sheet excelSheet = excelBook.getSheetAt(0);
Iterator itr = excelSheet.rowIterator();
Row headerRow = (Row) itr.next();
if (isRowEmpty(headerRow)) {
return ret;
}
while (itr.hasNext()) {
Row row = (Row) itr.next();
if (!isRowEmpty(row)) {
String[] data = new String[row.getLastCellNum()];
for (int i = 0; i < row.getLastCellNum(); i++) {
data[i] = (row.getCell(i) != null) ? row.getCell(i).getStringCellValue().trim() : null;
}
ret.add(data);
}
}
return ret;
}
@Override
public boolean connect(File resource) {
try
( FileInputStream inputStream = new FileInputStream(resource);
Workbook wb = new XSSFWorkbook(inputStream);
){
Sheet sheet = wb.getSheetAt(0);
sheet.rowIterator();
// first row with name and time ofFormat
Row infoRow = rowIterator.next();
name = infoRow.getCell(0).getStringCellValue();
String timeFormat = infoRow.getCell(1).getStringCellValue();
id =FormatUtils.extractInteger(timeFormat);
isDateTwoColumn = id== TimeFormatType.yyyy_MM_ddHmsz.id;
dateTimeFormatter = FormatUtils.getDateTimeFormatter(id);
String currencyString = infoRow.getCell(2).getStringCellValue().replaceAll("\\s","").toUpperCase();
if(currencyString==null){
currencyString = Parameter.DEFAULT_CURRENCY;
}
currency = Currency.getInstance(currencyString);
} catch (IOException e) {
log.error("Error connecting with .xls file: " + e.getMessage());
e.printStackTrace();
return false;
}
return true;
}
private void printSheetContent(Sheet sheet) {
//printColumnHeads(sheet);
MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
out.format("<tbody>%n");
Iterator<Row> rows = sheet.rowIterator();
int rowIndex = 1;
while (rows.hasNext()) {
Row row = rows.next();
out.format(" <tr style='height:%spx;'>%n", row.getHeight() / 15);
//out.format(" <td class='%s'>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
for (int i = firstColumn; i < endColumn; i++) {
if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
String content = " ";
CellStyle style = null;
if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
Cell cell = row.getCell(i);
if (cell != null) {
style = cell.getCellStyle();
content = cellValueHelper.getHtmlValue(cell);
}
}
if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
out.format(" <td rowspan='%s' colspan='%s' class='%s' >%s</td>%n",
rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
} else {
out.format(" <td class='%s'>%s</td>%n", styleName(style), content);
}
}
}
out.format(" </tr>%n");
rowIndex++;
}
out.format("</tbody>%n");
}
private DataFileDto parseWorkbook(final Sheet sheet, final FileParserMessage<DataFileColumn> msg, final Boolean readFirstColumnAsColumnName) {
final DataFileDto dataFileDto = new DataFileDto();
dataFileDto.setRowTypes(buildRowTypes(sheet.getRow(readFirstColumnAsColumnName ? 1 : 0)));
final Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
dataFileDto.addRow(readRowAsRowInMemory(rowIterator.next(), dataFileDto.getRowTypes()));
}
return dataFileDto;
}
private static void parseTemplate(Sheet sheet, Map<String, Object> map)
throws Exception {
Iterator<Row> rows = sheet.rowIterator();
Row row;
while (rows.hasNext()) {
row = rows.next();
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
setValueForCellByMap(row.getCell(i), map);
}
}
}
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
cfSpreadSheetData spreadsheet = null;
String cols;
/*
* Collect up the parameters
*/
spreadsheet = (cfSpreadSheetData)parameters.get(1);
cols = parameters.get(0).getString();
Sheet sheet = spreadsheet.getActiveSheet();
Set<Integer> numbers = tagUtils.getNumberSet( cols );
Iterator<Row> rowIT = sheet.rowIterator();
while ( rowIT.hasNext() ){
Row row = rowIT.next();
Iterator<Integer> columnIndx = numbers.iterator();
while ( columnIndx.hasNext() ){
Cell cell = row.getCell( columnIndx.next() - 1 );
if ( cell != null ){
row.removeCell( cell );
}
}
}
return cfBooleanData.TRUE;
}
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
String fileLocation = LondonLonelinessImporter.DatasourceId.lonelinessPrevalence.datasourceSpec.getUrl();
InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xlsx");
List<TimedValue> timedValues = new ArrayList<TimedValue>();
XSSFWorkbook workbook = new XSSFWorkbook(isr);
if (geographyScope == null || geographyScope.isEmpty()) {
geographyScope = new ArrayList<>();
geographyScope.add("localAuthority");
log.warn("No geography scope provided. Defaulting to Local Authority");
}
for (String geographyLabel : geographyScope) {
Sheet datatypeSheet = null;
SubjectType subjectType = null;
List<Integer> validAttributes = new ArrayList<>();
switch (geographyLabel) {
case "localAuthority":
datatypeSheet = workbook.getSheetAt(1);
subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
validAttributes.addAll(Arrays.asList(0,1,2,3));
break;
case "msoa":
datatypeSheet = workbook.getSheetAt(2);
subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));
validAttributes.addAll(Arrays.asList(0,1,2,3,4));
break;
case "lsoa":
datatypeSheet = workbook.getSheetAt(3);
subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(2));
validAttributes.addAll(Arrays.asList(0,2,3,4));
break;
}
// Creating the row iterator object
Iterator<Row> rowIterator = datatypeSheet.rowIterator();
LocalDateTime timestamp = TimedValueUtils.parseTimestampString("Jun-15");
log.info("The analysis was made in {} and we persist it as {}", "June 2015", timestamp);
// Skipping unrelevant rows
rowIterator.next();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String geograghy = String.valueOf(row.getCell(0)).trim();
Subject subject = SubjectUtils.getSubjectByTypeAndLabel(subjectType, geograghy);
if (subject != null) {
try {
ListIterator<Integer> it = validAttributes.listIterator();
while (it.hasNext()) {
Double record = row.getCell(it.nextIndex()+2).getNumericCellValue();
Attribute attribute = datasource.getTimedValueAttributes().get(it.next());
timedValues.add(new TimedValue(
subject,
attribute,
timestamp,
record));
}
} catch (IllegalStateException e) {
log.warn("Value for subject " + subject.getLabel() + " not found. " +
"Defaulting to 0.0. Consider using a BackoffField or ConstantField.");
continue;
}
}
}
}
saveAndClearTimedValueBuffer(timedValues);
workbook.close();
}
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
String fileLocation = DatasourceId.ONSWellbeing.datasourceSpec.getUrl();
InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");
// This dataset contains both subject types
SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));
List<TimedValue> timedValues = new ArrayList<TimedValue>();
HSSFWorkbook workbook = new HSSFWorkbook(isr);
int attributeIndex = 0;
// Looping through the excell sheets
for (int sheet = 1; sheet <= 8; sheet = sheet+2){
Sheet datatypeSheet = workbook.getSheetAt(sheet);
Row rowTime = datatypeSheet.getRow(5);
// Creating the row iterator object
Iterator<Row> rowIterator = datatypeSheet.rowIterator();
// Skipping unrelevant rows
int ignore = 0;
while (ignore++ < 7) {
rowIterator.next();
}
// Looping through rows
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String geograghy = String.valueOf(row.getCell(0)).trim();
Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geograghy);
subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geograghy);
// Checking if subject is null
if (subject != null) {
// Looping through the time values
for (int timeValuesIndex=2; timeValuesIndex <= 7; timeValuesIndex++ ) {
// This is the row number that contains our time values (years) in the dataset
String year = rowTime.getCell(timeValuesIndex).toString();
year = year.substring(0, year.length() - 3);
LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
log.info("Time is presented in the dataset as {} and we persist it as {}", year, timestamp);
try {
Double record = row.getCell(timeValuesIndex).getNumericCellValue();
// Here is where we are assigning the values of our .xls file to the attribute fields we
// created.
Attribute attribute = datasource.getTimedValueAttributes().get(attributeIndex);
timedValues.add(new TimedValue(
subject,
attribute,
timestamp,
record));
} catch (IllegalStateException e) {
log.warn("Value for subject " + subject.getLabel() + " not found. " +
"Defaulting to 0.0. Consider using a BackoffField or ConstantField.");
continue;
}
}
}
}
attributeIndex++;
}
saveAndClearTimedValueBuffer(timedValues);
workbook.close();
}
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
String fileLocation = DatasourceId.ONSNewBusinessSurvival.datasourceSpec.getUrl();
InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");
// This dataset contains both subject types
SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));
List<TimedValue> timedValues = new ArrayList<TimedValue>();
HSSFWorkbook workbook = new HSSFWorkbook(isr);
// Looping through the excell sheets
for (int sheet = 13; sheet <= 17; sheet++){
Sheet datatypeSheet = workbook.getSheetAt(sheet);
Iterator<Row> rowIterator = datatypeSheet.rowIterator();
int ignore = 0;
while (ignore++ < 6) {
rowIterator.next();
}
// Hardcoded year of survey
String year = null;
switch (sheet){
case 13:
year = "2011";
break;
case 14:
year = "2012";
break;
case 15:
year = "2013";
break;
case 16:
year = "2014";
break;
case 17:
year = "2015";
break;
}
Row rowAttribute = datatypeSheet.getRow(6);
// Looping through rows
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String geography = String.valueOf(row.getCell(0)).trim();
Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geography);
subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geography);
// Checking if subject is null
if (subject != null) {
// loop through attribute columns
for (int i=3; i<=12;i++){
String attributeName = String.valueOf(rowAttribute.getCell(i)).trim();
LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
try {
Double record;
if (attributeName.contains("per cent")) {
record = row.getCell(i).getNumericCellValue() / 100;
log.info("Value for " + subject.getLabel()+". Appears as: "+ row.getCell(i).getNumericCellValue()+
" Saving as: "+record);
} else {
record = row.getCell(i).getNumericCellValue();
}
row.getCell(i).getNumericCellValue();
Attribute attribute = AttributeId.getAttributeIdByEqual(attributeName).attribute;
timedValues.add(new TimedValue(
subject,
attribute,
timestamp,
record));
} catch (IllegalStateException | NullPointerException e) {
log.warn("Invalid value for subject " + subject.getLabel()+". Skipping");
continue;
}
}
}
}
}
saveAndClearTimedValueBuffer(timedValues);
workbook.close();
}
/**
* create table by sheet
*
* @param sheet
*/
private void createTable(Sheet sheet) {
StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
createTableSql.append(sheet.getSheetName());
createTableSql.append("(");
Iterator<Row> rit = sheet.rowIterator();
Row rowHeader = rit.next();
List<String> columns = new ArrayList<>();
for (int i = 0; i < rowHeader.getPhysicalNumberOfCells(); i++) {
createTableSql.append(rowHeader.getCell(i).getStringCellValue());
if (i == rowHeader.getPhysicalNumberOfCells() - 1) {
createTableSql.append(" TEXT");
} else {
createTableSql.append(" TEXT,");
}
columns.add(rowHeader.getCell(i).getStringCellValue());
}
createTableSql.append(")");
database.execSQL(createTableSql.toString());
while (rit.hasNext()) {
Row row = rit.next();
ContentValues values = new ContentValues();
for (int n = 0; n < row.getPhysicalNumberOfCells(); n++) {
if (row.getCell(n) == null) {
continue;
}
if (row.getCell(n).getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(row.getCell(n))) {
if (sdf == null) {
values.put(columns.get(n), DateFormat.getDateTimeInstance().format(row.getCell(n).getDateCellValue()));
} else {
values.put(columns.get(n), sdf.format(row.getCell(n).getDateCellValue()));
}
} else {
String value = getRealStringValueOfDouble(row.getCell(n).getNumericCellValue());
values.put(columns.get(n), value);
}
} else if (row.getCell(n).getCellType() == Cell.CELL_TYPE_STRING) {
values.put(columns.get(n), row.getCell(n).getStringCellValue());
}
}
if (values.size() == 0)
continue;
long result = database.insert(sheet.getSheetName(), null, values);
if (result < 0) {
throw new RuntimeException("Insert value failed!");
}
}
}