POI:支持xls/xlsx文件格式按cell类型解析相关内容(exls 2003/2007 兼容)

2015-01-01 隐居地球

/**
 * POI:支持xls/xlsx文件格式按cell类型解析相关内容
 * 1.支持xls和xlsx文件格式的解析(exls 2003/2007 兼容)
 * 2.遍历sheet总数
 * 3.遍历row总数
 * 4.遍历cell总数
 * 5.可以判断常见数据类型
 * 6.日期格式化显示
 * @author Administrator
 * 
 */

只需要的jar包:poi-3.10.1-20140818.jar,poi-ooxml-3.10.1-20140818.jar,xmlbeans-2.6.0.jar,poi-ooxml-schemas-3.10.1-20140818.jar,dom4j-1.6.1.jar
官网POI下载地址:http://poi.apache.org/download.html
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.Test;


/**
 * POI:支持xls/xlsx文件格式按cell类型解析相关内容
 * 1.支持xls和xlsx文件格式的解析(exls 2003/2007 兼容)
 * 2.遍历sheet总数
 * 3.遍历row总数
 * 4.遍历cell总数
 * 5.可以判断常见数据类型
 * 6.日期格式化显示
 * @author Administrator
*
*/
public class MyPoiTest {
@Test
public void getMyXLS() {
//ArrayList<Map<String,Object>> xlsMapList = new ArrayList<Map<String,Object>>();
File xlsOrxlsxFile = new File("D:/upload_goods_asset_teml.xlsx");
if(!xlsOrxlsxFile.exists()){
return ;
}
try {
Workbook wb = WorkbookFactory.create(xlsOrxlsxFile);
int sheetNum = wb.getNumberOfSheets();
Sheet sheet = null;
for(int sheetIndex = 0;sheetIndex<sheetNum;sheetIndex++){//遍历sheet(index 0开始)
System.out.println("sheet:"+sheetIndex);
sheet = wb.getSheetAt(sheetIndex);
Row row = null;
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int rowIndex = firstRowNum;rowIndex<=lastRowNum;rowIndex++ ) {//遍历row(行 0开始)
row = sheet.getRow(rowIndex);
if(null != row){
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {//遍历cell(列 0开始)
Cell cell = row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL);
if (null != cell) {
Object cellValue = null;//cellValue的值
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue()
.getString());
cellValue = cell.getRichStringCellValue()
.getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
cellValue= cell.getDateCellValue();
//TODO 可以按日期格式转换
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
String time = formatter.format(cellValue);
System.out.println("formater time:"+time);
} else {
System.out.println(cell.getNumericCellValue());
cellValue=cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
cellValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
cellValue = cell.getCellFormula();
break;
default:
System.out.println("not find match type.");
}
System.out.println("value:"+cellValue);
} else {
//TODO cell is null 用 *** 代替输出
System.out.println("***");
}
}//end cell
}else{
//TODO row is null
}
}//end row
}//end sheet
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}