import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
public class Readtwo {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
"data.xls"));
HSSFWorkbook workBook = new HSSFWorkbook(fs);
System.out.println("工作表个数 :" + workBook.getNumberOfSheets());
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
System.out.println( i+ " ***************工作表名称:" + workBook.getSheetName(i)
+ " ************");
// 创建工作表
HSSFSheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
if (rows > 0) {
sheet.getMargin(HSSFSheet.TopMargin);
for (int j = 0; j < rows; j++) { // 行循环
HSSFRow row = sheet.getRow(j);
if (row != null) {
int cells = row.getLastCellNum();//获得列数
for (int k = 0; k < cells; k++) { // 列循环
HSSFCell cell = row.getCell(k);
// /////////////////////
if (cell != null) {
System.out.println("第"+j+"行,第"+k+"列值:"+readcell(cell));
}
}
}
}
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static String readcell(HSSFCell cell) {
String value = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是date类型则 ,获取该cell的date值
Date date = new Date() ;
date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat SDF=new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
value = SDF.format(date) ;
} else {// 纯数字
cell.setCellType (Cell.CELL_TYPE_STRING);
value = cell.getRichStringCellValue().toString();
//value = String.valueOf(cell.getNumericCellValue());//这会出现1.0 2.0的情况
}
break;
//此行表示单元格的内容为string类型
case HSSFCell.CELL_TYPE_STRING: // 字符串型
value = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_FORMULA:// 公式型
// 读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getRichStringCellValue().toString();
}
// cell.getCellFormula();读公式
break;
case HSSFCell.CELL_TYPE_BOOLEAN:// 布尔
value = " " + cell.getBooleanCellValue();
break;
//此行表示该单元格值为空
case HSSFCell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
value = "";
break;
default:
value = cell.getRichStringCellValue().toString();
}
return value;
}
}
评论