Apache POI实现Excel导入，导出（附项目源码+笔记）
程序员文章站
2022-06-02 23:30:01
...
本项目基于SpringBoot，做之前先要保证自己的SpringBoot项目能跑起来哦！
准备工作
准备数据库：
DROP TABLE IF EXISTS `poitest`;
CREATE TABLE `poitest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`birthday` date NOT NULL,
`sex` int(255) NOT NULL COMMENT '1:男 2：女',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `poitest` VALUES (1, '张三', '123456', '2020-06-03', 1);
INSERT INTO `poitest` VALUES (2, '李四', '123456', '2020-06-03', 2);
INSERT INTO `poitest` VALUES (3, '王五', '123456', '2020-06-03', 1);
添加依赖：
<!-- poi处理excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
1.POI实现将数据库数据导出到Excel中
三层架构的写法这里不在多说了。
需要用到的工具类：
DateUtil
package com.xiexuchun.Utils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
public class DateUtil {
/**
* yyyyMMdd hhmmssSSS
* 日期对象转字符串
*/
public static String formatDate(Date date,String format){
String result="";
SimpleDateFormat sdf=new SimpleDateFormat(format);
if(date!=null){
result=sdf.format(date);
}
return result;
}
/**
* 字符串转日期对象
* @param str
* @param format
* @return
* @throws Exception
*/
public static Date formatString(String str,String format){
if(StringUtil.isEmpty(str)){
return null;
}
SimpleDateFormat sdf=new SimpleDateFormat(format);
try {
return sdf.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
public static String getCurrentDateStr()throws Exception{
Date date=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
return sdf.format(date);
}
/**
* 输入一个月份，2017-01
* 输出 2017-01-31
*/
public static String getDays(String str){
str = dateAddMonth(str, "yyyy-MM", 1);//2017-01---2017-02
str = str+"-01";//2017-02-01
str = dateAddDay(str, "yyyy-MM-dd", -1);////2017-01-31
return str;
}
/**
* 算2个时间差几分
* @param end
* @param begin
* @return
*/
public static long date_between(Date end,Date begin ){
/*SimpleDateFormat dfs = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date begin=dfs.parse("2004-01-02 11:30:24");
Date end = dfs.parse("2004-01-02 11:31:40");*/
long between=(end.getTime()-begin.getTime())/1000;//除以1000是为了转换成秒
long min=between/60;
System.out.println("差几分"+min);
return min;
}
/**
* 任意格式的日期
* @param date1
* @param date2
* @return 1比2大 返回1 相等返回0 小于返回-1
*/
public static int compareTo(String date1,String date2){
return date1.compareTo(date2);
}
/**
* 任意格式 加1天 或者 加-1天
* @param dateStr 时间字符串 2018-05-05 12:11
* @param dateFormat 时间格式 yyyy-MM-dd HH:mmssSSS
* @param n 加多少天
*/
public static String dateAddDay(String dateStr,String dateFormat, int n) {
try {
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
Calendar cd = Calendar.getInstance();
cd.setTime(sdf.parse(dateStr));
cd.add(Calendar.DATE, n);// 增加一天
// cd.add(Calendar.MONTH, n);//增加一个月
return sdf.format(cd.getTime());
} catch (Exception e) {
return null;
}
}
/**
* 任意格式 加1月 或者 加-1月
* @param dateStr 时间字符串 2018-05-05 12:11
* @param dateFormat 时间格式 yyyy-MM-dd HH:mmssSSS
* @param n 加多少月
*/
public static String dateAddMonth(String dateStr,String dateFormat, int n) {
try {
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
Calendar cd = Calendar.getInstance();
cd.setTime(sdf.parse(dateStr));
//cd.add(Calendar.DATE, n);// 增加一天
cd.add(Calendar.MONTH, n);//增加一个月
return sdf.format(cd.getTime());
} catch (Exception e) {
return null;
}
}
/**
* 任意格式 加1小时 或者 加-1小时
* @param dateStr 时间字符串 2018-05-05 12:11
* @param dateFormat 时间格式 yyyy-MM-dd HH:mmssSSS
* @param n 加多少月
*/
public static String dateAddHour(String dateStr,String dateFormat, int n) {
try {
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
Calendar cd = Calendar.getInstance();
cd.setTime(sdf.parse(dateStr));
cd.add(Calendar.HOUR, n);//增加一个月
return sdf.format(cd.getTime());
} catch (Exception e) {
return null;
}
}
}
ResponseUtil
package com.xiexuchun.Utils;
import java.io.OutputStream;
import java.io.PrintWriter;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
public class ResponseUtil {
public static void write(HttpServletResponse response,Object o)throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out=response.getWriter();
out.println(o.toString());
out.flush();
out.close();
}
public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}
StringUtil
package com.xiexuchun.Utils;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
public class StringUtil {
/**
* 判断是否是空
*/
public static boolean isEmpty(String str) {
if (str == null || "".equals(str.trim())) {
return true;
} else {
return false;
}
}
/**
* 判断是否不是空
*/
public static boolean isNotEmpty(String str) {
if ((str != null) && !"".equals(str.trim())) {
return true;
} else {
return false;
}
}
/**
* 格式化模糊查询
*/
public static String formatLike(String str) {
if (isNotEmpty(str)) {
return "%" + str + "%";
} else {
return null;
}
}
}
Controller写法：
PoiDownloadController:
package com.xiexuchun.controller;
import com.xiexuchun.Utils.DateUtil;
import com.xiexuchun.Utils.ResponseUtil;
import com.xiexuchun.pojo.Poi;
import com.xiexuchun.service.PoiService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
/**
* @author xie_xuchun
* @create 2020-06-13 22:52
*/
@RestController
public class PoiDownloadController {
@Resource
private PoiService poiService;
@RequestMapping("listdown")
public void listdown(HttpServletResponse response, HttpServletRequest request) throws Exception {
String realPath = request.getServletContext().getRealPath("/");
List<Poi> allPoi = poiService.getAllPoi();
System.out.println("allPoi:"+allPoi);
Workbook wb = fillExcelDataWithTemplate(allPoi, "client_down_model.xls");
ResponseUtil.export(response,wb,"poi.xls");
}
/**
* @param templateFileUrl
* excel模板的路径 client_down_model.xls 也可以自定义
* @return
*/
public static Workbook fillExcelDataWithTemplate(List<Poi> allPoi , String templateFileUrl) {
POIFSFileSystem fs = null;
Workbook wb = null ;
try {
fs = new POIFSFileSystem(new FileInputStream(templateFileUrl));
wb = new HSSFWorkbook(fs);
// 取得 模板的 第一个sheet 页
Sheet sheet = wb.getSheetAt(0);
// 拿到sheet页有 多少列
int cellNums = sheet.getRow(0).getLastCellNum();
// 从第2行 开搞 下标1 就是第2行
int rowIndex = 1;
Row row ;
for(Poi poi : allPoi){
row = sheet.createRow(rowIndex);
rowIndex ++;
row.createCell(0).setCellValue(poi.getId());
row.createCell(1).setCellValue(poi.getUsername());
row.createCell(2).setCellValue(poi.getPassword());
row.createCell(3).setCellValue(DateUtil.formatDate(poi.getBirthday(), "yyyy-MM-dd"));
row.createCell(4).setCellValue(poi.getSex());
//这里按照你数据库中的情况写
}
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
}
client_down_model.xls 的解释：
我们按照数据库结构新建了一个xls格式的Excel文档，作为模板，放在项目根目录下，下载的时候只是在这个模板的基础上进行数据的填充。这里注意格式，是xls格式，不是xlsx格式哦！这样通过前端listdown请求就可以实现下载。
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>POI报表</title>
</head>
<body>
<a href="/listdown"><input type="button" value="下载报表" name="download"></a>
<input type="file" value="上传文件" name="upload">
</body>
</html>
这里将下载报表按钮写到a标签中。 当然我页面写的得简单，意思说明白即可。
下载成功！
这样就可以实现将数据库中的数据批量导出到Excel文件中。
2.POI实现上传Excel并写到数据库
需要的工具类：
ExcelUtil:
package com.xiexuchun.Utils;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
public class ExcelUtil {
public static void main(String[] args) throws IOException {
}
/**
* 格式化单元格返回其内容 格式化成string返回。
*
* @param cell
* @return
*/
public static String formatCell(HSSFCell cell) {
if (cell == null) {
return "";
} else {
if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
}
/**
* 返回int类型
*/
public static int formatInt(HSSFCell cell){
int a = 0;
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
a = (int)cell.getNumericCellValue();
}
return a;
}
/**
* 返回 日期 date 2018/3/28 14:18:00 这种类型的可以。
*/
public static Date formatDate(HSSFCell cell) throws ParseException {
String str = cell.toString();
Date date = null;
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
date = new SimpleDateFormat("yyyy-MM-dd").parse(str);
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
date = cell.getDateCellValue();
}
return date;
}
/**
* 返回 BigDecimal 数据
*/
public static BigDecimal formatBigDecimal(HSSFCell cell) throws ParseException {
String str = ExcelUtil.formatCell(cell);
BigDecimal num = new BigDecimal(str.trim());
return num;
}
}
FileUtil
package com.xiexuchun.Utils;
import java.io.File;
public class FileUtil {
/**
* 创建一个文件夹
* 如果存在 不创建
* 如果不存在 创建
* @param filePath
* @return
*/
public static boolean makeDirs(String filePath) {
File folder = new File(filePath);
return (folder.exists() && folder.isDirectory()) ? true : folder.mkdirs();
}
/**
* 删除单个文件
* 路径 是全路径
* c盘啥啥的全路径
* @param fileName 要删除的文件的文件名
* @return 单个文件删除成功返回true，否则返回false
*/
public static boolean deleteFile(String fileName) {
File file = new File(fileName);
// 如果文件路径所对应的文件存在，并且是一个文件，则直接删除
if (file.exists() && file.isFile()) {
if (file.delete()) {
return true;
} else {
//System.out.println("删除单个文件" + fileName + "失败！");
return false;
}
} else {
//System.out.println("删除单个文件失败：" + fileName + "不存在！");
return false;
}
}
}
三层架构的写法这里不在多说了。注意PoiMapper.xml写法：
<insert id="insertPoi" parameterType="com.xiexuchun.pojo.Poi">
INSERT into poitest (username,`password`,birthday,sex) VALUES(#{username},#{password},#{birthday},#{sex});
</insert>
Controller写法：
PoiUploadController
package com.xiexuchun.controller;
import com.xiexuchun.Utils.DateUtil;
import com.xiexuchun.Utils.ExcelUtil;
import com.xiexuchun.Utils.FileUtil;
import com.xiexuchun.pojo.Poi;
import com.xiexuchun.service.PoiService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.boot.configurationprocessor.json.JSONObject;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author xie_xuchun
* @create 2020-06-14 9:12
*/
@RestController
public class PoiUploadController {
@Resource
private PoiService poiService;
/**
* 接受文件 解析 上传资料。
* upload_excel
*/
@RequestMapping("/upload_excel")
public int upload_excel(@RequestParam("upload") MultipartFile file, HttpServletResponse response, HttpServletRequest request)throws Exception {
//这里的upload要和页面name属性保持一致。
System.out.println(file.getOriginalFilename()); //取得文件的名称
int i=0;
if(!file.isEmpty()){
String webPath=request.getServletContext().getRealPath("");
String filePath= "/excel";
//把文件名子换成（时间搓+文件名）
String fileName=DateUtil.formatDate(new Date(), "yyyyMMdd-HHmmssSSS")+"_"+file.getOriginalFilename();
//String fileName= DateUtil.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss")+"_"+file.getOriginalFilename();
//检测文件夹是否存在，不存在则生成filepath路径的文件夹
FileUtil.makeDirs(webPath+filePath);
//保存服务器
file.transferTo(new File(webPath+filePath+fileName));
//保存服务器
//解析 excel
List<Poi> list = excelInfo(new File(webPath+filePath+fileName));
//解析
//开始 上传 数据库
for(Poi poi:list) {
i = poiService.insertPoi(poi);
}
//开始 上传 数据库
//删除用过的文件
FileUtil.deleteFile(webPath+filePath+fileName);
//删除用过的文件
}
return i;
}
private List<Poi> excelInfo(File userUploadFile) throws ParseException {
List<Poi> list = new ArrayList<Poi>();
Poi poi = null;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
//获取第一个sheet页
HSSFSheet sheet = wb.getSheetAt(0);
if(sheet!=null){
//1 代表从第二行开始
for(int rowNum =1;rowNum<=sheet.getLastRowNum();rowNum++){
HSSFRow row = sheet.getRow(rowNum);
if(row==null){
continue;
}
poi = new Poi();
poi.setId(ExcelUtil.formatInt(row.getCell(0)));
poi.setUsername(ExcelUtil.formatCell(row.getCell(1)));
poi.setPassword(ExcelUtil.formatCell(row.getCell(2)));
poi.setBirthday(ExcelUtil.formatDate(row.getCell(3)));
poi.setSex(ExcelUtil.formatInt(row.getCell(4)));
list.add(poi);
//根据需要的数据类型调用不同的方法。主要有三种，String，int，date
}
}
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
}
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>POI报表</title>
</head>
<script type="text/javascript" src="js/jquery-1.8.12.js"></script>
<!--添加异步 上传 的js 支持加载-->
<script type="text/javascript" src="AjaxFileUpload/ajaxfileupload.js"></script>
<!--添加异步 上传 的js 支持加载-->
<body>
<a href="/listdown"><input type="button" value="下载报表" name="download"></a>
<input type="file" id="file" value="上传文件" name="upload" onchange="uploadFile(this)"></input>
</body>
<script type="text/javascript">
function uploadFile(file) {
$.ajaxFileUpload({
url: '/upload_excel', //用于文件上传的服务器端请求地址
secureuri: false, //一般设置为false
fileElementId: 'file', //文件上传空间的id属性 <input type="file" id="file" name="file" />
type: 'post',
dataType: 'text', //返回值类型 一般设置为
success: function (res){ //服务器成功响应处理函数
alert("上传成功！"+res);
},
error: function (result){//服务器响应失败处理函数
alert("上传失败！")
}
});
return false;
}
</script>
</html>
注意html引入的几个资源。ajaxfileupload.js
这里先要说明一点，还是和之前一样，数据库结构和xls结构用保持一致。
测试成功！
项目源码我已上传百度网盘，如有需要可自行下载。
链接: https://pan.baidu.com/s/1rG4dpy2yPKEypkBLW3uSFw.
提取码：4ifl