import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
File file= FileUtils.getFile(path);
public String read2007Excel(File file) throws Exception {
if (file == null){
return null;
}
// 构造XSSFWorkBook对象
DecimalFormat df = new DecimalFormat("#");
List<String[]> list = new ArrayList<String[]>();
String question=null;
try {
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
// System.out.println("===SheetsNum===" + xwb.getNumberOfSheets());// 获取sheet数
int sheetSize = xwb.getNumberOfSheets();
for (int numSheets = 0; numSheets < sheetSize; numSheets++) {
if (null != xwb.getSheetAt(numSheets)) {
XSSFSheet aSheet = xwb.getSheetAt(numSheets);// 获得一个sheet
int j = 0;
Iterator<Row> rowIterator=aSheet.rowIterator();
while(rowIterator.hasNext()&&j<201){
XSSFRow aRow = (XSSFRow) rowIterator.next();
if(aRow!=null){
j++;
Iterator<Cell> cellIterator=aRow.cellIterator();
if(cellIterator.hasNext()){
String[] values = new String[4];
for (int cellNumOfRow = 0; cellNumOfRow <4; cellNumOfRow++) {
if (null != aRow.getCell(cellNumOfRow)) {
XSSFCell aCell = aRow.getCell(cellNumOfRow);
int cellType = aCell.getCellType();
String strCell = "";
switch (cellType) {
case XSSFCell.CELL_TYPE_NUMERIC:// Numeric
strCell = df.format(aCell
.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_STRING:// String
strCell = aCell.getStringCellValue();
break;
default:
strCell = "";
}
values[cellNumOfRow] = strCell;
} else {
values[cellNumOfRow] = "";
}
}
list.add(values);
}
}
}
}
}
List<MpmsVoiceContent> list5 = new ArrayList<MpmsVoiceContent>();
if(list.size()>201){
question =FileUtil.FALSE4;
return question;
}
if(list!=null&&list.size()>1){
for(int j=1;j<list.size();j++){
MpmsVoiceContent e = new MpmsVoiceContent();
int length =list.get(j).length;
if( length==1){
if(!StringUtils.isEmpty(list.get(j)[0])){
question =FileUtil.FALSE2;
return question;
}
else{
question =FileUtil.FALSE1;
return question;
}
}
else if(length==2){
if(StringUtils.isEmpty(list.get(j)[0])){
question =FileUtil.FALSE1;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0].trim()) && list.get(j)[0].trim().length()>400){
question =FileUtil.FALSE6;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0])&&StringUtils.isEmpty(list.get(j)[1])){
question =FileUtil.FALSE2;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0].trim()) && list.get(j)[1].trim().length()>400){
question =FileUtil.FALSE6;
return question;
}
e.setQuestion(list.get(j)[0]);
e.setAnswer1(list.get(j)[1]);
}
else if(length==3){
if(StringUtils.isEmpty(list.get(j)[0])){
question =FileUtil.FALSE1;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0].trim()) && list.get(j)[0].trim().length()>400){
question =FileUtil.FALSE6;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0])&&StringUtils.isEmpty(list.get(j)[1])&&StringUtils.isEmpty(list.get(j)[2])){
question =FileUtil.FALSE2;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0].trim())&&(list.get(j)[1].trim().length()>400 || list.get(j)[2].trim().length()>400)){
question =FileUtil.FALSE6;
return question;
}
e.setQuestion(list.get(j)[0]);
if(!StringUtils.isEmpty(list.get(j)[1])){
e.setAnswer1(list.get(j)[1]);
}
if(!StringUtils.isEmpty(list.get(j)[2])){
e.setAnswer2(list.get(j)[2]);
}
}
else if(length==4) {
if(StringUtils.isEmpty(list.get(j)[0])){
question =FileUtil.FALSE1;
return question;
}
if(list.get(j)[0].trim().length()>400){
question =FileUtil.FALSE6;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0])&&StringUtils.isEmpty(list.get(j)[1])&&StringUtils.isEmpty(list.get(j)[2])&&StringUtils.isEmpty(list.get(j)[3])){
question =FileUtil.FALSE2;
return question;
}
if(!StringUtils.isEmpty(list.get(j)[0].trim())&&(list.get(j)[1].trim().length()>400 || list.get(j)[2].trim().length()>400 || list.get(j)[3].trim().length()>400)){
question =FileUtil.FALSE6;
return question;
}
e.setQuestion(list.get(j)[0]);
if(!StringUtils.isEmpty(list.get(j)[1])){
e.setAnswer1(list.get(j)[1]);
}
if(!StringUtils.isEmpty(list.get(j)[2])){
e.setAnswer2(list.get(j)[2]);
}
if(!StringUtils.isEmpty(list.get(j)[3])){
e.setAnswer3(list.get(j)[3]);
}
}else{
question=FileUtil.FALSE5;
return question;
}
list5.add(e);
}
JSONArray jsonarray = JSONArray.fromObject(list5);
return jsonarray.toString();
}else{
question=FileUtil.FALSE0;
mpmsLogService.insertLogservice("Onekeymport",(short)1,"voice","parse Excel exception");
return question;
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}