Apache POI实现Excel导入，导出（附项目源码+笔记）

最新推荐文章于 2024-05-17 11:22:12 发布

傻啦猫@_@

最新推荐文章于 2024-05-17 11:22:12 发布

阅读量1.3k

点赞数 8

分类专栏： java开发文章标签： java

本文链接：https://blog.csdn.net/weixin_45842494/article/details/106754022

版权

java开发专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本项目基于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

傻啦猫@_@

关注

8
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
Apache POI实现Excel导入，导出（附项目源码+笔记）

本项目基于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(2
复制链接

扫一扫