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>