运用jxls实现excel的导出

运用jxls实现excel的导出

引言：
前两天与一个小姐姐交流学习了导入功能，小姐姐又给我说了一个导出功能，并把她记录的笔记发给我了，通过小姐姐的笔记以及网上查看的一些博客，用jxls简单实现了excel的导出功能，在此向大家分享一下。（小姐姐的笔记记录的很好哟~~）

1、jsxl的介绍

jxls是一个简单的、轻量级的excel导出库，使用特定的标记在excel模板文件中来定义输出格式和布局。java中成熟的excel导出工具有pol、jxl，但他们都是使用java代码的方式来导出excel，编码效率很低且不方便维护。

2、demo

2.1 依赖

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.14-beta1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.14-beta1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14-beta1</version></dependency><dependency><groupId>org.apache.httpcomponents</groupId><artifactId>httpclient</artifactId><version>4.5.2</version></dependency><dependency><groupId>net.sf.jxls</groupId><artifactId>jxls-core</artifactId><version>1.0.6</version></dependency>

2.2 Model

import com.fasterxml.jackson.annotation.JsonFormat;import java.util.Date;public class Employee {private String name;@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")private Date birthDate;private int payment;private double bonus;public Employee(String name, Date birthDate, int payment, double bonus) {this.name = name;this.birthDate = birthDate;this.payment = payment;this.bonus = bonus;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Date getBirthDate() {return birthDate;}public void setBirthDate(Date birthDate) {this.birthDate = birthDate;}public int getPayment() {return payment;}public void setPayment(int payment) {this.payment = payment;}public double getBonus() {return bonus;}public void setBonus(double bonus) {this.bonus = bonus;}
}

2.3 Controller

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.siit.checkdemo.pojo.Employee;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import net.sf.jxls.transformer.XLSTransformer;@Controller
public class FileController {@RequestMapping("/test/downLoad") //请求映射public void toDownload(HttpServletRequest request, HttpServletResponse response) throws ParseException {//调用service层postService接口//List<Post> list = postService.queryAllPost(selectVal,inputVal);List<Employee> list = new ArrayList<>();SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.CHINESE);list.add( new Employee("Elsa", dateFormat.parse("1970-01-10"), 1500, 0.15) );list.add( new Employee("Oleg", dateFormat.parse("1973-12-30"), 2300, 0.25) );list.add( new Employee("Neil", dateFormat.parse("1975-10-05"), 2500, 0.00) );list.add( new Employee("Maria", dateFormat.parse("1978-06-07"), 1700, 0.15) );list.add( new Employee("John", dateFormat.parse("1969-03-30"), 2800, 0.20) );//获取输入流，原始模板位置项目/OA/src/main/resources/template/excelFile.xlsxString templateFileName = getClass().getResource("/templates/excelFile.xlsx").getPath();//假如以中文名下载的话，设置下载文件名称String destFileName = "post.xlsx";//使用map存放查询出来的数据Map<String, Object> map = new HashMap<>();//userList与Excel模板excelFile.xlsx<jx:forEach items="${userList}" var="post">中的userList一致map.put("userList", list);XLSTransformer transformer = new XLSTransformer();InputStream in = null;OutputStream out = null;//设置响应//设置文件下载头response.setHeader("Content-Disposition", "attachment;filename=" + destFileName);//1.设置文件ContentType类型，这样设置，会自动判断下载文件类型response.setContentType("application/vnd.ms-excel");try {in = new BufferedInputStream(new FileInputStream(templateFileName));//生成Excel文件,利用transformXLS来输出文件Workbook workBook = transformer.transformXLS(in, map);//取得输出流out = response.getOutputStream();//将内容写入输出流并把缓存的内容全部发出去workBook.write(out);out.flush();} catch (Exception e) {e.printStackTrace();} finally {if (in != null) {try {in.close();} catch (IOException e) {e.printStackTrace();}}if (out != null) {try {out.close();} catch (IOException e) {e.printStackTrace();}}}}
}

2.4 模板文件

根据自己所需字段，设置模板文件

jxls使用 Apache JEXL表达式语言来解析定义在excel模板中的表达式。JEXL与JSTL相似，并对JSTL进行了扩展。具体的大家可以百度自己查看。
官网：http://jxls.sourceforge.net/reference/excel_markup.html
eg：

${department.chief.age} //属性可以是无限深度

2.5 测试

运行项目，浏览器输入url请求，回车即可。

最后，感谢小姐姐的笔记~~