excel相关系列(2)---解析excel文档

这些都是调用的PIO对java提供的插件,PIO相关资料可以查看:http://blog.youkuaiyun.com/jinchaomail/article/details/50616887

excel有两种后缀类型:.xls和.xlsx,下面是对两种的解析,也提供了对不同数据类型读取的一个方法

文档结构系列一就有

首先是对xls类型的处理

package analyzeexcel;

import java.io.FileInputStream;
import java.io.InputStream;
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;

public class AnalyzaXls {
   private List<List<String>> readXls(String path) throws Exception{
	   InputStream is=new FileInputStream(path);
	   HSSFWorkbook workbook =new HSSFWorkbook(is);
	   List<List<String>> ret=new ArrayList<List<String>>();
       for(int numSheet=0;numSheet<workbook.getNumberOfSheets();numSheet++){
    	   HSSFSheet sheet=workbook.getSheetAt(numSheet);
    	   if(sheet==null){
    		  continue; 
    	   }
    	   for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++){
    		   HSSFRow row=sheet.getRow(rowNum);
    		   int minColIx=row.getFirstCellNum();
    		   int maxColIx=row.getLastCellNum();
    		   List<String> rowList=new ArrayList<String>();
    		   for(int colIx=minColIx;colIx<maxColIx;colIx++){
    			   HSSFCell cell=row.getCell(colIx);
    			   if(cell==null){
    				   continue;
    			   }
    			   rowList.add(cell.toString());
    		   }
    		   ret.add(rowList);
    	   }
       }
       return ret;
   
   }
}

对于另一种的xlsx类型的处理,很相似

package analyzeexcel;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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 AnalyzeXlsx {
	private List<List<String>> readXlsx(String path)throws Exception{
		InputStream is=new FileInputStream(path);
		XSSFWorkbook workbook=new XSSFWorkbook(is);
		List<List<String>> ret=new ArrayList<List<String>>();
		for(int numSheet=0;numSheet<workbook.getNumberOfSheets();numSheet++){
	    	   XSSFSheet sheet=workbook.getSheetAt(numSheet);
			 if(sheet==null){
	    		  continue; 
	    	   }
	    	   for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++){
	    		   XSSFRow row=sheet.getRow(rowNum);
	    		   int minColIx=row.getFirstCellNum();
	    		   int maxColIx=row.getLastCellNum();
	    		   List<String> rowList=new ArrayList<String>();
	    		   for(int colIx=minColIx;colIx<maxColIx;colIx++){
	    			   XSSFCell cell=row.getCell(colIx);
	    			   if(cell==null){
	    				   continue;
	    			   }
	    			   rowList.add(cell.toString());
	    		   }
	    		   ret.add(rowList);
	    	   }
	       }
	       return ret;
		
	}
	public static void main(String[] args) {
		AnalyzeXlsx temp=new AnalyzeXlsx();
		List<List<String>> ret;
		try {
			ret = temp.readXlsx("E:/ѧϰ/dang/��Ա.xlsx");
			for(List<String> list :ret){
				for(String str : list){
					System.out.print(str+"\t");
				}
				System.out.println();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

可能得到的数据类型不让自己满意,可以自己写一个调用数据处理的方法

package analyzeexcel;

import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.util.LocaleUtil;

public class ExcelUtils {

	public static String getStringVal(HSSFCell cell) {
		switch (cell.getCellTypeEnum()) {
		case BLANK:
			return "";
		case BOOLEAN:
			return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
		case ERROR:
			return "";
		case FORMULA:
			try {
				return String.valueOf(cell.getNumericCellValue());
			} catch (IllegalStateException e) {
				return String.valueOf(cell.getRichStringCellValue());
			}
		case NUMERIC:
			
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy",
						LocaleUtil.getUserLocale());
				sdf.setTimeZone(LocaleUtil.getUserTimeZone());
				return sdf.format(cell.getDateCellValue());
			} else {
				double value = cell.getNumericCellValue();
				int intValue = (int) value;
				return value - intValue == 0 ? String.valueOf(intValue)
						: String.valueOf(value);

			}

		case STRING:
			return cell.getStringCellValue();
		default:
			return cell.toString().trim();

		}
	}
}