springboot+vue 导出excel

package com.tianwen.springcloud.microservice.base.util;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.tianwen.springcloud.microservice.base.dto.course.CourseExcelDTO;
 
public class CourseExcelUtil 
{
    /**
     * 课程导出类
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    public static void exportCourseExcel(HttpServletResponse response, 
            String fileName,
            List<CourseExcelDTO> dataList)
    {
        List<String> columnList = new ArrayList<String>();
        columnList.add("课程名称");
        columnList.add("主讲人");
        columnList.add("查看权限");
        columnList.add("课程方式");
        columnList.add("课程分类");
        columnList.add("提交人");
        columnList.add("参与人数");
        columnList.add("浏览次数");
        columnList.add("课时数");
        columnList.add("课时总时长");

        //声明输出流
        OutputStream os = null;
        //设置响应头
        setResponseHeader(response,fileName);
        try 
        {
            //获取输出流
            os = response.getOutputStream();
            //内存中保留5000条数据,以免内存溢出,其余写入硬盘
            SXSSFWorkbook wb = new SXSSFWorkbook(5000);
            //获取该工作区的第一个sheet
            Sheet sheet1 = wb.createSheet("sheet1");
            int excelRow = 0;
            //创建标题行
            Row titleRow = sheet1.createRow(excelRow++);
            for(int i = 0;i<columnList.size();i++)
            {
                //创建该行下的每一列,并写入标题数据
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(columnList.get(i));
            }
            //设置内容行
            if(dataList != null && dataList.size() > 0)
            {
                //外层for循环创建行
                for(int i = 0;i<dataList.size();i++)
                {
                    Row dataRow = sheet1.createRow(excelRow++);
                    //内层for循环创建每行对应的列,并赋值
                    CourseExcelDTO courseObj = dataList.get(i);
                    for(int j = 0; j < columnList.size(); j++)
                    {
                        Cell cell = dataRow.createCell(j);
                        if(j == 0)
                        {
                            cell.setCellValue(courseObj.getCourseName());
                        }
                        else if(j == 1)
                        {
                            cell.setCellValue(courseObj.getTeachernames());
                        }
                        else if(j == 2)
                        {
                            cell.setCellValue(courseObj.getViewRole());
                        }
                        else if(j == 3)
                        {
                            cell.setCellValue(courseObj.getLessonType());
                        }
                        else if(j == 4)
                        {
                            cell.setCellValue(courseObj.getCourseTypeName());
                        }
                        else if(j == 5)
                        {
                            cell.setCellValue(courseObj.getCreateUserName());
                        }
                        else if(j == 6)
                        {
                            cell.setCellValue(courseObj.getJoins());
                        }
                        else if(j == 7)
                        {
                            cell.setCellValue(courseObj.getViews());
                        }
                        else if(j == 8)
                        {
                            cell.setCellValue(courseObj.getLessons());
                        }
                        else if(j == 9)
                        {
                            cell.setCellValue(courseObj.getCourseLessonTotalTime());
                        }
                    }
                }
            }
            
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xls");
            response.setContentType("application/octet-stream;charset=utf-8");
            //将整理好的excel数据写入流中
            wb.write(os);
        } 
        catch (IOException e) 
        {
            e.printStackTrace();
        }
        finally 
        {
            try 
            {
                // 关闭输出流
                if (os != null) 
                {
                    os.flush();
                    os.close();
                }
            } 
            catch (IOException e) 
            {
                e.printStackTrace();
            }
        }
    }
 
    /*
        设置浏览器下载响应头
     */
    private 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=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } 
        catch (Exception ex) 
        {
            ex.printStackTrace();
        }
    }
}

 

 

vm.$axios
        .post(this.$app.jhEduUrl + '/course/exportCourseList', param, { responseType: 'blob' })
        .then((resp) => {
          if (!resp) {
            console.log('下载数据返回空')
            return
          }
          const link = document.createElement('a');
          let blob = new Blob([resp.data], {type: 'application/vnd.ms-excel'});
          link.style.display = 'none';
          link.href = URL.createObjectURL(blob);

          link.setAttribute('download', '课程列表信息' + '.xls');
          document.body.appendChild(link);
          link.click();
          document.body.removeChild(link);
        })

  

posted @ 2022-05-12 16:38  漫步CODE人生  阅读(616)  评论(0编辑  收藏  举报