Biegral Blog

package excel;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class testExcel {
	private XSSFWorkbook xssfWorkbook;
	private HSSFWorkbook hSSFWorkbook;
	public List<List<String>> readXls(String path) throws Exception{
		InputStream is = new FileInputStream(path);
		hSSFWorkbook = new HSSFWorkbook(is);
		List<List<String>> result = new ArrayList<List<String>>();
		//循环每一页，并处理当前循环页
		for(int numSheet=0;numSheet<hSSFWorkbook.getNumberOfSheets();numSheet++){
			//这里的HSSFSheet表示某一页
			HSSFSheet hssfSheet = hSSFWorkbook.getSheetAt(numSheet);
			if(hssfSheet == null){
				continue;
			}
			//处理当前页，循环读取每一行
			for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
				//HSSFRow表示行
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				int minColIx = hssfRow.getFirstCellNum();
				int maxColIx = hssfRow.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				//遍历该行，获取处理每个cell元素
				for(int colIx =minColIx;colIx<maxColIx;colIx++){
					//HSSFCell表示单元格
					HSSFCell cell = hssfRow.getCell(colIx);
					if(cell==null){
						continue;
					}
					rowList.add(getStringVal(cell));
				}
				result.add(rowList);
			}
		}
		return result;
	}
	public List<List<String>> readXlsx(String path) throws Exception{
		InputStream is = new FileInputStream(path);
		xssfWorkbook = new XSSFWorkbook(is);
		List<List<String>> result = new ArrayList<List<String>>();
		//循环每一页，并处理当前循环页
		for(XSSFSheet xssfSheet : xssfWorkbook){
			if(xssfSheet == null){
				continue;
			}
			//处理当前页，循环读取每一行
			for(int rowNum=1;rowNum<=xssfSheet.getLastRowNum();rowNum++){
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				int minColIx = xssfRow.getFirstCellNum();
				int maxColIx = xssfRow.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				//遍历该行，获取处理每个cell元素
				for(int colIx =minColIx;colIx<maxColIx;colIx++){
					//HSSFCell表示单元格
					XSSFCell cell = xssfRow.getCell(colIx);
					if(cell==null){
						continue;
					}
//					rowList.add(cell.toString());
					rowList.add(getStringVal(cell));
				}
				result.add(rowList);
			}
		}
		return result;
	}
	
	 public String getStringVal(Cell cell){
     	switch(cell.getCellType()){
     		case Cell.CELL_TYPE_BOOLEAN:
     			return cell.getBooleanCellValue()?"TRUE":"FALSE";
     		case Cell.CELL_TYPE_FORMULA:
     			return  cell.getCellFormula();
     		case Cell.CELL_TYPE_NUMERIC:
     			if (DateUtil.isCellDateFormatted(cell)) {
         			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
         			return sdf.format(cell.getDateCellValue());
         		}
     			cell.setCellType(Cell.CELL_TYPE_STRING);;
     		case Cell.CELL_TYPE_STRING:
     			return cell.getStringCellValue();
     		default:
     			return "";
     	}
     }
	 /*
	  * 此方法与上方法对比发现是重载关系，此方法中的参数实现了上一方法的接口类型的参数，这两个方法不糊冲突
	  * 实际使用的时候回自动选择
	  */
     public String getStringVal(XSSFCell cell){
     	switch(cell.getCellType()){
     	case Cell.CELL_TYPE_BOOLEAN:
     		return cell.getBooleanCellValue()?"TRUE":"FALSE";
     	case Cell.CELL_TYPE_FORMULA:
     		return  cell.getCellFormula();
     	case Cell.CELL_TYPE_NUMERIC:
     		if (DateUtil.isCellDateFormatted(cell)) {
     			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
     			return sdf.format(cell.getDateCellValue());
     		}
     		cell.setCellType(Cell.CELL_TYPE_STRING);
     	case Cell.CELL_TYPE_STRING:
     		return cell.getStringCellValue();
     	default:
     		return "";
     	}
     }
	
	public static void main(String[] args) {
		testExcel t = new testExcel();
		try {
			List<List<String>> list = t.readXlsx("E:\\test\\excel\\test.xlsx");
			System.out.println(list);
			List<List<String>> list1 = t.readXls("E:\\test\\excel\\test.xls");
			System.out.println(list1);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}