let’s go

jxls简介：Jxls provides jxls-reader module to read XLS files and populate Java beans with spreadsheet data. XML configuration is used to indicate how exactly an input Excel file should be parsed and how to populate the data.

优点

简单
效率接近POI，满足大部分工作需求
支持.xls和.xlsx格式的Excel

先看下官方文档，我将要素摘出来了

—— [ 官方文档链接]

Maven dependency

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-reader</artifactId>
    <version>2.0.3</version>
</dependency>

或者

<dependency>
    <groupId>net.sf.jxls</groupId>
    <artifactId>jxls-reader</artifactId>
    <version>1.0.6</version>
</dependency>

要读取的Excel中的数据展示

jxls导入Excel（即读取Excel中的数据）let’s go下面是我写的Demo，基于springMVC框架

—— [点击这里下载Excel示例]

Construction of XLSReader using XML config file

xmlConfig.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<workbook>
    <worksheet name="Sheet1">
        <section startRow="0" endRow="6">
            <mapping cell="B1">department.name</mapping>
            <mapping cell="A4">department.chief.name</mapping>
            <mapping cell="B4">department.chief.age</mapping>
            <mapping cell="D4">department.chief.payment</mapping>
            <mapping row="3" col="4">department.chief.bonus</mapping>
        </section>
        <loop startRow="7" endRow="7" items="department.staff" var="employee" varType="org.jxls.reader.sample.Employee">
            <section startRow="7" endRow="7">
                <mapping row="7" col="0">employee.name</mapping>
                <mapping row="7" col="1">employee.age</mapping>
                <mapping row="7" col="3">employee.payment</mapping>
                <mapping row="7" col="4">employee.bonus</mapping>
            </section>
            <loopbreakcondition>
                <rowcheck offset="0">
                    <cellcheck offset="0">Employee Payment Totals:</cellcheck>
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>
</workbook>

xml配置文件解释

1.workbook can contain any number of child worksheet elements;
2.worksheet element can contain any number of section and loop child elements;
3.section element represents a simple block of spreadsheet cells. The first and the last rows of the block are specified with startRow and endRow attributes(从0开始，前闭后开)

mapping标签：将Excel文件中的单元格映射到javaBean的属性中。单元格映射有下面3种方式：
- Ⅰ.
  
  mapping for B1
- Ⅱ.
  
  mapping for E4（zero-based：行号和列号从0开始）
- Ⅲ.
  
  遍历，startRow=”7” endRow=”7” 表示第8行，准确的说第8行为遍历的起始行

Attention：loop element can contain any number of inner section and loop elements and HAVE TO contain loopbreakcondition definition. This describes break condition to stop loop iteration. In our sample it is as simple as specifying that next row after employees data must contain “Employee Payment Totals:” string in the first cell.

注意：loop标签必须包含loopbreakcondition标签，用作终止循环。这里是下一行的第一个单元格如果是 Employee Payment Totals: 则终止该循环

官方示例代码

InputStream inputXML = new BufferedInputStream(getClass().getResourceAsStream(xmlConfig));
    XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );
    InputStream inputXLS = new BufferedInputStream(getClass().getResourceAsStream(dataXLS));
    Department department = new Department();
    Department hrDepartment = new Department();
    List departments = new ArrayList();
    Map beans = new HashMap();
    beans.put("department", department);
    beans.put("hrDepartment", hrDepartment);
    beans.put("departments", departments);
    XLSReadStatus readStatus = mainReader.read( inputXLS, beans);

Sheet mapping by index

有的时候我们并不知道sheet的名字，因此jxls也为我们提供了通过索引idx来指定sheet

xmlConfig.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
   <workbook>
       <worksheet idx="0">
           <section startRow="0" endRow="6">
           <mapping cell="B1">department.name</mapping>
           <mapping cell="A4">department.chief.name</mapping>
           <mapping cell="B4">department.chief.age</mapping>
           <mapping cell="D4">department.chief.payment</mapping>
           <mapping row="3" col="4">department.chief.bonus</mapping>
           </section>
           <loop startRow="7" endRow="7" items="department.staff" var="employee" varType="org.jxls.reader.sample.Employee">
               <section startRow="7" endRow="7">
               <mapping row="7" col="0">employee.name</mapping>
               <mapping row="7" col="1">employee.age</mapping>
               <mapping row="7" col="3">employee.payment</mapping>
               <mapping row="7" col="4">employee.bonus</mapping>
               </section>
               <loopbreakcondition>
                   <rowcheck offset="0">
                       <cellcheck offset="0">Employee Payment Totals:</cellcheck>
                   </rowcheck>
               </loopbreakcondition>
           </loop>
       </worksheet>
   </workbook>

Error Processing略

Conversion Mechanism略

下面是我写的Demo，基于springMVC框架

departmentdata.xls

—— Excel下载

xmlConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
    <workbook>
        <worksheet name="Sheet1">
            <section startRow="0" endRow="6">
                <mapping cell="B1">department.name</mapping>
                <mapping cell="A4">department.chief.name</mapping>
                <mapping cell="B4">department.chief.age</mapping>
                <mapping cell="D4">department.chief.payment</mapping>
                <mapping row="3" col="4">department.chief.bonus</mapping>
            </section>
            <loop startRow="7" endRow="7" items="employees" var="employee" varType="com.enation.app.shop.core.order.model.Staff">
                <section startRow="7" endRow="7">
                    <mapping row="7" col="0">employee.name</mapping>
                    <mapping row="7" col="1">employee.age</mapping>
                    <mapping row="7" col="3">employee.payment</mapping>
                    <mapping row="7" col="4">employee.bonus</mapping>
                </section>
                <loopbreakcondition>
                    <rowcheck offset="0">
                        <cellcheck offset="0">Employee Payment Totals:</cellcheck>
                    </rowcheck>
                </loopbreakcondition>
            </loop>
        </worksheet>
    </workbook>

Staff

public class Staff {

        private  String name;
        private  int age;
        private  String  birthDateOfString;
        private  Double payment;
        private  Double bonus;
        private  String superiorName;

        public Staff() {

        }

        public Staff(String name, int age, String birthDateOfString, Double payment, Double bonus, String superiorName) {
            super();
            this.name = name;
            this.age = age;
            this.birthDateOfString = birthDateOfString;
            this.payment = payment;
            this.bonus = bonus;
            this.superiorName = superiorName;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public int getAge() {
            return age;
        }
        public void setAge(int age) {
            this.age = age;
        }
        public String getBirthDateOfString() {
            return birthDateOfString;
        }
        public void setBirthDateOfString(String birthDateOfString) {
            this.birthDateOfString = birthDateOfString;
        }
        public Double getPayment() {
            return payment;
        }
        public void setPayment(Double payment) {
            this.payment = payment;
        }
        public Double getBonus() {
            return bonus;
        }
        public void setBonus(Double bonus) {
            this.bonus = bonus;
        }
        public String getSuperiorName() {
            return superiorName;
        }
        public void setSuperiorName(String superiorName) {
            this.superiorName = superiorName;
        }

        @Override
        public String toString() {
            return "Staff [name=" + name + ", age=" + age + ", birthDateOfString=" + birthDateOfString + ", payment=" + payment + ", bonus="
                    + bonus + ", superiorName=" + superiorName + "]";
        }
    }

Department

public class Department {
    private String name;
    private Staff chief = new Staff();//注意：这里一定要new，否则映射不了  department.chief.name

    public Department() {

    }
    public Department(String name, Staff chief) {
        super();
        this.name = name;
        this.chief = chief;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Staff getChief() {
        return chief;
    }
    public void setChief(Staff chief) {
        this.chief = chief;
    }
    @Override
    public String toString() {
        return "Department [name=" + name + ", chief=" + chief + "]";
    }

}

Controller

@Controller  
@RequestMapping("employee")  
public class EmployeeController {  
    @RequestMapping("read")  
    public void read(HttpServletRequest request,HttpServletResponse response) throws IOException, SAXException, InvalidFormatException{  
        //配置文件
        FileInputStream xmlFin = new FileInputStream(new File("d:/test/read/xmlConfig.xml"));
        //要导入的Excel
        FileInputStream dataFin = new FileInputStream(new File("d:/test/read/departmentdata.xls"));

        InputStream inputXML = new BufferedInputStream(xmlFin);

        InputStream inputXLS = new BufferedInputStream(dataFin);

        Department department = new Department();

        List<Staff> employees = new ArrayList<Staff>();

        Map<String,Object> beanparams = new HashMap<String,Object>();

        beanparams.put("department",department);
        beanparams.put("employees",employees);

        XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
        XLSReadStatus readStatus = mainReader.read(inputXLS,beanparams);
        //测试
        System.out.println(department.toString());
        for (Staff employee : employees) {
            System.out.println(employee.toString());
    }
}

jsp

<div>
        <a class="readdExcel" href="javascript:void (0)">读取Excel</a>
</div>

<script>

    $('.readdExcel').click(function(){
        $.ajax({
            type:"POST",
            url :"${ctx}/shop/admin/payment/read.do", 
            data:{},
            dataType: "json", 
            success : function() {

            },error : function() {

            }
        });

    }); 
</script>

注意：所有和poi相关的依赖的版本必须一致，主要关注poi和poi-ooxml版本是否一致

jxls导入Excel（即读取Excel中的数据）let’s go下面是我写的Demo，基于springMVC框架

let’s go

优点

先看下官方文档，我将要素摘出来了

下面是我写的Demo，基于springMVC框架

继续阅读

关于Gradle配置的小结

Java小案例——随机数猜测随机数猜测

nginx location中斜线的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method