POI解析Excel

 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");

}

}