ssm+layui+poi做条件导出Excel

最新推荐文章于 2021-05-12 21:30:35 发布

jq1223

最新推荐文章于 2021-05-12 21:30:35 发布

阅读量301

点赞数

分类专栏： layui spring boot 文章标签： excel poi

本文链接：https://blog.csdn.net/jq1223/article/details/115370580

版权

spring boot 同时被 2 个专栏收录

31 篇文章 1 订阅

订阅专栏

layui

11 篇文章 0 订阅

订阅专栏

效果
在这里插入图片描述

注：此为SSM+layui项目

配置依赖

        <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.17</version>
        </dependency>

layui前端代码：
from绑定一个id，条件查询每个属性给个name值，用于根据条件查询导出
在这里插入图片描述

1.头部工具栏添加一个按钮

<!-- 头部工具栏 -->
    <script type="text/html" id="toolbarDemo">
        <div class="layui-btn-container">
            <button class="layui-btn layui-btn-sm layui-btn-normal" lay-event="exportExcel"><i class="layui-icon layui-icon-export"></i>导出用户</button>
        </div>
    </script>

2.在监听头工具栏事件定义方法和实现传参

 //监听头工具栏事件
        table.on('toolbar(userList)', function(obj){
            var checkStatus = table.checkStatus(obj.config.id),
                data = checkStatus.data; //获取选中的数据
            switch(obj.event){
              //导出
                case 'exportExcel':
                    exportExcel();
                    break;
            };
        });

        //导出
        function exportExcel(){
            var searchForm = document.getElementById("searchForm");
            searchForm.action="/user/exportExcel";
            searchForm.submit();
        }

3.在UserController接参

package com.ff.controller;
import com.ff.common.Constast;
import com.ff.common.DataGridView;
import com.ff.common.PageBean;
import com.ff.common.ResultObj;
import com.ff.domain.Role;
import com.ff.domain.User;
import com.ff.service.UserService;
import com.ff.util.ExcelUtil;
import com.ff.vo.UserVo;
import io.netty.util.Constant;
import org.apache.ibatis.binding.BindingException;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;
import org.apache.shiro.crypto.hash.Md5Hash;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.script.DigestUtils;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    private UserService userService;

  

    //导出
    @RequestMapping("exportExcel")
    public void exportExcel(UserVo vo, HttpServletResponse response, HttpServletRequest request){
        List<User> userList = userService.queryUserList(vo);
        // 1.创建一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 2.创建sheet页
        XSSFSheet sheet = workbook.createSheet();

        // 给列设置默认宽度
        sheet.setDefaultColumnWidth(30);
        // 合并单元格
        CellRangeAddress range = new CellRangeAddress(0,0,0,10);
        sheet.addMergedRegion(range);
        CellRangeAddress range2 = new CellRangeAddress(1,1,0,10);
        sheet.addMergedRegion(range2);

        // 3.创建行
        int row = 0;
        XSSFRow row1 = sheet.createRow(row);

        // 4.创建单元格
        XSSFCell cell = row1.createCell(0);
        // 5.给单元格赋值
        cell.setCellValue("用户数据");
        // 设置第一行样式
        XSSFCellStyle titleStyle = createTitleStyle(workbook);
        cell.setCellStyle(titleStyle);

        // 创建第二行
        XSSFRow row2 = sheet.createRow(++row);
        XSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("总数："+userList.size()+"条，导出时间："+new Date().toLocaleString());
        // 设置第二行样式
        XSSFCellStyle subTitleStyle = createSubTitleStyle(workbook);
        cell2.setCellStyle(subTitleStyle);

        // 创建第三行
        XSSFRow row3 = sheet.createRow(++row);
        String[] titles = {"用户编号","用户名称","用户头像","登录名称","用户地址","用户性别","用户备注","所属部门","直属领导","是否启用","入职时间"};
        for (int i = 0; i <titles.length ; i++) {
            XSSFCell row3_cell = row3.createCell(i);
            row3_cell.setCellValue(titles[i]);
            XSSFCellStyle tableTitle = createTableTitle(workbook);
            row3_cell.setCellStyle(tableTitle);
        }

        XSSFCellStyle baseStyle = createBaseStyle(workbook);

        try {
        // 创建数据行
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);
            XSSFRow dataRow = sheet.createRow(++row);


            // 插入 图片至 Excel
            String path = request.getServletContext().getRealPath("");
            String posterPath = user.getImgpath();
            File file=new File(path+posterPath);
            if(file.exists()) {
                InputStream is = new FileInputStream(path+posterPath);
                byte[] bytes = IOUtils.toByteArray(is);
                int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                CreationHelper helper = workbook.getCreationHelper();
                Drawing drawing = sheet.createDrawingPatriarch();
                // anchor主要用于设置图片的属性
                ClientAnchor anchor = helper.createClientAnchor();
                // 图片插入坐标
                anchor.setCol1(2); //列
                anchor.setRow1(i+1); //行
                // 使用固定的长宽比例系数
                double a = 1;
                double b = 1;
                // 插入图片
                Picture pict = drawing.createPicture(anchor, pictureIdx);
                pict.resize(a,b);
            }


            XSSFCell dataRowCell1 = dataRow.createCell(0);
            dataRowCell1.setCellValue(i+1);
            dataRowCell1.setCellStyle(baseStyle);

            XSSFCell dataRowCell2 = dataRow.createCell(1);
            dataRowCell2.setCellValue(user.getName());
            dataRowCell2.setCellStyle(baseStyle);

           XSSFCell dataRowCell3 = dataRow.createCell(2);
            dataRowCell3.setCellValue(user.getImgpath());
            dataRowCell3.setCellStyle(baseStyle);

            XSSFCell dataRowCell4 = dataRow.createCell(3);
            dataRowCell4.setCellValue(user.getLoginname());
            dataRowCell4.setCellStyle(baseStyle);

            XSSFCell dataRowCell5 = dataRow.createCell(4);
            dataRowCell5.setCellValue(user.getAddress());
            dataRowCell5.setCellStyle(baseStyle);

            XSSFCell dataRowCell6 = dataRow.createCell(5);
            dataRowCell6.setCellValue(user.getSex()==null?"":user.getSex()==0?"男":"女");
            dataRowCell6.setCellStyle(baseStyle);

            XSSFCell dataRowCell7 = dataRow.createCell(6);
            dataRowCell7.setCellValue(user.getRemark());
            dataRowCell7.setCellStyle(baseStyle);

            XSSFCell dataRowCell8 = dataRow.createCell(7);
            dataRowCell8.setCellValue(user.getDeptname());
            dataRowCell8.setCellStyle(baseStyle);

            XSSFCell dataRowCell9 = dataRow.createCell(8);
            dataRowCell9.setCellValue(user.getLeadername());
            dataRowCell9.setCellStyle(baseStyle);

            XSSFCell dataRowCel20 = dataRow.createCell(9);
            dataRowCel20.setCellValue(user.getAvailable()==null?"":user.getAvailable()==0?"禁用":"启用");
            dataRowCel20.setCellStyle(baseStyle);


            XSSFCell dataRowCe121 = dataRow.createCell(10);
            dataRowCe121.setCellValue(user.getHiredate().toLocaleString());
            dataRowCe121.setCellStyle(baseStyle);
        }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 导出Excel
        ExcelUtil.excelDownload(workbook,request,response,"用户列表.xlsx");
    }

    /**
     * 设置技术样式
     * 水平居中和垂直居中
     * @param workbook
     * @return
     */
    public XSSFCellStyle createBaseStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = workbook.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 设置标题样式
     */
    public XSSFCellStyle createTitleStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 30);
        // 字体颜色
        font.setColor(HSSFColor.BLUE.index);
        // 字体样式
        font.setFontName("华文彩云");
        style.setFont(font);
        return style;
    }

    /**
     * 设置小标题样式
     */
    public XSSFCellStyle createSubTitleStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 20);
        // 字体颜色
        font.setColor(HSSFColor.RED.index);
        // 字体样式
        font.setFontName("微软雅黑");
        style.setFont(font);
        return style;
    }

    /**
     * 设置表格标题样式
     */
    public XSSFCellStyle createTableTitle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 15);
        // 字体颜色
//        font.setColor(HSSFColor.RED.index);
        // 字体样式
        font.setFontName("宋体");
        style.setFont(font);
        return style;
    }

}

华文彩云字体下载

4.创建ExcelUtil 工具类

package com.ff.util;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class ExcelUtil {

    public static void excelDownload(XSSFWorkbook wirthExcelWB, HttpServletRequest request,HttpServletResponse response, String fileName) {
        OutputStream out = null;
        try {
        	
        	//解决下载文件名中文乱码问题
        	if(request.getHeader("User-agent").toLowerCase().indexOf("firefox")!=-1){
        		fileName = new String(fileName.getBytes("utf-8"),"iso-8859-1");
        	}else{
        		fileName = URLEncoder.encode(fileName,"utf-8");
        	}
        	
            out = response.getOutputStream();
            // 让浏览器识别是什么类型的文件
            response.reset(); // 重点突出
            response.setCharacterEncoding("UTF-8"); // 重点突出
            response.setContentType("application/x-msdownload");// 不同类型的文件对应不同的MIME类型
                                                                // // 重点突出
            // inline在浏览器中直接显示，不提示用户下载
            // attachment弹出对话框，提示用户进行下载保存本地
            // 默认为inline方式
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            wirthExcelWB.write(out);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != out) {
                try {
                    out.close();
                    out = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

在UserMapper.xml中执行Sql文件

<?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.fh.mapper.UserMapper">
    <sql id="where">
        <where>
            <if test="vo.name != null and vo.name != ''">
                u1.name like concat('%',#{vo.name},'%')
            </if>
           <if test="vo.deptid != null  and vo.deptid!=-1">
            and u1.deptid=#{vo.deptid}
            </if>

            <if test="vo.startTime != null">
                and u1.hiredate &gt;= #{vo.startTime}
            </if>

            <if test="vo.endTime != null">
                and u1.hiredate &lt;= #{vo.endTime}
            </if>

            <if test="vo.available != null">
                and u1.available = #{vo.available}
            </if>
        </where>
    </sql>
    <!--查询用户列表-->
    <select id="queryuser" resultType="com.fh.domain.User">
        SELECT
        u1.*,d.title deptname
        FROM
        sys_user u1
        LEFT JOIN sys_dept d ON u1.deptid = d.id
        <include refid="where"></include> order by u1.id desc
        limit #{vo.startIndex},#{vo.limit}
    </select>

    <!--导出-->
    <select id="queryUserList" resultType="com.fh.domain.User">
        SELECT
        u1.*,d.title as deptname
        FROM
        sys_user u1
        left join sys_dept d on u1.deptid=d.id
        <include refid="where"></include>
        order by id desc
    </select>

    <!--总条数-->
    <select id="queryCount" resultType="long">
        select count(*) from sys_user u1 <include refid="where"></include>
    </select>
</mapper>