开发环境
开发工具: idea 2018.2.5
jdk版本 : 1.8
maven版本: 3.3.9
系统架构:ssm
这里主要实现数据导出到文件功能
1、在现有的maven项目中添加 poi jar包(pom.xml文件)
在 properties 标题中添加poi版本号,然后在 dependencies 标签中添加 poi 坐标。
<poi.version>3.9</poi.version>
<!-- poi office -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
2、编写实体类(这里表示成绩实体类)
实体类:Score.java
package com.java.ssm.domain;
import java.io.Serializable;
public class Score implements Serializable {
private String name;//姓名
private Integer chinese;//语文
private Integer mathematics;//数学
private Integer english;//英语
public Score() {
}
public Score(String name, Integer chinese, Integer mathematics, Integer english) {
this.name = name;
this.chinese = chinese;
this.mathematics = mathematics;
this.english = english;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getChinese() {
return chinese;
}
public void setChinese(Integer chinese) {
this.chinese = chinese;
}
public Integer getMathematics() {
return mathematics;
}
public void setMathematics(Integer mathematics) {
this.mathematics = mathematics;
}
public Integer getEnglish() {
return english;
}
public void setEnglish(Integer english) {
this.english = english;
}
}
3、编写控制器类
控制器:ScoreController.java , 添加数据列表功能,这里没有查询数据库,使用了一些测试数据。
package com.java.ssm.web;
import com.java.ssm.domain.Score;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;
@Controller
@RequestMapping("/score")
public class ScoreController {
public static List<Score> list = new ArrayList<>();
public static Random random = new Random();
/**
* 成绩列表
* @return
*/
@RequestMapping("/list")
public ModelAndView list() {
ModelAndView mv = new ModelAndView();
for (int i = 0 ; i < 10 ; i++ ){
String name = "测试员 " + (i + 1);
int chinese = random.nextInt(100);
int mathematics = random.nextInt(100);
int english = random.nextInt(100);
Score score = new Score(name , chinese , mathematics , english);
list.add(score);
}
mv.addObject("list" , list);
mv.setViewName("Score/list");
return mv;
}
}
3、编写列表页面
页面文件: list.jsp
注意试图解析器配置,在相应的地方创建jsp页面文件,否则会找出现不到页面错误。
<!-- 配置视图解析器 -->
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!-- JSP文件所在的目录 -->
<property name="prefix" value="/WEB-INF/pages/" />
<!-- 文件的后缀名 -->
<property name="suffix" value=".jsp" />
</bean>
我这里是在 /WEB-INF/pages/ 目录下创建了 Score目录,然后再 Score 目录下创建 list.jsp 文件
list.jsp 文件代码:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<html>
<head>
<title>成绩表</title>
</head>
<body>
<input type="button" id="exportBtn1" style="width: 140px;background-color: blue;color: #FFFFFF;height: 30px;border: none;
border-radius: 5px;" value="后端导出" />
<input type="button" style="width: 140px;background-color: grey;color: #FFFFFF;height: 30px;border: none;
border-radius: 5px;" value="前端导出" >
<br>
<br>
<table border="1" style="width: 500px;text-align: -webkit-center;text-align: center;border-collapse: collapse;">
<tr>
<th>序号</th>
<th>姓名</th>
<th>语文</th>
<th>数学</th>
<th>英语</th>
</tr>
<c:forEach items="${requestScope.list}" var="score" varStatus="status" >
<tr>
<td>${status.index + 1}</td>
<td>${score.name}</td>
<td>${score.chinese}</td>
<td>${score.mathematics}</td>
<td>${score.english}</td>
</tr>
</c:forEach>
</table>
<script type="text/javascript">
var rootPath = "<%=basePath %>";
var exportBtn1 = document.getElementById("exportBtn1");
exportBtn1.addEventListener("click" , function(){
alert("后端导出");
var exportUrl = rootPath + "score/export";
window.open(exportUrl);
})
</script>
</body>
</html>
4、测试查询列表
访问地址: http://localhost:8080/score/list
5、实现数据导出到xls文件中(单表格)
代码
在页面中,添加导出代码(上面的list.jsp文件中已经有了):
<script type="text/javascript">
var rootPath = "<%=basePath %>";
var exportBtn1 = document.getElementById("exportBtn1");
exportBtn1.addEventListener("click" , function(){
alert("后端导出");
var exportUrl = rootPath + "score/export";
window.open(exportUrl);
})
</script>
控制器类中,添加导出数据代码:
/**
* 导出数据
* @return
*/
@RequestMapping("/export")
public String export(HttpServletResponse response){
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
StringBuffer fileNameBuffer = new StringBuffer("");
fileNameBuffer.append("成绩表").append("-").append(sdf.format(new Date())).append(".xls");
String filename = URLEncoder.encode(fileNameBuffer.toString(), "UTF-8");
//定义导出信息
//表名定义
String shellNameObjects[] = {
"成绩表"
};
//列名定义
String shellHeaderObjects[][] = new String[][]{
{"姓名", "语文", "数学" , "英语"},
};
//创建工作表
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
//创建表格名称
HSSFSheet sheet0 = wb.createSheet(shellNameObjects[0]);
//设置每行每列单元格大小
sheet0.autoSizeColumn(1, true);
//单独设置每列的宽度:这个非常有用,假设有些列内容过长时,就需要设置一定的长度显示内容了。不设置则使用默认宽度。
sheet0.setColumnWidth(0 , 25 * 256);
//sheet0.setColumnWidth(2 , 80 * 256);
//创建(填充)表格标题(表格第一行)
HSSFRow sheet0HeaderRow = sheet0.createRow(0);
sheet0HeaderRow.setHeightInPoints(20);//目的是想把行高设置成20px
for (int i = 0; i < shellHeaderObjects[0].length; i++) {
HSSFCell cell = sheet0HeaderRow.createCell((short) i);
cell.setCellValue(shellHeaderObjects[0][i]);
cell.setCellStyle(style);
}
//填充表格(单元格)内容
if (null != list && list.size() > 0)
{
//从表格第二行开始填充,因为第一行已经填充标题了
int index = 1;
for (Score bean: list){
HSSFRow sheetRow = sheet0.createRow(index);
sheetRow.setHeightInPoints(30);//目的是想把行高设置成20px
sheetRow.createCell(0).setCellValue(bean.getName());
sheetRow.createCell(1).setCellValue(bean.getChinese());
sheetRow.createCell(2).setCellValue(bean.getMathematics());
sheetRow.createCell(3).setCellValue(bean.getEnglish());
index++;
}
}
// 回去输出流
OutputStream out = response.getOutputStream();
response.setHeader("Cache-Control", "private");
response.setHeader("Pragma", "private");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Type", "application/force-download");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
功能截图:
6、实现数据导出到xls文件中(多表格)
修改控制器类中的导出数据代码:
/**
* 导出数据
* @return
*/
@RequestMapping("/export")
public String export(HttpServletResponse response){
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
StringBuffer fileNameBuffer = new StringBuffer("");
fileNameBuffer.append("成绩表多表导出").append("-").append(sdf.format(new Date())).append(".xls");
String filename = URLEncoder.encode(fileNameBuffer.toString(), "UTF-8");
//定义导出信息
//表名定义
String shellNameObjects[] = {
"成绩表" , "语文数学表" , "语文英语表"
};
//列名定义
String shellHeaderObjects[][] = new String[][]{
{"姓名", "语文", "数学" , "英语"},
{"姓名", "语文", "数学" },
{"姓名", "语文", "英语" }
};
//创建工作表
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
/*************** 创建表1 (成绩表) ****************/
//创建表格名称
HSSFSheet sheet0 = wb.createSheet(shellNameObjects[0]);
//设置每行每列单元格大小
sheet0.autoSizeColumn(1, true);
//单独设置每列的宽度:这个非常有用,假设有些列内容过长时,就需要设置一定的长度显示内容了。不设置则使用默认宽度。
sheet0.setColumnWidth(0 , 25 * 256);
//sheet0.setColumnWidth(2 , 80 * 256);
//创建(填充)表格标题(表格第一行)
HSSFRow sheet0HeaderRow = sheet0.createRow(0);
sheet0HeaderRow.setHeightInPoints(20);//目的是想把行高设置成20px
for (int i = 0; i < shellHeaderObjects[0].length; i++) {
HSSFCell cell = sheet0HeaderRow.createCell((short) i);
cell.setCellValue(shellHeaderObjects[0][i]);
cell.setCellStyle(style);
}
//填充表格(单元格)内容
if (null != list && list.size() > 0)
{
//从表格第二行开始填充,因为第一行已经填充标题了
int index = 1;
for (Score bean: list){
HSSFRow sheetRow = sheet0.createRow(index);
sheetRow.setHeightInPoints(30);//目的是想把行高设置成20px
sheetRow.createCell(0).setCellValue(bean.getName());
sheetRow.createCell(1).setCellValue(bean.getChinese());
sheetRow.createCell(2).setCellValue(bean.getMathematics());
sheetRow.createCell(3).setCellValue(bean.getEnglish());
index++;
}
}
/*************** 创建表2 (语文数学表) ****************/
//创建表格名称
HSSFSheet sheet1 = wb.createSheet(shellNameObjects[1]);
//设置每行每列单元格大小
sheet1.autoSizeColumn(1, true);
//单独设置每列的宽度:这个非常有用,假设有些列内容过长时,就需要设置一定的长度显示内容了。不设置则使用默认宽度。
sheet1.setColumnWidth(0 , 25 * 256);
//sheet1.setColumnWidth(2 , 80 * 256);
//创建(填充)表格标题(表格第一行)
HSSFRow sheet1HeaderRow = sheet1.createRow(0);
sheet1HeaderRow.setHeightInPoints(20);//目的是想把行高设置成20px
for (int i = 0; i < shellHeaderObjects[1].length; i++) {
HSSFCell cell = sheet1HeaderRow.createCell((short) i);
cell.setCellValue(shellHeaderObjects[1][i]);
cell.setCellStyle(style);
}
//填充表格(单元格)内容
if (null != list && list.size() > 0)
{
//从表格第二行开始填充,因为第一行已经填充标题了
int index = 1;
for (Score bean: list){
HSSFRow sheetRow = sheet1.createRow(index);
sheetRow.setHeightInPoints(30);//目的是想把行高设置成20px
sheetRow.createCell(0).setCellValue(bean.getName());
sheetRow.createCell(1).setCellValue(bean.getChinese());
sheetRow.createCell(2).setCellValue(bean.getMathematics());
index++;
}
}
/*************** 创建表3 (语文英语表) ****************/
//创建表格名称
HSSFSheet sheet2 = wb.createSheet(shellNameObjects[2]);
//设置每行每列单元格大小
sheet2.autoSizeColumn(1, true);
//单独设置每列的宽度:这个非常有用,假设有些列内容过长时,就需要设置一定的长度显示内容了。不设置则使用默认宽度。
sheet2.setColumnWidth(0 , 25 * 256);
//sheet2.setColumnWidth(2 , 80 * 256);
//创建(填充)表格标题(表格第一行)
HSSFRow sheet2HeaderRow = sheet2.createRow(0);
sheet2HeaderRow.setHeightInPoints(20);//目的是想把行高设置成20px
for (int i = 0; i < shellHeaderObjects[2].length; i++) {
HSSFCell cell = sheet2HeaderRow.createCell((short) i);
cell.setCellValue(shellHeaderObjects[2][i]);
cell.setCellStyle(style);
}
//填充表格(单元格)内容
if (null != list && list.size() > 0)
{
//从表格第二行开始填充,因为第一行已经填充标题了
int index = 1;
for (Score bean: list){
HSSFRow sheetRow = sheet2.createRow(index);
sheetRow.setHeightInPoints(30);//目的是想把行高设置成20px
sheetRow.createCell(0).setCellValue(bean.getName());
sheetRow.createCell(1).setCellValue(bean.getChinese());
sheetRow.createCell(2).setCellValue(bean.getEnglish());
index++;
}
}
// 回去输出流
OutputStream out = response.getOutputStream();
response.setHeader("Cache-Control", "private");
response.setHeader("Pragma", "private");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Type", "application/force-download");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
功能截图: