上篇我们学了如何上传下载文件,但很多时候,业务需要将查询到的数据导出为excel表格到客户端,
这篇我们就来学习如何使用jxl动态导出数据。
一,添加依赖包
<!-- 导出excel -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
二,编写实现类ExcelExportServlet
package com.example.servlet;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.fastjson.JSON;
import com.example.entity.UserInfo;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 将文本内容导出成excel表格
* 实现过程:
* 读取文本内容,写入.xlsx文件存到服务器,
* 然后从服务器下载到客户端
* @author Administrator
*
*/
public class ExcelExportServlet extends HttpServlet {
private static final long serialVersionUID = 1647435458512117259L;
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
super.doGet(req, resp);
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
/*
* 1,自定义excel
* 文件名,和时间戳生成下载文件名
* 表头,页面传参以字符串格式","连接,后台转成数组
* 正文内容,页面传参为json字符串,需要转成list
*/
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
//导出文件名
String fileName = req.getParameter("fileName");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
fileName = fileName+"-"+sdf.format(new Date())+".xls";
//表头数据
String columnName = req.getParameter("columnName");
String[] columnNames = columnName.split(",");
//正文数据,json字符串,需要转成list
String content = req.getParameter("content");
List<UserInfo> listContent = JSON.parseArray(content, UserInfo.class);
//获取输出流
OutputStream out = resp.getOutputStream();
//清空输出流数据,避免文件写出格式错误
resp.reset();
//设置输出格式
resp.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode(fileName, "UTF-8"));
try {
//创建excel工作簿
WritableWorkbook workbook = Workbook.createWorkbook(out);
//创建工作表
WritableSheet sheet = workbook.createSheet("Sheet1", 0);
//添加格式
//设置默认值
sheet.getSettings().setDefaultColumnWidth(30);//默认列宽
sheet.getSettings().setDefaultRowHeight(500);//默认行高
sheet.setRowView(0,700);//设置第一行标题栏的行高
//设置打印格式
sheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);// 设置为横向打印
sheet.getSettings().setPaperSize(PaperSize.A4);// 设置纸张
sheet.getSettings().setFitHeight(297);// 打印区高度
sheet.getSettings().setFitWidth(210);// 打印区宽度
// 用于标题
WritableCellFormat title_form = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD));
title_form.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
title_form.setAlignment(Alignment.CENTRE); // 文字水平对齐
title_form.setWrap(false); // 文字是否换行
// 用于表头
WritableCellFormat head_form = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD));
head_form.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
head_form.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
head_form.setAlignment(Alignment.CENTRE); // 文字水平对齐
head_form.setWrap(false); // 文字是否换行
// 用于文本
WritableCellFormat body_form = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10));
body_form.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
body_form.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
body_form.setAlignment(Alignment.CENTRE); // 文字水平对齐
body_form.setWrap(true); // 文字是否换行
//第一行填入表名,合并单元格
sheet.mergeCells(0, 0, columnNames.length-1, 0);
Label label = new Label(0, 0,fileName,title_form);
sheet.addCell(label);
//从第二行开始,插入表头
for (int i = 0; i < columnNames.length; i++) {
sheet.addCell(new Label(i, 1,columnNames[i],head_form));
}
//从第三行开始,插入正文
Field[] fields=null;
int i = 2;
for (Object obj : listContent) {
fields = obj.getClass().getDeclaredFields();
int j = 0;
for (Field v : fields) {
v.setAccessible(true);
Object va = v.get(obj);
if (va == null) {
va = "";
}
sheet.addCell(new Label(j, i, va.toString(),body_form));
j++;
}
i++;
}
// 写入数据并关闭文件
workbook.write();
workbook.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
三,在web.xml中添加映射:
<!-- 导出excel -->
<servlet>
<servlet-name>ExcelExportServlet</servlet-name>
<servlet-class>com.example.servlet.ExcelExportServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ExcelExportServlet</servlet-name>
<url-pattern>/excel_export</url-pattern>
</servlet-mapping>
四,模拟客户端导出功能,新建excel.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="./js/export.js"></script>
</head>
<body>
<table width='200px' border='1' cellspacing='0' cellpadding='0'>
<thead>
<tr>
<td>用户名</td>
<td>密码</td>
</tr>
<tr>
<td>张三</td>
<td>123</td>
</tr>
<tr>
<td>李四</td>
<td>342</td>
</tr>
</thead>
</table>
<br />
<button οnclick="export_excel();">导出excel</button>
</body>
</html>
五,新建export.js,实现export_excel()方法
function export_excel() {
var fileName = "用户信息表";
var columnName = "用户名,用户密码";
var content = "[{\"name\":\"张三\",\"password\":\"123\"},{\"name\":\"李四\",\"password\":\"134\"}]";
var param = 'fileName='+fileName+'&columnName='+columnName+'&content='+content;
window.location.href = "http://localhost:8088/webDemo/excel_export?"+param;
}
这里说明一下,同文件下载一样,返回数据是以文件流的形式直接输出到浏览器端的,所以不能用ajax请求,
这里我们使用get请求,也可以模拟表单使用post请求访问。
六,启动服务,访问测试,查看结果:
查看导出的数据:
这里演示的,是从前端传递数据到后台处理后,响应excel文件到客户端
实际应用中,由于数据量比较大,查询条件比较多,往往需要从数据库查询数据再导出,
那么下节我们将学习数据库的连接以及相关操作,后续再实现从数据库查询并导出excel。
下篇,java_web 学习记录(八):jdbc + mysql