下面列出了org.apache.poi.ss.usermodel.Sheet#getLastRowNum ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
private List<Node> getNodesFromExcelFile(final String excelFilePath) {
List<Node> nodes = new ArrayList<>();
try (Workbook workbook = new XSSFWorkbook(new FileInputStream(excelFilePath))) {
Sheet sheet = workbook.getSheetAt(0);
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row currentRow = sheet.getRow(rowNum);
if (Objects.isNull(currentRow.getCell(0)) || Strings
.isNullOrEmpty(currentRow.getCell(0).getStringCellValue())) {
break;
}
nodes.add(getNodeFromExcelRow(currentRow));
}
if (nodes.isEmpty()) {
throw new ParseExcelFileException("No node info in uploaded excel file.");
}
return nodes;
} catch (final IOException | IllegalArgumentException e) {
throw new ParseExcelFileException(e);
}
}
/**
* シートの最大列数を取得する。
* <p>{@literal jxl.Sheet.getColumns()}</p>
* @param sheet シートオブジェクト
* @return 最大列数
* @throws IllegalArgumentException {@literal sheet == null.}
*/
public static int getColumns(final Sheet sheet) {
ArgUtils.notNull(sheet, "sheet");
int minRowIndex = sheet.getFirstRowNum();
int maxRowIndex = sheet.getLastRowNum();
int maxColumnsIndex = 0;
for(int i = minRowIndex; i <= maxRowIndex; i++) {
final Row row = sheet.getRow(i);
if(row == null) {
continue;
}
final int column = row.getLastCellNum();
if(column > maxColumnsIndex) {
maxColumnsIndex = column;
}
}
return maxColumnsIndex;
}
/**
* 先判断删除,省得影响效率
*
* @param sheet
* @param map
* @throws Exception
*/
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
Row row = null;
Cell cell = null;
int index = 0;
while (index <= sheet.getLastRowNum()) {
row = sheet.getRow(index++);
if (row == null) {
continue;
}
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
cell = row.getCell(i);
if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
cell.setCellType(Cell.CELL_TYPE_STRING);
String text = cell.getStringCellValue();
if (text.contains(IF_DELETE)) {
if (Boolean.valueOf(eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map).toString())) {
PoiSheetUtility.deleteColumn(sheet, i);
}
cell.setCellValue("");
}
}
}
}
}
/**
* 获取Sheet页内容
*
* @param sheet
* @return
*/
public static final List<Object[]> listFromSheet(Sheet sheet) {
List<Object[]> list = new ArrayList<Object[]>();
for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null || row.getPhysicalNumberOfCells() == 0) continue;
Object[] cells = new Object[row.getLastCellNum()];
for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell == null) continue;
//判断是否为日期类型
cells[c] = getValueFromCell(cell);
}
list.add(cells);
}
return list;
}
/**
* Converts xls sheet to json format.
* Currently considering the username.
*
* @param sheet : The XLS sheet that needs to be converted.
* @return : Json string which represents the sheet.
*/
public String xlsToJSON(Sheet sheet) {
int limit = sheet.getLastRowNum();
users = new JsonArray();
if (log.isDebugEnabled()) {
log.debug("Converting XLS sheet to json.");
}
for (int i = 1; i < limit + 1; i++) {
Row row = sheet.getRow(i);
Cell cell = row.getCell(0);
String name = cell.getStringCellValue();
JsonPrimitive userJson = new JsonPrimitive(name);
users.add(userJson);
}
content.add(UserMgtConstants.USERS, users);
return content.toString();
}
/**
* Inserts rows at the specified indexes in the worksheet
*
* @param worksheet Worksheet where rows will be inserted
* @param rowIndexList List of row indexes where rows will be inserted
*/
public static void shiftRows(final Sheet worksheet, final List<Integer> rowIndexList) {
int insertPoint;
int nRows;
int i = 0;
while (i < rowIndexList.size()) {
insertPoint = rowIndexList.get(i);
nRows = 1;
while (i < rowIndexList.size() - 1 && (insertPoint + nRows == rowIndexList.get(i + 1))) {
nRows++;
i++;
}
if (insertPoint > worksheet.getLastRowNum()) {
for (int j = insertPoint; j < insertPoint + nRows; j++) {
worksheet.createRow(j);
}
} else {
worksheet.shiftRows(insertPoint, worksheet.getLastRowNum(), nRows, false, true);
}
i++;
}
}
/**
* Constructs a list of indexes where the data will be added in the worksheet
*
* @param index A list of indexes
* @param worksheet The worksheet where the data will be added
* @param rowData Data that will be added to the worksheet
* @param rowDelimiter rowData row delimiter
* @param columnDelimiter rowData column delimiter
* @param isRow true - if the index list (param) contains row indexes
* @return List of indexes where data will be added in the worksheet
*/
public static List<Integer> processIndex(final String index, final Sheet worksheet, final String rowData, final String rowDelimiter,
final String columnDelimiter, final boolean isRow, final boolean hasHeader) {
final String[] rows = rowData.split(rowDelimiter);
String[] indexArray = null;
if (!StringUtils.isBlank(index)) {
indexArray = index.split(",");
}
int sheetLastRowIndex = worksheet.getLastRowNum();
if (sheetLastRowIndex > 0) {
sheetLastRowIndex++;
}
final int dataRows = rows.length;
final int dataColumns = rows[0].split(columnDelimiter).length;
int headerOffset = 0;
if (hasHeader) {
headerOffset = 1;
}
if (isRow) {
return processIndexWithOffset(indexArray, headerOffset, sheetLastRowIndex, sheetLastRowIndex + dataRows);
} else {
return processIndexWithOffset(indexArray, 0, 0, dataColumns);
}
}
public PersonSheetConfigurator(XSSFWorkbook workbook, Sheet sheet) {
this.sheetIndex = workbook.getSheetIndex(sheet);
Row row = sheet.getRow(sheet.getFirstRowNum());
this.firstRow = sheet.getFirstRowNum() + 1;
this.lastRow = sheet.getLastRowNum();
memoColumn = row.getLastCellNum() + 1;
for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
if (null != cell) {
String str = this.getCellStringValue(cell);
if (StringUtils.isNotEmpty(str)) {
if (nameItems.contains(str)) {
this.nameColumn = i;
} else if (uniqueItems.contains(str)) {
this.uniqueColumn = i;
} else if (employeeItems.contains(str)) {
this.employeeColumn = i;
} else if (mobileItems.contains(str)) {
this.mobileColumn = i;
} else if (mailItems.contains(str)) {
this.mailColumn = i;
} else if (genderTypeItems.contains(str)) {
this.genderTypeColumn = i;
} else {
Matcher matcher = attributePattern.matcher(str);
if (matcher.matches()) {
String attribute = matcher.group(1);
this.attributes.put(attribute, new Integer(i));
}
}
}
}
}
}
/**
* 获取表中所有数据
*/
public List<List> getDataSheet(int sheetNumber)
throws FileNotFoundException {
Sheet sheet = workbook.getSheetAt( sheetNumber );
List<List> result = new ArrayList<List>();
// 获取数据总行数，编号是从0开始的
int rowcount = sheet.getLastRowNum() + 1;
if (rowcount < 1) {
return result;
}
// 逐行读取数据
for (int i = 0; i < rowcount; i++) {
// 获取行对象
Row row = sheet.getRow( i );
if (row != null) {
List<Object> rowData = new ArrayList<Object>();
// 获取本行中单元格个数
int column = row.getLastCellNum();
// 获取本行中各单元格的数据
for (int cindex = 0; cindex < column; cindex++) {
Cell cell = row.getCell( cindex );
// 获得指定单元格中的数据
//getCellString( cell );
rowData.add( cell.getStringCellValue() );
}
result.add( rowData );
}
}
return result;
}
/**
* Given a sheet, this method deletes a column from a sheet and moves
* all the columns to the right of it to the left one cell.
*
* Note, this method will not update any formula references.
*
* @param sheet
* @param column
*/
public static void deleteColumn(Sheet sheet, int columnToDelete) {
int maxColumn = 0;
for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
Row row = sheet.getRow(r);
// if no row exists here; then nothing to do; next!
if (row == null)
continue;
// if the row doesn't have this many columns then we are good; next!
int lastColumn = row.getLastCellNum();
if (lastColumn > maxColumn)
maxColumn = lastColumn;
if (lastColumn < columnToDelete)
continue;
for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
Cell oldCell = row.getCell(x - 1);
if (oldCell != null)
row.removeCell(oldCell);
Cell nextCell = row.getCell(x);
if (nextCell != null) {
Cell newCell = row.createCell(x - 1, nextCell.getCellType());
cloneCell(newCell, nextCell);
}
}
}
// Adjust the column widths
for (int c = 0; c < maxColumn; c++) {
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
}
}
/**
* Given a sheet, this method deletes a column from a sheet and moves all
* the columns to the right of it to the left one cell.
*
* Note, this method will not update any formula references.
*
* @param sheet
* @param column
*/
public static void deleteColumn(Sheet sheet, int columnToDelete) {
int maxColumn = 0;
for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
Row row = sheet.getRow(r);
// if no row exists here; then nothing to do; next!
if (row == null)
continue;
// if the row doesn't have this many columns then we are good; next!
int lastColumn = row.getLastCellNum();
if (lastColumn > maxColumn)
maxColumn = lastColumn;
if (lastColumn < columnToDelete)
continue;
for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
Cell oldCell = row.getCell(x - 1);
if (oldCell != null)
row.removeCell(oldCell);
Cell nextCell = row.getCell(x);
if (nextCell != null) {
Cell newCell = row.createCell(x - 1, nextCell.getCellType());
cloneCell(newCell, nextCell);
}
}
}
// Adjust the column widths
for (int c = 0; c < maxColumn; c++) {
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
}
}
/**
* Create new row.
* @param sheet Sheet
* @return Row Row
*/
private Row createRow(final Sheet sheet) {
Row row;
if (this.index == -1) {
final int num = sheet.getLastRowNum();
row = sheet.createRow(num);
sheet.createRow(num + 1);
} else {
row = sheet.getRow(this.index - 1);
if (row == null) {
row = sheet.createRow(this.index - 1);
}
}
return row;
}
private void readThenConsume(Sheet sheet, Map<Integer, Field> fieldMap, Consumer<T> consumer, Function<T, Boolean> function) {
long startTime = System.currentTimeMillis();
final int firstRowNum = sheet.getFirstRowNum();
final int lastRowNum = sheet.getLastRowNum();
log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
if (lastRowNum < 0) {
log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
return;
}
DataFormatter formatter = new DataFormatter();
for (int i = firstRowNum; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (row == null) {
log.info("Row of {} is null,it will be ignored.", i);
continue;
}
boolean noMatchResult = rowFilter.negate().test(row);
if (noMatchResult) {
log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
continue;
}
int lastColNum = row.getLastCellNum();
if (lastColNum < 0) {
continue;
}
T obj = instanceObj(fieldMap, formatter, row);
if (beanFilter.test(obj)) {
if (consumer != null) {
consumer.accept(obj);
} else if (function != null) {
Boolean noStop = function.apply(obj);
if (!noStop) {
break;
}
}
}
}
log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
}
public static Map<String, String> deleteCell(@NotNull final DeleteCellInputs deleteCellInputs) {
try {
final String excelFileName = deleteCellInputs.getCommonInputs().getExcelFileName();
final Workbook excelDoc = getExcelDoc(excelFileName);
final Sheet worksheet = getWorksheet(excelDoc, deleteCellInputs.getCommonInputs().getWorksheetName());
final int firstRowIndex = worksheet.getFirstRowNum();
final int firstColumnIndex = 0;
final int lastRowIndex = worksheet.getLastRowNum();
final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);
final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
final String rowIndex = defaultIfEmpty(deleteCellInputs.getRowIndex(), rowIndexDefault);
final String columnIndex = defaultIfEmpty(deleteCellInputs.getColumnIndex(), columnIndexDefault);
final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);
if (rowIndexList.size() != 0 && columnIndexList.size() != 0) {
final int deleteCellResult = deleteCell(worksheet, rowIndexList, columnIndexList);
//update formula cells
final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
for (Row r : worksheet) {
for (Cell c : r) {
if (c.getCellType() == CellType.FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
updateWorkbook(excelDoc, excelFileName);
return getSuccessResultsMap(String.valueOf(deleteCellResult));
} else {
return getSuccessResultsMap("0");
}
} catch (Exception e) {
return getFailureResultsMap(e.getMessage());
}
}
private void handleContinuedTags(String continued, Sheet tagnameSheet, String tagname){
//handles continued tags over several lines
Sheet overviewSheet;
if (wb.getSheet(overview) != null){
overviewSheet = wb.getSheet(overview);
}
else{
return;
}
int lastRowIdxOfFirstSheet = overviewSheet.getLastRowNum();
if (lastRowIdxOfFirstSheet == 0){
return;
}
int lastRowIdxOfTagnameSheet = tagnameSheet.getLastRowNum();
if (lastRowIdxOfTagnameSheet == 0){
return;
}
Row prevRowOfOverviewSheet = overviewSheet.getRow(lastRowIdxOfFirstSheet);
String allAttributes = prevRowOfOverviewSheet.getCell(prevRowOfOverviewSheet.getLastCellNum()-1).getStringCellValue();
int i = 1;
while (!allAttributes.contains(tagname) && lastRowIdxOfFirstSheet>i){
prevRowOfOverviewSheet = overviewSheet.getRow(lastRowIdxOfFirstSheet-i);
allAttributes = prevRowOfOverviewSheet.getCell(prevRowOfOverviewSheet.getLastCellNum()-1).getStringCellValue();
i++;
if(allAttributes.contains(tagname)){
logger.debug("same tagname found");
break;
}
}
Row prevRowOfTagnameSheet = tagnameSheet.getRow(lastRowIdxOfTagnameSheet);
String currValue = prevRowOfOverviewSheet.getCell(0).getStringCellValue();
String lastChar = currValue.substring(currValue.length()-1);
logger.debug("last char is " + lastChar);
if (lastChar.matches("[\\\u00AD\\\u002D\\\u00AC\\\u003D]")){
logger.debug("last char is soft hyphen, minus, not sign, equal sign");
lastChar = lastChar.replaceAll("[\\\u00AD\\\u002D\\\u00AC\\\u003D]", "");
}
else{
lastChar = lastChar.concat(" ");
}
currValue = currValue.substring(0, currValue.length()-1).concat(lastChar).concat(continued);
logger.debug("value to store " + currValue);
prevRowOfOverviewSheet.getCell(0).setCellValue(currValue);
String currValueTag = prevRowOfTagnameSheet.getCell(0).getStringCellValue();
String lastCharTag = currValueTag.substring(currValueTag.length()-1);
logger.debug("last char is " + lastCharTag);
if (lastCharTag.matches("[\\\u00AD\\\u002D\\\u00AC\\\u003D]")){
logger.debug("last char is soft hyphen, minus, not sign, equal sign");
lastCharTag = lastCharTag.replaceAll("[\\\u00AD\\\u002D\\\u00AC\\\u003D]", "");
}
else{
lastCharTag = lastCharTag.concat(" ");
}
currValueTag = currValueTag.substring(0, currValueTag.length()-1).concat(lastCharTag).concat(continued);
logger.debug("value to store " + currValueTag);
prevRowOfTagnameSheet.getCell(0).setCellValue(currValueTag);
// //soft hyphen
// currValue = currValue.replaceAll("\u00AD", "");
// //minus
// currValue = currValue.replaceAll("\u002D", "");
// //not sign
// currValue = currValue.replaceAll("\u00AC", "");
// //= equal sign
// currValue = currValue.replaceAll("\u003D", "");
}
@NotNull
public static Map<String, String> modifyCell(@NotNull final ModifyCellInputs modifyCellInputs) {
try {
final String excelFileName = modifyCellInputs.getCommonInputs().getExcelFileName();
final Workbook excelDoc = getExcelDoc(excelFileName);
final Sheet worksheet = getWorksheet(excelDoc, modifyCellInputs.getCommonInputs().getWorksheetName());
final int firstRowIndex = worksheet.getFirstRowNum();
final int lastRowIndex = worksheet.getLastRowNum();
final int firstColumnIndex = 0;
final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);
final String columnDelimiter = modifyCellInputs.getColumnDelimiter();
final String newValue = modifyCellInputs.getNewValue();
final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
final String rowIndex = defaultIfEmpty(modifyCellInputs.getRowIndex(), rowIndexDefault);
final String columnIndex = defaultIfEmpty(modifyCellInputs.getColumnIndex(), columnIndexDefault);
final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);
final List<String> dataList = getDataList(newValue, columnIndexList, columnDelimiter);
incompleted = false;
final int modifyCellDataResult = modifyCellData(worksheet, rowIndexList, columnIndexList, dataList);
if (modifyCellDataResult != 0) {
//update formula cells
final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
for (Row row : worksheet) {
for (Cell cell : row) {
if (cell.getCellType() == CellType.FORMULA) {
evaluator.evaluateFormulaCell(cell);
}
}
}
updateWorkbook(excelDoc, excelFileName);
}
if (modifyCellDataResult == rowIndexList.size() && !incompleted) {
return getSuccessResultsMap(String.valueOf(modifyCellDataResult));
} else {
return getFailureResultsMap(String.valueOf(modifyCellDataResult));
}
} catch (Exception e) {
return getFailureResultsMap(e.getMessage());
}
}
/***
* 构建macro表cache，由于macro表大多数情况下都一样，所以加缓存优化
*/
HashMap<String, String> init_macro_with_cache(List<SchemeConf.DataInfo> src_list) {
LinkedList<HashMap<String, String>> data_filled = new LinkedList<HashMap<String, String>>();
IdentifyDescriptor column_ident = new IdentifyDescriptor();
// 枚举所有macro表信息
for (SchemeConf.DataInfo src : src_list) {
String file_path = "";
if (false == src.file_path.isEmpty()) {
file_path = src.file_path;
}
String fp_name = file_path + "/" + src.table_name;
// 优先读缓存
MacroFileCache res = macro_cache.cache.getOrDefault(fp_name, null);
if (null != res) {
if (res.file.file_path.equals(file_path) && res.file.table_name.equals(src.table_name)
&& res.file.data_row == src.data_row && res.file.data_col == src.data_col) {
data_filled.add(res.macros);
continue;
} else {
ProgramOptions.getLoger().warn(
"try to open macro source \"%s:%s\" (row=%d,col=%d) but already has cache \"%s:%s\" (row=%d,col=%d). the old macros will be covered",
file_path, src.table_name, src.data_row, src.data_col, res.file.file_path,
res.file.table_name, res.file.data_row, res.file.data_col);
}
}
res = new MacroFileCache(src, file_path);
if (file_path.isEmpty() || src.table_name.isEmpty() || src.data_col <= 0 || src.data_row <= 0) {
ProgramOptions.getLoger().warn("macro source \"%s\" (%s:%d，%d) ignored.", file_path, src.table_name,
src.data_row, src.data_col);
continue;
}
Sheet tb = ExcelEngine.openSheet(file_path, src.table_name);
if (null == tb) {
ProgramOptions.getLoger().warn("open macro source \"%s\" or sheet %s failed.", file_path,
src.table_name);
continue;
}
FormulaEvaluator evalor = tb.getWorkbook().getCreationHelper().createFormulaEvaluator();
int row_num = tb.getLastRowNum() + 1;
for (int i = src.data_row - 1; i < row_num; ++i) {
Row row = tb.getRow(i);
column_ident.index = src.data_col - 1;
DataContainer<String> data_cache = getStringCache("");
ExcelEngine.cell2s(data_cache, row, column_ident);
String key = data_cache.get();
column_ident.index = src.data_col;
data_cache = getStringCache("");
ExcelEngine.cell2s(data_cache, row, column_ident, evalor);
String val = data_cache.get();
if (null != key && null != val && !key.isEmpty() && !val.isEmpty()) {
if (res.macros.containsKey(key)) {
ProgramOptions.getLoger().warn("macro key \"%s\" is used more than once.", key);
}
res.macros.put(key, val);
}
}
macro_cache.cache.put(fp_name, res);
data_filled.add(res.macros);
}
// 空对象特殊处理
if (data_filled.isEmpty()) {
return macro_cache.empty;
}
// 只有一个macro项，则直接返回
if (1 == data_filled.size()) {
return data_filled.getFirst();
}
HashMap<String, String> ret = new HashMap<String, String>();
for (HashMap<String, String> copy_from : data_filled) {
ret.putAll(copy_from);
}
return ret;
}
/**
* 读取excel文件
* @param wb
* @param sheetIndex sheet页下标：从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
*/
public static ArrayList<Map<String,String>> readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row = null;
ArrayList<Map<String,String>> result = new ArrayList<Map<String,String>>();
for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {
row = sheet.getRow(i);
Map<String,String> map = new HashMap<String,String>();
for(Cell c : row) {
String returnStr = "";
boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//判断是否具有合并单元格
if(isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
// System.out.print(rs + "------ ");
returnStr = rs;
}else {
// System.out.print(c.getRichStringCellValue()+"++++ ");
returnStr = c.getRichStringCellValue().getString();
}
if(c.getColumnIndex()==0){
map.put("id",returnStr);
}else if(c.getColumnIndex()==1){
map.put("base",returnStr);
}else if(c.getColumnIndex()==2){
map.put("siteName",returnStr);
}else if(c.getColumnIndex()==3){
map.put("articleName",returnStr);
}else if(c.getColumnIndex()==4){
map.put("mediaName",returnStr);
}else if(c.getColumnIndex()==5){
map.put("mediaUrl",returnStr);
}else if(c.getColumnIndex()==6){
map.put("newsSource",returnStr);
}else if(c.getColumnIndex()==7){
map.put("isRecord",returnStr);
}else if(c.getColumnIndex()==8){
map.put("recordTime",returnStr);
}else if(c.getColumnIndex()==9){
map.put("remark",returnStr);
}
}
result.add(map);
// System.out.println();
}
return result;
}
private static String getRowIndex(final Sheet worksheet,
final int firstRow,
final String input,
final int columnIndex,
final String operator) {
String result = "";
double cellValueNumeric;
String cellFormat;
double inputNumeric = processValueInput(input);
for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
Row row = worksheet.getRow(i);
if (row == null) {
row = worksheet.createRow(i);
}
if (row != null) {
Cell cell = row.getCell(columnIndex);
if (cell == null) {
cell = row.createCell(columnIndex);
}
if (cell != null) {
CellType cellType = cell.getCellType();
if (cellType != CellType.ERROR) {
cellFormat = getCellType(cell);
//string comparison
if (cellFormat.equalsIgnoreCase("string") && inputFormat.equalsIgnoreCase("string")) {
DataFormatter aFormatter = new DataFormatter();
String aCellString = aFormatter.formatCellValue(cell);
if (compareStringValue(aCellString, input, operator)) {
result += i + ",";
}
}
//value input is empty, and the cell in the worksheet is in numeric type
else if (!cellFormat.equalsIgnoreCase(inputFormat))
//((cellType != CellType.STRING && inputFormat.equalsIgnoreCase("string"))||
//(cellType != CellType.NUMERIC && !inputFormat.equalsIgnoreCase("string")))
{
if (operator.equals("!=")) {
result += i + ",";
}
}
//numeric comparison
else if (cellType == CellType.NUMERIC && !inputFormat.equalsIgnoreCase("string")) {
cellValueNumeric = cell.getNumericCellValue();
//both are date or time
if ((cellFormat.equalsIgnoreCase("date") && inputFormat.equalsIgnoreCase("date")) ||
(cellFormat.equalsIgnoreCase("time") && inputFormat.equalsIgnoreCase("time")) ||
(cellFormat.equalsIgnoreCase("num") && inputFormat.equalsIgnoreCase("num"))) {
if (compareNumericValue(cellValueNumeric, inputNumeric, operator)) {
result += i + ",";
}
}
}
}
}
}
}
if (!result.isEmpty()) {
final int index = result.lastIndexOf(',');
result = result.substring(0, index);
}
return result;
}
protected Workbook makeWorkbook(String sheetName, List<List<Object>> rowSpecs){
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
for (List<Object> rowSpec : rowSpecs) {
int rowId = (sheet.getPhysicalNumberOfRows()==0)?0:sheet.getLastRowNum()+1;
Row row = sheet.createRow(rowId);
for (Object cellSpec : rowSpec) {
// Note that sheet.getLastRowNum() and row.getLastCellNum() do not behave alike
int cellId = (row.getPhysicalNumberOfCells()==0)?0:row.getLastCellNum();
if (cellSpec == null) {
row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
continue;
}
switch (cellSpec.getClass().getCanonicalName()) {
case "java.lang.Integer":
row.createCell(cellId).setCellValue((Integer)cellSpec);
break;
case "java.lang.String":
row.createCell(cellId).setCellValue((String)cellSpec);
break;
case "java.lang.Double":
row.createCell(cellId).setCellValue((Double)cellSpec);
break;
case "java.lang.Boolean":
row.createCell(cellId).setCellValue((Boolean)cellSpec);
break;
case "java.util.Date":
row.createCell(cellId).setCellValue((Date)cellSpec);
break;
case "java.util.Calendar":
row.createCell(cellId).setCellValue((Calendar)cellSpec);
break;
case "org.apache.poi.ss.formula.Formula":
row.createCell(cellId).setCellType(Cell.CELL_TYPE_FORMULA);
Cell cell = row.getCell(row.getLastCellNum());
cell.setCellFormula(((Formula)cellSpec).toString());
break;
default:
row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
}
}
}
return workbook;
}