private File testExcel;
/** 上传的EXCEL文件的名称 */
private String testExcelFileName;
/*读取excel文件*/
is = new FileInputStream(activationRecordExcel);
/**
* 读取Excel数据内容
*
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public static List<RecordExcelDto> readExcelContent(InputStream is, String type) {
List<RecordExcelDto> recordExcelDtoList= new ArrayList<RecordExcelDto>();
try {
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
HSSFRow row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
RecordExcelDto recordExcelDto= new RecordExcelDto();
row = sheet.getRow(i);
if (null != row) {
if (colNum >= 1) {
Long id = Long.valueOf(getCellFormatValue(row.getCell(0)));
recordExcelDto.setId(id);
}
if (colNum >= 2) {
String mobile = getCellFormatValue(row.getCell(1));
recordExcelDto.setMobile(mobile);
}
if ("CPA".equals(type)) {
if (colNum >= 3) {
Date createTime = getCellFormatDateValue(row.getCell(2));
recordExcelDto.setCreateTime(createTime);
}
} else {// CPA+首投 或CPS
if (colNum >= 3) {
Date tradeTime = getCellFormatDateValue(row.getCell(2));
recordExcelDto.setTradeTime(tradeTime);
}
}
if (colNum >= 4) {
String institutionalRuleName = getCellFormatValue(row.getCell(3));
activationRecordExcelDto.setInstitutionalRuleName(institutionalRuleName);
}
if (colNum >= 5) {
Date banlanceTime = getCellFormatDateValue(row.getCell(4));
if (banlanceTime == null) {
recordExcelDto.setBalanceTime(new Date());
} else {
recordExcelDto.setBalanceTime(banlanceTime);
}
}
if (colNum >= 6) {
String status = getCellFormatValue(row.getCell(5));
if ("未结算".equals(status)) {
recordExcelDto.setCostStatus((byte) 0);
} else if ("结算成功".equals(status)) {
recordExcelDto.setCostStatus((byte) 1);
} else {// 未抓取到相关字段,不修改状态
recordExcelDto.setCostStatus((byte) -1);
}
}
recordExcelDtoList.add(recordExcelDto);
} else {
break;
}
}
} catch (IOException e) {
logger.error("读取Excel文件异常!", e);
} catch (Exception e) {
logger.error("读取Excel文件异常!", e);
}
return recordExcelDtoList;
}
/**
* 根据HSSFCell类型设置数据
*
* @param cell
* @return
*/
public static String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cellvalue = cell.getStringCellValue();
}
return cellvalue;
}
/**
* 根据HSSFCell类型读取日期格式
*/
public static Date getCellFormatDateValue(HSSFCell cell) {
Date date = null;
if (HSSFDateUtil.isCellDateFormatted(cell)) {
date = cell.getDateCellValue();
}
return date;
}