Spring Boot 实现excel表格导出Demo

1.首先pom文件导入所用包

 

           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.6</version>
            </dependency>

2.控制层controller

package com.hawkcloud.modules.out.controller;


import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import com.hawkcloud.core.boot.ctrl.HawkController;


import com.hawkcloud.modules.memo.vo.KmyebReceiveVO;

import com.hawkcloud.modules.out.service.IOutExcelService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;

/** 控制器
*excel导出
* @author HD
* @since 2021-08-09
*/
@RestController
@AllArgsConstructor
@RequestMapping("hawk-out")
@Api(value = "", tags = "接口")
public class OutExcelController extends HawkController {

    private final IOutExcelService outExcelService;


    /**
     * 期初余额数据导出
     */
    @PostMapping("/outQcye")
    @ApiOperationSupport(order = 1)
    @ApiOperation(value = "详情", notes = "")
    public void outqcye(@RequestBody KmyebReceiveVO kmyeb, HttpServletResponse response) {
          outExcelService.outqcye(null, null, kmyeb,response);
    }
}

3.接口和实现类的导出处理

package com.hawkcloud.modules.out.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.hawkcloud.modules.memo.vo.KmyebReceiveVO;
import com.hawkcloud.modules.memo.vo.LedgerVO;

import javax.servlet.http.HttpServletResponse;

/**
 *  服务类
 *
 * @author   HD
 * @since 2021-09-10
 */
public interface IOutExcelService {

    void outqcye(IPage<LedgerVO> page, LedgerVO ledger, KmyebReceiveVO kmyeb, HttpServletResponse response);

}

package com.hawkcloud.modules.out.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.hawkcloud.modules.memo.entity.Ledger;
import com.hawkcloud.modules.memo.mapper.LedgerMapper;
import com.hawkcloud.modules.memo.vo.KmyebReceiveVO;
import com.hawkcloud.modules.memo.vo.LedgerVO;
import com.hawkcloud.modules.out.service.IOutExcelService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * 服务实现类
 *
 * @author HD
 * @since 2021-09-10
 */
@Service
public class OutExcelServiceImpl implements IOutExcelService {
    @Resource
    private LedgerMapper ledgerMapper;

    @Override
    public void outqcye(IPage<LedgerVO> page, LedgerVO ledger, KmyebReceiveVO kmyeb, HttpServletResponse response) {
        List<LedgerVO> ledgerVOS = ledgerMapper.qcyeList(page, ledger, kmyeb);
      //创建Excel文档
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Excel表格导出");
        HSSFRow row = null;
        row = sheet.createRow(0);
        row.setHeight((short)(26.25*20));
        //设置起始行号，终止行号， 起始列号，终止列号
        CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 0);
        sheet.addMergedRegion(rowRegion);
        //创建第一行
         row = sheet.createRow(0);
         //设置行高
         row.setHeight((short)(22.50*20));
         row.createCell(0).setCellValue("年度");
        row.createCell(1).setCellValue("币种");
        row.createCell(2).setCellValue("科目编码");
        row.createCell(3).setCellValue("科目名称");
        row.createCell(4).setCellValue("方向");
        row.createCell(5).setCellValue("数量");
        row.createCell(6).setCellValue("期初原币余额");
        row.createCell(7).setCellValue("期初本币余额");
   //方便遍历中对每一行设置值定义自增变量m
        int m =0;
        //遍历所获取的数据
        for (Ledger ledgerVO : ledgerVOS) {
            row = sheet.createRow(1+m);
            row.createCell(0).setCellValue(ledgerVO.getCreateyear());
            row.createCell(1).setCellValue(ledgerVO.getCurrtypename());
            row.createCell(2).setCellValue(ledgerVO.getSubjcode());
            row.createCell(3).setCellValue(ledgerVO.getSubjname());
            row.createCell(4).setCellValue(ledgerVO.getQcfx());
            row.createCell(5).setCellValue(ledgerVO.getQcsl());
            row.createCell(6).setCellValue(ledgerVO.getYbqcye());
            row.createCell(7).setCellValue(ledgerVO.getBbqcye());
            m++;
        }

        sheet.setDefaultRowHeight((short)(16.5*20));
        //列宽自适应
        for (int i = 0;i<=13;i++){
            sheet.autoSizeColumn(i);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            OutputStream outputStream = response.getOutputStream();
            response.setHeader("Content-disposition","attachment;filename=QCYE.xls");
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

 4.mapper接口和配置文件

    /**
     * 期初余额查询
     * @param page
     * @param ledger
     * @return
     */
    List<LedgerVO> qcyeList(IPage<LedgerVO> page, LedgerVO ledger,@Param("qcye")KmyebReceiveVO kmyeb);

 

 <select id="qcyeList" resultMap="ledgerResultMap">
        SELECT
        accfa.subjcode,
        accfa.subjname,
        temp.year createyear,
        temp.CURRTYPENAME,
        CASE

        WHEN SUM( bbqcye ) >= 0 THEN
        '借' ELSE '贷'
        END AS qcfx,
        abs( SUM( qcsl ) ) AS qcsl,
        abs( SUM( bbqcye ) ) AS bbqcye,
        abs( SUM( ybqcye ) ) AS ybqcye
        FROM
        (
        SELECT
        bl.year,
        cu.CURRTYPENAME,
        bl.pk_glorgbook,
        bl.pk_accsubj,
        bl.subjcode,
        bl.subjname,
        SUM( bl.localdebitamount ) - SUM( bl.localcreditamount ) AS bbqcye,
        SUM( bl.DEBITAMOUNT ) - SUM( bl.CREDITAMOUNT ) AS ybqcye,
        ROUND(SUM( bl.DEBITQUANTITY ) - SUM( bl.CREDITQUANTITY ),4) AS qcsl
        FROM
        hk_kmye bl LEFT JOIN bd_currtype cu on bl.pk_currtype=cu.PK_CURRTYPE
        WHERE
        1 = 1
        <if test="qcye.pkGlorgbook!=null and qcye.pkGlorgbook!=''">
            AND bl.pk_glorgbook = #{qcye.pkGlorgbook}
        </if>

        <if test="qcye.pkCurrtype!=null and qcye.pkCurrtype!=''">
            and bl.pk_currtype = #{qcye.pkCurrtype}
        </if>

        <if test="qcye.year!=null and qcye.year!=''">
            AND bl.YEAR = #{qcye.year}
        </if>

        AND bl.period = '00'

        GROUP BY
        bl.pk_glorgbook,cu.CURRTYPENAME,
        bl.pk_accsubj,
        bl.subjcode,
        bl.subjname
        ) temp
        JOIN bd_accsubj accfa ON temp.pk_glorgbook = accfa.pk_glorgbook
        AND temp.subjcode LIKE concat( accfa.subjcode, '%' )
        GROUP BY
        accfa.subjcode,temp.YEAR,temp.CURRTYPENAME,
        accfa.subjname
        ORDER BY
        accfa.subjcode

    </select>

5.最后给你们看测试结果，如果用postman测试有些注意点截图放最后边了。

 一定要采用这种请求。不然返回的就全部是乱码了