poi实现数据库数据的导入导出

一、导出文件

    /**
    *  描述:实现导出功能
    *  时间[2019/12/12 15:09]
    *  作者：李清伟
    *  params:                  return:
    */

    @RequestMapping("outPoiExcl")
    public void  outPoiExcl(HttpServletResponse response) throws IOException {

        /*每次写100行数据，就刷新数据出缓存*/
        SXSSFWorkbook workbook = new SXSSFWorkbook(5);
        SXSSFSheet sheet = workbook.createSheet();

        /*业务逻辑*/
        List<Map<String,Object>> schoolList = schoolDao.getAllSchool(new HashMap<>());
        /*创建一个String类型的数据*/
        String[] titles = {"编号","学年"};
        Row row = sheet.createRow(0);

        /*第一行设置标题*/
        for (int i= 0;i<titles.length;i++)
        {
            String title = titles[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(title);
        }

        /*导出数据*/
        for (int rowSize = 0;rowSize<schoolList.size();rowSize++)
        {
            SXSSFRow rowData = sheet.createRow(rowSize + 1);
            /*根据业务逻辑，填写数据*/
            Map<String, Object> map = schoolList.get(rowSize);

            /*设置第一列的值*/
            SXSSFCell cell1 = rowData.createCell(0);
            cell1.setCellValue(map .get("id").toString());

            /*第二列的值*/
            SXSSFCell cell2 = rowData.createCell(1);
            cell2.setCellValue(map .get("schoolName").toString());
        }
        String exclFileName = "导出.xlsx";
        response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exclFileName,"utf-8"));
        workbook.write(response.getOutputStream());
        workbook.close();
    }

需引入依赖(版本号需对应)

    <!--poi文件的导入导出功能-->
        <!--excl的读取-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--excl的导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

二、导出文件

导出文件这里使用了百度的poi导入工具类，简化了导入的难度
工具类如下：

package com.lan.springbootday01.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入工具类
 */
public class ExcelPoiImportUtils {


    /**
     * 导入
     * @param file
     * @return
     */
    public static List<List<String>> importFromExcel(File file) {
        // 用于保存读取的Excel信息
        List<List<String>> excelListList = new ArrayList<List<String>>();
        // 创建工作簿
        Workbook workBook = null;
        // 获取文件名
        String fileName = file.getName();
        // 判断Excel类型，是Excel2003还是Excel2007，通过文件名后缀判断
        try {
            if (fileName.endsWith("xls")) {
                workBook = new HSSFWorkbook(new FileInputStream(file));
            } else if (fileName.endsWith("xlsx")) {
                workBook = new XSSFWorkbook(new FileInputStream(file));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 获得第一个sheet
        Sheet sheet = workBook.getSheetAt(0);
        // 获得该sheet的所有行
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            // 获取一行
            Row row = sheet.getRow(i);
            // 第一行为标题行，跳过
            if (i == 0) {
                continue;
            }
            // 获得列数
            int cellNums = row.getLastCellNum();
            // 用于保存每行数据
            List<String> excelList = new ArrayList<String>();
            // 一次保存列信息
            for (int j = 0; j < cellNums; j++) {
                Cell cell = row.getCell(j);
                String cellValue = getCellValue(cell).trim();
                excelList.add(cellValue);
            }
            excelListList.add(excelList);
        }

        return excelListList;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String cellValue = null;
        if (cell == null) {
            cellValue = "";
        }
        // 获取单元格类型
        int cellType = cell.getCellType();
        DecimalFormat decimalFormat = new DecimalFormat("0");
        switch (cellType) {
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            case Cell.CELL_TYPE_NUMERIC:
                // 判断是否为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = dateFormat.format(cell.getDateCellValue());
                } else {
                    String number = String.valueOf(cell.getNumericCellValue());
                    // 是否是浮点数
                    if (number.indexOf(".") != -1) {
                        decimalFormat = new DecimalFormat("#.###");
                    }
                    cellValue = decimalFormat.format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                // 公式需要获取其数值
                cell.setCellType(CellType.NUMERIC);
                cellValue = decimalFormat.format(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                cellValue = cell.getStringCellValue();
                break;
        }

        return cellValue;
    }

}

导入到数据库的方法:

    /**
    *  描述:导入excl接口
    *  时间[2019/12/13 11:47]
    *  作者：李清伟
    *  params:     file             return:
    */

    @RequestMapping("inputPoiExcl")
    @ResponseBody
    public Map<String,Object> inputPoiExcl(String filePath) throws IOException {
        /*得到文件*/
        File inputExcl = new File(filePath);
        Map<String,Object> map = new HashMap<>();
        Map<String,Object> resultMap = new HashMap<>();
        List<List<String>> excelListList = importFromExcel(inputExcl);
        for (int i = 0; i < excelListList.size(); i++) {
            List<String> groupList = excelListList.get(i);
                map.put("pg_id",groupList.get(0));
                map.put("pg_name",groupList.get(1));
                map.put("pg_message",groupList.get(2));
                map.put("pg_createtime",groupList.get(3));
                map.put("pg_createname",groupList.get(4));
                map.put("pg_create_id",groupList.get(5));
                poiDao.insertGroup(map);
                resultMap.put("code",200);
        }
        return resultMap;
    }

前台:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@include file="../include/mete.jsp" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<div>
    <button type="button" class="layui-btn" id="test3" ><i class="layui-icon"></i>导入</button>
    <div id="demoText"></div>
</div>
</body>
<script>
    var loading;
    layui.use(['form', 'layedit', 'laydate','jquery','upload'], function() {
        var form = layui.form
            , layer = layui.layer
            , layedit = layui.layedit
            , laydate = layui.laydate
            , upload = layui.upload
            , $ = layui.jquery;
        //普通图片上传
        var uploadInst = upload.render({
            elem: '#test3'
            ,url: '${ctx}/upload/uploadImg',
            accept:'file',
            exts:'xls|xlsx'
            ,before: function(obj){
                loading = layer.load(2);
            }
            ,done: function(res){
                //如果上传成功
                if(res.code == 0){
                    $.ajax({
                        url:'${ctx}/poi/inputPoiExcl',
                        data:{
                            filePath:res.cunfangPath
                        },
                        type:'post',
                        success:function (data) {
                            if (data.code == 200){
                                layer.close(loading);
                                layer.msg("导入成功");
                                window.parent.location.reload();
                                return;
                            }
                        }
                    });
                }
                //上传成功
            }
            ,error: function(){
                //演示失败状态，并实现重传
                var demoText = $('#demoText');
                demoText.html('<span style="color: #FF5722;">上传失败</span> <a class="layui-btn layui-btn-xs demo-reload">重试</a>');
                demoText.find('.demo-reload').on('click', function(){
                    uploadInst.upload();
                });
            }
        });
    });
</script>
</html>