springboot数据库查询并导入exce表格步骤详解

1、前端添加按键

<!DOCTYPE html>
<html>
<body>
<table border="0" style="margin-top:4px; margin-left: 18px">
    <tr>
        <td><a href="http://localhost:8181/Sludge/sludgeOutToExcel" class="easyui-linkbutton" onclick="downloadfile();">导出报表</a></td>
    </tr>
</table>
</body>
</html>

2、后端

数据库

思路过程：controller层接收前端浏览器请求，调用service层方法，service实现类方法调用mapper层的方法返回查询的数据，写入流中返回给浏览器。

pom.xml依赖：

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
</dependency>

mapper层

@Select("SELECT * from sludge")
public List<Sludge> OutToExcel();

service层接口

public interface SludgeService extends IService<Sludge> {
    //9.1
    public void sludgeOutToExcel(HttpServletRequest request,HttpServletResponse response)throws Exception;
}

service层接口的实现类

public void sludgeOutToExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sludge");
        List<Sludge> sludgeList = sludgeMapper.OutToExcel();
        String fileName = "sludge" + ".xls";
        int rowNum = 1;
        String[] headers = {"id","processing","watercontent","factoryname","createtime","modifiedtime"};
        HSSFRow row = sheet.createRow(0);
        for (int i=0; i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        for (Sludge sludge:sludgeList){
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(sludge.getId());
            row1.createCell(1).setCellValue(sludge.getProcessing().toString());
            row1.createCell(2).setCellValue(sludge.getWaterContent().toString());
            row1.createCell(3).setCellValue(sludge.getFactoryName());
            row1.createCell(4).setCellValue(sludge.getGmtCreate().toString());
            row1.createCell(5).setCellValue(sludge.getGmtModified().toString());
            rowNum++;
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition","attachment;filename="+fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

View Code

controller层

@RestController
@RequestMapping("Sludge")
public class SludgeController {

    @Autowired
    private SludgeService sludgeService;
    //9.1
    @GetMapping("sludgeOutToExcel")
    public void sludgeOutToExcel(HttpServletRequest request, HttpServletResponse response)throws Exception{
         sludgeService.sludgeOutToExcel(request,response);
    }
}