【背景描述】
最近做项目的时候经常遇到需要将清单导出下载的功能,大致流程就是从数据库中读取数据,然后写入Excel表格,然后实现文件流写出,或者在本地生成一个文件,或者前端网页实现下载。
【需要的jar包】
和POI相关的有(去官网下载POIjar包,注意不同版本可能写法会有些许出入):
poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-2.6.0.jar
commons-collections4-4.1.jar
MSSQL JDBC 链接数据库: mssql-jdbc-6.2.2.jre8.jar
MyBatis框架:mybatis-3.4.4.jar
日志输出打印:log4j-1.2.17.jar log4j-core-2.11.0.jar
【项目结构】
【Web版本】
index.jsp <body>添加【下载】按钮
<form action="ExportDataServlet" method="post">
<input type="submit" value="下载">
</form>
ExportDataServlet.java
package com.demo.servlet;
import com.demo.beans.UserInfoBean;
import com.demo.service.UserInfoDataService;
import com.demo.tools.ExcelUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
@WebServlet("/ExportDataServlet")
public class ExportDataServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("-------------ExportDataServlet----------------");
UserInfoDataService userInfoDataService = new UserInfoDataService();
//get数据
ArrayList<UserInfoBean> userInfoBeanList = userInfoDataService.selectAll();
String[] headerName = {"ID号", "姓名", "出生日期", "国籍"};
String[] headerKey = {"id", "username", "birthdate", "nationality"};
XSSFWorkbook workbook = ExcelUtils.createExcelTwo(userInfoBeanList);
if (workbook == null) {
System.out.println("---------------------workbook 为空!--------------------");
return;
}
response.reset();
response.setContentType("application/vnd.ms-excel");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
Date date = new Date();
String str = sdf.format(date);
String fileName = "用户信息表" + str;
response.setHeader("Content-Disposition", "attachment;filename=" +
new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
System.out.println("fileName: " + fileName);
OutputStream outputStream = response.getOutputStream();
outputStream.flush();
workbook.write(outputStream);
outputStream.close();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
首先从数据库中读取数据,保存到一个ArrayList userInfoBeanList中 ,具体代码省略,整个过程比较简单,可参考:
https://blog.csdn.net/coralime/article/details/81663354
需要注意的是这里用的是mybatis框架,也可以用原生的JDBC经典模式。
JDBC-DAO经典模式 实现对数据库的增、删、改、查:https://www.cnblogs.com/hoobey/p/5293958.html
使用JDBD驱动程序:https://docs.microsoft.com/zh-cn/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-2017
设置Excel表格的表头和表头的key值,然后新建一个XSSFWorkbook,通过ExcelUtils类的createExcelTwo函数来生成,稍后贴代码,将userInfoBeanList转化成workbook。
ExcelUtils.java
package com.demo.tools;
import com.demo.beans.UserInfoBean;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class ExcelUtils {
public ExcelUtils(){
super();
}
// 限定了下载数据的类型,list固定的
public static XSSFWorkbook createExcelTwo(ArrayList<UserInfoBean> listdata){
//---操作.xls 用HSSFWorkbook 操作.xlsx用XSSFWorkbook--
// 创建一个workbook,对应一个Excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel中的一个sheet
XSSFSheet sheet = workbook.createSheet("用户详情表");
// 在sheet中添加表头第0行,老版本poi对Excel行数列数有限制short
XSSFRow row = sheet.createRow((int)0);
// 创建单元格,设置值表头,设置表头居中
XSSFCellStyle style = workbook.createCellStyle();
//居中格式
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
//设置表头
String[] titles = new String[]{"ID号","用户名","出生日期","国籍"};
//创建表头单元格
XSSFCell cell = null;
for(int i = 0; i < titles.length; i++){
cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
}
// 生成Excel格式后要将数据写入Excel,循环
for(int i = 0; i<listdata.size(); i++){
row = sheet.createRow(i+1);
UserInfoBean bean = listdata.get(i);
row.createCell(0).setCellValue(bean.getID());
row.createCell(1).setCellValue(bean.getUsername());
row.createCell(2).setCellValue(bean.getBirthdate());
row.createCell(3).setCellValue(bean.getNationality());
}
//将Excel以流输出,不弹出下载框
// try {
// FileOutputStream outputStream = new FileOutputStream("F:\\test.xlsx");
// workbook.write(outputStream);
// outputStream.flush();
// outputStream.close();
// } catch (FileNotFoundException e) {
// e.printStackTrace();
// } catch (IOException e) {
// e.printStackTrace();
// }
if(workbook!=null){
System.out.println("workbook 不为空!");
}
return workbook;
}
}
输出结果:
【Console版本】
ExcelUtils.java
package com.mybatisdemo.tools;
import com.mybatisdemo.beans.UserInfoBean;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
public class ExcelUtils {
public ExcelUtils(){
super();
}
public void createExcelTwo(ArrayList<UserInfoBean> listdata){
//---操作.xls 用HSSFWorkbook 操作.xlsx用XSSFWorkbook--
// 创建一个workbook,对应一个Excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel中的一个sheet
XSSFSheet sheet = workbook.createSheet("用户详情表");
// 在sheet中添加表头第0行,老版本poi对Excel行数列数有限制short
XSSFRow row = sheet.createRow((int)0);
// 创建单元格,设置值表头,设置表头居中
XSSFCellStyle style = workbook.createCellStyle();
//居中格式
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
//设置表头
String[] titles = new String[]{"ID号","用户名","出生日期","国籍"};
//创建表头单元格
XSSFCell cell = null;
for(int i = 0; i < titles.length; i++){
cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
}
// 生成Excel格式后要将数据写入Excel,循环
for(int i = 0; i<listdata.size(); i++){
row = sheet.createRow(i+1);
UserInfoBean bean = listdata.get(i);
row.createCell(0).setCellValue(bean.getID());
row.createCell(1).setCellValue(bean.getUsername());
row.createCell(2).setCellValue(bean.getBirthdate());
row.createCell(3).setCellValue(bean.getNationality());
}
//将Excel以流输出,不弹出下载框
try {
FileOutputStream outputStream = new FileOutputStream("F:\\test.xlsx");
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Main.java
package com.mybatisdemo.main;
import com.mybatisdemo.beans.UserInfoBean;
import com.mybatisdemo.service.UserInfoDataService;
import com.mybatisdemo.tools.ExcelUtils;
import java.util.ArrayList;
public class Main {
public static void main(String[] args) {
// write your code here
//生成一个dataservice类辅助数据操作
UserInfoDataService userInfoDataService = new UserInfoDataService();
//查询所有记录
ArrayList<UserInfoBean> listdata = userInfoDataService.selectAll();
//---------------测试生成Excel并导出下载-------------
ExcelUtils excelUtils = new ExcelUtils();
excelUtils.createExcelTwo(listdata);
}
}
输出结果:在电脑本地F盘会生成一个 test.xlsx 文件
【参考】
#1-> POI操作Excel表格
APACHE-POI官网(提供最新版本的下载):https://poi.apache.org/
Apache POI使用详解:https://www.cnblogs.com/huajiezh/p/5467821.html
POI入门及简单应用:https://blog.csdn.net/u012453843/article/details/52924718
POI 3.17 读取Excel(模板)、导出Excel:https://blog.csdn.net/phil_jing/article/details/78307819
Java利用POI生成Excel表格:https://www.cnblogs.com/hjw-zq/p/9041956.html
java使用poi包将数据写入Excel表格:https://www.cnblogs.com/dingziyin/p/6208749.html
JAVA WEB POI Excel导出导入:https://blog.csdn.net/su19921021/article/details/49793801
利用POI将数据写入Excel:https://blog.csdn.net/youlinmin/article/details/69774889
java 生成Excel表传给前端下载:https://blog.csdn.net/BestSmilesHi/article/details/77503885
Java实现Excel的生成,利用POI jar包实现Excel的生成:https://blog.csdn.net/echohuangshihuxue/article/details/81837771
javaweb中如何使用POI把数据导出为Excel(有下载提示框)详细教程:https://blog.csdn.net/qq_39187822/article/details/79956440
Javaweb后端之Spring+HTTPServletResponse+WritableWorkbook实现网页下载Excel:https://www.cnblogs.com/scu0413biubiubiu/p/7274362.html
#2-> 点击按钮实现下载
使用js实现点击按钮下载文件:https://www.cnblogs.com/jasmine-95/p/6054652.html
js实现点击按钮,下载文件:https://blog.csdn.net/hamupp/article/details/71417921
java web 文件下载功能实现:https://blog.csdn.net/longshengguoji/article/details/39433307
java 从服务器下载文件到本地(页面、后台、配置都有):https://www.cnblogs.com/Donnnnnn/p/7902718.html
java浏览器文件下载实例(附源码下载地址):https://blog.csdn.net/Coding13/article/details/68942858