Excel 解析2003/2007工具类 （java）

Excel 解析2003/2007工具类  （java）

 

1：工具类

package com.kingdee.eas.custom.ssc.hrp.common;

import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileNotFoundException;  
import java.io.IOException;  
import java.text.DecimalFormat;  
import java.text.SimpleDateFormat;  
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;  
import java.util.List;  
import java.util.Map;

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


/**
 * 谈荣涛 2018/06/22
 * 说明：该类中有三个方法，对外提供的读取excel文件的方法，和两个私有的分别读取excel2003和excel2007的方法。
 * 外部使用，只需调用readExcel 方法，传入一个File 参数，程序根据文件扩展名来判断选取那个方法来读取Excel文件。
 */
public class ReadExcel {


	/** 
	 * 对外提供读取excel 的方法 
	 * file Excel 
	 * 
	 * */  
	public static List<Map<String, Object>> readExcel(File file, String[] columnKeys) throws IOException{  
		String fileName = file.getName();  
		String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);  
		if("xls".equals(extension)){  
			return readExcel2003(file,columnKeys);  
		}else if("xlsx".equals(extension)){  
			return readExcel2007(file,columnKeys);   
		}else{  
			throw new IOException("不支持的文件类型");  
		}
	}  
	
	
	/** 
	 * 读取 office 2003 excel 
	 * @param columnKeys 
	 * @throws IOException  
	 * @throws FileNotFoundException */  
	private static List<Map<String, Object>> readExcel2003(File file, String[] columnKeys) throws IOException{  
		List<Map<String, Object>> list = new LinkedList<Map<String, Object>>();  
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));  
		HSSFSheet sheet = hwb.getSheetAt(0);  
		HSSFRow row = null;  

		for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){  
			Map<String, Object> rowMap = new HashMap<String, Object>();
			row = sheet.getRow(i);  
			if (row == null) {  
				continue;  
			}  
			
			for (int j=0;j<columnKeys.length;j++) {   
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符  
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
				Object value = null;
				
				HSSFCell cell = row.getCell(j); 
				if (cell == null) {  
					rowMap.put(columnKeys [j], ""); 
					continue;  
				}
				switch (cell.getCellType()) {  
				case XSSFCell.CELL_TYPE_STRING:  
					System.out.println(i+"行"+j+" 列 is String type");  
					value = cell.getStringCellValue();  
					break;  
				case XSSFCell.CELL_TYPE_NUMERIC:  
					System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());  
					if("@".equals(cell.getCellStyle().getDataFormatString())){  
						value = df.format(cell.getNumericCellValue());  
					} else if("General".equals(cell.getCellStyle().getDataFormatString())){  
						value = nf.format(cell.getNumericCellValue());  
					}else{  
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
					}  
					break;  
				case XSSFCell.CELL_TYPE_BOOLEAN:  
					System.out.println(i+"行"+j+" 列 is Boolean type");  
					value = cell.getBooleanCellValue();  
					break;  
				case XSSFCell.CELL_TYPE_BLANK:  
					System.out.println(i+"行"+j+" 列 is Blank type");  
					value = "";  
					break;  
				default:  
					System.out.println(i+"行"+j+" 列 is default type");  
					value = cell.toString();  
				}  
				rowMap.put(columnKeys [j], value);  
			}  
			list.add(rowMap);   
		}  
		return list;  
	}  
	
	
	/** 
	 * 读取Office 2007 excel 
	 * */  
	private static List<Map<String, Object>> readExcel2007(File file,String[] columnKeys) throws IOException {
		
		List<Map<String, Object>> list = new LinkedList<Map<String, Object>>(); 
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径  
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));  
		// 读取第一个页签表格内容  
		XSSFSheet sheet = xwb.getSheetAt(0);  
		XSSFRow row = null;  
		
		for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {  
			Map<String, Object> rowMap = new HashMap<String, Object>();
			row = sheet.getRow(i);  
			if (row == null) {  
				continue;  
			}  
			
			for (int j=0;j<columnKeys.length;j++) {  
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符  
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
				Object value = null; 

				XSSFCell cell = row.getCell(j);  
				if (cell == null) {  
					rowMap.put(columnKeys [j], ""); 
					continue;  
				}
				switch (cell.getCellType()) {  
				case XSSFCell.CELL_TYPE_STRING:  
					System.out.println(i+"行"+j+" 列 is String type");  
					value = cell.getStringCellValue();  
					break;  
				case XSSFCell.CELL_TYPE_NUMERIC:  
					System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());  
					if("@".equals(cell.getCellStyle().getDataFormatString())){  
						value = df.format(cell.getNumericCellValue());  
					} else if("General".equals(cell.getCellStyle().getDataFormatString())){  
						value = nf.format(cell.getNumericCellValue());  
					}else{  
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
					}  
					break;  
				case XSSFCell.CELL_TYPE_BOOLEAN:  
					System.out.println(i+"行"+j+" 列 is Boolean type");  
					value = cell.getBooleanCellValue();  
					break;  
				case XSSFCell.CELL_TYPE_BLANK:  
					System.out.println(i+"行"+j+" 列 is Blank type");  
					value = "";  
					break;  
				default:  
					System.out.println(i+"行"+j+" 列 is default type");  
					value = cell.toString();  
				}  
				rowMap.put(columnKeys [j], value);  
			}  
			list.add(rowMap);
		}  
		return list;  
	} 
	

}


 

2：调用案例