POI导入导出的简单实现

数据的导入导出

POI简介

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

Apache POI是用Java编写的免费开源的跨平台的JavaAPI,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为"Poor Obfuscation Implementation"的首字母缩写，以为"可怜的模糊实现"。

用它可以使用Java读取和创建，修改MS Excel文件，而且还可以使用Java读取和创建MS Word和MSPowerPoint文件，Apache POI提供Java操作Excel解决方案（适用于Excel97-2008）

结构：

HSSF － 提供读写Microsoft Excel格式档案的功能。

XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

HWPF － 提供读写Microsoft Word格式档案的功能。

HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

HDGF － 提供读写Microsoft Visio格式档案的功能。

POI入门小程序：

首先搭建一个工程，这里我就用Springboot快速搭建，之后引入我们的POI坐标，我们要使用POI当然要先引入它的坐标。然后创建一个class，来写我们的小程序。

POM坐标：

<!-- 引入poi不会自动加入另外需要的包
        引入poi-ooxml就会自动引入操作xlsx文件所用到的其他包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

入门代码：

package com.example.demo.testpoi;

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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class PoiDemo {
    public static void main(String[] args) {
        //创建了一个工作簿
        HSSFWorkbook wk = new HSSFWorkbook();
        //创建了一个工作表
        HSSFSheet sheet = wk.createSheet();
        //创建第一行,一定要先创建行后在创建单元格，不需要创建列
        //下标从0开始，为第一行
        HSSFRow row = sheet.createRow(0);
        //创建单元格,列的索引也是从0开始
        HSSFCell cell = row.createCell(0);
        //给单元格赋值
        cell.setCellValue("测试");

        //保存测试的这个excel,用一个文件输出流
        try {
            FileOutputStream fos = new FileOutputStream("f://poitest.xls");
            wk.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

最后展示一下效果：

几个简单的关于POI在入门程序中用到的API已经在注释中阐明。

如果要设置样式呢？比如列宽：

//设置列宽
        sheet.setColumnWidth(0,5000);

第一个代表哪个单元格，第二个参数为宽度，效果为这样：

width:这个参数的大小跟字体有关，每个字符大小乘以256即为列的宽度

POI连接数据库导出excel文件

首先搭建一套springboot+mybatis查询的项目。

POM文件:

<?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 https://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.4.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</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.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- 引入poi不会自动加入另外需要的包
        引入poi-ooxml就会自动引入操作xlsx文件所用到的其他包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <!--mysql连接依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <!--mybatis启动依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!--数据库连接池依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>

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

</project>

在这个pom文件中我们引入了springboot的启动依赖，mybatis的启动依赖，以及我们要用到的poi的面向对象操作的依赖，lombok和druid等的依赖不细说。

yml配置：

server:
  port: 8091

mybatis:
  mapper-locations: classpath:dao/*.xml
spring:
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/shiro?characterEncoding=UTF-8

先写一个接口用来查询数据：

dao:

@Mapper
public interface ExportService {

    /**
     * 查询所有数据
     * @return
     */
    List<ExcelEntity> selectOne();
    
}

 service

@Service
public interface ExportService {

    /**
     * 查询所有数据
     * @return
     */
    List<ExcelEntity> selectOne();

    
}

serviceimpl

@Service
public class ExportServiceImpl implements ExportService {

    @Autowired
    private IexportDao iexportDao;

    @Override
    public List<ExcelEntity> selectOne() {
        List<ExcelEntity> excelEntities = iexportDao.selectOne();
        return excelEntities;
    }

   
}

 controller

@RestController
@RequestMapping("/export")
public class ExportController {
    @Autowired
    private ExportService exportService;

    /**
     * 查询数据
     * @return
     */
    @GetMapping("/select")
    public List<ExcelEntity> select(){
        List<ExcelEntity> excelEntities = exportService.selectOne();
        return excelEntities;
    }

}

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.example.demo.testpoi.export.dao.IexportDao">
    <select id="selectOne" resultType="com.example.demo.testpoi.export.pojo.ExcelEntity">
        select * from sheet2
    </select>
</mapper>

 

postman测试：

一个查询接口就写完了。

POI导出

下面编写POI导出的接口：

首先dao层：

@Mapper
public interface IexportDao {

    /**
     * 查询所有数据
     * @return
     */
    List<ExcelEntity> selectOne();

    /**
     * 导出数据
     * @param os
     */
    void export(OutputStream os);
}

在接口中传入一个文件输出流的对象

service：

public interface ExportService {

    /**
     * 查询所有数据
     * @return
     */
    List<ExcelEntity> selectOne();

    /**
     * 导出数据
     * @param os
     */
    void export(OutputStream os);
}

serviceimpl：

@Service
public class ExportServiceImpl implements ExportService {

    @Autowired
    private IexportDao iexportDao;

    @Override
    public List<ExcelEntity> selectOne() {
        List<ExcelEntity> excelEntities = iexportDao.selectOne();
        return excelEntities;
    }

    /**
     * 导出数据
     * @param os
     */
    @Override
    public void export(OutputStream os) {
        //获取要导出的数据列表
        List<ExcelEntity> excelEntities = iexportDao.selectOne();
        //创建了一个工作簿
        HSSFWorkbook wk = new HSSFWorkbook();
        //创建了一个工作表
        HSSFSheet sheet = wk.createSheet("excel1");
        //创建第一行,一定要先创建行后在创建单元格，不需要创建列
        //下标从0开始，为第一行，写标题
        HSSFRow row = sheet.createRow(0);
        //将表头放入一个数组
        String[] header = {"设备名称","横坐标","纵坐标"};
        //先定义一个cell;
        HSSFCell cell=null;
        //遍历数组
        for (int i = 0; i <header.length ; i++) {
            //在遍历数组的同时创建单元格
            cell=row.createCell(i);
            //在遍历中给创建好的单元格赋值
            cell.setCellValue(header[i]);
        }
        //导出的内容
        /**因为表头为0，所以表中的数据从下标1开始*/
        int rowCount=1;
        for (ExcelEntity excelEntity : excelEntities) {
            row=sheet.createRow(rowCount);
            //设备名称
            row.createCell(0).setCellValue(excelEntity.getSbmc());
            //横坐标
            row.createCell(1).setCellValue(excelEntity.getX());
            //纵坐标
            row.createCell(2).setCellValue(excelEntity.getY());
            //添加完数据之后自增一行
            rowCount++;
        }

        try {
            wk.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                wk.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

具体的代码注释已经标明在代码中

下面是导出的controller：

@RestController
@RequestMapping("/export")
public class ExportController {
    @Autowired
    private ExportService exportService;

    /**
     * 查询数据
     * @return
     */
    @GetMapping("/select")
    public List<ExcelEntity> select(){
        List<ExcelEntity> excelEntities = exportService.selectOne();
        return excelEntities;
    }

    /**
     * 导出数据
     */
    @GetMapping("/dc")
    public void export(HttpServletResponse response,HttpServletRequest request){
        String fileName = "设备表";
        try {
            //响应对象
            ServletOutputStream outputStream = response.getOutputStream();
            //设置输出流的格式，实现下载，指定为excel
           setResponseHeader(response,fileName,request);
            exportService.export(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 公共方法
     * @param response
     * @param fileName
     * @param request
     */
    public void setResponseHeader(HttpServletResponse response, String fileName, HttpServletRequest request) {
        try {
            try {
                String userAgent=request.getHeader("user-agent");
                if (userAgent!=null && userAgent.indexOf("Firefox")>=0 || userAgent.indexOf("Chrome")>=0){
                    fileName = new String(fileName.getBytes(), "ISO8859-1")+".xls";
                }else{
                    fileName = URLEncoder.encode(fileName, "UTF-8")+".xls";
                }
            } catch (UnsupportedEncodingException var4) {
                var4.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception var5) {
            var5.printStackTrace();
        }
    }
}

导出效果：

 

 导出excel就完成了

POI导入

导入需求是需要我们上传一份excel文件，上传之后将数据库中没有的内容进行新增操作，已经有的数据则根据数据库要改变的表中的一个字段进行判断，如果这个字段中的内容已经有了，上传的excel文件也有了，则证明是重复的一条数据，就进行更新操作，如果表中没有就进行新增操作。

首先我们已经有了一个新增的接口，再来编写一个更新的接口：

dao层：

    /**
     * 更新数据
     * @param sbmc
     */
    void update(String sbmc,String x,String y);

 

 

 

 

 

 

 

mapper文件： 

<!--根据设备名称更新sql-->
    <update id="update" parameterType="String">
        update sheet0 set sbmc='${sbmc}',x='${x}',y='${y}' where sbmc='${sbmc}'
    </update>

 因为目前我们暂时不需要在控制层用到这个更新的方法，所以暂时不写service层的更新方法，那么接下来就是我们导入的主角方法登场了：

service层:

 /**
     * 导入数据
     * @param ip
     */
    void doImport(InputStream ip) throws IOException;

service实现类:

/**
     * 导入数据
     * @param ip
     */
    @Override
    public void doImport(InputStream ip) throws IOException{
        HSSFWorkbook wk=null;
        try {
            wk = new HSSFWorkbook(ip);
            //拿到excel文档的第一个sheet
            HSSFSheet sheet = wk.getSheetAt(0);
            //读取数据
            //最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            ExcelEntity excelEntity = null;
            //使用最后一行作为for循环的最后一层
            for (int i=1;i<=lastRowNum;i++){
                excelEntity = new ExcelEntity();
                //设备名称列
                excelEntity.setSbmc(sheet.getRow(i).getCell(0).getStringCellValue());
                //判断是否已经存在,通过设备名称来查询
                List<ExcelEntity> excelEntities = iexportDao.selectBySbmc(excelEntity.getSbmc());
                //如果这个集合有记录
                if (excelEntities.size()>0){
                    //集合大小大于一说明根据这个名称查出了数据，将xy更新
                    excelEntity = excelEntities.get(0);
                }
                //设置x列
                excelEntity.setX(sheet.getRow(i).getCell(1).getStringCellValue());
                //设置y列
                excelEntity.setY(sheet.getRow(i).getCell(2).getStringCellValue());
                iexportDao.update(excelEntity.getSbmc(),excelEntity.getX(),excelEntity.getY());
                //如果这个集合是0就新增
                if (excelEntities.size()==0){
                    //新增
                    iexportDao.addExcelEntity(excelEntity);
                }
            }
        } finally {
            if (null != wk){
                try {
                    wk.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

controller层:

    @PostMapping("/upload")
    public String doImport(MultipartFile file) {
        try {
            exportService.doImport(file.getInputStream());
            return "上传成功";
        } catch (IOException e) {
            e.printStackTrace();
            return "上传失败";
        }
    }

导入的代码就完成了。