导入Excel工具（java公用工具）示例

1、编写辅助类

package xxxx.xx;

import java.io.Serializable;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.Element;
import org.jdom.Document;
import org.jdom.input.SAXBuilder;

/**
 * 辅助类
 * @author zhanghao
 */
public class ParserHelper implements Serializable{
	private String fileName = "exg-file-config.xml";  //Excel结构配置文件
	//可纳入spring管理，通过spring注入
	public void setFileName(String fileName){
		this.fileName = fileName;
	}

	private HashMap map = new HashMap();
	protected Log log ;
	
	/**
	 * 默认构造函数
	 */
	public ParserHelper(){
		
	}    
	
	/**
	* 初始化
	* 纳入spring管理，可指定初始化方法
	* 如：
	*  	<bean name="parserHelper" class="com.xxx.xxx.ParserHelper" init-method="initConfig">	
	*		<property name="fileName"><value>exg-file-config.xml</value></property>
	*	</bean>
	*/
	public void initConfig(){
		SAXBuilder sb = new SAXBuilder(); // 新建立构造器
		InputStream in = getClass().getClassLoader().getResourceAsStream(fileName);
		log.debug("in = " + in);
		Document doc = null;
		try{
			doc = sb.build(in); 
		}catch(Exception e){
			e.printStackTrace();
		}	
		Element root = doc.getRootElement();
		parseRoot(root); //解析
	}	
	/**
	* 解析xml，初始化map
	*/
	protected void parseRoot(Element root){
		java.util.List list = root.getChildren();
		for(int i = 0 ; i < list.size() ; i++){
			Element element = (Element)list.get(i);
			String functionNo  = element.getAttributeValue("no");
			log.debug("functionNo=" + functionNo);
			if(functionNo == null){
				continue;
			}else{
				map.put(functionNo , buildSheetContent(functionNo , element));
			}
		}
	}
	
	/**
	* 构建预定义结构
	* 包含内容：sheet页--》index   开始行--> row-start  开始列--> column-start
	*/
	protected SheetContent buildSheetContent(String key , Element element){
		SheetContent sc = new SheetContent();
		Element sheet = element.getChild("sheet");
		sc.setIndex(Integer.parseInt(sheet.getAttributeValue("index")));
		log.debug(">> : index=" + sc.getIndex());
		sc.setRowStart(Integer.parseInt(sheet.getAttributeValue("row-start")));
		log.debug(">> : row-start=" + sc.getColStart());
		sc.setColStart(Integer.parseInt(sheet.getAttributeValue("column-start")));
		log.debug(">> : column-start=" + sc.getRowStart());		
		List children = sheet.getChildren("column");
		String[] columns = new String[children.size()];
		log.debug("column count=" + children.size());
		for(int i = 0 ; i < children.size() ; i++){
			columns[i] = ((Element)children.get(i)).getTextTrim();
			log.debug(">> : column=" + columns[i]);
		}
		sc.setColumn(columns);
		return sc;
	}	

	
	/**
	 * 取得【配置文件中某一结构的所有[属性名称/字段名称]】
	 */
	public SheetContent getSheetContent(int functionNo){
		return (SheetContent)map.get("" + functionNo);
	}

	
}

package com.nstc.dpms.server.exg;

import java.io.Serializable;

/**
 * excel结构封装
 */
public class SheetContent implements Serializable{
	private int index = 0;
	private int rowStart = 1;  //开始行
	private int colStart = 1;  //开始列
	
	private String[] column = null; //映射列(属性)名称集合
	
	public SheetContent(){
		
	}

	
	public int getColStart() {
		return colStart;
	}

	
	public void setColStart(int colStart) {
		this.colStart = colStart;
	}

	
	public String[] getColumn() {
		return column;
	}

	
	public void setColumn(String[] column) {
		this.column = column;
	}

	
	public int getIndex() {
		return index;
	}

	
	public void setIndex(int index) {
		this.index = index;
	}

	
	public int getRowStart() {
		return rowStart;
	}

	
	public void setRowStart(int rowStart) {
		this.rowStart = rowStart;
	}	
	
}

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

import xxxxx.ParseDataException;
/**
 * 文件解析器
 * @author zhanghao
 */
public interface FileParser {
	
	public List parse(int functionNo , FileInputStream inputStream) 
			throws ParseDataException;
	
}


/**
 * Excel文件解析器
 * @author zhanghao
 */
public abstract class AbstractFileParser implements FileParser {
	
	/**
	 * 默认构造函数
	 */
	public AbstractFileParser(){

	}

	protected ParserHelper parserHelper;

	/**
	 * 实现FileParser接口方法
	 */
	public List parse(int functionNo , FileInputStream inputStream) throws ParseDataException{
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		try{
			fs = new POIFSFileSystem(inputStream);
			wb = new HSSFWorkbook(fs);
		}catch(IOException ioe){
			ioe.printStackTrace();
			throw new ParseDataException(ioe.getMessage());
		}catch(RecordFormatException rfe){
			rfe.printStackTrace();
			throw new ParseDataException("不支持excel的高级功能，请禁用筛选等功能！");
		}
		//取得 导入Excel文件的配置文件中的预设结果
		SheetContent sc = parserHelper.getSheetContent(functionNo);
		//取得  预设文件中指定sheet页
		HSSFSheet sheet = wb.getSheetAt(sc.getIndex());

		ArrayList list = new ArrayList();
		int rows  = sheet.getPhysicalNumberOfRows();//取得所有行
		
		if(log.isDebugEnabled()){
			log.debug("\trows=" + rows);
		}

		try{
			//rows 是从1开始 与java数组不同
			for(int j = sc.getRowStart(); j < rows ; j++){ 				
				if( null == sheet.getRow(j).getCell( (short) 0 ) ){
					break ;
				}
				String index = null;
				if(HSSFCell.CELL_TYPE_NUMERIC == sheet.getRow(j).getCell( (short) 0 ).getCellType()){
					index = String.valueOf(sheet.getRow(j).getCell( (short) 0 ).getNumericCellValue());
				}else if(HSSFCell.CELL_TYPE_STRING == sheet.getRow(j).getCell( (short) 0 ).getCellType()){
					index = sheet.getRow(j).getCell( (short) 0 ).getStringCellValue();
				}
				if( null == index || "".equals(index)){
					break;
				}
				/*
				* 根据传入的参数（结构、一个row）, 构建对象
				* 在使用可以添加参数来修改
				*/
				XXXXXXentity view = getData(sc , sheet.getRow(j));				
				list.add(view);				
			}
		}catch(Exception e){
			throw new ParseDataException(e.getMessage());
		}

		return list;
	}

	//解析返回对象
	abstract protected XXXXXXentity getData(SheetContent sheet , HSSFRow row);
}

import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
/**
* 构造器实现
*/
public class ExcelParser extends AbstractFileParser {

	/**
	 * 默认构造函数
	 */
	public ExcelParser(){
	}

	protected BillViewPay getData(SheetContent sheet , HSSFRow row){
		String fieldName = null;
		ImportBillData view = new ImportBillData();
		//组装对象
		BeanWrapper wrapper = new BeanWrapperImpl(view);
		for(short i = (short)sheet.getColStart() ; i < sheet.getColumn().length + sheet.getColStart(); i++){
			HSSFCell cell  = row.getCell(i);
			fieldName = sheet.getColumn()[i - sheet.getColStart()];
			if("disabled".equals(fieldName)){
				continue;
			}
			String value = null;
			if (cell != null) {
				switch (cell.getCellType()) {
                	case HSSFCell.CELL_TYPE_NUMERIC :
                		value = format(cell.getNumericCellValue());
                		break;
                	case HSSFCell.CELL_TYPE_STRING :
                		value = cell.getStringCellValue();
                		if(null != value){
                			value = value.toString().trim();
                		}
                		break;
                	default :
				}
				if(null != value){
					//给对象指定属性名附值
					wrapper.setPropertyValue(fieldName , value);
				}
			}
			if(log.isDebugEnabled())
				log.debug("excel data : " + i + " >> " + fieldName + " = "  + value);
		}
		return view;
	}	

}

2、配置文件（模板文件）

<?xml version = "1.0" encoding = "GB2312"?>
<exg name="bms" version="300" file-type="excel">
	<function no="1"> <!--土地信息-->
		<sheet index="0" row-start="1" column-start="1">
			<column offset="1">billTypeByName</column>  			
			<column offset="2">billStyleByName</column>  			
			<column offset="3">payName</column>						
			<column offset="4">payNo</column>						
			<column offset="5">payBank</column>						
			<column offset="6">recName</column>						
			<column offset="7">recNo</column>						
			<column offset="8">recBankNo</column>				   
			<column offset="9">recBank</column>					    
			<column offset="10">accptrName</column>					
			<column offset="11">accptrNo</column>					
			<column offset="12">accptrBank</column>					
			<column offset="13">accptrBankNo</column>				
			<column offset="14">accptrBankAdr</column>				
			<column offset="15">startDay</column>					
			<column offset="16">endDay</column>						
			<column offset="17">amount</column>						
			<column offset="18">payFactor.deposetAmount</column>    
		</sheet>
	</function>
	<function no="2"> <!--租借信息-->
		<sheet index="0" row-start="1" column-start="1">
			<column offset="1">billTypeByName</column>  			
			<column offset="2">billStyleByName</column>  			
			<column offset="3">payName</column>						
			<column offset="4">payNo</column>						
			<column offset="5">payBank</column>						
			<column offset="6">recName</column>						
			<column offset="7">recNo</column>						
			<column offset="8">recBankNo</column>				   
			<column offset="9">recBank</column>					    
			<column offset="10">accptrName</column>					
			<column offset="11">accptrNo</column>					
			<column offset="12">accptrBank</column>					
			<column offset="13">accptrBankNo</column>				
			<column offset="14">accptrBankAdr</column>				
			<column offset="15">startDay</column>					
			<column offset="16">endDay</column>						
			<column offset="17">amount</column>						
			<column offset="18">payFactor.deposetAmount</column>    
		</sheet>
	</function>
</exg>

3、spring配置，指定加载时调用初始化方法

	*  	<bean name="parserHelper" class="com.xxx.xxx.ParserHelper" init-method="initConfig">	
	*		<property name="fileName"><value>exg-file-config.xml</value></property>
	*	</bean>

评论

1 楼 chenghong726 2017-01-22  

有完整代码吗？