一个完整的idea spring boot 项目及坑---3.Controller

知识点:

1.将list对象转换为本地excel文件

2.通过controller返回特定类型数据给前端下载.

excel 下载原理:

1.后台将list生成excel文档,存入服务器.

2.向前端提供访问地址,前端访问此文件后实现下载.

package com.example.demo.Controller;

import com.example.demo.FilterBean.FilterConditions;
import com.example.demo.bean.*;
import com.example.demo.mapper.OrderInfoMapper;
import com.example.demo.service.GoodsService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;


import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

@Controller
public class OrderController {

    @Autowired
    GoodsService goodsService;

    @Autowired
    OrderInfoMapper orderInfoMapper;

    //导出excel用的数据
    List<OrderInfo> data;

    @RequestMapping("/hello")
    public String show(){
        System.out.println("开始读取..");
        return "ordermessage";
    }



    @RequestMapping("/change_orderid")
    @ResponseBody
    public ResponseResult<Void>  test(MetShopv2Order metShopv2Order){
        System.out.println("修改："+metShopv2Order.toString());
        goodsService.change_orderid(metShopv2Order);
        return new ResponseResult<Void>(1,"插入成功") ;
    }

    //根据条件筛选数据方法，学校名称、支付起止时间、商品类别
    @RequestMapping("/getlist")
    @ResponseBody
    public EUDataGridResult OrderList(Integer page,Integer rows,FilterConditions filterConditions){

        System.out.println("筛选条件："+filterConditions);
        EUDataGridResult euDataGridResult = goodsService.getList(page,rows,filterConditions);
        data= orderInfoMapper.getListByCondition(filterConditions);

        return euDataGridResult;
    }

    /**
     * 将list对象转换为本地excel文件
     * @return
     * @throws Exception
     */
    @RequestMapping("/LeadToExcel")
    @ResponseBody
    public ResponseResult<Void> leadToExcelQuestionBank() throws Exception {
        System.out.println(data);
        String fileName = null;
//////////////////////////将ArrayList中的数据写入到本地excel中///////////////////////////
        //第一步，创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第二步，在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet("用户表一");
        //第三步，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("订单号");
        cell = row.createCell(1);
        cell.setCellValue("学校名称");
        cell=row.createCell(2);
        cell.setCellValue("学生姓名");
        cell=row.createCell(3);
        cell.setCellValue("年级");
        cell=row.createCell(4);
        cell.setCellValue("班级");
        cell=row.createCell(5);
        cell.setCellValue("商品");
        cell=row.createCell(6);
        cell.setCellValue("支付时间");
        cell=row.createCell(7);
        cell.setCellValue("数量");
        cell=row.createCell(8);
        cell.setCellValue("单价");
        cell=row.createCell(9);
        cell.setCellValue("总价");
        cell=row.createCell(10);
        cell.setCellValue("状态");
        cell=row.createCell(11);
        cell.setCellValue("商品规格");
        cell=row.createCell(12);
        cell.setCellValue("手机号");
        cell=row.createCell(13);
        cell.setCellValue("商家留言");
        cell=row.createCell(14);
        cell.setCellValue("顾客留言");

        //第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
        for (int i = 0; i <data.size(); i++)
        {
            HSSFRow row1 = sheet.createRow(i+1);
            OrderInfo or=data.get(i);
            //创建单元格设值
            row1.createCell(0).setCellValue(or.getOrderid());
            row1.createCell(1).setCellValue(or.getName());
            row1.createCell(2).setCellValue(or.getStudentname());
            row1.createCell(3).setCellValue(or.getStudentsession());
            row1.createCell(4).setCellValue(or.getStudentclass());
            row1.createCell(5).setCellValue(or.getPname());
            row1.createCell(6).setCellValue(or.getPtime());
            row1.createCell(7).setCellValue(or.getPamount());
            row1.createCell(8).setCellValue(or.getPuprice());
            row1.createCell(9).setCellValue(or.getPrice());
            row1.createCell(10).setCellValue(or.getState());
            row1.createCell(11).setCellValue(or.getPara());
            row1.createCell(12).setCellValue(or.getUsername());
            row1.createCell(13).setCellValue(or.getRemark());
            row1.createCell(14).setCellValue(or.getMessage());

        }
        //将文件保存到指定的位置
        try
        {
            //本地测试用
//            fileName = "D:\\folders\\Book.xls";
            //放在服务器上
             fileName = "E:\\Program Files\\wamp\\www\\YiTongNJ\\plus\\Book.xls";
            FileOutputStream fos = new FileOutputStream(fileName);
            workbook.write(fos);
            System.out.println("恭喜您！写入成功！！！！！！");
            fos.close();
        }
        catch (IOException e)
        {
            System.out.println("写入文件出错啦！");
            e.printStackTrace();
        }
        return new ResponseResult<Void>(1,fileName);
    }
//spingboot 下载指定资源方法,与之上的生成excel方法共同使用.
    @RequestMapping(value = "media")
    public ResponseEntity<InputStreamResource> downloadFile()
            throws IOException {
        String filePath = "D:\\folders\\Book.xls";
        FileSystemResource file = new FileSystemResource(filePath);
        HttpHeaders headers = new HttpHeaders();
        headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
        headers.add("Content-Disposition", String.format("attachment; filename=\"%s\"", file.getFilename()));
        headers.add("Pragma", "no-cache");
        headers.add("Expires", "0");

        return ResponseEntity
                .ok()
                .headers(headers)
                .contentLength(file.contentLength())
                .contentType(MediaType.parseMediaType("application/octet-stream"))
                .body(new InputStreamResource(file.getInputStream()));
    }
}