jxls导入Excel（即读取Excel中的数据）

最新推荐文章于 2024-04-22 21:43:09 发布

瓦蓝色的天空

最新推荐文章于 2024-04-22 21:43:09 发布

阅读量8.8k

点赞数 3

分类专栏： Excel导入文章标签： java excel jxls

本文链接：https://blog.csdn.net/qq_37487520/article/details/77572162

版权

Excel导入专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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中的数据展示

—— [点击这里下载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 cell="B1">department.name</mapping>    
```
  mapping for B1
- Ⅱ.
```
<mapping row="3" col="4">department.chief.bonus</mapping> 
```
  mapping for E4（zero-based：行号和列号从0开始）
- Ⅲ.
```
<loop startRow="7" endRow="7" items="department.staff" var="employee" varType="org.jxls.reader.sample.Employee">
```
  遍历，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>