excel 导出，在项目使用中十分广泛，本文讲解如何导出 excel
本文使用 springboot + mybatis 框架
yml 配置文件如下
server:
port: 9001
spring:
datasource:
url: jdbc:mysql://105.42.21.92:3306/orcl?useUnicode=true&characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
mybatis:
mapper-locations: classpath:mapper/*Mapper.xml
config-location: classpath:mapper/config/sqlMapConfig.xml 1、首先在项目中引入 poi jar包，本文使用 poi 解析2003以下版本
<!--poi对excel2003以下版本的支持-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>2、数据库表结构及表中数据如下
建表及添加数据 sql 语句
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` double DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`user_addr` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '贾宝玉', '16', '怡红院');
INSERT INTO `user` VALUES ('2', '林黛玉', '16', '潇湘馆');
INSERT INTO `user` VALUES ('3', '薛宝钗', '17', '衡芜院');
INSERT INTO `user` VALUES ('4', '妙玉', '18', '栊翠庵');
INSERT INTO `user` VALUES ('5', '史湘云', '16', '藕香榭');
INSERT INTO `user` VALUES ('1', '唐三藏', '26', '长安');
INSERT INTO `user` VALUES ('2', '孙悟空', '526', '花果山');
INSERT INTO `user` VALUES ('3', '猪八戒', '700', '高老庄');
INSERT INTO `user` VALUES ('4', '沙悟净', '800', '流沙河');
INSERT INTO `user` VALUES ('5', '黑熊精', '1000', '黑风洞');
INSERT INTO `user` VALUES ('6', '金角', null, '金山');
INSERT INTO `user` VALUES ('7', null, '1000', '金山');
3、实体类如下
package com.demo.bean;
public class User {
private double userId;
private String userName;
private Integer age;
private String userAddr;
public double getUserId() {
return userId;
}
public void setUserId(double userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getUserAddr() {
return userAddr;
}
public void setUserAddr(String userAddr) {
this.userAddr = userAddr;
}
@Override
public String toString() {
return "User [userId=" + userId + ", userName=" + userName + ", age=" + age + ", userAddr=" + userAddr + "]";
}
}
4、mapper 层如下
package com.demo.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.demo.bean.User;
@Mapper
public interface UserMapper {
//批量将数据添加到数据库
int insertForeach(List<User> list);
List<User> getAllUserList();
}
5、mapper.xml 文件如下
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.demo.mapper.UserMapper">
<insert id="insertForeach" parameterType="java.util.List" useGeneratedKeys="false">
insert into user ( user_id, user_name, age, user_addr) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.userId}, #{item.userName}, #{item.age}, #{item.userAddr})
</foreach>
</insert>
<resultMap id="ResultMap" type="com.demo.bean.User">
<id column="user_id" property="userId" jdbcType="DOUBLE" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="user_addr" property="userAddr" jdbcType="VARCHAR" />
</resultMap>
<select id="getAllUserList" resultMap="ResultMap">
select * from user
</select>
</mapper>
6、controller 层代码如下
package com.demo.controller;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import com.demo.bean.User;
import com.demo.mapper.UserMapper;
@Controller
public class ExcelExportController {
@Autowired
private UserMapper userMapper;
@RequestMapping("/export")
public ModelAndView exportIndex() {
ModelAndView mav = new ModelAndView();
mav.setViewName("export");
return mav;
}
@RequestMapping("/exportExcel")
public void exportExcel(String fileName, HttpServletResponse response) {
List<User> list = userMapper.getAllUserList();
Workbook wb = new HSSFWorkbook(); //定义一个新的工作簿
Sheet sheet = wb.createSheet("sheet的名字"); //设置sheet名称
//设置表头
Row header = sheet.createRow(0);
for(int k=0; k<4; k++) {
Cell cell = header.createCell(k);
if(k == 0) {
cell.setCellValue("ID");
}
if(k == 1) {
cell.setCellValue("姓名");
}
if(k == 2) {
cell.setCellValue("年龄");
}
if(k == 3) {
cell.setCellValue("地址");
}
}
for(int i=0; i<list.size(); i++) {
Row row = sheet.createRow(i+1); //i+1 是从表头的下一行开始
for(int j=0; j<4; j++) {
Cell cell = row.createCell(j);
if(j == 0) {
cell.setCellValue(list.get(i).getUserId());
}
if(j == 1) {
//判断 list.get(i).getUserName() 为空的情况
if("".equals(list.get(i).getUserName())) {
//如果为空，设置默认值
cell.setCellValue("");
}else {
cell.setCellValue(list.get(i).getUserName());
}
}
if(j == 2) {
//判断 list.get(i).getAge() 为空的情况
if(null == list.get(i).getAge()) {
//如果为空，设置默认值
cell.setCellValue(0);
}else {
cell.setCellValue(list.get(i).getAge());
}
}
if(j == 3) {
cell.setCellValue(list.get(i).getUserAddr());
}
}
}
String webFileName;
try {
webFileName = URLEncoder.encode(fileName, "UTF-8");//解决浏览器下载中文乱码问题
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename="+webFileName+".xls");//Excel文件名
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
try {
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
}
7、创建 export.ftl 页面文件
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>excel导出</title>
</head>
<body>
<form action="/exportExcel">
<input type="text" name="fileName" />
<input type="submit" value="下载" />
</form>
</body>
</html>8、测试
浏览器访问 http://localhost:9001/export
随意输入 excel 的文件名，点击下载
打开 excel 文件
如果要解析 excel2007以上版本示例如下
先引入 jar 包
<!--poi对excel2007以上版本的支持-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>controller 层代码如下
package com.demo.controller;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import com.demo.bean.User;
import com.demo.mapper.UserMapper;
@Controller
public class Excel2007Controller {
@Autowired
private UserMapper userMapper;
@RequestMapping("/excel2007")
public ModelAndView excel2007() {
ModelAndView mav = new ModelAndView();
mav.setViewName("excel2007");
return mav;
}
@RequestMapping("/exportExcel2007")
public void exportExcel2007(String fileName, HttpServletResponse response) {
List<User> list = userMapper.getAllUserList();
XSSFWorkbook xwb = new XSSFWorkbook();
XSSFSheet xsheet = xwb.createSheet("自定义sheet名称");
XSSFRow xheader = xsheet.createRow(0);
for(int i=0; i<4; i++) {
XSSFCell xcell = xheader.createCell(i);
if(i == 0) {
xcell.setCellValue("ID");
}
if(i == 1) {
xcell.setCellValue("姓名");
}
if(i == 2) {
xcell.setCellValue("年龄");
}
if(i == 3) {
xcell.setCellValue("地址");
}
}
for(int i=0; i<list.size(); i++) {
XSSFRow xrow = xsheet.createRow(i+1);
for(int j=0; j<4; j++) {
XSSFCell xcell = xrow.createCell(j);
if(j == 0) {
xcell.setCellValue(list.get(i).getUserId());
}
if(j == 1) {
//判断 list.get(i).getUserName() 为空的情况
if("".equals(list.get(i).getUserName())) {
//如果为空，设置默认值
xcell.setCellValue("");
}else {
xcell.setCellValue(list.get(i).getUserName());
}
}
if(j == 2) {
//判断 list.get(i).getAge() 为空的情况
if(null == list.get(i).getAge()) {
//如果为空，设置默认值
xcell.setCellValue(0);
}else {
xcell.setCellValue(list.get(i).getAge());
}
}
if(j == 3) {
xcell.setCellValue(list.get(i).getUserAddr());
}
}
}
String webFileName;
try {
webFileName = URLEncoder.encode(fileName, "UTF-8");//解决浏览器下载中文乱码问题
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename="+webFileName+".xlsx");//Excel文件名
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
try {
xwb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
}
ftl 页面如下
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>excel2007导出</title>
</head>
<body>
<form action="/exportExcel2007">
<input type="text" name="fileName" />
<input type="submit" value="下载" />
</form>
</body>
</html>
963
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
