public List<String> getDatasInSheet(int sheetNumber,int colnum,int datanum,int colNums){
List<String> result = new ArrayList<String>();
//获得指定的sheet
HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
//获得sheet总行数
int rowCount = sheet.getLastRowNum();
if(rowCount < 1){
return result;
}
//遍历行row
HSSFRow row=null;
String pList="";
for (short rowIndex = (short)(datanum-1); rowIndex < rowCount; rowIndex++) {
//获得行对象
row = sheet.getRow(rowIndex);
int nullLen=0;
if(null != row){
pList="";
//获得本行中单元格的个数
int cellCount = colnum-1+colNums;//row.getLastCellNum();
//遍历列cell
HSSFCell cell=null;
String cellValue="";
for (int cellIndex = colnum-1; cellIndex < cellCount; cellIndex++) {
cell= row.getCell(cellIndex);
cellValue=this.getCellString(cell);
if(cellValue ==null|| "".equals(cellValue.trim())){
nullLen++;
cellValue="^^";
}
pList+=cellValue+",";
}
}else{
return result;
}
if(nullLen<colNums){
if(result.size()>5000){
//效率问题 太大list溢出
break;
}
result.add(pList);
}else{
//本行记录全部为空值 报异常
break;
}
}
return result;
}
private String getCellString(HSSFCell cell) {
// TODO Auto-generated method stub
String result = null;
if(cell==null){
return "";
}
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)){
result=sdf.format(cell.getDateCellValue());
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
result=cell.getStringCellValue();
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
result = "";
break;
default:
System.out.println("枚举了所有类型");
break;
}
return result;
}Excel
1 xxxxx系统故障月报
2
3部门: xxxxx 月份: 2012-08
4系统名称 故障类型 故障照片 使用人 使用时间 故障等级 备份 其他
5营销系统 无法登陆 阿杰 2114-56-65 2 达到 达到
6营销系统 无法登陆 阿杰 2114-56-65 2 达到 达到试试试试死死死死死死死死死死死死
sheet.getRow(4) 总为NULL 怎么解决
List<String> result = new ArrayList<String>();
//获得指定的sheet
HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
//获得sheet总行数
int rowCount = sheet.getLastRowNum();
if(rowCount < 1){
return result;
}
//遍历行row
HSSFRow row=null;
String pList="";
for (short rowIndex = (short)(datanum-1); rowIndex < rowCount; rowIndex++) {
//获得行对象
row = sheet.getRow(rowIndex);
int nullLen=0;
if(null != row){
pList="";
//获得本行中单元格的个数
int cellCount = colnum-1+colNums;//row.getLastCellNum();
//遍历列cell
HSSFCell cell=null;
String cellValue="";
for (int cellIndex = colnum-1; cellIndex < cellCount; cellIndex++) {
cell= row.getCell(cellIndex);
cellValue=this.getCellString(cell);
if(cellValue ==null|| "".equals(cellValue.trim())){
nullLen++;
cellValue="^^";
}
pList+=cellValue+",";
}
}else{
return result;
}
if(nullLen<colNums){
if(result.size()>5000){
//效率问题 太大list溢出
break;
}
result.add(pList);
}else{
//本行记录全部为空值 报异常
break;
}
}
return result;
}
private String getCellString(HSSFCell cell) {
// TODO Auto-generated method stub
String result = null;
if(cell==null){
return "";
}
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)){
result=sdf.format(cell.getDateCellValue());
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
result=cell.getStringCellValue();
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
result = "";
break;
default:
System.out.println("枚举了所有类型");
break;
}
return result;
}Excel
1 xxxxx系统故障月报
2
3部门: xxxxx 月份: 2012-08
4系统名称 故障类型 故障照片 使用人 使用时间 故障等级 备份 其他
5营销系统 无法登陆 阿杰 2114-56-65 2 达到 达到
6营销系统 无法登陆 阿杰 2114-56-65 2 达到 达到试试试试死死死死死死死死死死死死
sheet.getRow(4) 总为NULL 怎么解决
row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}没有用过的row就是null,null时要创建的。
在getRow后面按上面的代码改下,试试看。
row = sheet.getRow(rowIndex) 你这里使用的是Index
Num和Index应该不是等价的东西。另外看POI的文档:
public HSSFRow getRow(int rowIndex)
Returns the logical row (not physical) 0-based. If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet.
返回以0为第一个索引的逻辑行(非物理行)。如果你要获得一个没有定义的行那么将得到null(应该是你那个所谓的第4行没有定义的缘故)。当然第四行代表表格的第五行