博客分类：
这周因为需要向数据库中导入数据，有多个文件，就想将读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
上午08:39:29
package com.wds.
import java.io.F
import java.util.M
import org.dom4j.DocumentE
import org.dom4j.E
import com.wds.excel.vo.ExcelC
import com.wds.excel.xml.XMLO
* 初始化配置文件
* @author wangdongsong
上午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 =
private static ExcelConfiguration ec = new ExcelConfiguration();
private Configuration() {
public static Configuration getConfiguration(String path)
throws DocumentException {
getConfiguration(new File(path));
* 初始化配置信息，将从XML读取的关于Excel的信息放入到Excel配置对象中
下午11:57:10
* @param file
* @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"));
public ExcelConfiguration getExcelConfig() {
接下来看xml包下面的类和文件
一个是XMLOperation类
package com.wds.excel.
import java.io.F
import java.util.ArrayL
import java.util.HashM
import java.util.I
import java.util.L
import java.util.M
import org.dom4j.A
import org.dom4j.D
import org.dom4j.DocumentE
import org.dom4j.E
import org.dom4j.io.SAXR
import com.wds.excel.vo.ColumnC
* XML文件操作类
* @author wangdongsong
public class XMLOperation {
* 根据File文件获得XML文件的根元素
* @param file
public static Element getRootElement(File file){
SAXReader reader = new SAXReader();
Document doc =
Element root =
doc = reader.read(file);
root = doc.getRootElement();
} catch (DocumentException e) {
e.printStackTrace();
* 根据XML文件路径获得XML根元素
* @param path
public static Element getRootElement(String path) {
return getRootElement(new File(path));
* 获得元素的所有属性，属性名作为key,属性值作为value
* @param element
@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);
@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
@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 =
String type =
String format =
for (Object childColumnObj:column.elements()) {
Element childColumn = (Element)childColumnO
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);
另一个是XML Schema，主要是验证XML文件
&?xml version="1.0" encoding="UTF-8"?&
&xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace=".cn/schema/excel"
xmlns=".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.
* 列信息类
* 包含列的下标索引，对该对应的属性，类型，及格式
* @author wangdongsong
public class ColumnConfiguration {
public int getIndex() {
public void setIndex(int index) {
this.index =
public String getAttribute() {
public void setAttribute(String attribute) {
this.attribute =
public String getType() {
public void setType(String type) {
this.type =
public void setFormat(String format) {
this.format =
public String getFormat() {
* ExcelVO.java
上午09:10:46
package com.wds.excel.
import java.util.L
* Excel文件配置信息类
* 包含Excel文件路径、与之对应的对象、列与对象属性的关联，及sheet的设定
* @author wangdongsong
public class ExcelConfiguration {
private String excelP
private String objectP
private List&ColumnConfiguration&
private int sheetI
* @return the excelPath
public String getExcelPath() {
return excelP
* @param excelPath the excelPath to set
public void setExcelPath(String excelPath) {
this.excelPath = excelP
* @return the objectPath
public String getObjectPath() {
return objectP
* @param objectPath the objectPath to set
public void setObjectPath(String objectPath) {
this.objectPath = objectP
* @return the column
public List&ColumnConfiguration& getColumn() {
* @param column the column to set
public void setColumn(List&ColumnConfiguration& column) {
this.column =
public void setSheetIndex(int sheetIndex) {
this.sheetIndex = sheetI
public int getSheetIndex() {
return sheetI
反射包下面的反射类，主要负责获得方法并执行
package com.wds.excel.
import java.lang.reflect.F
import java.lang.reflect.InvocationTargetE
import java.lang.reflect.M
import org.apache.log4j.L
* @author wangdongsong
public class Reflection {
Logger logger = Logger.getLogger(this.getClass());
private Class&?&
public Reflection(String className) {
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(){
* 执行set方法，将值注入到对象当中
* @param attribute 实体类的属性
* @param parameter set方法的参数
* @throws IllegalAccessException
* @throws InvocationTargetException
public void execSetMethod(Method method, Object parameter){
method.invoke(obj, new Object[]{parameter});
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
(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));
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();
read包下面的ReadExce类
package com.wds.excel.
import java.io.IOE
import java.lang.reflect.InvocationTargetE
import java.text.ParseE
import java.text.SimpleDateF
import java.util.ArrayL
import java.util.D
import java.util.L
import jxl.C
import jxl.CellT
import jxl.DateC
import jxl.S
import jxl.read.biff.BiffE
import org.apache.log4j.L
import com.wds.excel.excel.E
import com.wds.excel.reflect.R
import com.wds.excel.vo.ColumnC
import com.wds.excel.vo.ExcelC
* 读取Excel文件
* @author wangdongsong
public class ReadExcel {
private Logger logger = Logger.getLogger(this.getClass());
private Sheet sheet =
private List&ColumnConfiguration& columnConfig=
private StringBuffer infoLog =
* 构造方法，初始化参数
* @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
* @throws ClassNotFoundException
* @throws InstantiationException
* @throws IllegalAccessException
public List&Object& getExcelData(int beginRow, int beginColumn) throws ClassNotFoundException, InstantiationException, IllegalAccessException{
List&Object& list = new ArrayList&Object&();
for(int rowIndex = beginR rowIndex&sheet.getRows(); rowIndex++){
Cell[] c = sheet.getRow(rowIndex);
("第" + rowIndex + "行读取数据开始");
obj = this.reflection.getObject();
infoLog = new StringBuffer();
infoLog.append("第");
infoLog.append(rowIndex);
infoLog.append("行");
for(int columnIndex = beginC columnIndex&c. columnIndex++){
Cell cell = c[columnIndex];
this.getCellValue(cell);
(infoLog);
("第" + rowIndex + "行读取数据结束");
list.add(obj);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
* 获得单元格的值
* @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)
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));
if(cell.getType() == CellType.DATE){
SimpleDateFormat dateFormat =
if(cc.getFormat() != null){
dateFormat = new SimpleDateFormat(cc.getFormat());
dateFormat = new SimpleDateFormat("yyyy-MM-dd");
DateCell dc = (DateCell)
Date date = dc.getDate();
content = dateFormat.format(date);
this.reflection.execSetMethod(this.reflection.getSetMethod(attribute), content);
infoLog.append(content + "\t");
excel包下的类
package com.wds.excel.
import java.io.F
import java.io.IOE
import jxl.S
import jxl.W
import jxl.read.biff.BiffE
* 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);
* 获得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);
主要的类已经完毕。
我以成绩表为例，写了一个demo，Excel文件结构如下
与文件对应的VO类
* ScoreVO.java
下午05:00:45
package com.wds.excel.demo.
下午05:00:45
public class ScoreVO {
private String cS
private String j2EE;
private String specE
private String avgS
* @return the cSharp
public String getNo() {
public void setNo(String no) {
public String getName() {
public void setName(String name) {
this.name =
public String getCSharp() {
* @param cSharp the cSharp to set
public void setCSharp(String cSharp) {
this.cSharp = cS
* @return the j2EE
public String getJ2EE() {
return j2EE;
* @param j2ee the j2EE to set
public void setJ2EE(String j2ee) {
* @return the specEnglish
public String getSpecEnglish() {
return specE
* @param specEnglish the specEnglish to set
public void setSpecEnglish(String specEnglish) {
this.specEnglish = specE
* @return the develop
public String getDevelop() {
* @param develop the develop to set
public void setDevelop(String develop) {
this.develop =
* @return the job
public String getJob() {
* @param job the job to set
public void setJob(String job) {
this.job =
* @return the labour
public String getLabour() {
* @param labour the labour to set
public void setLabour(String labour) {
this.labour =
* @return the literature
public String getLiterature() {
* @param literature the literature to set
public void setLiterature(String literature) {
this.literature =
* @return the design
public String getDesign() {
* @param design the design to set
public void setDesign(String design) {
this.design =
* @return the movie
public String getMovie() {
* @param movie the movie to set
public void setMovie(String movie) {
this.movie =
* @return the culture
public String getCulture() {
* @param culture the culture to set
public void setCulture(String culture) {
this.culture =
* @return the avgScore
public String getAvgScore() {
return avgS
* @param avgScore the avgScore to set
public void setAvgScore(String avgScore) {
this.avgScore = avgS
* @return the order
public String getOrder() {
* @param order the order to set
public void setOrder(String order) {
this.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&
&index&0&/index&
&attribute&no&/attribute&
&index&1&/index&
&attribute&name&/attribute&
&index&2&/index&
&attribute&cSharp&/attribute&
&index&3&/index&
&attribute&j2EE&/attribute&
&index&4&/index&
&attribute&specEnglish&/attribute&
&index&5&/index&
&attribute&develop&/attribute&
&index&6&/index&
&attribute&job&/attribute&
&index&7&/index&
&attribute&labour&/attribute&
&index&8&/index&
&attribute&literature&/attribute&
&index&9&/index&
&attribute&design&/attribute&
&index&10&/index&
&attribute&movie&/attribute&
&index&11&/index&
&attribute&culture&/attribute&
&index&12&/index&
&attribute&avgScore&/attribute&
&index&13&/index&
&attribute&order&/attribute&
&/ExcelObject&
package com.wds.excel.
import java.io.F
import java.io.IOE
import java.util.L
import jxl.read.biff.BiffE
import org.dom4j.DocumentE
import com.wds.excel.C
import com.wds.excel.read.ReadE
import com.wds.excel.vo.ExcelC
public class Test {
public static void main(String[] args) {
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();
程序不成熟，有许多要改进的地方，我和我的同事正在努力完善，希望各位说一下意见，我好学习。谢谢
wangdongsong
浏览: 25959 次
来自: 武汉
读取excel文件，可以试试用插件实现，网上蛮多这种插件的，可 ...
操作excel文档的话，试试插件么，很简单，PageOffic ...
顶 a 一 a 下！
用xml来生成Excel 为什么没人用往xml里添加标签去做呢 ...java读取excel文件的两种方法
作者：仰望天空
字体：[ ] 类型：转载 时间：
这篇文章主要为大家详细介绍了java读取excel文件的两种方法，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
本文实例为大家分享了Android九宫格图片展示的具体代码，供大家参考，具体内容如下
package com.ij34.
* @author Admin
* @date 创建时间：日 下午2:07:59
* @version 1.0
*@type_name myclass
import java.io.F
import java.io.IOE
import jxl.C
import jxl.S
import jxl.W
import jxl.read.biff.BiffE
public class Test05 {
public static void main(String args[]){
File f=new File("table01.xls");
Workbook book=Workbook.getWorkbook(f);//
Sheet sheet=book.getSheet(0); //获得第一个工作表对象
for(int i=0;i&sheet.getRows();i++){
for(int j=0;j&sheet.getColumns();j++){
Cell cell=sheet.getCell(j, i); //获得单元格
System.out.print(cell.getContents()+" ");
System.out.print("\n");
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
package com.ij34.
import java.io.F
import java.io.FileInputS
import java.io.FileNotFoundE
import java.io.IOE
import org.apache.poi.hssf.usermodel.HSSFS
import org.apache.poi.hssf.usermodel.HSSFW
import org.apache.poi.ss.usermodel.C
import org.apache.poi.ss.usermodel.DateU
import org.apache.poi.ss.usermodel.R
* @author Admin
* @date 创建时间：日 下午4:01:06
* @version 1.0
*@type_name Test02
public class Test02 {
public static void main(String[] args) throws FileNotFoundException, IOException {
File excelFile = new File("table01.xls");
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
HSSFSheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING://字符串
System.out.print(cell.getRichStringCellValue().getString());
System.out.print(" ");
case Cell.CELL_TYPE_NUMERIC://数值与日期
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print(String.valueOf(cell.getDateCellValue()));
System.out.print(cell.getNumericCellValue());
System.out.print(" ");
case Cell.CELL_TYPE_BOOLEAN://boolean类型
System.out.print(cell.getBooleanCellValue());
System.out.print(" ");
System.out.println();
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。&
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具