很多时候我们数据库里的数据需要导出到excel表上来进行分析,今天小编写了这篇文章来讲解java导出数据到excel中。
上面标注红框的包是导出到excel必须要的包,其他的可以根据自己的ssm框架酌情增减
标红框的是导出到excel需要配置的xml,其他的是ssm框架需要引入的配置文件,这需要大家有ssm框架的基础。下面是配置文件的代码,这个我没写具体的注释大家直接引用就行
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation=
"http://maven.apache.org/POM/4.0.0
http://maven.apache.org/maven-v4_0_0.xsd">
<dependencies>
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
</dependencies>
</project>
首页
<%@ 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>Insert title here</title>
</head>
<body>
<a href="${pageContext.request.contextPath }/user/export.do">导出</a>
</body>
</html>
控制层(Controller)
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.lm.pojo.User;
import com.lm.service.UserService;
/**
*
* @author LM
*
*/
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
User user;
/**
* 用于导出excel的查询结果
* @param queryJson
* @return
*/
@RequestMapping("/export.do")
public void export(HttpServletRequest request, HttpServletResponse response) {
//查询用户表的全部数据
List<User> userlList = new ArrayList<User>(userService.getUserDeviceForExcel());
//查询用户表有多少行记录
Integer count=userService.selectAll();
//创建excel表的表头
String[] headers = {"姓名", "性别", "年龄" };
//创建Excel工作簿
HSSFWorkbook workbook=new HSSFWorkbook();
//创建一个工作表sheet
HSSFSheet sheet=workbook.createSheet();
//创建第一行
HSSFRow row=sheet.createRow(0);
//定义一个单元格,相当于在第一行插入了三个单元格值分别是 "姓名", "性别", "年龄"
HSSFCell cell=null;
//插入第一行数据
for(int i=0;i<headers.length;i++) {
cell=row.createCell(i);
cell.setCellValue(headers[i]);
}
//追加数据
for(int i=1;i<=count;i++) {
HSSFRow nextrow=sheet.createRow(i);
HSSFCell cell2=nextrow.createCell(0);
cell2.setCellValue(userlList.get(i-1).getName());
cell2=nextrow.createCell(1);
cell2.setCellValue(userlList.get(i-1).getSex());
cell2=nextrow.createCell(2);
cell2.setCellValue(userlList.get(i-1).getAge());
}
//创建一个文件
File file=new File("e:/poi_test.xls");
try {
file.createNewFile();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
//将内容存盘
FileOutputStream stream;
try {
stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
Service层
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.lm.dao.UserDaoMapper;
import com.lm.pojo.User;
/**
*
* @author LM
*
*/
@Service
@Transactional
public class UserService {
@Autowired
private UserDaoMapper userDao;
/**
* 根据查询条件查询出所有的记录
*/
public List<User> getUserDeviceForExcel() {
return userDao.getUserForExcel();
}
/**
* 查询用户表总共有多少条记录
*/
public Integer selectAll() {
return userDao.selectAll();
}
}
Dao层
import java.util.List;
import com.lm.pojo.User;
public interface UserDaoMapper {
/**
* 根据查询条件查询出所有的记录
*/
List<User> getUserForExcel();
/**
* 查询用户表总共有多少条记录
* @return
*/
Integer selectAll();
}
Mapper映射文件
<?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.lm.dao.UserDaoMapper">
<!--根据查询条件查询出所有的记录,不用分页,用于excel导出功能 -->
<select id="getUserForExcel" parameterType="com.lm.pojo.User" resultType="com.lm.pojo.User">
select name,sex,age
from juser_table
</select>
<!-- 查询总共有多少条记录 -->
<select id="selectAll" resultType="Integer">
select count(*) from juser_table
</select>
</mapper>
数据库情况