原文链接：https://blog.csdn.net/fanrenxiang/article/details/80985879
项目中经常需要使用导入导出功能来加快数据的操作，尤其是一些项目的后台管理系统，特此奉上我们项目中使用到导入导出代码，均可以实际使用。准备工作：pom中加入以下依赖:
-
<dependency>
-
<groupId>org.apache.poi</groupId>
-
<artifactId>poi</artifactId>
-
<version>
3.15-beta2</version>
-
</dependency>
-
<dependency>
-
<groupId>org.apache.poi</groupId>
-
<artifactId>poi-ooxml</artifactId>
-
<version>
3.15-beta2</version>
-
</dependency>
导入功能
基本思路：读取到文件--->创建表格并把文件流内容读取到表格中--->解析表格--->持久化
-
package com.simons.cn.springbootdemo.util;
-
-
import com.simons.cn.springbootdemo.bean.Movie;
-
import com.simons.cn.springbootdemo.service.Weixin.IndexService;
-
import org.apache.poi.ss.usermodel.*;
-
import org.slf4j.Logger;
-
import org.slf4j.LoggerFactory;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.stereotype.Service;
-
import org.springframework.util.FileCopyUtils;
-
import org.springframework.web.multipart.MultipartFile;
-
-
import java.io.File;
-
import java.io.FileInputStream;
-
import java.util.ArrayList;
-
import java.util.List;
-
-
@Service
-
public
class FilePortUtil {
-
private
static
final Logger log = LoggerFactory.getLogger(FilePortUtil.class);
-
-
@Autowired
-
private IndexService indexService;
-
-
/**
-
* 导入功能
-
*
-
* @param multipartFile
-
* @return
-
*/
-
public int fileImport(MultipartFile multipartFile) {
-
File file =
null;
-
Workbook workbook =
null;
-
int totalNum =
0;
-
/*得到的path是 /D:/springbootdemo/target/springbootdemo/WEB-INF/classes/ */
-
String path = FilePortUtil.class.getClassLoader().getResource(
"/").getPath();
-
/*拼接后的path就是 /D:/springbootdemo/target/springbootdemo/WEB-INF/电影正式资源.xlsx /*/
-
path = path.substring(
0, path.indexOf(
"WEB-INF") +
"WEB-INF".length()) +
"/" + multipartFile.getOriginalFilename();
-
file =
new File(path);
-
try {
-
/*把文件流copy读取到文件中*/
-
FileCopyUtils.copy(multipartFile.getBytes(), file);
-
workbook = WorkbookFactory.create(
new FileInputStream(file));
-
List<Movie> list =
new ArrayList<>();
-
/*遍历sheet页*/
-
for (
int i =
0; i < workbook.getNumberOfSheets(); i++) {
-
Sheet sheet = workbook.getSheetAt(i);
-
if (sheet ==
null) {
-
continue;
-
}
-
/*统计导入的总条数，要是你的excell包含了表头，就不用加1了*/
-
if (sheet.getLastRowNum() >
0) {
-
totalNum += sheet.getLastRowNum() +
1;
-
}
-
/*遍历行，这里j的初始值取1是因为我的表格里第一行是表头*/
-
for (
int j =
1; j < sheet.getPhysicalNumberOfRows(); j++) {
-
Row row = sheet.getRow(j);
-
/*解析列，下标从0开始*/
-
Cell cell2 = row.getCell(
2);
-
Cell cell3 = row.getCell(
3);
-
if (cell2 ==
null || cell3 ==
null) {
-
continue;
-
}
-
String name =
this.getCellValue(cell2);
-
String original =
this.getCellValue(cell3);
-
-
/*我这里省略了很多数据清洗、校验的代码*/
-
-
Movie movie =
new Movie();
-
movie.setName(name);
-
movie.setOriginal(original);
-
list.add(movie);
-
}
-
/*持久化：批量新增*/
-
indexService.insertBatch(list);
-
}
-
/*解析完删除此路径下的文件*/
-
file.delete();
-
return totalNum;
-
}
catch (Exception e) {
-
e.printStackTrace();
-
log.error(
"导入功能公用类异常exception={}", e);
-
}
-
return totalNum;
-
}
-
-
public String getCellValue(Cell cell) {
-
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
-
return String.valueOf(cell.getBooleanCellValue());
-
}
else
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
-
Double d = cell.getNumericCellValue();
-
return String.valueOf(d.intValue());
-
}
-
return String.valueOf(cell.getStringCellValue());
-
}
-
-
}
一般来说，每个导入功能处理的逻辑不太一样，里面的校验、数据对象也不太一样，所以我这里就没有封装成公用类而仅仅写成service，里面具体的逻辑交由程序员填充。
导出功能
导出功能是最常用的，下面的代码中用到了反射的思想，比如，你查询出来的List<T>结果集，只想导出指定的那些字段数据，就非常方便了，代码如下:
基本思路：创建表格对象--->将数据set进表格--->将表格流写入response返回
-
package com.simons.cn.springbootdemo.util;
-
-
import org.apache.commons.collections.CollectionUtils;
-
import org.apache.commons.lang.time.DateFormatUtils;
-
import org.apache.poi.hssf.usermodel.*;
-
import org.slf4j.Logger;
-
import org.slf4j.LoggerFactory;
-
import org.springframework.stereotype.Service;
-
-
import javax.servlet.http.HttpServletResponse;
-
import java.beans.PropertyDescriptor;
-
import java.lang.reflect.Field;
-
import java.lang.reflect.Method;
-
import java.util.Date;
-
import java.util.Iterator;
-
import java.util.List;
-
-
public
class FilePortUtil {
-
private
static
final Logger log = LoggerFactory.getLogger(FilePortUtil.class);
-
-
/**
-
* 导出功能
-
* 注意：泛型T类字段名和containBean集合里字段名字的一致性
-
*
-
* @param response
-
* @param title 表名
-
* @param headers 表头
-
* @param list 数据集
-
* @param containBean 数据集类型字段
-
* @param <T>
-
* @throws Exception
-
*/
-
public
static <T>
void exportExcel(HttpServletResponse response, String title, String[] headers, List<T> list, List<String> containBean) throws Exception {
-
HSSFWorkbook workbook =
null;
-
try {
-
workbook =
new HSSFWorkbook();
-
HSSFSheet sheet = workbook.createSheet(title);
-
HSSFRow row = sheet.createRow(
0);
-
/*创建第一行表头*/
-
for (
short i =
0; i < headers.length; i++) {
-
HSSFCell cell = row.createCell(i);
-
HSSFRichTextString text =
new HSSFRichTextString(headers[i]);
-
cell.setCellValue(text);
-
}
-
Iterator<T> it = list.iterator();
-
int index =
0;
-
while (it.hasNext()) {
-
index++;
-
row = sheet.createRow(index);
-
T t = (T) it.next();
-
/*反射得到字段*/
-
Field[] fields = t.getClass().getDeclaredFields();
-
/*如果需要匹配*/
-
if (CollectionUtils.isNotEmpty(containBean)) {
-
for (
int j =
0; j < containBean.size(); j++) {
-
for (
int i =
0; i < fields.length; i++) {
-
Field field = fields[i];
-
if (!field.getName().equals(containBean.get(j)))
-
continue;
-
/*给每一列set值*/
-
setCellValue(t, field, row, j);
-
}
-
}
-
}
else {
-
for (
int i =
0; i < fields.length; i++) {
-
Field field = fields[i];
-
setCellValue(t, field, row, i);
-
}
-
}
-
}
-
/*application/vnd.ms-excel告诉浏览器要下载的是个excel*/
-
response.setContentType(
"application/vnd.ms-excel;charset=UTF-8");
-
/*请求头设置，Content-Disposition为下载标识，attachment标识以附件方式下载*/
-
response.addHeader(
"Content-Disposition",
"attachment;filename=" +
new String((title).getBytes(),
"ISO8859-1") +
".xls");
-
workbook.write(response.getOutputStream());
-
}
finally {
-
if (workbook !=
null) {
-
workbook.close();
-
}
-
}
-
}
-
-
/**
-
* 设置每一行中的列
-
*
-
* @param t
-
* @param field
-
* @param row
-
* @param index
-
* @param <T>
-
*/
-
private
static <T>
void setCellValue(T t, Field field, HSSFRow row, int index) {
-
HSSFCell cell = row.createCell(index);
-
Object value = invoke(t, field);
-
String textValue =
null;
-
if (value !=
null) {
-
if (value
instanceof Date) {
-
Date date = (Date) value;
-
textValue = DateFormatUtils.format(date,
"yyyy-MM-dd HH:mm:ss");
-
}
else {
-
textValue = value.toString();
-
}
-
}
-
if (textValue !=
null) {
-
cell.setCellValue(textValue);
-
}
-
}
-
-
/**
-
* 反射映射数据集字段
-
*
-
* @param t
-
* @param field
-
* @param <T>
-
* @return
-
*/
-
private
static <T>
Object invoke(T t, Field field) {
-
try {
-
String fieldName = field.getName();
-
PropertyDescriptor pd =
new PropertyDescriptor(fieldName, t.getClass());
-
Method method = pd.getReadMethod();
-
return method.invoke(t);
-
}
catch (Exception e) {
-
return
null;
-
}
-
}
-
}
-
@ResponseBody
-
@RequestMapping(value =
"/fileport", method = RequestMethod.GET)
-
public void filePort(HttpServletResponse response) {
-
//导出的表名
-
String title =
"测试导出活动参与记录";
-
//表中第一行表头字段
-
String[] headers = {
"主键id",
"用户名",
"活动id",
"奖品id",
"中奖时间"};
-
//实际数据结果集
-
List<UserReward> listObject = userRewardDao.queryUserActivityInfo(
"2018shuangdan_act",
"2018shuangdan_evt",
"sdthanks");
-
//具体需要写入excel需要哪些字段，这些字段取自UserReward类，也就是上面的实际数据结果集的泛型
-
List<String> listColumn = Arrays.asList(
"id",
"username",
"actId",
"rewardId",
"winRewardTime");
-
try {
-
FilePortUtil.exportExcel(response, title, headers, listObject, listColumn);
-
}
catch (Exception e) {
-
e.printStackTrace();
-
}
-
}
888
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
