1. 添加依赖
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
2. 自定义注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 作用于需要导出的字段名上
*/
//运行时注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
//列名
String name() default "";
//排序,实体类中从1开始排序,0被序号列占用
int sort();
//列宽
int width() default 15;
}
3. Excel工具类
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.mybatis.logging.Logger;
import org.mybatis.logging.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;
@Slf4j
public class ExcelUtils {
private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
/**
* @param list 导出的数据list
* @param clazz 导出的对象class
* @param fileName 导出的文件名称
* @param response response
* @param <T>
*/
public static <T> void exportExcel(List<T> list, Class<T> clazz, String fileName, HttpServletResponse response) {
SXSSFWorkbook workBook = getWorkBook(list, clazz);
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"); // .xlsx 用这个
//response.setContentType("application/vnd.ms-excel;charset=UTF-8"); // .xls 用这个
response.setCharacterEncoding("UTF-8");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
OutputStream output = response.getOutputStream();
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
workBook.write(bufferedOutPut);
bufferedOutPut.flush();
bufferedOutPut.close();
output.close();
workBook.close();
} catch (Exception e) {
//记录日志
log.info(e.getMessage());
//throw new BadRequestException(e.getMessage());
}
}
private static <T> SXSSFWorkbook getWorkBook(List<T> list, Class<T> clazz) {
SXSSFWorkbook wb = new SXSSFWorkbook(100);
// 单元格样式
CellStyle titleStyle = titleStyle(wb);// 表头样式
CellStyle orderStyle = orderStyle(wb);// 序号样式
CellStyle contentStyle = contentStyle(wb);// 普通单元格样式
// 遍历集合数据,产生数据行
Iterator<?> it = list.iterator();
int index = 0;//数据条数(行数)
int rowIndex = 0;// 每新建一个sheet此数值归零
Sheet sheet = null;
while (it.hasNext()) {
if (index == 0 || index % 5000 == 0) {//此处是限制单个sheet数据量,每5000条数据新建一个sheet
rowIndex = 0;
//创建新的sheet
sheet = wb.createSheet();
Row row = sheet.createRow(0);
row.setHeightInPoints(23);
Field[] declaredFields = getAllFields(clazz);
//添加序号
Cell cellOrder = row.createCell(0);
cellOrder.setCellValue("序号");
cellOrder.setCellStyle(titleStyle);
sheet.setColumnWidth(0, 10 * 256);
//设置表头
for (Field field : declaredFields) {
ExcelAttribute annotation = field.getAnnotation(ExcelAttribute.class);
if (annotation != null) {
Cell cell = row.createCell(Integer.valueOf(annotation.sort()));
cell.setCellValue(annotation.name());
cell.setCellStyle(titleStyle);
//设置列的宽度
sheet.setColumnWidth(Integer.valueOf(annotation.sort()), (int) annotation.width() * 256);
}
}
}
index++;
rowIndex++;
//设置序号值
Row row = sheet.createRow(rowIndex);
Cell cell = row.createCell(0);
cell.setCellStyle(orderStyle);
//cell.setCellValue(index);//新的sheet序号接着上个sheet的序号累加
cell.setCellValue(rowIndex);//新的sheet序号从头开始累加
row.setHeightInPoints(18);
T t = (T) it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = getAllFields(t.getClass());
for (Field field : fields) {
ExcelAttribute annotation = field.getAnnotation(ExcelAttribute.class);
if (annotation != null) {
cell = row.createCell(Integer.valueOf(annotation.sort()));
cell.setCellStyle(contentStyle);
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Method getMethod = t.getClass().getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
cell.setCellValue(null == value ? "" : String.valueOf(value));
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
return wb;
}
//设置标题样式
private static CellStyle titleStyle(SXSSFWorkbook wb) {
CellStyle cellStyle = contentStyle(wb);
//设置居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置单元格背景颜色
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置单元格填充样式(使用纯色背景颜色填充)
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置字体加粗
Font font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
//设置字体
font.setFontName("宋体");
cellStyle.setFont(font);
return cellStyle;
}
//设置单元格样式
private static CellStyle contentStyle(SXSSFWorkbook wb) {
//给单元格设置样式
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 设置字体
font.setFontName("宋体");
// 给字体设置样式
cellStyle.setFont(font);
// 字体设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
// 设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
// 设置单元格样式
private static CellStyle orderStyle(SXSSFWorkbook wb) {
CellStyle cellStyle = contentStyle(wb);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
return cellStyle;
}
private static <T> Field[] getAllFields(Class<T> clazz) {
Field[] declaredFields = clazz.getDeclaredFields();
Class superClass = clazz.getSuperclass();
if (!superClass.equals(Object.class)) {
Field[] superFields = superClass.getDeclaredFields();
int sonLength = declaredFields.length;
int superLength = superFields.length;
// 合并两个数组
Field[] newFields = new Field[sonLength + superLength];
System.arraycopy(declaredFields, 0, newFields, 0, sonLength);
System.arraycopy(superFields, 0, newFields, sonLength, superLength);
return newFields;
}
return declaredFields;
}
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* 根据文件后缀名类型获取对应的工作簿对象
*
* @param inputStream 读取文件的输入流
* @param fileType 文件后缀名类型(xls或xlsx)
* @return 包含文件数据的工作簿对象
* @throws IOException
*/
public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
/**
* 导入Excel
*/
public static Workbook importExcel(MultipartFile file) throws IOException {
//读取文件流
InputStream is = file.getInputStream();
//文件名
String fileName = file.getOriginalFilename();
//判断文件格式是否为excel
boolean notNull = false;
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
//throw new BusinessException("上传文件格式不正确");
}
Workbook wb = null;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
//xlsx格式
wb = new XSSFWorkbook(is);
} else {
//xls格式
wb = new HSSFWorkbook(is);
}
return wb;
}
}
4. 实体类使用注解
import lombok.Data;
import java.io.Serializable;
@Data
public class UserInfoVo implements Serializable {
@ExcelAttribute(name = "用户id", sort = 1, width = 20)
private Long id; //用户id
@ExcelAttribute(name = "昵称", sort = 2, width = 20)
private String nickname; //昵称
@ExcelAttribute(name = "生日", sort = 3, width = 20)
private String birthday; //生日
@ExcelAttribute(name = "性别", sort = 4, width = 20)
private String gender; //性别
@ExcelAttribute(name = "年龄", sort = 5, width = 20)
private String age; //年龄
@ExcelAttribute(name = "城市", sort = 6, width = 20)
private String city; //城市
}
5. 调用工具类
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/api")
public class ExcelController {
/**
* 导出
*/
@PostMapping("/export")
public void export(HttpServletResponse response, @RequestBody Map param) {
//查询数据
List<UserInfoVo> userInfoVoList = new ArrayList<>();//此处调用service查询需要导出的数据
//导出
ExcelUtils.exportExcel(userInfoVoList, UserInfoVo.class, "用户表", response);
}
}
6.前端实现
// 导出excel
exportTb(){
let params = JSON.parse(JSON.stringify(this.fmData));
recordExport(params).then(res => {
console.log(res)
downloadFile(res, '记录', 'xlsx')
})
}
// 下载文件
function downloadFile(obj, name, suffix) {
/** 版本1 **/
const url = window.URL.createObjectURL(new Blob([obj]))
const link = document.createElement('a')
link.style.display = 'none'
link.href = url
const fileName = parseTime(new Date()) + '-' + name + '.' + suffix
link.setAttribute('download', fileName)
document.body.appendChild(link)
link.click()
document.body.removeChild(link)
/** 版本2 **/
//let blob = new Blob([obj.data], {
// // 这里一定要和后端对应,不然可能出现乱码或者打不开文件
// type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
//});
//if (window.navigator.msSaveOrOpenBlob) {
// navigator.msSaveBlob(blob, fileName)
//} else {
// const link = document.createElement('a');
// link.href = window.URL.createObjectURL(blob);
// link.download = name + '.' + suffix; // 在前端也可以设置文件名字
// link.click();
// //释放内存
// window.URL.revokeObjectURL(link.href)
//}
}
// 记录列表导出
export function recordExport(params){
return request({
url: '/xxx/export',
method: 'post',
data: params,
responseType: 'blob'//这个很重要,表示二进制类型,不加这个下载了打不开
})
}