excle 导入/导出工具类

 工具类 

package com.center.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel文件操作工具类，包括读、写等功能
 *
 * @group : java小白白
 * @Version : 1.00
 * @Date : 2020-03-03
 */
public final class ExcelUtil2 {
    /**
     * 导出时,创建 SXSSFWorkbook 对象
     * @param title --exlce中的标题
     * @param headerList --exlce中的标题行list
     * @param values --exlce中的数据行数组
     * @return
     */
    public static SXSSFWorkbook getSXSSFWorkbook(String title, List<String> headerList, String[][] values) {
        //构造一个空工作簿并指定行访问的窗口。
        SXSSFWorkbook wb = new SXSSFWorkbook();
        //当前行号
        int rownum = 0;
        Sheet sheet = null;
        // 创建excle标题
        if (StringUtils.isNotBlank(title)){
            sheet = wb.createSheet(title);
            Row titleRow = sheet.createRow(rownum++);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            //创建标题单元格样式
            CellStyle style = wb.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            Font titleFont = wb.createFont();
            titleFont.setFontName("Arial");
            titleFont.setFontHeightInPoints((short) 16);
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(titleFont);
            titleCell.setCellStyle(style);
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
        }else{
            sheet = wb.createSheet();
        }

        if (headerList == null){
            throw new RuntimeException("标题行不能为空");
        }
        //创建公共单元格样式
        CellStyle commonStyle = wb.createCellStyle();
        commonStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        commonStyle.setBorderRight(CellStyle.BORDER_THIN);
        commonStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        commonStyle.setBorderLeft(CellStyle.BORDER_THIN);
        commonStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        commonStyle.setBorderTop(CellStyle.BORDER_THIN);
        commonStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        commonStyle.setBorderBottom(CellStyle.BORDER_THIN);
        commonStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        // 创建标题行
        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints(16);
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.cloneStyleFrom(commonStyle);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        headerStyle.setFont(headerFont);
        for (int i = 0; i < headerList.size(); i++) {
            Cell cell = headerRow.createCell(i);
            //创建 第一列各个单元格(字段)标题样式 居中
            cell.setCellStyle(headerStyle);
            cell.setCellValue(headerList.get(i));
            //列宽自适应
            sheet.autoSizeColumn(i);
            int colWidth = sheet.getColumnWidth(i)*2;
            //解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
        }

        //创建数据行
        if (values == null){
            throw new RuntimeException("导出数据不能为空不能为空");
        }
        //创建 align = 0 单元格样式 自动
        CellStyle dataStyle = wb.createCellStyle();
        dataStyle.cloneStyleFrom(commonStyle);
        dataStyle.setAlignment(CellStyle.ALIGN_CENTER);
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        dataStyle.setFont(dataFont);
        //创建内容 i 代表行
        for(int i=0;i<values.length;i++){
            Row row = sheet.createRow(rownum + i);
            // j为列
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                Cell cell = row.createCell(j);
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(dataStyle);
            }
        }
        return wb;
    }

    /**
     * 导出响应到客户端
     * @param response
     * @param fileName --文件名称
     * @param wb --SXSSFWorkbook对象
     */
    public static void writeExlce(HttpServletResponse response, String fileName, SXSSFWorkbook wb) {
        try {
            setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 发送响应流方法
     * @param response
     * @param fileName
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 导出模板文件保存到本地
     * @param request
     * @param response
     * @param file --所保存对象
     * @param fileName --文件名称
     * @throws IOException
     */
    public static void savaFile(HttpServletRequest request, HttpServletResponse response, File file, String fileName) throws IOException {
        //  文件存在才下载
        if (file.exists()) {
            OutputStream out = null;
            FileInputStream in = null;
            try {
                // 1.读取要下载的内容
                in = new FileInputStream(file);
                //解决各大浏览器下载文件，文件名中文乱码的问题
                String agent = request.getHeader("user-agent");
                if (agent.contains("MSIE") || agent.contains("Trident") || agent.contains("Edge") ) {
                    //IE浏览器的乱码问题解决
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                } else {
                    //其他浏览器解决方式
                    fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                }
                // 设置下载文件的mineType，告诉浏览器下载文件类型
                String mineType = request.getServletContext().getMimeType(fileName);
                response.setContentType(mineType);
                // 设置一个响应头，无论是否被浏览器解析，都下载
                //  "attachment; filename=" 作用是服务器推荐客户端保存文件时使用指定的文件名
                response.setHeader("Content-disposition", "attachment; filename=" + fileName);
                response.setCharacterEncoding("UTF-8");
                // 将要下载的文件内容通过输出流写到浏览器
                out = response.getOutputStream();
                int len = 0;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    out.write(buffer, 0, len);
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (out != null) {
                    out.close();
                }
                if (in != null) {
                    in.close();
                }
            }
        }
    }

    /**
     * 判断导入文件格式是否正确
     * @param file --导入的文件对象
     * @param response
     * @return
     */
    public static String determineFormatFile(MultipartFile file, HttpServletResponse response){
        String fileName = file.getOriginalFilename();
        String filetype = fileName.substring(fileName.lastIndexOf("."), fileName.length());
        if (StringUtils.isBlank(filetype) || (!filetype.equals(".xls") && !filetype.equals(".xlsx"))) {
            throw new RuntimeException("文件导入格式不正确,应上传.xls或.xlsx结尾的文件.");
        }
        // 文件名以当前时间戳替换，防止重名
        fileName = Long.toString(System.currentTimeMillis())
                + fileName.substring(fileName.lastIndexOf("."))
                .toLowerCase();
        return fileName;
    }

    /**
     * 判断导入文件 读出 excle 数据
     * @param filePath --文件所在路径
     * @param filetype --文件类型,即文件后缀名
     * @return
     * @throws IOException
     */
    public static List<Object[]> readExcel(String filePath,String filetype) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }
        FileInputStream fis = new FileInputStream(file);
        Workbook wb = null;
        if (".xls".equals(filetype)) {
            // 使用xls方式读取
            wb = new HSSFWorkbook(fis);
        } else if (".xlsx".equals(filetype)) {
            // 使用xlsx方式读取
            wb = new XSSFWorkbook(fis);
        } else {
            throw new RuntimeException("文档格式不正确!");
        }

        List<Object[]> rowList = new ArrayList<Object[]>();
        //默认获取第一个 sheet对象中的数据
        if (wb.getNumberOfSheets()<0){
            throw new RuntimeException("文档中没有工作表!");
        }
        // 读取Excel 2007版，xlsx格式  默认读取第一个工作表
        Sheet sheet = wb.getSheetAt(0);
        //判断有没有标题
        int tempheaderNum = 0;
        int headerNum = 0;
        Row headertemp = sheet.getRow(tempheaderNum);
        Row headertemp2 = sheet.getRow(tempheaderNum+1);
        //获取实工作表中所占理论列数,多列合并不为一列 headertemp.getLastCellNum()
        //获取实际工作表中所占列数,多列合并为一列 headertemp.getPhysicalNumberOfCells()
        if(headertemp!=null && headertemp2!=null){
            if(headertemp.getPhysicalNumberOfCells()<headertemp2.getPhysicalNumberOfCells()){
                //标题行 行号
                headerNum = tempheaderNum+1;
            }else{
                //标题行 行号
                headerNum = tempheaderNum;
            }
        }else{
            throw new RuntimeException("文档数据有误!!");
        }
        int dataRowNum = headerNum+1;
        int LastDataRowNum =  sheet.getLastRowNum()+headerNum;
        for (int i = dataRowNum; i <= LastDataRowNum; i++) {
            Row row = sheet.getRow(i);
            if(row!=null){
                Object[] objrArray = new Object[row.getLastCellNum()];
                for (int j=0;row!=null && j<row.getLastCellNum();j++ ){
                    Cell cell = row.getCell(j);
                    if (cell != null){
                        Object val = "";
                        //判断单元格数据类型
                        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
                            // 当excel 中的数据为数值或日期是需要特殊处理
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                double d = cell.getNumericCellValue();
                                Date date = HSSFDateUtil.getJavaDate(d);
                                SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
                                val = dformat.format(date);
                            } else {
                                NumberFormat nf = NumberFormat.getInstance();
                                // true时的格式：1,234,567,890
                                nf.setGroupingUsed(false);
                                // 数值类型的数据为double，所以需要转换一下
                                val = nf.format(cell.getNumericCellValue());
                            }
                        }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
                            val = cell.getStringCellValue();
                        }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
                            val = cell.getCellFormula();
                        }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
                            val = cell.getBooleanCellValue();
                        }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
                            val = cell.getErrorCellValue();
                        }
                        objrArray[j]=val;
                    }
                }
                rowList.add(objrArray);
            }
        }
        return rowList;
    }
}

Controller层类

/**
     * 导出
     */
    @RequestMapping("/export")
    public void export(@RequestParam Map<String, Object> params, HttpServletResponse response) {
        List<String> headerList = new ArrayList<>();
        headerList.add("第一列");
        headerList.add("第二列");
        headerList.add("第三列");
        headerList.add("第四列");
        headerList.add("第五列");
        headerList.add("第六列");
        headerList.add("第七列");
        //根据自己的框架,查询列表数据
        Query query = new Query(params);
        List<ResJpyEntity> resJpyList = resJpyService.queryList(query);
        String fileName = "某某业务表.xlsx";

　　　　　//下面这段代码的逻辑,要根据自己实际业务需要就行编写
        if(resJpyList!=null && resJpyList.size()>0){
            String[][] values = new String[resJpyList.size()][];
            for (int i=0;i<resJpyList.size();i++){
                ResJpyEntity resJpy = resJpyList.get(i);
                if(resJpy!=null){
                    values[i] = new String[headerList.size()];
                    for(int j=0;j<headerList.size();j++){
                        switch (j){
                            case 0:
　　　　　　　　　　　　　　　　　　　//　　获取每一行第一列的值
                                values[i][j]= resJpy.get第一列的值();
                                break;
                            case 1:
　　　　　　　　　　　　　　　　　　　//　　获取每一行第二列的值
                                values[i][j]= resJpy.get第二列的值();
                                break;
                            case 2:
　　　　　　　　　　　　　　　　　　  //　　获取每一行第三列的值
                                values[i][j]= resJpy.get第三列的值();
                                break;
                            case 3:
　　　　　　　　　　　　　　　　　　　//　　获取每一行第四列的值
                                values[i][j]= resJpy.get第四列的值();
                                break;
                            case 4:
　　　　　　　　　　　　　　　　　　　//　　获取每一行第五列的值
                                values[i][j]= resJpy.get第五列的值();
                                break;
                            case 5:
　　　　　　　　　　　　　　　　　　　//　　获取每一行第六列的值
                                values[i][j]= resJpy.get第六列的值();
                                break;
                            default:
　　　　　　　　　　　　　　　　　　　//　　获取每一行其他列的值
                                values[i][j]= resJpy.get其他列的值();
                                break;
                        }
                    }
                }
            }
　　　　　　　//调用工具类创建 excle 工作空间对象
            SXSSFWorkbook wb = ExcelUtil2.getSXSSFWorkbook("excle表标题",headerList,values);
　　　　　　　//将对象从浏览器中输出
            ExcelUtil2.writeExlce(response,fileName,wb);
        }
    }

    /**
     * 导入  调用导入弹窗jsp页面
     */
    @RequestMapping("/import")
    public ModelAndView improtExecl(ModelAndView mv) {
        mv.setViewName("/页面路径/resjpyImport");
        return mv;
    }

    /**
     * 导出模板
     */
    @RequestMapping(value ="/exportTemplate")
    public void exportTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String path = request.getSession().getServletContext().getRealPath("excleTemplate");
        String filename = "某某业务表.xlsx";
        File file = new File(path+"/"+filename);
　　　　　//通过浏览器,将服务器上,存在的excle保存到本地
        ExcelUtil2.savaFile(request, response,file,filename);
    }

    /**
     * 导入Excel文件保存数据
     */
    @RequestMapping("/upload")
    @ResponseBody
    public JSONObject upload(@RequestBody MultipartFile file,HttpServletRequest request, HttpServletResponse response) {
        JSONObject result = new JSONObject();
        try {
            //判断导入文件格式是否正确
            String fileName = ExcelUtil2.determineFormatFile(file,response);

            String path = request.getSession().getServletContext()
                    .getRealPath("upload")
                    + DateFormatUtils.format(new Date(), "/yyyy/MM/dd");
            File targetFile = new File(path, fileName);
            if (!targetFile.exists()) {
                targetFile.mkdirs();
            }
            file.transferTo(targetFile);
            String filePath = path+"/"+fileName;
            String filetype = fileName.substring(fileName.lastIndexOf("."), fileName.length());

            //读取excle文件中的数据
            List<Object[]> list = ExcelUtil2.readExcel(filePath,filetype);
            int successCount = 0;
            int failCount = 0;
            for (int i=0;list!=null && i<list.size();i++){
                ResJpyEntity resJpy = new ResJpyEntity();
                Object[] obj = list.get(i);
                for (int j=0;obj!=null && j<obj.length;j++){
                    String str = (String) obj[j];
                    switch (j){
                        case 0:
                            resJpy.set属性值(str);
                            break;
                        case 1:
                            resJpy.set属性值(str);
                            break;
                        case 2:
                            resJpy.set属性值(str);
                            break;
                        case 3:
                            resJpy.set属性值(str);
                            break;
                        case 4:
                            resJpy.set属性值(str);
                            break;
                        case 5:
                            resJpy.set属性值(str);
                            break;
                        default:
                            resJpy.set属性值(str);
                            break;
                    }
                }
                //保存对象
                result = resJpyService.sava数据值(resJpy);
                if((Integer)result.get("code")==0){
　　　　　　　　　　　　// 成功加一
                    successCount++;
                }else{
　　　　　　　　　　　　// 失败加一
                    failCount++;
                }
            }
            result.put("code", 0);
            result.put("msg", "成功导入"+successCount+"条数据,失败"+failCount+"条数据");
        } catch (IOException e) {
            result.put("code", 1);
            result.put("msg", "excel导入失败！");
            e.printStackTrace();
        }
        return result;
    }

jsp列表页面

<button type="button" id="export" class="layui-btn layui-btn-normal " lay-event="stop" style="float: right;margin-right: 15px;">导出</button>
<button type="button" id="exportTemplate" class="layui-btn layui-btn-normal " lay-event="stop" style="float: right;margin-right: 15px;">导出模板</button>
<button type="button" class="layui-btn layui-btn-normal center" id="dr"  style="float: right;" >+批量导入</button>

　　　　//导出
        $('#export').click(function() {
　　　　　　　//　　获取layui数据列表中 当前页码值,以及每页显示条数
　　　　　　　var page = $(".layui-laypage-em").next().html(); //当前页码值
            window.location.href='/resjpy/export?limit='+10+'&page='+page;
        });
        //导出模板
        $('#exportTemplate').click(function() {
            window.location.href='/resjpy/exportTemplate';
        });
        //批量导入  layui框架弹出导入页面
        $('#dr').click(function(){
            layer.open({
                type: 2,
                skin: 'more-class',
                area: ['420px', '260px'],
                title : '<div class="open-header"><i class="blueLine"></i>附件上传</div>',
                content: '${ctx}/resjpy/import',
            });
        })

导入弹窗页面代码

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
//　　引入jsp所需要的标签库
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <script src="${ctx}/statics/js/layui/layui.all.js"></script>
    <script src="${ctx}/statics/js/jquery.min.js"></script>
    <style>
        .contain{
            margin-top: 24px;
            text-align: center;
        }
        .rec_buttom{
            margin-top: 50px;
        }
        .wenzi{
            font-size: 18px;
            margin-bottom: 24px;
        }
    </style>
<body>
<div>
    <div class="contain">
        <div class="wenzi">附件上传</div>
        <div class="form_msg">
　　　　　　　<%--  文件,图片,音频,视频的表单,一定要设置  enctype="multipart/form-data" --%>
　　　　　　　<form action="" enctype="multipart/form-data" method="post" >
                <input id="file" type="file"  name="file" />
                <button type="button" class="xiazai" id="excleMuban">Excel模板下载</button>
            </form>
            <div class="rec_buttom">
                <button class="layui-btn layui-btn-normal" id="save">确定</button>
                <button class="layui-btn layui-btn-normal" id="cancel">取消</button>
            </div>
        </div>
    </div>
</div>
</body>
<script>
    $(document).ready(function() {
        $('#cancel').click(function() {
            parent.location.reload();
        })
        $("#excleMuban").click(function(){
            //调用导出接口
            window.location.href='${ctx}/resjpy/exportTemplate';
        });
        $("#save").click(function(){
            var fileM=document.querySelector("#file");
            //获取文件对象，files是文件选取控件的属性，存储的是文件选取控件选取的文件对象，类型是一个数组
            var fileObj = fileM.files[0];
            // 创建
            var formData = new FormData();
            // 获取文件
            formData.append("file", fileObj);
            $.ajax({
                url: '${ctx}/resjpy/upload',
                type: "post",
                dataType: "json",
                data: formData,
                cache: false,
                contentType: false,
                processData: false,
                success: function (result) {
                    if(result.code==0){
                        layer.msg(result.msg,{icon: 6,time:2000}, function () {
                            //重新加载父页面  作用的弹窗自动关闭,父页面数据自动刷新
                            parent.location.reload();
                        });
                    }else {
                        layer.msg(result.msg,{icon: 5,time:2000});
                    }
                },
            });
        });
    })
</script>
</html>