导入POI相关坐标依赖
<!-- POI相关依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
创建自定义注解类
ExportExcelTitle
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* ExcelTitle
* TODO - 自定义注解，导出到Excel文件名称信息
*
* @author Anhk丶
* @version 1.0
* @date 2021/9/18 11:43 星期六
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportExcelTitle {
/**
* 导出字段个数
* @return
*/
int length();
/**
* 导出文件名
* @return
*/
String description();
}
ExportExcelField
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* ExcelField
* TODO - 自定义注解，导出到Excel文件字段
*
* @author Anhk丶
* @version 1.0
* @date 2021/9/18 11:36 星期六
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportExcelField {
/**
* 导出字段在Excel文件中的列索引
*
* @return
*/
int index();
/**
* 描述，字段的中文描述
*
* @return
*/
String description();
}
ImportExcelField
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* ImportExcelField
* TODO - 自定义注解，导入Excel文件字段
*
* @author Anhk丶
* @version 1.0
* @date 2021/9/18 16:18 星期六
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ImportExcelField {
/**
* 导入字段在Excel文件的列索引
* @return
*/
int index();
}
创建相关实体类信息，并在需要导入导出的属性上面使用自定义的注解
import com.anhk.annotation.ExportExcelField;
import com.anhk.annotation.ExportExcelTitle;
import com.anhk.annotation.ImportExcelField;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
/**
* User
* TODO - 用户信息实体类
*
* @author Anhk丶
* @version 1.0
* @date 2021/9/17 10:25 星期五
*/
@TableName("db_user")
@ExportExcelTitle(length = 4, description = "用户信息")
public class User {
/**
* 主键ID
*/
@TableId(type = IdType.UUID)
private String id;
/**
* 用户名
*/
@ImportExcelField(index = 0)
@ExportExcelField(index = 0, description = "用户名")
private String userName;
/**
* 密码
*/
@ImportExcelField(index = 1)
private String password;
/**
* 联系方式
*/
@ImportExcelField(index = 2)
@ExportExcelField(index = 1, description = "联系方式")
private String mobile;
/**
* 邮箱
*/
@ImportExcelField(index = 3)
@ExportExcelField(index = 2, description = "邮箱")
private String mail;
/**
* 住址
*/
@ImportExcelField(index = 4)
@ExportExcelField(index = 3, description = "住址")
private String address;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getMail() {
return mail;
}
public void setMail(String mail) {
this.mail = mail;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User() {
}
public User(String id, String userName, String password, String mobile, String mail, String address) {
this.id = id;
this.userName = userName;
this.password = password;
this.mobile = mobile;
this.mail = mail;
this.address = address;
}
}
创建导入导出相关API接口
import com.anhk.exception.CustomerException;
import com.anhk.exception.ExceptionEnum;
import com.anhk.pojo.User;
import com.anhk.service.UserService;
import com.anhk.utils.ExcelUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* UserController
* TODO - 用户信息控制层
*
* @author Anhk丶
* @version 1.0
* @date 2021/9/18 9:47 星期六
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
/**
* 导出用户信息到Excel文件
*
* @param request 请求
* @param response 响应
* @param userName 用户名
* @return 无
*/
@RequestMapping("/exportUserToExcel")
public ResponseEntity<Void> exportUserToExcel(HttpServletRequest request, HttpServletResponse response, String userName) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
if (!StringUtils.isEmpty(userName)) {
queryWrapper.lambda().like(User::getUserName, userName);
}
List<User> userList = userService.list(queryWrapper);
ExcelUtils.exportDataToExcel(request, response, userList, User.class);
return ResponseEntity.ok().build();
}
/**
* 从Excel文件导入用户信息
*
* @param file 文件
* @return 无
*/
@PostMapping("/importUserFromExcel")
@Transactional(rollbackFor = Exception.class)
public ResponseEntity<Void> importUserFromExcel(MultipartFile file) {
InputStream inputStream = null;
try {
String filename = file.getOriginalFilename();
inputStream = file.getInputStream();
List<User> userList = ExcelUtils.importDataFromExcel(inputStream, filename, User.class);
boolean saveBatch = userService.saveBatch(userList);
if (!saveBatch) {
throw new CustomerException(ExceptionEnum.IMPORT_DATA_ERROR);
}
} catch (CustomerException e) {
throw e;
} catch (Exception e) {
e.printStackTrace();
throw new CustomerException(ExceptionEnum.IMPORT_DATA_ERROR);
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return ResponseEntity.ok().build();
}
}
创建导入导出相关工具类
import com.anhk.annotation.ExportExcelField;
import com.anhk.annotation.ExportExcelTitle;
import com.anhk.annotation.ImportExcelField;
import com.anhk.exception.CustomerException;
import com.anhk.exception.ExceptionEnum;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* ExcelUtils
* TODO - Excel导入导出工具类
*
* @author Anhk丶
* @version 1.0
* @date 2021/9/18 11:18 星期六
*/
public class ExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
/**
* 导出数据到Excel文件
*
* @param request 请求
* @param response 响应
* @param dataList 数据集合
* @param <T> 泛型
*/
public static <T> void exportDataToExcel(HttpServletRequest request, HttpServletResponse response, List<T> dataList, Class<T> clazz) {
//定义工作簿对象
Workbook workbook = new SXSSFWorkbook();
//定义字节输出流
ByteArrayOutputStream byteArrayOutputStream = null;
try {
ExportExcelTitle exportExcelTitle = clazz.getAnnotation(ExportExcelTitle.class);
if (exportExcelTitle == null) {
throw new CustomerException(ExceptionEnum.CAN_NOT_EXPORT_THIS_TYPE_DATA);
}
//定义页
Sheet sheet = workbook.createSheet(exportExcelTitle.description());
//定义第一行，标题行
Row row = sheet.createRow(0);
//定义第一行的列
Cell cell = row.createCell(0);
//设置标题列
cell.setCellValue(exportExcelTitle.description());
//合并标题行， 参数1：起始行 参数2：结束行 参数3：起始列 参数4：结束列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, exportExcelTitle.length() - 1));
//定义第二行，表格头
row = sheet.createRow(1);
//定义字段集合，用来存放需要导出的字段信息
ArrayList<Field> fields = new ArrayList<>();
//获取所有字段信息
Field[] filedArr = clazz.getDeclaredFields();
//遍历字段信息
for (Field field : filedArr) {
//获取字段上的ExportExcelField注解
ExportExcelField exportExcelField = field.getAnnotation(ExportExcelField.class);
//判断，是否含有该注解
if (exportExcelField != null) {
//根据注解上的值，创建单元格，并给单元格赋值
cell = row.createCell(exportExcelField.index());
cell.setCellValue(exportExcelField.description());
//将字段保存到集合中
fields.add(field);
}
}
//定义行序号
int rowNum = 2;
//遍历数据集合，生成数据表格
for (T t : dataList) {
//建立数据行
row = sheet.createRow(rowNum++);
//遍历需要导出的字段信息集合
for (Field field : fields) {
ExportExcelField ExportExcelField = field.getAnnotation(ExportExcelField.class);
//获取字段名称
String fieldName = field.getName();
//处理字段名称，组装为该字段的get方法
String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
//获取该方法
Method method = clazz.getDeclaredMethod(methodName);
//执行get方法，拿到返回值
Object cellValue = method.invoke(t);
//判断字段类型，将返回值转换为对应的类型
/*if (field.getType() == String.class) {
cell = row.createCell(ExportExcelField.index());
cell.setCellValue(String.valueOf(cellValue));
} else if (field.getType() == Integer.class) {
cell = row.createCell(ExportExcelField.index());
cell.setCellValue(Integer.parseInt(String.valueOf(cellValue)));
}*/
cell = row.createCell(ExportExcelField.index());
setCellValue(workbook, cell, cellValue);
}
}
byteArrayOutputStream = new ByteArrayOutputStream();
//将工作簿写入输出流流
workbook.write(byteArrayOutputStream);
//导出下载文件
DownLoadUtil.download(byteArrayOutputStream, exportExcelTitle.description() + ".xlsx", request, response);
} catch (CustomerException e) {
throw e;
} catch (Exception e) {
e.printStackTrace();
throw new CustomerException(ExceptionEnum.EXPORT_DATA_ERROR);
} finally {
if (byteArrayOutputStream != null) {
try {
byteArrayOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 从Excel文件导入数据
*
* @param inputStream 输入流
* @param fileName 文件名
* @param clazz 导入的实体类
* @param <T> 泛型
* @return 导入数据集合
*/
public static <T> List<T> importDataFromExcel(InputStream inputStream, String fileName, Class<T> clazz) {
//判断输入流是否为空
if (inputStream == null) {
return null;
}
Workbook workbook = null;
ArrayList<T> dataList = null;
try {
//获取文件后缀
String suffix = fileName.substring(fileName.lastIndexOf("."));
if (".xlsx".equals(suffix)) {
workbook = new XSSFWorkbook(inputStream);
} else if (".xls".equals(suffix)) {
workbook = new HSSFWorkbook(inputStream);
} else {
throw new CustomerException(ExceptionEnum.IMPORT_FILE_TYPE_ERROR);
}
//获取工作簿第一页
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
throw new CustomerException(ExceptionEnum.FILE_DATA_ERROR);
}
//获取最后一行，数据条数
int lastRowNum = sheet.getLastRowNum();
//定义数据集合，集合长度为数据条数
dataList = new ArrayList<>(lastRowNum);
//获取class的所有字段信息
Field[] fieldArr = clazz.getDeclaredFields();
//遍历，从第2行开始，到最后一行（第1行为表格头信息）
Row row = null;
Cell cell = null;
//遍历，知道该页的最后一行数据
for (int i = 1; i < lastRowNum; i++) {
//获取行
row = sheet.getRow(i);
//实例化对象信息
T t = clazz.newInstance();
for (Field field : fieldArr) {
//获取注解信息
ImportExcelField importExcelField = field.getAnnotation(ImportExcelField.class);
//判断该字段是否为导入字段
if (importExcelField != null) {
//获取该列
cell = row.getCell(importExcelField.index());
//设置私有字段强制性访问
field.setAccessible(true);
//判断字段类型，并赋值
if (field.getType() == Integer.class) {
//处理数值类型的转换，默认是double类型
int intVal = (int) Math.round(cell.getNumericCellValue());
if (Double.parseDouble(intVal + ".0") == cell.getNumericCellValue()) {
field.set(t, intVal);
}
} else if (field.getType() == Double.class) {
field.set(t, cell.getNumericCellValue());
} else if (field.getType() == Long.class) {
//处理数值类型的转换，默认是double类型
long longVal = Math.round(cell.getNumericCellValue());
field.set(t, longVal);
} else if (field.getType() == Boolean.class) {
field.set(t, cell.getBooleanCellValue());
} else if (field.getType() == Date.class) {
field.set(t, cell.getDateCellValue());
} else {
field.set(t, cell.getStringCellValue());
}
}
}
//将对象添加进集合汇总
dataList.add(t);
}
} catch (CustomerException e) {
throw e;
} catch (Exception e) {
e.printStackTrace();
throw new CustomerException(ExceptionEnum.IMPORT_DATA_ERROR);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return dataList;
}
/**
* 设置Excel列值
* @param workbook 工作簿对象
* @param cell 列对象
* @param fieldVal 值信息
*/
public static void setCellValue(Workbook workbook, Cell cell, Object fieldVal) {
// 生成一个格式化工具
DecimalFormat df = new DecimalFormat("######0.000");
if (fieldVal instanceof Date) {
//设置单元格格式
DataFormat dataFormat = workbook.createDataFormat();
CellStyle cellStyle = text(workbook);
cellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
cell.setCellStyle(cellStyle);
//转换日期类型，并写进excel对象
cell.setCellValue((Date) fieldVal);
} else if (fieldVal instanceof Integer) {
int intVal = (Integer) fieldVal;
// 写进excel对象
cell.setCellValue(intVal);
cell.setCellStyle(text(workbook));
} else if (fieldVal instanceof Float) {
Float fVal = (Float) fieldVal;
BigDecimal b1 = new BigDecimal(df.format(fVal) + "");
// style2.setDataFormat(format.getFormat("0.00")); // 两位小数
cell.setCellValue(
Float.parseFloat(String.valueOf(b1).substring(0, String.valueOf(b1).indexOf(".") + 3)));
cell.setCellStyle(text(workbook));
} else if (fieldVal instanceof Double) {
double dVal = (Double) fieldVal;
BigDecimal b1 = new BigDecimal(df.format(dVal) + "");
// style2.setDataFormat(format.getFormat("0.00"));
cell.setCellValue(Double
.parseDouble(String.valueOf(b1).substring(0, String.valueOf(b1).indexOf(".") + 3)));
cell.setCellStyle(text(workbook));
} else if (fieldVal instanceof Long) {// Long
long longValue = (Long) fieldVal;
cell.setCellValue(longValue);
cell.setCellStyle(text(workbook));
} else {
// 其它数据类型都当作字符串简单处理
if (fieldVal != null) {
cell.setCellValue(fieldVal.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(text(workbook));
}
}
/**
* 大标题的样式
*
* @param wb
* @return
*/
public static CellStyle bigTitle(Workbook wb) {
CellStyle style = wb.createCellStyle();
//设置字体样式
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16); //字体大小
font.setBold(true);//字体加粗
style.setFont(font);
//设置边框样式
style.setAlignment(HorizontalAlignment.CENTER); // 横向居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
return style;
}
/**
* 小标题样式
*
* @param wb
* @return
*/
public static CellStyle title(Workbook wb) {
CellStyle style = wb.createCellStyle();
//设置字体样式
Font font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 14); //字体大小
style.setFont(font);
//设置边框样式
style.setAlignment(HorizontalAlignment.CENTER); // 横向居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
style.setBorderTop(BorderStyle.THIN); // 上细线
style.setBorderBottom(BorderStyle.THIN); // 下细线
style.setBorderLeft(BorderStyle.THIN); // 左细线
style.setBorderRight(BorderStyle.THIN); // 右细线
return style;
}
/**
* 文字样式
*
* @param wb
* @return
*/
public static CellStyle text(Workbook wb) {
CellStyle style = wb.createCellStyle();
//设置字体样式
Font font = wb.createFont();
font.setFontName("等线");
font.setFontHeightInPoints((short) 12); //字体大小
style.setFont(font);
//设置边框样式
style.setAlignment(HorizontalAlignment.LEFT); // 横向居左
style.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
style.setBorderTop(BorderStyle.THIN); // 上细线
style.setBorderBottom(BorderStyle.THIN); // 下细线
style.setBorderLeft(BorderStyle.THIN); // 左细线
style.setBorderRight(BorderStyle.THIN); // 右细线
return style;
}
}
创建下载工具类
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
public class DownLoadUtil {
/**
* 下载文件
*
* @param byteArrayOutputStream 文件的字节输出流
* @param returnName 文件名
@param request 请求
* @param response 响应
* @throws IOException
*/
public static void download(ByteArrayOutputStream byteArrayOutputStream, String returnName, HttpServletRequest request, HttpServletResponse response) throws IOException {
response.setContentType("application/octet-stream;charset=utf-8");
//解决IE下导出中文乱码问题
request.setCharacterEncoding("UTF-8");
String header = request.getHeader("User-Agent").toUpperCase();
if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
returnName = URLEncoder.encode(returnName, "utf-8");
returnName = returnName.replace("+", "%20"); //IE下载文件名空格变+号问题
} else {
returnName = new String(returnName.getBytes(), "ISO8859-1");
}
//保存的文件名必须和页面编码一致，否则乱码
//returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));
response.addHeader("Content-Disposition", "attachment;filename=\"" + returnName + "\"");
response.setContentLength(byteArrayOutputStream.size());
//获取输出流
ServletOutputStream outputStream = response.getOutputStream();
//写进输出流
byteArrayOutputStream.writeTo(outputStream);
//刷新数据
byteArrayOutputStream.close();
outputStream.flush();
}
}
浙公网安备 33010602011771号