public List<String> readExcel(String zh) throws IOException {
List<String> values = new ArrayList<String>();
FileInputStream fise = new FileInputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook(fise);
Sheet sheet3 = wb.getSheetAt(1);
Row r = null;
int linenum = 0;
Cell cell = null;
for (int i = 8; i < sheet3.getPhysicalNumberOfRows(); i++) {
r = sheet3.getRow(i);
cell = r.getCell(3);
if (cell.toString().contains(zh)) {
linenum = i;
break;
}
}
r = sheet3.getRow(linenum);
String value = null;
for (int i = 15; i <= 21; i++) {
cell = r.getCell(i);
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
HSSFDataFormatter df = new HSSFDataFormatter();
String cellf = df.formatCellValue(cell);
value = cellf;
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
value = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
value = "0";
}
values.add(value);
}
cell = r.getCell(24);
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
HSSFDataFormatter df = new HSSFDataFormatter();
String cellf = df.formatCellValue(cell);
value = cellf;
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
value = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
value = "0";
}
values.add(value);
// System.out.println(values);
return values;
}
private String[] rowToStringArray(Row row) {
stringCache.clear();
for (Cell cell : CollectionUtils.asIterable(row.iterator())) {
while (stringCache.size() < cell.getColumnIndex()) stringCache.add(""); // handle empty cells
stringCache.add(formatter.formatCellValue(cell, evaluator));
}
return stringCache.toArray(new String[stringCache.size()]);
}
/**
* Formats the given numeric of date Cell's contents as a String, in as close as we can to the way
* that Excel would do so. Uses the various format records to manage this.
*
* <p>TODO - move this to a central class in such a way that hssf.usermodel can make use of it too
*/
public String formatNumberDateCell(CellValueRecordInterface cell) {
double value;
if (cell instanceof NumberRecord) {
value = ((NumberRecord) cell).getValue();
} else if (cell instanceof FormulaRecord) {
value = ((FormulaRecord) cell).getValue();
} else {
throw new IllegalArgumentException("Unsupported CellValue Record passed in " + cell);
}
// Get the built in format, if there is one
int formatIndex = getFormatIndex(cell);
String formatString = getFormatString(cell);
if (formatString == null) {
return _defaultFormat.format(value);
}
// Format, using the nice new
// HSSFDataFormatter to do the work for us
return _formatter.formatRawCellContents(value, formatIndex, formatString);
}
/** Retrieves the text contents of the file */
public String getText() {
StringBuffer text = new StringBuffer();
// We don't care about the difference between
// null (missing) and blank cells
_wb.setMissingCellPolicy(HSSFRow.RETURN_BLANK_AS_NULL);
// Process each sheet in turn
for (int i = 0; i < _wb.getNumberOfSheets(); i++) {
HSSFSheet sheet = _wb.getSheetAt(i);
if (sheet == null) {
continue;
}
if (_includeSheetNames) {
String name = _wb.getSheetName(i);
if (name != null) {
text.append(name);
text.append("\n");
}
}
// Header text, if there is any
if (_includeHeadersFooters) {
text.append(_extractHeaderFooter(sheet.getHeader()));
}
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
for (int j = firstRow; j <= lastRow; j++) {
HSSFRow row = sheet.getRow(j);
if (row == null) {
continue;
}
// Check each cell in turn
int firstCell = row.getFirstCellNum();
int lastCell = row.getLastCellNum();
if (_includeBlankCells) {
firstCell = 0;
}
for (int k = firstCell; k < lastCell; k++) {
HSSFCell cell = row.getCell(k);
boolean outputContents = true;
if (cell == null) {
// Only output if requested
outputContents = _includeBlankCells;
} else {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
text.append(cell.getRichStringCellValue().getString());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
text.append(_formatter.formatCellValue(cell));
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
text.append(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
text.append(ErrorEval.getText(cell.getErrorCellValue()));
break;
case HSSFCell.CELL_TYPE_FORMULA:
if (!_shouldEvaluateFormulas) {
text.append(cell.getCellFormula());
} else {
switch (cell.getCachedFormulaResultType()) {
case HSSFCell.CELL_TYPE_STRING:
HSSFRichTextString str = cell.getRichStringCellValue();
if (str != null && str.length() > 0) {
text.append(str.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
HSSFCellStyle style = cell.getCellStyle();
if (style == null) {
text.append(cell.getNumericCellValue());
} else {
text.append(
_formatter.formatRawCellContents(
cell.getNumericCellValue(),
style.getDataFormat(),
style.getDataFormatString()));
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
text.append(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
text.append(ErrorEval.getText(cell.getErrorCellValue()));
break;
}
}
break;
default:
throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
}
// Output the comment, if requested and exists
HSSFComment comment = cell.getCellComment();
if (_includeCellComments && comment != null) {
// Replace any newlines with spaces, otherwise it
// breaks the output
String commentText = comment.getString().getString().replace('\n', ' ');
text.append(" Comment by " + comment.getAuthor() + ": " + commentText);
}
}
// Output a tab if we're not on the last cell
if (outputContents && k < (lastCell - 1)) {
text.append("\t");
}
}
// Finish off the row
text.append("\n");
}
// Finally Footer text, if there is any
if (_includeHeadersFooters) {
text.append(_extractHeaderFooter(sheet.getFooter()));
}
}
return text.toString();
}
