package com.hw.importdata;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
public class ReadExcel {
private List<ElData> list=new ArrayList<ElData>();
public void readExcel(String filePath){
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
filePath));
// 创建工作簿
HSSFWorkbook workBook = new HSSFWorkbook(fs);
/**
* 获得Excel中工作表个数
*/
System.out.println("工作表个数 :" + workBook.getNumberOfSheets() );
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
// 创建工作表
HSSFSheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
if (rows > 0) {
sheet.getMargin(HSSFSheet.TopMargin);
for (int r = 0; r < rows; r++) { // 行循环
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum();// 获得列数
for (short c = 0; c < cells; c++) { // 列循环
HSSFCell cell = row.getCell(c);
if (cell != null) {
String value=getValue(cell);
System.out.println("第"+r+"行 "+"第"+c+"列:"+value);
}
}
}
}
}
//查询合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++){
System.out.println("第"+i+"个合并单元格");
Region region = sheet.getMergedRegionAt(i);
int row=region.getRowTo()-region.getRowFrom()+1;
int col=region.getColumnTo()-region.getColumnFrom()+1;
System.out.println("起始行:"+region.getRowFrom());
System.out.println("起始列:"+region.getRowTo());
System.out.println("所占行:"+row);
System.out.println("所占列:"+col);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
public String getValue(HSSFCell cell){
String value = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
if (HSSFDateUtil
.isCellDateFormatted(cell)) {
// 如果是date类型则 ,获取该cell的date值
value = HSSFDateUtil.getJavaDate(
cell.getNumericCellValue())
.toString();
} else {// 纯数字
value = String.valueOf(cell
.getNumericCellValue());
}
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;
}
public static void main(String args[]){
ReadExcel im=new ReadExcel();
im.readExcel("D:/平顶山谐波数据2008.12.xls");
}
}