Excel表格解析

//add by yangwenpei WGCW-144 使用Excel表格导入纸票记录 20161212 start
/**
* @param fileInputStream
* @param column 表示要获取的列数
* @return
* @throws ParseDataException
*/
public Map parseDra(FileInputStream fileInputStream,int column) throws ParseDataException{
Map map = new HashMap();
List lTraders = new ArrayList();
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fs = new POIFSFileSystem(fileInputStream);
wb = new HSSFWorkbook(fs);
}catch (IOException ioe) {
ioe.printStackTrace();
throw new ParseDataException(ioe.getMessage());
}catch(Exception e){
throw new ParseDataException("文件导入错误，请查看导入EXCEL版本（支持2003以下版本），文件内容是否与模板匹配");
} HSSFSheet sheet = wb.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();//获取表格总行数
int index = 1;
boolean boo = false;//结束for循环结束标记
StringBuffer messgae = new StringBuffer();//记录校验信息
List lists = new ArrayList();
try{
for (int j = 1; j < rows; j++) {
if (sheet.getRow(j) == null) {
throw new ParseDataException("导入文件格式不对，请按照模板的格式进行导入。");
}
// 如果编号为空
if (null == sheet.getRow(j).getCell((short) 0)
|| "".equals((sheet.getRow(j).getCell((short) 0).getStringCellValue().trim()))) {
messgae.append("第" + j + "行编号为空!<br>");
HSSFRow row = sheet.getRow(j);
int count = 0;
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {// 遍历表格的一行数据 if (row.getCell((short) i) == null
|| "".equals(row.getCell((short) i).getStringCellValue().trim())) {
count++;
}
if (row.getPhysicalNumberOfCells() == 0) {// 如果一行的数据全为null,结束当前循环
boo = true;
break;
}
if (count == row.getPhysicalNumberOfCells() && count > (column - 1) && (count == i + 1)
|| count == row.getPhysicalNumberOfCells()) {// 如果连续至少column行为空时结束外循环或者为空的数量的当前行的列数
boo = true;
break;
} }
}
if (boo) {// 判断是否结束循环
if (rows == 500) {
String errMsg = "批量导入上限不能超过500行!<br>";
messgae.append(errMsg);
throw new ParseDataException(errMsg);
}
break;
}
List list = getRowContent(sheet.getRow(j), column);// 解析一行的数据
lists.add(list);
index++;
}
map.put("message", messgae.toString());
}catch(ParseDataException e){
String errMsg = "EXCEL第" + index + "行，";
throw new ParseDataException(errMsg + e.getMessage());
}
map.put("lists", lists);
return map;
}
//add by yangwenpei WGCW-144 使用Excel表格导入纸票记录 20161212 end abstract protected Bill getData(SheetContent sheet , HSSFRow row);
//add by yangwenpei WGCW-144 使用Excel表格导入纸票记录 20161212 start
abstract protected List getRowContent(HSSFRow row,int column) throws ParseDataException;
//add by yangwenpei WGCW-144 使用Excel表格导入纸票记录 20161212 end