JXLS导出数据到Excel表格

最新推荐文章于 2023-09-01 10:43:07 发布

原创最新推荐文章于 2023-09-01 10:43:07 发布 · 5.9k 阅读

3 ·

CC 4.0 BY-SA版权

java后端专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了如何利用Jxls库在Java项目中实现Excel数据导出功能，包括项目构建、实体类定义、数据访问层实现、Servlet处理及模版设计等步骤。

直接贴代码了，就不讲概念了。。。。

先列出项目工程结构：

1.先创建一个web项目，这里我使用maven构建

pom.xml文件需要导入的jar包

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.4.6</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>1.0.15</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>1.0.7</version>
</dependency>

2.创建实体类，这里创建Employee类

        private Integer id;
	private String empName;
	private Integer age;
	private String address;
	
	public Employee(){}
	
	public Employee(Integer id, String empName, Integer age, String address) {
		this.id = id;
		this.empName = empName;
		this.age = age;
		this.address = address;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getEmpName() {
		return empName;
	}
	public void setEmpName(String empName) {
		this.empName = empName;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}

3.创建数据访问层类，这里就不连接数据库了，直接初始化数据。（创建EmpDao类）

public List<Employee> getEmployee(){
        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee(1,"张三",16,"xxx区");
	Employee emp2 = new Employee(2,"李四",20,"xxxx家湾");
	Employee emp3 = new Employee(3,"王五",36,"xxx区");
	Employee emp4 = new Employee(4,"赵柳",39,"xxx北");
	Employee emp5 = new Employee(5,"田七",21,"xxx区");
	Employee emp6 = new Employee(6,"王八",42,"xx科技园");
		
	empList.add(emp1);
	empList.add(emp2);
	empList.add(emp3);
	empList.add(emp4);
	empList.add(emp5);
	empList.add(emp6);
		
	return empList;
}

4.创建Servlet类，处理数据导入

package com.kys.controller;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;

import com.kys.dao.EmpDao;

public class EmployeeServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	private EmpDao empDao = new EmpDao();

	public void doGet(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {
		this.doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {
		/**
		 * InputStream is = EmployeeServlet.class.getResourceAsStream("/report/emp.xlsx")
		 * 这行代码主要是读取excel模块(excel模版下面在介绍)，模版我放在根路径下 : /report/
		 */
		/**
		 * OutputStream os = new FileOutputStream("target/emp.xlsx")
		 * 主要导出的路径、路径存在maven工程下target文件下
		 */
		/**
		 *  context.putVar("emps",empDao.getEmployee());
		 *  把获取到的数据保存到作用域中
		 */
		/**
		 * JxlsHelper.getInstance().processTemplate(is, os, context);
		 * 生成到excel表格中
		 */
		try(InputStream is = EmployeeServlet.class.getResourceAsStream("/report/emp.xlsx")) {
	        try (OutputStream os = new FileOutputStream("target/emp.xlsx")) {
	            Context context = new Context();
	            context.putVar("emps",empDao.getEmployee());
	            JxlsHelper.getInstance().processTemplate(is, os, context);
	        }
	    }
	}
}

5.页面层index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Employee</title>
</head>
<body>
	<!-- Servlet的访问地址：empSer -->
	<a href="empSer">导出</a>
</body>
</html>

6.Excel模版我保存到src/main/resources/report目录下

下面介绍excel表格需要怎么写：

先看一下官网的解释：