- 浏览: 35237 次
- 性别:
- 来自: 武汉
最新评论
-
贝塔ZQ:
读取excel文件,可以试试用插件实现,网上蛮多这种插件的,可 ...
(Java)运用JXL包读取Excel文件 -
贝塔ZQ:
操作excel文档的话,试试插件么,很简单,PageOffic ...
POI+XOM+Java操作Excel与XML之间转换 -
whlei01:
写得很棒
Flex+BlazeDs+Java的教程及Demo -
chencvgk:
顶 a 一 a 下!
(Java)运用JXL包读取Excel文件 -
梦中有你:
用xml来生成Excel 为什么没人用往xml里添加标签去做呢 ...
POI+XOM+Java操作Excel与XML之间转换
这周因为需要向数据库中导入数据,有多个文件,就想将读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(); } } }
程序不成熟,有许多要改进的地方,我和我的同事正在努力完善,希望各位说一下意见,我好学习。谢谢
相关推荐
java利用jxl.jar包读取excel文件
JAVA利用jxl读取Excel所需jar。 JAVA可以利用jxl简单快速的读取excel文件的内容。由于版本限制,只能读取97-03 xls格式的Excel。
java读取Excel文件-jxl附件列表: ReadAndWriteExcel.java (5.52 KB) jxl.jar (688.03 KB) jxl读Excel文件.doc (88.50 KB)
jxl java读取、插入、删除、更新excel文件源代码 欢迎下载
(java读取excel表格的jar包)
java读取excel必备jar包 付此jar包读取excel范例txt文件 整体打包为RAR格式 一起学习哈
poi包括三种常用jar包,各自支持的格式在文件名里表示出来了。jxl相对操作容易但是功能简单,poi操作复杂但是功能更强大,具体用哪个看自己吧。另外poi还支持对PPT,WORD等文件的支持。。
用jxl实现的读取excel文件,生成相应的对象集合。对于读取html格式的excel文件抛出的BiffException异常做了相应的处理
Jxl是一个开源的Java Excel API项目,通过Jxl,Java可以很方便的操作微软的Excel文档。除了Jxl之外,还有Apache的一个POI项目,也可以操作Excel,两者相比之下:Jxl使用方便,但功能相对POI比较弱。POI使用复杂,...
jxl用于读取Excel的Java类(个人总结综合教程,适合新入门的朋友阅读)
java使用JXL对Excel创建、修改、读取操作
JXL(Java操作Excel文件Oracle数据库)
java读取excel java操作excel jxl API
java JXL导出Excel源码及jfreechart 生成折线图,饼图 java JXL导出Excel源码及jfreechart 生成折线图,饼图 java JXL导出Excel源码及jfreechart 生成折线图,饼图
java直接操作excel文件,JXL是韩国的以为开发人员编写,与poi相比功能是简单了点,但是对于普通的一些开发是完全可以满足需求
一个读取Excel文档,将数据存入map集合的方法
Java生成excel jxl 代码Java生成excel jxl 代码
用jxl读取excel内容的java工程源码,放到eclipse中稍微建立包名即可测试
jxl读取excel数据(带实例) 里面有一个自己写的实例
Java-Excel开发包 jxl.jar 可用Java来操作Excel,读取,写入