springMVC+hibernate框架实现后台数据导出至excel表格中,并进行条件导出数据

首先介绍一下目前导出excel的几种格式:Excel 2003、Excel 2007

 Excel 2003:在POI中使用HSSF对象时,excel 2003最多只允许存储65536条数据,一般用来处理较少的数据量。这时对于百万级别数据,Excel肯定容纳不了。

 Excel 2007:当POI升级到XSSF对象时,它可以直接支持excel2007以上版本,因为它采用ooxml格式。这时excel可以支持1048576条数据,单个sheet表就支持近104万条数据了,虽然这时导出100万数据能满足要求,但使用XSSF测试后发现偶尔还是会发生堆溢出,所以也不适合百万数据的导出。

在POI3.8之后新增加了一个类,SXSSFWorkbook,采用当数据加工时不是类似前面版本的对象,它可以控制excel数据占用的内存,他通过控制在内存中的行数来实现资源管理,即当创建对象超过了设定的行数,它会自动刷新内存,将数据写入文件,这样导致打印时,占用的CPU,和内存很少。

首先是页面:如果没有进行查询,默认导出的是所有的数据,如果进行了查询,就是导出查询符合条件的数据,

前端用的是bootstrap框架

html代码

<nav class="navbar navbar-default">
    <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
        <form class="navbar-form navbar-left" id="fromSearch" role="search" onsubmit="return false;">
            <div class="form-group">
                <div class="form-group">
                    <input type="text" name="Course_Name" id="txtCourse_Name" class="form-control input-sm" placeholder="课程名字">
                </div>
                <div class="form-group  ">
                    <select style="width: 256px;" id="selBrand" name="brandNames" class="form-control    input-sm" multiple="multiple"
                            data-placeholder="品牌商"></select>
                </div>
                <div class="form-group  ">
                    <select style="width: 270px;" id="selCourse_Type" name="course_Types" onchange="getVal()" class="form-control input-sm" placeholder="课程分类"
                            data-placeholder="课程分类" multiple="multiple"></select>
                </div>
                <div class="form-group">
                    <select style="width: 270px;" id="selCourse_Status" name="course_Status" class="form-control input-sm" placeholder="课程状态"
                            data-placeholder="课程状态"></select>
                </div>
                <div class="form-group">
                    <select id="selMemberApplyStatus" name="memberApplyStatus" class="form-control input-sm" placeholder="会员报名状态"
                            data-placeholder="会员报名状态"></select>
                </div>
            </div>

            <div class="form-group" id="g1">

                <div class="form-group">
                    <input type="text" name="stuName" id="stuName" class="form-control input-sm " placeholder="姓名">
                </div>

                <div class="form-group">
                    <input type="text" name="stuPhone" id="stuPhone" class="form-control input-sm" placeholder="手机号">
                </div>

                <label class="form-groupl" for="beginTime">开始时间:</label>
                <div class="form-group">
                    <input type="date" class="form-control" name="beginTime" id="beginTime" style="resize: none;" placeholder="报名时间"/>
                </div>
                <label class="form-groupl" for="endTime">结束时间:</label>
                <div class="form-group">
                    <input type="date" class="form-control" name="endTime" id="endTime" style="resize: none;" placeholder="报名时间"/>
                </div>
                <a class="btn btn btn-info btn-sm" id="btnSearch" href="javascript:void(0);">搜索</a>
                <a class="btn btn btn-default" id="btnAdd" href="javascript:void(0);">新增</a>
                <a class="btn btn-info" id="btnExport" href="javascript:void(0);">批量导出</a>
            </div>

        </form>
    </div>
</nav>

js代码:特别注意的是不能使用ajax请求,必须用location.href就是重定向到控制层,查询参数可以在url后面进行字符串拼接,类似于 url+?+xxx=?&xxx=?,但是我这种是更简洁,但是后天必须使用JsonUtil工具进行json转java对象

$("#btnExport").on("click", function () {
                    bootstrapQ.confirm('您确认要批量导出课程信息吗?', function () {
                        var searchEntity = $.basepage.getFormJson("#fromSearch");
                        if (searchEntity.brandNames && !Array.isArray(searchEntity.brandNames)) {
                            searchEntity.brandNames = [searchEntity.brandNames];
                        }
                        if (searchEntity.course_Types && !Array.isArray(searchEntity.course_Types)) {
                            searchEntity.course_Types = [searchEntity.course_Types];
                        }
                        var JsonData = JSON.stringify(searchEntity);
                        // url上带有转成的json数据
                        window.location.href = "/stuMemberApply/getDownloadCourseInfo?JsonData="+encodeURIComponent(JsonData);
                    }, '');
                });

控制层代码:如果没有进行查询,默认导出的是所有的数据,如果进行了查询,就是导出查询符合条件的数据

/**
     * @return void
     * @Author 
     * @Description 批量导出课程数据//TODO
     * @Date 2019/1/8 15:10
     * @Param [response]
     **/
    @ResponseBody
    @RequestMapping(value = "/getDownloadCourseInfo", method = RequestMethod.GET, produces = "application/json")
    public void getDownloadCourseInfo(@RequestParam(value = "JsonData") String JsonData, HttpServletResponse response) {

        // json转java对象
        CourseStuMemberSel_ReqDto courseStuMemberSel_reqDto = JsonUtils.parseToBean(JsonData, CourseStuMemberSel_ReqDto.class);

        // excel文件名
        String fileName = "课程报名列表" + System.currentTimeMillis() + ".xls";

        // excel标题
        String[] titles = {"姓名", "性别", "手机号", "课程名", "课程类型", "品牌商", "报名时间", "会员报名状态", "已交定金", "课程状态", "上课时间", "备注"};

        // sheet名
        String sheetName = "课程报名列表";

        List studentMemberCount = courseStuMemberApplyService.getStudentMemberCount();

        BigInteger integer = (BigInteger) studentMemberCount.get(0);
        // 设置页面数据数量大小,这里没有分页导出数据,所以是设置数据的最大数量,即查询出数据库学生报名的总数量设为页面的大小
        courseStuMemberSel_reqDto.setPageSize(Integer.valueOf(integer.toString()));
        courseStuMemberSel_reqDto.setPageIndex(1);

        List<CourseStuMemberDto> listsChild = courseStuMemberApplyService.getMemberListByConditions(courseStuMemberSel_reqDto).getRows();

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<Map<Integer, String>> lists = new ArrayList<>();

        for (int i = 0; i < listsChild.size(); i++) {

            CourseStuMemberDto courseStuMemberDto = listsChild.get(i);
            Map<Integer, String> paramsLists = new HashMap<>();

            //通过child.getGradesId()来查询班级名称
            paramsLists.put(0, courseStuMemberDto.getStuName());
            if (courseStuMemberDto.getGender() == 0) {
                paramsLists.put(1, "男");
            }
            if (courseStuMemberDto.getGender() == 1) {
                paramsLists.put(1, "女");
            }

            paramsLists.put(2, courseStuMemberDto.getContactPhone());
            paramsLists.put(3, courseStuMemberDto.getCourseName());
            paramsLists.put(4, courseStuMemberDto.getCourseTypeName());
            paramsLists.put(5, courseStuMemberDto.getBrandName());
            paramsLists.put(6, dateFormat.format(courseStuMemberDto.getApplyTime()));

            // 查询字典表  与数据库字典值相比对
            List<SysCode> sysCodeListByName = sysCodeService.getSysCodeListByName(SysCodeConstant.CHEF_COURSE_APPLY_STATUS);
            for (SysCode sysCode : sysCodeListByName) {
                if (sysCode.getId().equals(courseStuMemberDto.getStuApplyStatus())) {
                    paramsLists.put(7, sysCode.getDdValue());
                }
            }
            paramsLists.put(8, String.valueOf(courseStuMemberDto.getOrderMoney()));

            if (courseStuMemberDto.getCourseStatus() == 0) {
                paramsLists.put(9, "未定义状态");
            }
            if (courseStuMemberDto.getCourseStatus() == 1) {
                paramsLists.put(9, "未开始");
            }
            if (courseStuMemberDto.getCourseStatus() == 2) {
                paramsLists.put(9, "报名中");
            }
            if (courseStuMemberDto.getCourseStatus() == 3) {
                paramsLists.put(9, "上课中");
            }
            if (courseStuMemberDto.getCourseStatus() == 4) {
                paramsLists.put(9, "课程结束");
            }

            String str = dateFormat.format(courseStuMemberDto.getCourseBeginTime()) + "至" + dateFormat.format(courseStuMemberDto.getCourseEndTime());
            paramsLists.put(10, str);

            lists.add(paramsLists);
        }

        // 创建HSSFWorkbook
        HSSFWorkbook hssfWorkbook = ExcelUtil.writeExcel(sheetName, titles, lists);

        // 响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            hssfWorkbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @return void
     * @Author 
     * @Description 发送响应流方法//TODO
     * @Date 2019/1/9 10:32
     * @Param [response, fileName]
     **/
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/x-download");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

excel表格工具类,其中用到的方法中我已经做了标识

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

import java.io.*;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author 
 * @create 2018-12-26 11:09
 * @description 导出数据为excel表格工具类
 **/
public class ExcelUtil {
    /**
     * @param
     * @return
     * @info 写出Excel标题
     */
    public static void writeExcelTitle(String filePath, String[] ss)
            throws IOException {
        OutputStream fos = new FileOutputStream(filePath);
        HSSFWorkbook xls = new HSSFWorkbook();
        HSSFSheet sheet = xls.createSheet();
        HSSFRow row = sheet.createRow(0);// 第一行
        for (int i = 0; i < ss.length; i++) {
            row.createCell(i).setCellValue(ss[i]);
        }
        xls.write(fos);
        fos.close();
    }

    /**
     * @param
     * @return
     * @info 写出Excel标题内容
     */
    public static byte[] writeExcel(String[] titles, List<Map<Integer, String>> lists) throws IOException {
        HSSFWorkbook xls = new HSSFWorkbook();
        HSSFSheet sheet = xls.createSheet();
        HSSFRow row = sheet.createRow(0);// 第一行

        for (int i = 0; i < titles.length; i++) {
            row.createCell(i).setCellValue(titles[i]);
        }
        // 内容
        int rowNum = 1;
        for (Map<Integer, String> map : lists) {
            HSSFRow rowTmp = sheet.createRow(rowNum);
            int cols = map.size();
            for (int i = 0; i < cols; i++) {
                rowTmp.createCell(i).setCellValue(map.get(i));
            }
            rowNum++;
        }
        ByteArrayOutputStream fos = new ByteArrayOutputStream();
        xls.write(fos);
        byte[] buf = fos.toByteArray();// 获取内存缓冲区中的数据
        fos.close();
        return buf;
    }

    /**
     * @param filePath
     * @return
     * @info 读取Excel内容,List行,MAP行数据
     */
    public static List<Map<String, String>> reExcelKeyMap(String filePath) throws IOException {
        List<Map<String, String>> contents = new LinkedList<Map<String, String>>();
        InputStream is = new FileInputStream(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();

        HSSFRow row = sheet.getRow(0);// 第一行
        // 总列数
        int colNum = row.getPhysicalNumberOfCells();

        // 正文内容应该从第二行开始,第一行为表头的标题
        String[] keys = readExcelTitle(filePath);
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<String, String> content = new HashMap<String, String>();
            while (j < colNum) {
                String cellValue = getCellFormatValue(row.getCell(j)).trim();

                content.put(keys[j], cellValue);
                j++;
            }
            contents.add(content);
        }
        is.close();
        return contents;
    }

    public static List<Map<String, String>> readExcelKeyMap(InputStream is) throws IOException {
        List<Map<String, String>> contents = new LinkedList<Map<String, String>>();
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();

        HSSFRow row = sheet.getRow(0);// 第一行
        // 总列数
        int colNum = row.getPhysicalNumberOfCells();

        // 正文内容应该从第二行开始,第一行为表头的标题
        // 标题总列数
        String[] keys = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            keys[i] = getCellFormatValue(row.getCell(i));
        }
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<String, String> content = new HashMap<String, String>();
            while (j < colNum) {
                String cellValue = getCellFormatValue(row.getCell(j)).trim();

                content.put(keys[j], cellValue);
                j++;
            }
            contents.add(content);
        }
        is.close();
        return contents;
    }

    /**
     * @param
     * @return
     * @info 读取Excel标题
     */
    public static String[] readExcelTitle(String filePath) throws IOException {
        InputStream is = new FileInputStream(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);// 第一行
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = getCellFormatValue(row.getCell(i));
        }
        is.close();
        return title;
    }

    /**
     * @param filePath
     * @return
     * @info 读取Excel内容,List行,MAP行数据
     */
    public static List<Map<Integer, String>> readExcelContent(String filePath) throws IOException {
        List<Map<Integer, String>> contents = new LinkedList<Map<Integer, String>>();
        InputStream is = new FileInputStream(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();

        HSSFRow row = sheet.getRow(0);// 第一行
        // 总列数
        int colNum = row.getPhysicalNumberOfCells();

        // 正文内容应该从第二行开始,第一行为表头的标题

        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer, String> content = new HashMap<Integer, String>();
            while (j < colNum) {
                String cellValue = getCellFormatValue(row.getCell(j)).trim();
                content.put(j, cellValue);
                j++;
            }
            contents.add(content);
        }
        is.close();
        return contents;
    }

    /**
     * @param cell
     * @return
     * @info 读取Excel值
     */
    static String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    BigDecimal b = new BigDecimal(cell.getNumericCellValue());
                    cellvalue = b.toPlainString();
                    break;
                }
                case HSSFCell.CELL_TYPE_FORMULA: {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellvalue = cell.getStringCellValue();
                    //System.out.println(cellvalue);
                    break;
                }
                case HSSFCell.CELL_TYPE_STRING:
                    cellvalue = cell.getRichStringCellValue().getString();
                    //System.out.println(cellvalue);
                    break;
                default:
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

    /**
     * @param cell
     * @return
     * @info 读取Excel值
     */
    static String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * @param
     * @return 主要用的是这个方法
     * @info 写出Excel标题内容
     */
    public static HSSFWorkbook writeExcel(String sheetName, String[] titles, List<Map<Integer, String>> lists) {

        // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
        HSSFWorkbook xls = new HSSFWorkbook();

        // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = xls.createSheet(sheetName);

        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);// 第一行

        // 第四步,创建单元格,并设置值表头 设置表头居中
        HSSFCellStyle style = xls.createCellStyle();

        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //创建标题
        for (int i = 0; i < titles.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }

        // 创建内容
        int rowNum = 1;
        for (Map<Integer, String> map : lists) {
            HSSFRow rowTmp = sheet.createRow(rowNum);
            int cols = map.size();
            for (int i = 0; i < cols; i++) {
                rowTmp.createCell(i).setCellValue(map.get(i));
            }
            rowNum++;
        }

        return xls;
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkBook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {

        // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步,创建单元格,并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
}
CourseStuMemberSel_ReqDto请求类
import java.io.Serializable;
import java.util.List;

public class CourseStuMemberSel_ReqDto implements Serializable {
    private static final long serialVersionUID = -5797205455815395918L;

    private String Course_Name;
    private List<String> brandNames;
    private List<String> course_Types;
    private String course_Status;
    private String apply_Status;
    private String stuName;
    private String stuPhone;
    private String beginTime;
    private String endTime;
    private int PageSize;
    private int PageIndex;

    // set和get方法省略
}

这个只能实现15000条数据的存储,不能实现百万级的数据,

最后感谢各位博主的资源供我学习!贴出我借鉴博主学习的网址

https://blog.csdn.net/happyljw/article/details/52809244

https://blog.csdn.net/u013456370/article/details/51105481

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页