（Java）运用JXL包读取Excel文件

这周因为需要向数据库中导入数据，有多个文件，就想将读Excel文件的功能抽象出来，形成一个单独的组件，目前进展到还好，通过修改配置文件并建立相应的类，就能将Excel数据读到List中，不过针对的比较规正的文件，但是关于日志处理、异常处理做的不好，还没有想到一个比较好的解决办法，还请各位指教。

用法是：用户建立和Excel文件相对应VO类，例如Excel中的就是一个类，而Excel中的列就是该类的属性，VO类中提供该Excel列所应该的属性名并且提供getter和setter方法，此外，还要建立一个XMl文件，该文件中包括excel文件所在的位置，与该文件对应的VO类，sheet索引值，及每一个列与VO之间的对应关系，当用户建立完这两个文件之后，可以调用相就的API，那么就可以将Excel中的数据读到一个List<VO>中去。思路是：创建配置文件的类Configuration，完成用户所写的XML文件的读取并配置，在读Excel的过程，程序通过配置信息找到Excel文件及对应的类，列与类中属性的对应关系，通过反射的形式将Excel读出的数据set到对应的VO类中。

项目结果如下：

 

下面对包做个简单说明，demo是自己写的事例，excel主要是关于workbook和sheet的操作，exception是异常处理，暂时未用，read记Excel的操作，reflect反射，vo业务类，XML是XML操作类及XML Schmea文件，用于验证XML文件。

首先看Configuration这个类的代码

/**
 * Configeration.java
 * 2011-7-10 上午08:39:29
 */
package com.wds.excel;

import java.io.File;
import java.util.Map;

import org.dom4j.DocumentException;
import org.dom4j.Element;

import com.wds.excel.vo.ExcelConfiguration;
import com.wds.excel.xml.XMLOperation;

/**
 * 初始化配置文件
 * @author wangdongsong
 * 2011-7-10 上午08:39:29
 */
public class Configuration {

	private static final String SHEET_INDEX = "sheetIndex";
	private static final String OBJECT_CLASS = "class";
	private static final String EXCEL_PATH = "path";
	private static final String EXCEL_ELEMENT = "excel";
	private static Configuration configuration = null;
	private static ExcelConfiguration ec = new ExcelConfiguration();

	private Configuration() {
	}

	public static Configuration getConfiguration(String path)
			throws DocumentException {
		getConfiguration(new File(path));
		return configuration;
	}

	/**
	 * 初始化配置信息，将从XML读取的关于Excel的信息放入到Excel配置对象中
	 * 2011-7-17 下午11:57:10
	 * @param file
	 * @return
	 * @throws DocumentException
	 */
	public static Configuration getConfiguration(File file)	throws DocumentException {
		configuration = new Configuration();
		Element root = XMLOperation.getRootElement(file);
		Map<String, Object> attributes = XMLOperation.getAttributes(root.element(EXCEL_ELEMENT));
		ec.setExcelPath(attributes.get(EXCEL_PATH).toString());
		ec.setObjectPath(attributes.get(OBJECT_CLASS).toString());
		ec.setSheetIndex(Integer
				.valueOf(attributes.get(SHEET_INDEX).toString()));
		ec.setColumn(XMLOperation.getColumnList(root, "column"));

		return configuration;
	}

	public ExcelConfiguration getExcelConfig() {
		return ec;
	}

}

 接下来看xml包下面的类和文件

一个是XMLOperation类

package com.wds.excel.xml;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.wds.excel.vo.ColumnConfiguration;

/**
 * XML文件操作类
 * @author wangdongsong
 *
 */
public class XMLOperation {

	/**
	 * 根据File文件获得XML文件的根元素
	 * @param file
	 * @return
	 */
	public static Element getRootElement(File file){
		SAXReader reader = new SAXReader();
		Document doc = null;
		Element root = null;
		try {
			doc = reader.read(file);
			root = doc.getRootElement();
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		return root;
	}
	
	/**
	 * 根据XML文件路径获得XML根元素
	 * @param path
	 * @return
	 */
	public static Element getRootElement(String path) {
		return getRootElement(new File(path));
	}
	
	/**
	 * 获得元素的所有属性，属性名作为key,属性值作为value
	 * @param element
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map<String, Object> getAttributes(Element element){
		List<Attribute> attributeList = element.attributes();
		Map<String, Object> attribues = new HashMap<String, Object>();
		for (Attribute attribute : attributeList) {
			String attributeName = attribute.getName();
			Object attributeValue = attribute.getData().toString();
			attribues.put(attributeName, attributeValue);
		}
		return attribues;
	}
	
	@SuppressWarnings("unchecked")
	public static List<Element> getElement(Element parent, String childName){
		return (List<Element>) parent.elementIterator(childName);
	}
	
	/**
	 * 读取<column>节点下的index,attribute,type,format的元素
	 * @param parent 父亲节点
	 * @param childName 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<ColumnConfiguration> getColumnList(Element parent, String childName){
		List<ColumnConfiguration> lists = new ArrayList<ColumnConfiguration>();
		for (Iterator<Element> columns = parent.elementIterator(childName); columns.hasNext();) {
			Element column = columns.next();
			ColumnConfiguration cc = new ColumnConfiguration();
			int index = 0;
			String attribute = null;
			String type = null;
			String format = null;
			
			for (Object childColumnObj:column.elements()) {
				Element childColumn = (Element)childColumnObj;
				String nodeName = childColumn.getName();
				if(nodeName == "index"){
					index = Integer.valueOf(childColumn.getData().toString());
				}else if(nodeName == "attribute"){
					attribute = childColumn.getData().toString();
				}else if(nodeName == "type"){
					type = childColumn.getData().toString();
				}else if(nodeName == "format"){
					format = childColumn.getData().toString();
				}
			}
			
			cc.setIndex(index);
			cc.setAttribute(attribute);
			cc.setType(type);
			cc.setFormat(format);
			lists.add(cc);
		}
		return lists;
	}
}

 

另一个是XML Schema，主要是验证XML文件

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
	targetNamespace="http://www.longrise.com.cn/schema/excel" 
	xmlns="http://www.longrise.com.cn/schema/excel" 
	elementFormDefault="qualified">

	<xs:element name="excelobject">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="excel">
					<xs:complexType>
						<xs:attributeGroup ref="excelAttribute" />
					</xs:complexType>
				</xs:element>
				<xs:element ref="column" />
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	
	<xs:element name="column">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="index" maxOccurs="1" type="xs:integer" />
				<xs:element name="attribute" maxOccurs="1" type="xs:string" />
				<xs:element name="type" type="readType" minOccurs="0" maxOccurs="1" />
				<xs:element name="format" minOccurs="0" maxOccurs="1" type="xs:string" />
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	
	
	<xs:simpleType name="readType">
		<xs:restriction base="xs:string">
			<xs:enumeration value="Date" />
			<xs:enumeration value="Integer" />
			<xs:enumeration value="Double" />
			<xs:enumeration value="String" />
		</xs:restriction>
	</xs:simpleType>
	
	<xs:attributeGroup name="excelAttribute">
		<xs:attribute name="path" type="xs:string" use="required"/>
		<xs:attribute name="sheetIndex" type="xs:integer" use="required"/>
		<xs:attribute name="class" type="xs:string" use="required"/>
	</xs:attributeGroup>

</xs:schema>

 两个VO类，一个是关于Excel配置信息，一个是关于Excel中列和VO之间关系的信息

package com.wds.excel.vo;

/**
 * 列信息类
 * 包含列的下标索引，对该对应的属性，类型，及格式
 * @author wangdongsong
 *
 */
public class ColumnConfiguration {
	
	private int index;
	private String attribute;
	private String type;
	private String format;
	
	public int getIndex() {
		return index;
	}
	public void setIndex(int index) {
		this.index = index;
	}
	public String getAttribute() {
		return attribute;
	}
	public void setAttribute(String attribute) {
		this.attribute = attribute;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public void setFormat(String format) {
		this.format = format;
	}
	public String getFormat() {
		return format;
	}

}

 

/**
 * ExcelVO.java
 * 2011-7-10 上午09:10:46
 */
package com.wds.excel.vo;

import java.util.List;

/**
 * Excel文件配置信息类
 * 包含Excel文件路径、与之对应的对象、列与对象属性的关联，及sheet的设定
 * @author wangdongsong
 *
 */
public class ExcelConfiguration {
	
	private String excelPath;
	private String objectPath;
	private List<ColumnConfiguration> column;
	private int sheetIndex;
	
	/**
	 * @return the excelPath
	 */
	public String getExcelPath() {
		return excelPath;
	}
	/**
	 * @param excelPath the excelPath to set
	 */
	public void setExcelPath(String excelPath) {
		this.excelPath = excelPath;
	}
	/**
	 * @return the objectPath
	 */
	public String getObjectPath() {
		return objectPath;
	}
	/**
	 * @param objectPath the objectPath to set
	 */
	public void setObjectPath(String objectPath) {
		this.objectPath = objectPath;
	}
	/**
	 * @return the column
	 */
	public List<ColumnConfiguration> getColumn() {
		return column;
	}
	/**
	 * @param column the column to set
	 */
	public void setColumn(List<ColumnConfiguration> column) {
		this.column = column;
	}
	public void setSheetIndex(int sheetIndex) {
		this.sheetIndex = sheetIndex;
	}
	public int getSheetIndex() {
		return sheetIndex;
	}

}

 

反射包下面的反射类，主要负责获得方法并执行

package com.wds.excel.reflect;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import org.apache.log4j.Logger;

/**
 * 反射类
 * @author wangdongsong
 *
 */
public class Reflection {
	
	Logger logger = Logger.getLogger(this.getClass());
	private Class<?> clazz;
	private Object obj;
	
	public Reflection(String className) {
		try {
			this.clazz = Class.forName(className);
			obj = clazz.newInstance();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
	}
	
	public Object getObject(){
		return obj;
	}
	
	/**
	 * 执行set方法，将值注入到对象当中
	 * @param attribute 实体类的属性
	 * @param parameter set方法的参数
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	public void execSetMethod(Method method, Object parameter){
		try {
			method.invoke(obj, new Object[]{parameter});
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		logger.info(clazz.getName() + "." + method.getName() + "(value=" + parameter.toString() + ")");
	}
	

	/**
	 * 获得实体类的set方法
	 * @param objectClass 实体类
	 * @param fiedName 字段名称
	 * @return set
	 */
	@SuppressWarnings("unchecked")
	public Method getSetMethod(String fiedName) {
		Class[] parameterTypes = new Class[1];
		StringBuffer sb = new StringBuffer();
		sb.append("set");
		sb.append(fiedName.substring(0, 1).toUpperCase());
		sb.append(fiedName.substring(1));
		try {
			Field field = clazz.getDeclaredField(fiedName);
			parameterTypes[0] = field.getType();
			return clazz.getMethod(sb.toString(),parameterTypes);
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		}
		return null;
	}
}

 

read包下面的ReadExce类

package com.wds.excel.read;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.read.biff.BiffException;

import org.apache.log4j.Logger;

import com.wds.excel.excel.Excel;
import com.wds.excel.reflect.Reflection;
import com.wds.excel.vo.ColumnConfiguration;
import com.wds.excel.vo.ExcelConfiguration;

/**
 * 读取Excel文件
 * @author wangdongsong
 * 
 */
public class ReadExcel {
	
	private Logger logger = Logger.getLogger(this.getClass());
	private Sheet sheet = null;
	private List<ColumnConfiguration> columnConfig= null;
	private StringBuffer infoLog = null;
	private Reflection reflection;
	private Object obj;
	
	/**
	 * 构造方法，初始化参数
	 * @param config
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws BiffException
	 * @throws IOException
	 */
	public ReadExcel(ExcelConfiguration config) throws ClassNotFoundException, InstantiationException, IllegalAccessException, BiffException, IOException {
		reflection = new Reflection(config.getObjectPath());
		columnConfig = (List<ColumnConfiguration>) config.getColumn();
		sheet = Excel.getSheet(config.getExcelPath(), config.getSheetIndex());
	}
	
	
	/**
	 * 读取Excel数据
	 * @param beginRow 
	 * @param beginColumn
	 * @return
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public List<Object> getExcelData(int beginRow, int beginColumn) throws ClassNotFoundException, InstantiationException, IllegalAccessException{
		
		List<Object> list = new ArrayList<Object>();
		try {
			for(int rowIndex = beginRow; rowIndex<sheet.getRows(); rowIndex++){
				Cell[] c = sheet.getRow(rowIndex);
				logger.info("第" + rowIndex + "行读取数据开始");
				obj = this.reflection.getObject();
				infoLog = new StringBuffer();
				infoLog.append("第");
				infoLog.append(rowIndex);
				infoLog.append("行");
				
				for(int columnIndex = beginColumn; columnIndex<c.length; columnIndex++){
					Cell cell = c[columnIndex];
					this.getCellValue(cell);
				}
				
				logger.info(infoLog);
				logger.info("第" + rowIndex + "行读取数据结束");
				list.add(obj);
			}
			
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return list;
		
	}


	/**
	 * 获得单元格的值
	 * @param cell Excel单元格
	 * @throws ParseException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	private void getCellValue(Cell cell)
			throws ParseException, IllegalAccessException,
			InvocationTargetException {

		String content = cell.getContents();
		for(ColumnConfiguration cc : columnConfig){
			String attribute = cc.getAttribute();
			if(cc.getIndex() == cell.getColumn()){
				if("Date".equals(cc.getType())){
					SimpleDateFormat sdf = new SimpleDateFormat(cc.getFormat());
					DateCell dc = (DateCell)cell;
					Date date = dc.getDate();
					content = sdf.format(date);
					Date d = sdf.parse(content);
					this.reflection.execSetMethod( this.reflection.getSetMethod(attribute), d);
				}else if("Integer".equals(cc.getType())){
					this.reflection.execSetMethod(this.reflection.getSetMethod(attribute), Integer.valueOf(content));
				}else if("Double".equals(cc.getType())){
					this.reflection.execSetMethod(this.reflection.getSetMethod(attribute), Double.valueOf(content));
				}else{
					if(cell.getType() == CellType.DATE){
						SimpleDateFormat dateFormat = null;
						if(cc.getFormat() != null){
							dateFormat = new SimpleDateFormat(cc.getFormat());
						}else{
							dateFormat = new SimpleDateFormat("yyyy-MM-dd");
						}
						DateCell dc = (DateCell)cell;
						Date date = dc.getDate();
						content = dateFormat.format(date);
					}
					this.reflection.execSetMethod(this.reflection.getSetMethod(attribute), content);
				}
				
				infoLog.append(content + "\t");
				break;
			}
		}
	}

	
}

 

excel包下的类

package com.wds.excel.excel;

import java.io.File;
import java.io.IOException;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * Excel操作类
 * @author wangdongsong
 *
 */
public class Excel {

	/**
	 * 获得Excel文件中的Sheet
	 * @param path Excel文件路径
	 * @param sheetIndex Sheet索引值
	 * @return jxl.Sheet
	 * @throws BiffException
	 * @throws IOException
	 */
	public static Sheet getSheet(String path, int sheetIndex) throws BiffException,
			IOException {
		Workbook book = Workbook.getWorkbook(new File(path));
		Sheet sheet = book.getSheet(sheetIndex);
		return sheet;
	}

	/**
	 * 获得Excel文件中的Sheet
	 * @param path Excel文件路径
	 * @param sheetname sheetIndex Sheet索引值
	 * @return jxl.Sheet
	 * @throws BiffException
	 * @throws IOException
	 */
	public static Sheet getSheet(String path, String sheetname) throws BiffException,
			IOException {
		Workbook book = Workbook.getWorkbook(new File(path));
		Sheet sheet = book.getSheet(sheetname);
		return sheet;
	}

}

 

主要的类已经完毕。

我以成绩表为例，写了一个demo，Excel文件结构如下

与文件对应的VO类

/**
 *
 * ScoreVO.java
 * Test
 * 2011-7-10 下午05:00:45
 */
package com.wds.excel.demo.vo;

/** 
 * 2011-7-10 下午05:00:45
 */
public class ScoreVO {
	
	private String no;
	private String name;
	
	private String cSharp;
	private String j2EE;
	private String specEnglish;
	private String develop;
	private String job;
	private String labour;
	private String literature;
	private String design;
	private String movie;
	private String culture;
	private String avgScore;
	private String order;
	/**
	 * @return the cSharp
	 */
	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String getCSharp() {
		return cSharp;
	}
	/**
	 * @param cSharp the cSharp to set
	 */
	public void setCSharp(String cSharp) {
		this.cSharp = cSharp;
	}
	/**
	 * @return the j2EE
	 */
	public String getJ2EE() {
		return j2EE;
	}
	/**
	 * @param j2ee the j2EE to set
	 */
	public void setJ2EE(String j2ee) {
		j2EE = j2ee;
	}
	/**
	 * @return the specEnglish
	 */
	public String getSpecEnglish() {
		return specEnglish;
	}
	/**
	 * @param specEnglish the specEnglish to set
	 */
	public void setSpecEnglish(String specEnglish) {
		this.specEnglish = specEnglish;
	}
	/**
	 * @return the develop
	 */
	public String getDevelop() {
		return develop;
	}
	/**
	 * @param develop the develop to set
	 */
	public void setDevelop(String develop) {
		this.develop = develop;
	}
	/**
	 * @return the job
	 */
	public String getJob() {
		return job;
	}
	/**
	 * @param job the job to set
	 */
	public void setJob(String job) {
		this.job = job;
	}
	/**
	 * @return the labour
	 */
	public String getLabour() {
		return labour;
	}
	/**
	 * @param labour the labour to set
	 */
	public void setLabour(String labour) {
		this.labour = labour;
	}
	/**
	 * @return the literature
	 */
	public String getLiterature() {
		return literature;
	}
	/**
	 * @param literature the literature to set
	 */
	public void setLiterature(String literature) {
		this.literature = literature;
	}
	/**
	 * @return the design
	 */
	public String getDesign() {
		return design;
	}
	/**
	 * @param design the design to set
	 */
	public void setDesign(String design) {
		this.design = design;
	}
	/**
	 * @return the movie
	 */
	public String getMovie() {
		return movie;
	}
	/**
	 * @param movie the movie to set
	 */
	public void setMovie(String movie) {
		this.movie = movie;
	}
	/**
	 * @return the culture
	 */
	public String getCulture() {
		return culture;
	}
	/**
	 * @param culture the culture to set
	 */
	public void setCulture(String culture) {
		this.culture = culture;
	}
	/**
	 * @return the avgScore
	 */
	public String getAvgScore() {
		return avgScore;
	}
	/**
	 * @param avgScore the avgScore to set
	 */
	public void setAvgScore(String avgScore) {
		this.avgScore = avgScore;
	}
	/**
	 * @return the order
	 */
	public String getOrder() {
		return order;
	}
	/**
	 * @param order the order to set
	 */
	public void setOrder(String order) {
		this.order = order;
	}
	
}

 

配置文件的信息

<?xml version="1.0" encoding="UTF-8"?>
<ExcelObject xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<excel path="E:/registerdata/0805.xls" sheetIndex="0" class="com.wds.excel.vo.ScoreVO"></excel>
	<column>
		<index>0</index>
		<attribute>no</attribute>
	</column>
	<column>
		<index>1</index>
		<attribute>name</attribute>
	</column>
	<column>
		<index>2</index>
		<attribute>cSharp</attribute>
	</column>
	<column>
		<index>3</index>
		<attribute>j2EE</attribute>
	</column>
	<column>
		<index>4</index>
		<attribute>specEnglish</attribute>
	</column>
	<column>
		<index>5</index>
		<attribute>develop</attribute>
	</column>
	<column>
		<index>6</index>
		<attribute>job</attribute>
	</column>
	<column>
		<index>7</index>
		<attribute>labour</attribute>
	</column>
	<column>
		<index>8</index>
		<attribute>literature</attribute>
	</column>
	<column>
		<index>9</index>
		<attribute>design</attribute>
	</column>
	<column>
		<index>10</index>
		<attribute>movie</attribute>
	</column>
	<column>
		<index>11</index>
		<attribute>culture</attribute>
	</column>
	<column>
		<index>12</index>
		<attribute>avgScore</attribute>
	</column>
	<column>
		<index>13</index>
		<attribute>order</attribute>
	</column>
</ExcelObject>

 测试类

package com.wds.excel.demo;

import java.io.File;
import java.io.IOException;
import java.util.List;

import jxl.read.biff.BiffException;

import org.dom4j.DocumentException;

import com.wds.excel.Configuration;
import com.wds.excel.read.ReadExcel;
import com.wds.excel.vo.ExcelConfiguration;

public class Test {
	public static void main(String[] args) {
			try {
				Configuration configuration = Configuration.getConfiguration(new File("D:\\MyEclipse\\Test\\src\\com\\wds\\excel\\demo\\xml\\score.xml"));
				ExcelConfiguration ec = configuration.getExcelConfig();
				@SuppressWarnings("unused")
				List<Object> lists = new ReadExcel(ec).getExcelData(2, 1);
			} catch (DocumentException e) {
				e.printStackTrace();
			} catch (BiffException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
	}
}

 

程序不成熟，有许多要改进的地方，我和我的同事正在努力完善，希望各位说一下意见，我好学习。谢谢

评论

2 楼 贝塔ZQ 2017-02-21  

读取excel文件，可以试试用插件实现，网上蛮多这种插件的，可以试试pageoffice插件。

1 楼 chencvgk 2011-09-07  

顶 a 一 a 下！