java下载/导出文件方法

Java实现Excle导出

查询数据库中的数据进行Excle导出

Mapper.xml

<?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="cn.com.buba.vuecrud.mapper.SubjectMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.buba.vuecrud.entity.Subject">
        <result column="accountNum" property="accountnum" />
        <result column="sccountName" property="sccountname" />
        <result column="classHour" property="classhour" />
        <result column="termNum" property="termnum" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        accountNum, sccountName, classHour, termNum
    </sql>

    <select id="findAll" resultType="cn.com.buba.vuecrud.entity.Subject">
        select * from subject;
    </select>

    <select id="findById" parameterType="java.lang.Integer" resultType="String">
        SELECT sccountname from subject where accountnum = #{accountnum} ;
    </select>

    <insert id="addSubject">
        insert into subject(accountnum,sccountname,classhour,termnum) values (#{accountnum},#{sccountname},#{classhour},#{termnum})
    </insert>

</mapper>

Mapper.java

package cn.com.buba.vuecrud.mapper;

import cn.com.buba.vuecrud.entity.Subject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * <p>
 * InnoDB free: 8192 kB Mapper 接口
 * </p>
 *
 * @author ***
 * @since 2023-01-29
 */
@Mapper
public interface SubjectMapper extends BaseMapper<Subject> {
    public List<Subject> findAll();
}

service.java

package cn.com.buba.vuecrud.service;

import cn.com.buba.vuecrud.entity.Subject;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * <p>
 * InnoDB free: 8192 kB 服务类
 * </p>
 *
 * @author ***
 * @since 2023-01-29
 */
public interface SubjectService extends IService<Subject> {

    List<Subject> findAll();
}

serviceImpl.java

package cn.com.buba.vuecrud.service.impl;

import cn.com.buba.vuecrud.entity.Subject;
import cn.com.buba.vuecrud.mapper.SubjectMapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * <p>
 * InnoDB free: 8192 kB 服务实现类
 * </p>
 *
 * @author ***
 * @since 2023-01-29
 */
@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {

    @Autowired
    private SubjectMapper subjectMapper;

    @Override
    public List<Subject> findAll() {
        return subjectMapper.findAll();
    }
}

controller.java

package cn.com.buba.vuecrud.controller;


import cn.com.buba.vuecrud.entity.Subject;
import cn.com.buba.vuecrud.service.SubjectService;
import cn.com.buba.vuecrud.util.LedingOutExcleUtil;
import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

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

import java.util.List;

/**
 * <p>
 * InnoDB free: 8192 kB 前端控制器
 * </p>
 *
 * @author ***
 * @since 2023-01-29
 */
@RestController
@RequestMapping("/subject")
public class SubjectController {
    @Autowired
    private SubjectService subjectService;

    @GetMapping("/test")
    public List<Subject> findAll(){
        List<Subject> all = subjectService.findAll();
        LedingOutExcleUtil ledingOutExcleUtil = new LedingOutExcleUtil();
        ledingOutExcleUtil.createExcle(all);
        return all;
    }
}

LedingOutExcleUtil.java（导出为Excle的方法）

package cn.com.buba.vuecrud.util;


import cn.com.buba.vuecrud.entity.Subject;
import cn.com.buba.vuecrud.service.SubjectService;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

public class LedingOutExcleUtil {

    @Autowired
    private SubjectService subjectService;

    public String createExcle(List<Subject> subjectList){

        /*//查询数据
        List<Subject> subjectList = subjectService.findAll();*/

        //生成文件路径
        String newFilePath = "d:\\a";
        //文件名称
        String fileName = "people.xlsx";
        //输出流
        OutputStream out = null;
        //Excle文件
        XSSFWorkbook workBook = new XSSFWorkbook();
        //Excle页脚
        XSSFSheet sheet = workBook.createSheet("导出数据");
        //设置列的宽度
        sheet.setDefaultColumnWidth(16);
        //创建标题行
        XSSFRow titleRow = sheet.createRow(0);
        String[] title = new String[]{"accountNum","sccountName","classHour","termNum"};
        //设置标题字体样式
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        XSSFFont font = workBook.createFont();
        font.setBold(true); //加粗
        font.setFontHeightInPoints((short)14);  //设置字体大小
        cellStyle.setFont(font);
        //设置标题列
        for (int i = 0; i < title.length; i++) {
            //创建标题的单元格
            XSSFCell titleCell = titleRow.createCell(i);
            //填充标题数值
            titleCell.setCellValue(title[i]);
            //设置样式
            titleCell.setCellStyle(cellStyle);
        }
        //填充数据
        //第一行是标题所以要从第二行开始
        for (int i = 0; i < subjectList.size() ; i++) {
            Subject subject = subjectList.get(i);
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < title.length; j++) {
                XSSFCell titleCell = row.createCell(j);
                String exportKey = title[j];
                switch (exportKey) {
                    case "accountnum":
                        String s = subject.getAccountnum().toString();
                        titleCell.setCellValue(s);
                        break;
                    case "sccountName":
                        titleCell.setCellValue(subject.getSccountname());
                        break;
                    case "classHour":
                        String s1 = subject.getClasshour().toString();
                        titleCell.setCellValue(s1);
                        break;
                    case "termNum":
                        String s2 = subject.getTermnum().toString();
                        titleCell.setCellValue(s2);
                        break;
                }
            }
        }

        try {
            File file = new File(newFilePath + File.separator + fileName);
            out = new FileOutputStream(file);
            workBook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }
}

最后，启动项目，调用接口进行校验：

http://localhost:8080/subject/test

到对应文件夹下查看文件是否生成。