基于springboot,简单实现Excel的导出
一、简单说明
为了简单实现效果,这里不从数据库获取数据,而是直接写死几个固定,可以自己改为由数据库中获取
二、实现代码:
1、编写UserController类
package com.it.cfay.controller;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.util.Date;
@Controller
public class UserController {
/**
* 为了简单实现效果,这里不从数据库获取数据,而是直接写死几个固定,可以自己改为由数据库中获取
* @return
*/
public HSSFWorkbook exportExcel() {
//创建一个工作簿
HSSFWorkbook hw = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = hw.createSheet("用户信息");
//创建表头
HSSFRow rowHead = sheet.createRow((short) 0);
rowHead.createCell(0).setCellValue("用户编号");
rowHead.createCell(1).setCellValue("用户名称");
rowHead.createCell(2).setCellValue("用户年龄");
for (int i = 1; i < 4; i++) {
HSSFRow row = sheet.createRow((short) i);
row.createCell(0).setCellValue("a00" + i);
row.createCell(1).setCellValue("lcb" + i);
row.createCell(2).setCellValue(18 + i);
}
return hw;
}
/**
* excel的导出
* @param response
*/
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) {
try {
HSSFWorkbook workbook = exportExcel();
//清除首部的空白行
response.reset();
//设置编码
response.setContentType("application/octet-stream; charset=utf-8");
//设置导出excel的名称
response.setHeader("Content-Disposition", "attachment; filename=" + new Date().getTime() + ".xlsx");
workbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
}
2、pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.it.cfay</groupId>
<artifactId>excel-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excel-demo</name>
<description>简单Excel导出的实现</description>
<properties>
<java.version>1.8</java.version>
<poi.version>3.17</poi.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- apache poi start -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- apache poi end -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>