cn.afterturn
easypoi-spring-boot-starter
4.1.0
public enum ExcelTypeEnum {
XLS("xls"), XLSX("xlsx");
private String value;
private ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
@Component
public class ExcelExportUtils {
@Autowired
private HttpServletResponse response;
/**
* 导出excel
* @author 溪云阁
* @param list 泛型数据
* @param title 标题
* @param sheetName sheet的名称
* @param pojoClass 需要导出的对象
* @param fileName 文件名称
* @param isCreateHeader 是否创建表头
* @throws IOException void
*/
public void exportExcel(List list, Class pojoClass, String title, String sheetName, String fileName,
boolean isCreateHeader) throws IOException {
final ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
baseExport(list, pojoClass, fileName, exportParams);
}
/**
* 导出excel
* @author 溪云阁
* @param list 泛型数据
* @param title 标题
* @param sheetName sheet的名称
* @param pojoClass 需要导出的对象
* @param fileName 文件名称
* @param response
* @throws IOException void
*/
public void exportExcel(List list, Class pojoClass, String title, String sheetName, String fileName)
throws IOException {
baseExport(list, pojoClass, fileName, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* 导出excel
* @author 溪云阁
* @param list 泛型数据
* @param pojoClass 需要导出的对象
* @param fileName 文件名称
* @param exportParams 文件书香
* @param response
* @throws IOException void
*/
public void exportExcel(List list, Class pojoClass, String fileName, ExportParams exportParams)
throws IOException {
baseExport(list, pojoClass, fileName, exportParams);
}
/**
* 多个sheet导出
* @author 溪云阁
* @param list
* @param fileName
* @throws IOException void
*/
public void exportExcel(List> list, String fileName) throws IOException {
baseExport(list, fileName);
}
/**
* 最基础的对象导出
* @author 溪云阁
* @param list 数据列表
* @param pojoClass 导出对象
* @param fileName 文件名称
* @param exportParams 导出文件属性
* @throws IOException void
*/
private void baseExport(List list, Class pojoClass, String fileName, ExportParams exportParams)
throws IOException {
final Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, workbook);
}
/**
* 最基础的多sheet导出
* @author 溪云阁
* @param list 多个不同数据对象的列表
* @param fileName 文件名称
* @throws IOException void
*/
private void baseExport(List> list, String fileName) throws IOException {
final Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, workbook);
}
/**
* 文件下载
* @author 溪云阁
* @param fileName 文件名称
* @param workbook exce对象
* @throws IOException void
*/
private void downLoadExcel(String fileName, Workbook workbook) throws IOException {
ServletOutputStream output = null;
try {
final String downloadName = URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadName);
output = response.getOutputStream();
workbook.write(output);
}
catch (final Exception e) {
throw new IOException(e.getMessage());
}
finally {
if (output != null) {
output.flush();
output.close();
}
}
}
}
@Component
public class ExcelImportUtils {
/**
* 从指定位置获取文件后进行导入
* @author 溪云阁
* @param filePath 文件路径
* @param titleRows 表格标题行数,默认0
* @param headerRows 表头行数,默认1
* @param pojoClass 上传后需要转化的对象
* @return
* @throws IOException List
*/
public List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass)
throws Exception {
if (Strings.isEmpty(filePath)) {
return null;
} else {
final ImportParams params = new ImportParams();
// 表格标题行数,默认0
params.setTitleRows(titleRows);
// 表头行数,默认1
params.setHeadRows(headerRows);
// 是否需要保存上传的Excel
params.setNeedSave(true);
// 保存上传的Excel目录
params.setSaveUrl("/excel/");
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}
}
/**
* 上传文件导入
* @author 溪云阁
* @param file
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerfiy 是否检验excel内容
* @param pojoClass 导入的对象
* @return
* @throws Exception List
*/
public List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy,
Class pojoClass) throws Exception {
if (file == null) {
return null;
} else {
return baseImport(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
}
}
/**
* 最基础导入
* @author 溪云阁
* @param inputStream
* @param titleRows 表格标题行数,默认0
* @param headerRows 表头行数,默认1
* @param needVerify 是否需要检测excel
* @param pojoClass 导入的对象
* @return
* @throws IOException List
*/
private List baseImport(InputStream inputStream, Integer titleRows, Integer headerRows,
boolean needVerify, Class pojoClass) throws Exception {
if (inputStream == null) {
return null;
} else {
final ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
params.setNeedVerify(needVerify);
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
}
}
}
public class User implements Serializable {
// 数字格式化
private NumberFormat nf = NumberFormat.getNumberInstance();
private static final long serialVersionUID = 1L;
@Excel(name = "用户id", orderNum = "0", width = 15)
@Setter
@Getter
private long userId;
@Excel(name = "性别", orderNum = "1", width = 15, replace = { "男_1", "女_2" }, suffix = "孩")
@Setter
@Getter
private int sex;
@Excel(name = "金钱", orderNum = "2", width = 15)
@Setter
private double money;
public String getMoney() {
return nf.format(money);
}
@Excel(name = "用户信息", orderNum = "3", width = 15)
@Setter
@Getter
private String userName;
@Excel(name = "价格", orderNum = "4", width = 15)
@Setter
@Getter
private float price;
@Excel(name = "时间", orderNum = "5", width = 15, format = "yyyy-MM-dd")
@Setter
@Getter
private Date now;
}
@Api(tags = { "APP服务：数据接口" })
@RestController
@RequestMapping("view/ie")
public class ImportExportController {
@Autowired
private ExcelExportUtils excelExportUtils;
@Autowired
private ExcelImportUtils excelImportUtils;
/**
* 导出用户信息
* @author 溪云阁 void
*/
@ApiOperation(value = "导出excel")
@GetMapping(value = "/exportExcel")
public void exportExcel() throws Exception {
final List userList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
final User user = new User();
user.setUserId(i);
user.setSex(1);
user.setMoney(12332123 + i);
user.setUserName("小明" + i);
user.setPrice(23.1f + i);
user.setNow(new Date());
userList.add(user);
}
excelExportUtils.exportExcel(userList, User.class, "用户信息", "员工信息的sheet", "用户信息表");
}
/**
* 导入用户信息
* @author 溪云阁
* @param file
* @return
* @throws IOException Object
*/
@ApiOperation(value = "导入excel")
@GetMapping(value = "/importExcel")
public ResponseMsg> importExcel(@RequestParam("file") MultipartFile file) throws Exception {
final List userList = excelImportUtils.importExcel(file, 1, 1, false, User.class);
return MsgUtils.buildSuccessMsg(userList);
}
}
