packageedu.sjtu.erplab.poi;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.HashMap;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;/*** 操作Excel表格的功能类*/
public classExcelReader {privatePOIFSFileSystem fs;privateHSSFWorkbook wb;privateHSSFSheet sheet;privateHSSFRow row;/*** 读取Excel表格表头的内容
*@paramInputStream
*@returnString 表头内容的数组*/
publicString[] readExcelTitle(InputStream is) {try{
fs= newPOIFSFileSystem(is);
wb= newHSSFWorkbook(fs);
}catch(IOException e) {
e.printStackTrace();
}
sheet= wb.getSheetAt(0);
row= sheet.getRow(0);//标题总列数
int colNum =row.getPhysicalNumberOfCells();
System.out.println("colNum:" +colNum);
String[] title= newString[colNum];for (int i = 0; i < colNum; i++) {//title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}returntitle;
}/*** 读取Excel数据内容
*@paramInputStream
*@returnMap 包含单元格数据内容的Map对象*/
public MapreadExcelContent(InputStream is) {
Map content = new HashMap();
String str= "";try{
fs= newPOIFSFileSystem(is);
wb= newHSSFWorkbook(fs);
}catch(IOException e) {
e.printStackTrace();
}
sheet= wb.getSheetAt(0);//得到总行数
int rowNum =sheet.getLastRowNum();
row= sheet.getRow(0);int colNum =row.getPhysicalNumberOfCells();//正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row=sheet.getRow(i);int j = 0;while (j
str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
j++;
}
content.put(i, str);
str= "";
}returncontent;
}/*** 获取单元格数据内容为字符串类型的数据
*
*@paramcell Excel单元格
*@returnString 单元格数据内容*/
privateString getStringCellValue(HSSFCell cell) {
String strCell= "";switch(cell.getCellType()) {caseHSSFCell.CELL_TYPE_STRING:
strCell=cell.getStringCellValue();break;caseHSSFCell.CELL_TYPE_NUMERIC:
strCell=String.valueOf(cell.getNumericCellValue());break;caseHSSFCell.CELL_TYPE_BOOLEAN:
strCell=String.valueOf(cell.getBooleanCellValue());break;caseHSSFCell.CELL_TYPE_BLANK:
strCell= "";break;default:
strCell= "";break;
}if (strCell.equals("") || strCell == null) {return "";
}if (cell == null) {return "";
}returnstrCell;
}/*** 获取单元格数据内容为日期类型的数据
*
*@paramcell
* Excel单元格
*@returnString 单元格数据内容*/
privateString getDateCellValue(HSSFCell cell) {
String result= "";try{int cellType =cell.getCellType();if (cellType ==HSSFCell.CELL_TYPE_NUMERIC) {
Date date=cell.getDateCellValue();
result= (date.getYear() + 1900) + "-" + (date.getMonth() + 1)+ "-" +date.getDate();
}else if (cellType ==HSSFCell.CELL_TYPE_STRING) {
String date=getStringCellValue(cell);
result= date.replaceAll("[年月]", "-").replace("日", "").trim();
}else if (cellType ==HSSFCell.CELL_TYPE_BLANK) {
result= "";
}
}catch(Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}returnresult;
}/*** 根据HSSFCell类型设置数据
*@paramcell
*@return
*/
privateString getCellFormatValue(HSSFCell cell) {
String cellvalue= "";if (cell != null) {//判断当前Cell的Type
switch(cell.getCellType()) {//如果当前Cell的Type为NUMERIC
caseHSSFCell.CELL_TYPE_NUMERIC:caseHSSFCell.CELL_TYPE_FORMULA: {//判断当前的cell是否为Date
if(HSSFDateUtil.isCellDateFormatted(cell)) {//如果是Date类型则,转化为Data格式//方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00//cellvalue = cell.getDateCellValue().toLocaleString();//方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date =cell.getDateCellValue();
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
cellvalue=sdf.format(date);
}//如果是纯数字
else{//取得当前Cell的数值
cellvalue =String.valueOf(cell.getNumericCellValue());
}break;
}//如果当前Cell的Type为STRIN
caseHSSFCell.CELL_TYPE_STRING://取得当前的Cell字符串
cellvalue =cell.getRichStringCellValue().getString();break;//默认的Cell值
default:
cellvalue= " ";
}
}else{
cellvalue= "";
}returncellvalue;
}public static voidmain(String[] args) {try{//对读取Excel表格标题测试
InputStream is = new FileInputStream("d:\\test2.xls");
ExcelReader excelReader= newExcelReader();
String[] title=excelReader.readExcelTitle(is);
System.out.println("获得Excel表格的标题:");for(String s : title) {
System.out.print(s+ " ");
}//对读取Excel表格内容测试
InputStream is2 = new FileInputStream("d:\\test2.xls");
Map map =excelReader.readExcelContent(is2);
System.out.println("获得Excel表格的内容:");for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
}
}catch(FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
}
}