Java导出excel表格

对于大数据量的数据内容导出可参考

(10条消息) 导出Excel数据量大时间过慢问题_Ellis_li 的博客-CSDN博客_导出数据量过大

前端直接get请求即可

service接口

HSSFWorkbook expExcel(String date, HttpServletResponse response)

service层

@Override
public HSSFWorkbook expExcel(String date, HttpServletResponse response) {
        System.out.println(date+"------------");
        System.out.println(date+"------------");
        date="{'date':'"+date+"'}";
        System.out.println(date+"------------");
        System.out.println(date+"------------");
        //创建一个新的Excel
        HSSFWorkbook workBook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workBook.createSheet();
        //sheet页名称
        workBook.setSheetName(0, "xxx数据下载");
        //创建header页
        HSSFHeader header = sheet.getHeader();
        //设置标题居中
        header.setCenter("标题");
        //设置第一行为Header
        HSSFRow row = sheet.createRow(0);
        // HSSFCell cell0 = row.createCell(Short.parseShort("0"));
        HSSFCell cell1 = row.createCell(Short.parseShort("0"));
        HSSFCell cell2 = row.createCell(Short.parseShort("1"));
        HSSFCell cell3 = row.createCell(Short.parseShort("2"));
        HSSFCell cell4 = row.createCell(Short.parseShort("3"));
        HSSFCell cell5 = row.createCell(Short.parseShort("4"));

        //表头
        cell1.setCellValue("标题1");
        cell2.setCellValue("标题2");
        cell3.setCellValue("标题3");
        cell4.setCellValue("标题4");
        cell5.setCellValue("标题5");
        sheet.setColumnWidth((short) 0, (short) 4000);
        sheet.setColumnWidth((short) 1, (short) 4000);
        sheet.setColumnWidth((short) 2, (short) 4000);
        sheet.setColumnWidth((short) 3, (short) 20000);
        sheet.setColumnWidth((short) 4, (short) 4000);
        JSONArray xqOther = this.getXqOther(date)//获取需要导出的内容
        if (xqOther != null && xqOther.size() > 0){
                List<Object> objList = new ArrayList<>();
                for (Object o : xqOther) {
                        objList.add(o);
                }
                for (int i = 0; i < objList.size(); i++) {
                        XqOtherZdghVO jsonObject = (XqOtherZdghVO) objList.get(i);
                      //  JSONObject object = JSONObject.parseObject(jsonObject.toString());
                        row = sheet.createRow(i+1);
                        //创建列
                        //cell0 = row.createCell(Short.parseShort("0"));
                        cell1 = row.createCell(Short.parseShort("0"));
                        cell2 = row.createCell(Short.parseShort("1"));
                        cell3 = row.createCell(Short.parseShort("2"));
                        cell4 = row.createCell(Short.parseShort("3"));
                        cell5 = row.createCell(Short.parseShort("4"));
                        //设置列的值
                        // cell0.setCellValue(busOrderConfig.getSerialNumber());
                        cell1.setCellValue(jsonObject.getBatch_id());
                        cell2.setCellValue(jsonObject.getSence_id());
                        cell3.setCellValue(jsonObject.getSence_type());
                        cell4.setCellValue(jsonObject.getContent());
                        cell5.setCellValue(jsonObject.getCollect_date());

                        //设置列长度
                        //sheet.setColumnWidth((short) 0, (short) 3000);
                        sheet.setColumnWidth((short) 0, (short) 4000);
                        sheet.setColumnWidth((short) 1, (short) 4000);
                        sheet.setColumnWidth((short) 2, (short) 4000);
                        sheet.setColumnWidth((short) 3, (short) 20000);
                        sheet.setColumnWidth((short) 4, (short) 4000);
                }

        }


        //通过Response把数据以Excel格式保存
        response.reset();
        response.setContentType("application/msexcel;charset=UTF-8");
        try {
                response.addHeader("Content-Disposition", "attachment;filename=\""
                        + new String(("xxx数据下载" + ".xls").getBytes("GBK"),
                        "ISO8859_1") + "\"");
                OutputStream out = response.getOutputStream();
                workBook.write(out);
                out.flush();
                out.close();
        } catch (Exception e) {
                e.printStackTrace();
        }
        return null;
}