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测试有些注意点截图放最后边了。
一定要采用这种请求。不然返回的就全部是乱码了