使用poi进行Excel进行导入与导出数据

本文链接：https://blog.csdn.net/qq_39884965/article/details/88993330

poi进行Excel进行导入与导出数据
1.数据库
在这里插入图片描述
2.我用的SpringBoot+poi下面是我的目录结构

3.application.yml配置文件

4.pom.xml文件

<?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/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.zkyl</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.0.0</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.16-beta2</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

**5.pojo实体类**

**(1)**Pemo

package com.zkyl.pojo;

import lombok.Data;

/**
 * Created by libaofeng on 2019/3/29.
 */
@Data
public class Pemo {
    private Integer pid;
    private String pname;
    private String psex;
    private String paddress;
    private Integer page;

}

(2)PemoImportVo(负责结束Excel上的数据的实体类)

package com.zkyl.pojo;

import lombok.Data;

/**
 * Created by libaofeng on 2019/3/29.
 */
@Data
public class PemoImportVo {
    private String pid;
    private String pname;
    private String psex;
    private String paddress;
    private String page;

}

6.Controller

package com.zkyl.controller;

import com.zkyl.pojo.Pemo;
import com.zkyl.service.PemoService;
import com.zkyl.util.ResultVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * Created by libaofeng on 2019/3/29.
 */
@Controller
public class PemoController {
    @Autowired
    private PemoService pemoService;

    @ResponseBody
    @GetMapping("queryPemo")
    public List<Pemo> queryPemo() {
        return pemoService.querryPemo();
    }

    @GetMapping("poifile111")
    public String poifile(Model model) {
        return "poifile";
    }

    @ResponseBody
    @PostMapping("exportExcel1")
    public ResultVO exportExcel(HttpServletResponse response) throws Exception {
        return pemoService.exportExcel(response);
    }
    @ResponseBody
    @PostMapping("exportExcel")
    public ResultVO<List<Pemo>> importFile(MultipartFile file) throws Exception {
        return pemoService.importFile(file);

    }
}
**7.mapper接口与mapper.xml**

package com.zkyl.mapper;

import com.zkyl.pojo.Pemo;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * Created by libaofeng on 2019/3/29.
 */
@Mapper
public interface PemoMapper {
  List<Pemo> querryPemo();
  int insertPemo(List<Pemo> pemoList);
}


<?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.zkyl.mapper.PemoMapper">
<select id="querryPemo" resultType="com.zkyl.pojo.Pemo">
    SELECT *FROM  pemo;
</select>


    <insert id="insertPemo" parameterType="java.util.List">
        INSERT into pemo (pname,psex,paddress,page) VALUES
        <foreach collection="list" item="item" open="(" close=")" separator="),(">
            #{item.pname},#{item.psex},#{item.paddress},#{item.page}
        </foreach>
    </insert>
    </mapper>

8.util工具类 ExportUtil,POIClass,ResultVO,ResultVOBuilder

package com.zkyl.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * excel导出数据
 * @author dk
 */
@Slf4j
public class ExportUtil {

	private ExportUtil(){}
	
	/**
	 * 封装返回的流信息
	 * @param response
	 * @param fileName
	 * @throws Exception
	 */
	public static void toPackageOs(HttpServletResponse response , String fileName)throws Exception{
		response.setContentType("application/octet-stream;charset=utf-8");  
		String outFileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		response.setHeader("Content-Disposition", "attachment;filename=" + new String(outFileName.getBytes(),"iso-8859-1") + ".xls");  
	}

	public static void toPackageOs(HttpServletResponse response , String fileName, String suffix)throws Exception{
		response.setContentType("application/octet-stream;charset=utf-8");
		String outFileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		response.setHeader("Content-Disposition", "attachment;filename=" + new String(outFileName.getBytes(),"iso-8859-1") + suffix);
	}

	/**
	 * 生成zip返回流头部信息
	 * @param response
	 * @param zipName
	 * @throws Exception
	 */
	public static void toPackageZipOs(HttpServletResponse response , String zipName)throws Exception{
		response.setContentType("APPLICATION/OCTET-STREAM");  
	    response.setHeader("Content-Disposition","attachment; filename="+new String(zipName.getBytes(),"iso-8859-1"));
	}
	
	/**
	 * 生成模板输入流
	 * @param temPath
	 * @return
	 * @throws Exception
	 */
	public static InputStream toPackageIn(String temPath)throws Exception{
		return new ClassPathResource((temPath)).getInputStream();
	}
	
	/**
	 * 一次性导出全部数据
	 * @param <T>
	 * @param list
	 * @param os
	 */
	/*public static <T> void exportExcel(List<T> list, OutputStream os ,
			InputStream in)throws Exception{
		long exportExcelBegin = System.currentTimeMillis();
		log.warn("exportExcel begin: " + exportExcelBegin);
		Context context = new Context();
		context.putVar("list", list);
		JxlsHelper.getInstance().processTemplateAtCell(in, os, context, "Result!A1");
        os.flush();
		long exportExcelEnd = System.currentTimeMillis();
		log.warn("exportExcel fininshed in: " + (exportExcelEnd - exportExcelBegin));
	}*/
	

	
	/**
	 * 压缩制定目录下的文件, 生成并下载zip文件
	 * @param srcFile 目标目录
	 * @param zipPath 生成的zip文件的全路径
	 * @param os 返回流  把zip流写到返回流中
	 * @throws Exception
	 */
	public static void zipExcel(String srcFile , OutputStream os)throws Exception{
		// 要被压缩的文件夹
        File file = new File(srcFile);
        InputStream input = null;
        ZipOutputStream zipOut = new ZipOutputStream(os);
        // zip的名称为
        zipOut.setComment(file.getName());
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; ++i) {
                input = new FileInputStream(files[i]);
                zipOut.putNextEntry(new ZipEntry(file.getName() + File.separator + files[i].getName()));
                int temp = 0;
                while ((temp = input.read()) != -1) {
                    zipOut.write(temp);
                }
                input.close();
            } 
        }
        
        zipOut.close();
	}
	
	/**
	 * 删除文件夹下所有的文件
	 * @param path
	 * @return
	 */
	public static boolean delAllFile(String path) {
       boolean flag = false;
       File file = new File(path);
       if (!file.exists()) {
         return flag;
       }
       if (!file.isDirectory()) {
         return flag;
       }
       String[] tempList = file.list();
       File temp = null;
       for (int i = 0; i < tempList.length; i++) {
          if (path.endsWith(File.separator)) {
             temp = new File(path + tempList[i]);
          } else {
              temp = new File(path + File.separator + tempList[i]);
          }
          if (temp.isFile()) {
             temp.delete();
          }
          if (temp.isDirectory()) {
             delAllFile(path + "/" + tempList[i]);//先删除文件夹里面的文件
             flag = true;
          }
       }
       return flag;
	}
	
}

package com.zkyl.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

public class POIClass {
	/**
	 * 封装返回的流信息
	 * @param response
	 * @param fileName
	 * @throws Exception
	 */
	public static void toPackageOs(HttpServletResponse response , String fileName)throws Exception{
		//设置编码格式
		response.setContentType("application/octet-stream;charset=utf-8");
		//拼接名称加上日期
		String outFileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		//给输出的文件设置名称
		response.setHeader("Content-Disposition", "attachment;filename=" + new String(outFileName.getBytes(),"iso-8859-1") + ".xlsx");
	}

	/**
	 * 生成模板输入流
	 * @param temPath
	 * @return
	 * @throws Exception
	 */
	public static InputStream toPackageIn(String temPath)throws Exception{
		return new ClassPathResource((temPath)).getInputStream();
	}
	// 给具体的某个行中的某个列赋值
	public static void toCellValue(Row row, int cellColumn, String value) {
		Cell cell = row.createCell(cellColumn);
		cell.setCellValue(value);
	}

}

package com.zkyl.util;

import lombok.Data;

@Data
public class ResultVO<T> {
    private boolean success;
    private String errorCode;
    private String errorMessage;
    private T data;//正常数据
//    private Object hint;//异常数据
//    private Object[] errorArgs;

}

package com.zkyl.util;

import lombok.extern.java.Log;
/*import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.transaction.support.TransactionSynchronizationManager;*/

@Log
public class ResultVOBuilder {

    public static  <T> ResultVO<T> success(T t) {
        ResultVO<T> result = new ResultVO<>();
        result.setData(t);
        result.setSuccess(true);
        return result;
    }

    public static ResultVO error(String errorCode, String message){
        ResultVO result = new ResultVO();
        result.setSuccess(false);
        result.setErrorCode(errorCode);
        result.setErrorMessage(message);
        return result;
    }
    /*@Autowired
    public ResultVOBuilder(ResourceBundleMessageSource messageSource) {
        this.messageSource = messageSource;
    }*/

    // mockito 测试用
   /* private ResultVOBuilder(){
        ResourceBundleMessageSource messageSource = new ResourceBundleMessageSource();
        messageSource.setBasenames("errorMessage", "constantMessage");
        messageSource.setUseCodeAsDefaultMessage(true);
        this.messageSource = messageSource;
    }*/

//    private final ResourceBundleMessageSource messageSource;



    /*public <T> ResultVO<T> failure(ResultVO<?> origin) {
        return failure(origin.getErrorCode(), origin.getErrorArgs());
    }

    public <T> ResultVO<T> failure(String errorCode, Object... errorArgs) {
        ResultVO<T> result = new ResultVO<>();
        result.setErrorCode(errorCode);
        result.setErrorArgs(errorArgs);
        result.setErrorMessage(messageSource.getMessage(errorCode, errorArgs, LocaleContextHolder.getLocale()));
        result.setSuccess(false);
        log.warning(result.getErrorMessage());
        *//*if (TransactionSynchronizationManager.isActualTransactionActive()) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }*//*
        return result;
    }*/

}

9.service实现层，与接口层

package com.zkyl.service;

import com.zkyl.pojo.Pemo;
import com.zkyl.util.ResultVO;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * Created by libaofeng on 2019/3/29.
 */
public interface PemoService {
    List<Pemo> querryPemo();
    ResultVO exportExcel(HttpServletResponse resp) throws Exception;
//    ResultVO insertPemo(List<Pemo> pemoList);

    ResultVO<List<Pemo>> importFile(MultipartFile file) throws Exception;

}

package com.zkyl.service.impl;

import com.zkyl.mapper.PemoMapper;
import com.zkyl.pojo.Pemo;
import com.zkyl.pojo.PemoImportVo;
import com.zkyl.service.PemoService;
import com.zkyl.util.ExportUtil;
import com.zkyl.util.POIClass;
import com.zkyl.util.ResultVO;
import com.zkyl.util.ResultVOBuilder;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
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.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.beans.Transient;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by libaofeng on 2019/3/29.
 */
@Service
public class PemoServiceImpl implements PemoService {
    @Autowired
    private PemoMapper pemoMapper;

    @Override
    public List<Pemo> querryPemo() {
        return pemoMapper.querryPemo();
    }


    @Override
    public ResultVO exportExcel(HttpServletResponse resp) throws Exception {
        List<Pemo> pemos = pemoMapper.querryPemo();
        ServletOutputStream outputStream = resp.getOutputStream();
        POIClass.toPackageOs(resp, "PEMO文件导出");
        //读取模板中的数据
        InputStream in = ExportUtil.toPackageIn("static/Pemo.xlsx");
        //根据模板的数据、把查询出来的数据给摸版SHeet1组中的数据赋值、把excel输出到浏览器上
        writeDataToExcel(in, "Sheet1", pemos, outputStream);
        return null;
    }

    private void writeDataToExcel(InputStream in, String sheetName, List<Pemo> resultList, ServletOutputStream out) throws Exception {
        //POi读取模板
        XSSFWorkbook wb = new XSSFWorkbook(in);
        //读取sheet1中的数据
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet != null) {
            //向sheet1中赋值，设置样式
            toResultListValueInfo(sheet, resultList);
        }
        //把数据写入到输出流中
        wb.write(out);
        //关闭poi方法
        wb.close();
    }

    /**
     * 插入excel表中项目信息
     *
     * @param sheet
     */
    private void toResultListValueInfo(Sheet sheet, List<Pemo> pemoList) {
        //从第五行开始赋值
        int row_column = 4;
        //遍历数据集合
        for (Pemo pemo : pemoList) {
            //创建一行的方法
            Row row = sheet.createRow(row_column);
            // 给第一列序号赋值赋值
            POIClass.toCellValue(row, 0, pemo.getPid() + "");
            // 给第二列编码赋值
            POIClass.toCellValue(row, 1, pemo.getPname() + "");
            // 给第3列名称赋值
            POIClass.toCellValue(row, 2, pemo.getPsex() + "");
            // 给状态赋值
            POIClass.toCellValue(row, 3, pemo.getPaddress() + "");
            //给描述赋值
            POIClass.toCellValue(row, 4, pemo.getPage() + "");
            row_column++;
        }
    }

    /**
     * 导入数据
     *
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    @Transactional
    public ResultVO<List<Pemo>> importFile(MultipartFile file) throws Exception {
        List<PemoImportVo> pemoImportVos = new ArrayList<>();
        ResultVO<List<PemoImportVo>> parse = parse(pemoImportVos, file);
        List<Pemo> pemoList = transformation(parse.getData());
        pemoMapper.insertPemo(pemoList);
        return ResultVOBuilder.success(pemoList);
    }

    public List<Pemo> transformation(List<PemoImportVo> pemoImportVos) {
        List<Pemo> pemoList = new ArrayList<>();
        for (PemoImportVo pemoImportVo : pemoImportVos) {
            Pemo pemo = new Pemo();
            pemo.setPid(Integer.parseInt(pemoImportVo.getPid()));
            pemo.setPname(pemoImportVo.getPname());
            pemo.setPsex(pemoImportVo.getPsex());
            pemo.setPaddress(pemoImportVo.getPaddress());
            pemo.setPage(Integer.parseInt(pemoImportVo.getPage()));
            pemoList.add(pemo);
        }
        return pemoList;
    }


    private final static String PEMO = "Pemo";

    /**
     * 验证
     *
     * @param pemoImportVos
     * @param file
     * @return
     */
    public ResultVO<List<PemoImportVo>> parse(List<PemoImportVo> pemoImportVos, MultipartFile file) throws Exception {
        if (file == null) {
            return ResultVOBuilder.error("500", "文件不可以为空");
        }
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(".xls")) {
            return ResultVOBuilder.error("500", "模板必须是2007版本以上的");
        }
        //poi核心类，用来读取excel表格中的数据
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
        //获取单元格中的信息 at0是获取sheet1中的数据。
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        //验证第一列第一行的表格标头信息是否为 “询价单导入”，如果不是，提示模板错误。
        if (!String.valueOf(sheet.getRow(sheet.getFirstRowNum()).getCell(0)).equals(PEMO)) {
            return ResultVOBuilder.error("500", "模板错误，请检查模板！");
        }
        for (int i = sheet.getFirstRowNum() + 3; i < sheet.getLastRowNum(); i++) {
            XSSFRow xssfRow = sheet.getRow(i);
            //验证第一列数据、第二列、第三列数据是否为空
            if ((!StringUtils.isEmpty(String.valueOf(xssfRow.getCell(0)).trim()) &&
                    String.valueOf(xssfRow.getCell(0)) != null) ||
                    (!StringUtils.isEmpty(String.valueOf(xssfRow.getCell(1)).trim()) &&
                            !String.valueOf(xssfRow.getCell(1)).equals("null"))
                    ||
                    (!StringUtils.isEmpty(String.valueOf(xssfRow.getCell(2)).trim()) &&
                            !String.valueOf(xssfRow.getCell(2)).equals("null"))
                    ) {
                PemoImportVo pemoImportVo = build(xssfRow);
                pemoImportVos.add(pemoImportVo);
            }

        }
        return ResultVOBuilder.success(pemoImportVos);

    }

    /**
     * 把取出来的值传到实体类中
     *
     * @param xssfRow
     * @return
     * @throws Exception
     */
    private PemoImportVo build(XSSFRow xssfRow) throws Exception {
        PemoImportVo addVO = new PemoImportVo();
        addVO.setPid(String.valueOf(xssfRow.getCell(0)));
        addVO.setPname(String.valueOf(xssfRow.getCell(1)));
        addVO.setPsex(String.valueOf(xssfRow.getCell(2)));
        addVO.setPaddress(String.valueOf(xssfRow.getCell(3)));
        addVO.setPage(String.valueOf(xssfRow.getCell(4)));
        return addVO;
    }


}

10.前端代码

<!doctype html>
<html lang="ch">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<form action="/exportExcel" method="post" enctype="multipart/form-data"><!--enctype="multipart/form-data"是必须的-->
    <input type="file" name="file"><br>
    <input type="submit" id="btnSub" value="提交">
</form>
</body>
</html>

11.效果 http://localhost:8010/poifile111
在这里插入图片描述

到此导入成功：
第一次写博客，写的不好请见谅