为大家提供一套自己封装的excel导入导出功能。
1.项目添加maven依赖包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>2.定义excel导入导出模板
模板第一行是标题栏，不属于正式数据，其中红色背景数据为必填数据，最后合并的一行是模板的注释数据，也不属于正式数据，在数据解析的时候，第一行要做严格的数据校验，不允许用户修改定义好的excel模板，最后一行要过滤掉。
3.创建自定义数据模型
@Data
public class ExcelOutPut {
private ArrayList<String> titleList;
private ArrayList<ArrayList<String>> dataList;
public ExcelOutPut() {
}
public ExcelOutPut(ArrayList<String> titleList, ArrayList<ArrayList<String>> dataList) {
this.titleList = titleList;
this.dataList = dataList;
}
}excel模板中第一行标题数据放入titleList，正式数据放入dataList中
4.封装PoiHelper帮助类
PoiHelper类中包含三个方法，readXlsx/readXls/createExcel，分别是解析xlsx格式的excel、xls格式的文档、根据输入数据创建excel文件
public class PoiHelper {
/**
* 读取xlsx文件
*
* @param io 文件绝对路径
* @throws Exception
*/
public static ExcelOutPut readXlsx(InputStream io) throws Exception {
try {
ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
ArrayList<String> titleList = new ArrayList<String>();
XSSFWorkbook xssfWorkbook;
xssfWorkbook = new XSSFWorkbook(io);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
int rowstart = xssfSheet.getFirstRowNum();
int rowEnd = xssfSheet.getLastRowNum();
// 分离excel第一行
XSSFRow row1 = xssfSheet.getRow(rowstart);
if (null == row1) {
xssfWorkbook.close();
return null;
}
int cellStart1 = row1.getFirstCellNum();
int cellEnd1 = row1.getLastCellNum();
for (int k = cellStart1; k <= cellEnd1; k++) {
XSSFCell cell = row1.getCell(k);
if (null == cell) {
titleList.add("");
} else {
titleList.add(cell.toString());
}
}
for (int i = rowstart + 1; i <= rowEnd; i++) {
XSSFRow row = xssfSheet.getRow(i);
if (null == row) {
continue;
}
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
ArrayList<String> arrayList = new ArrayList<String>();
for (int k = cellStart; k <= cellEnd; k++) {
XSSFCell cell = row.getCell(k);
if (null == cell) {
arrayList.add("");
} else {
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
arrayList.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case STRING: // 字符串
arrayList.add(cell.getStringCellValue().trim());
break;
case BOOLEAN: // Boolean
arrayList.add(String.valueOf(cell.getBooleanCellValue()));
break;
case FORMULA: // 公式
arrayList.add(cell.getCellFormula());
break;
case BLANK: // 空值
arrayList.add("");
break;
case ERROR: // 故障
arrayList.add("");
break;
default:
arrayList.add("");
break;
}
}
}
dataList.add(arrayList);
}
ExcelOutPut excelOutPut = new ExcelOutPut(titleList, dataList);
xssfWorkbook.close();
return excelOutPut;
} catch (Exception e) {
throw new Exception("导入失败，请重试！" + e.toString() + "&&&&&&" + e.getMessage());
}
}
/**
* 读取xls文件
*
* @param io 文件绝对路径
* @throws Exception
*/
public static ExcelOutPut readXls(InputStream io) throws Exception {
try {
ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
ArrayList<String> titleList = new ArrayList<String>();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(io);
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
int rowstart = hssfSheet.getFirstRowNum();
int rowEnd = hssfSheet.getLastRowNum();
// 分离excel第一行
HSSFRow row1 = hssfSheet.getRow(rowstart);
if (null == row1) {
hssfWorkbook.close();
return null;
}
int cellStart1 = row1.getFirstCellNum();
int cellEnd1 = row1.getLastCellNum();
for (int k = cellStart1; k <= cellEnd1; k++) {
HSSFCell cell = row1.getCell(k);
if (null == cell) {
titleList.add("");
} else {
titleList.add(cell.toString());
}
}
for (int i = rowstart + 1; i <= rowEnd; i++) {
HSSFRow row = hssfSheet.getRow(i);
if (null == row) {
continue;
}
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
ArrayList<String> arrayList = new ArrayList<String>();
for (int k = cellStart; k <= cellEnd; k++) {
HSSFCell cell = row.getCell(k);
if (null == cell) {
arrayList.add("");
} else {
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
arrayList.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case STRING: // 字符串
arrayList.add(cell.getStringCellValue().trim());
break;
case BOOLEAN: // Boolean
arrayList.add(String.valueOf(cell.getBooleanCellValue()));
break;
case FORMULA: // 公式
arrayList.add(cell.getCellFormula());
break;
case BLANK: // 空值
arrayList.add("");
break;
case ERROR: // 故障
arrayList.add("");
break;
default:
arrayList.add("");
break;
}
}
dataList.add(arrayList);
}
}
ExcelOutPut excelOutPut = new ExcelOutPut(titleList, dataList);
hssfWorkbook.close();
return excelOutPut;
} catch (Exception e) {
throw new Exception("导入失败，请重试！" + e.toString() + "&&&&&&" + e.getMessage());
}
}
/**
* 使用POI创建excel工作簿
*
* @param response
* @param excelOutPut 输入数据
* @throws IOException
*/
public static boolean createExcel(HttpServletResponse response, ExcelOutPut excelOutPut) throws IOException {
ArrayList<String> titleList = excelOutPut.getTitleList();
ArrayList<ArrayList<String>> dataList = excelOutPut.getDataList();
// 创建excel工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建第一个sheet（页），命名为 new sheet
XSSFSheet sheet = wb.createSheet();
// wb.setSheetName(0, "sheet1");// 工作簿名称
// Row 行
// Cell 方格
// Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
XSSFRow row = sheet.createRow((short) 0);
for (int i = 0; i < titleList.size(); i++) {
// 在row行上创建一个方格,设置方格的显示
row.createCell(i).setCellValue(titleList.get(i));
}
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow((short) i + 1);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j).setCellValue(dataList.get(i).get(j));
}
}
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
Date date = new Date();
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + formatter.format(date) + ".xlsx");
// 告诉浏览器用什么软件可以打开此文件
response.setContentType("application/octet-stream;charset=utf-8");
OutputStream outStream = response.getOutputStream();
wb.write(outStream);
wb.close();
return true;
}
}
5.service层导入操作
导入操作分为6步：
- 校验文档是否上传成功和文档格式
- 校验文档列名
- 判断必填数据是否为空
- 根据具体需求判断不允许重复数据是否重复，如手机号、邮箱等
- 数据库校验：根据具体需求判断邮箱和手机号是否存在
- 数据持久化
@Override
@Transactional
public ResultInfo importData(HttpServletRequest request, HttpServletResponse response, MultipartFile file) throws Exception {
try {
//1.校验文档存在和文档格式
List<KeyValueVo> errorList = new ArrayList<>();
if (file.isEmpty()) {
errorList.add(new KeyValueVo("文件错误", "请上传表格文件!"));
return new ResultInfo(1, "", errorList);
}
String fileName = file.getOriginalFilename();
if (!(fileName.endsWith(".xls") || fileName.endsWith(".xlsx"))) {
errorList.add(new KeyValueVo("文件格式错误", "上传文件不是.xls或.xlsx文件!"));
return new ResultInfo(1, "", errorList);
}
ExcelOutPut data = fileName.endsWith(".xls") ? PoiHelper.readXls(file.getInputStream())
: PoiHelper.readXlsx(file.getInputStream());
//2.校验文档列名
if (data.getDataList().isEmpty()) {
errorList.add(new KeyValueVo("数据错误", "导入了空数据;"));
}
if ("邮箱".equals(data.getTitleList().get(0).trim()) == false) {
errorList.add(new KeyValueVo("数据格式错误", "表格第1列列名和模版不匹配，列明必须是“邮箱”！"));
}
if ("密码".equals(data.getTitleList().get(1).trim()) == false) {
errorList.add(new KeyValueVo("数据格式错误", "表格第2列列名和模版不匹配，列明必须是“密码”！"));
}
if ("用户名".equals(data.getTitleList().get(2).trim()) == false) {
errorList.add(new KeyValueVo("数据格式错误", "表格第3列列名和模版不匹配，列明必须是“用户名”！"));
}
if ("手机号".equals(data.getTitleList().get(3).trim()) == false) {
errorList.add(new KeyValueVo("数据格式错误", "表格第4列列名和模版不匹配，列明必须是“手机号”！"));
}
if ("组织机构".equals(data.getTitleList().get(4).trim()) == false) {
errorList.add(new KeyValueVo("数据格式错误", "表格第5列列名和模版不匹配，列明必须是“组织机构”！"));
}
if (errorList.isEmpty() == false) {
return new ResultInfo(1, "", errorList);
}
//3.判断必填数据是否为空
int indexNum = 0;
for (ArrayList<String> list : data.getDataList()) {
indexNum++;
if (list.get(0) == null || list.get(0).isEmpty() || list.get(1) == null || list.get(1).isEmpty()) {
errorList.add(new KeyValueVo("数据错误", "第" + indexNum + "列，数据不能为空;"));
continue;
}
}
if (errorList.isEmpty() == false) {
return new ResultInfo(1, "", errorList);
}
//4.判断邮箱和手机号不能重复
if (data.getDataList().stream().map(d -> d.get(0)).distinct().count() < data.getDataList().size()) {
Map<String, Long> groupList = data.getDataList().stream().map(d -> d.get(0))
.collect(Collectors.groupingBy(Function.identity(), Collectors.counting()));
List<String> tempList = groupList.entrySet().stream().filter(d -> d.getValue() > 1)
.map(d -> d.getKey()).distinct().collect(Collectors.toList());
errorList.add(new KeyValueVo("数据格式错误", "用户邮箱不能重复:" + tempList));
}
if (data.getDataList().stream().filter(d -> d.get(3) != null && d.get(3).isEmpty() == false).map(d -> d.get(3)).distinct().count()
< data.getDataList().stream().filter(d -> d.get(3) != null && d.get(3).isEmpty() == false).count()) {
Map<String, Long> groupList = data.getDataList().stream()
.filter(d -> d.get(3) != null && d.get(3).isEmpty() == false).map(d -> d.get(3))
.collect(Collectors.groupingBy(Function.identity(), Collectors.counting()));
List<String> tempList = groupList.entrySet().stream().filter(d -> d.getValue() > 1)
.map(d -> d.getKey()).distinct().collect(Collectors.toList());
errorList.add(new KeyValueVo("数据格式错误", "用户手机号不能重复:" + tempList));
}
if (errorList.isEmpty() == false) {
return new ResultInfo(1, "", errorList);
}
//5.数据库校验：如判断邮箱和手机号是否存在
List<User> userList = userDao.getList();
List<String> importEmailList = data.getDataList().stream().map(d -> d.get(0)).collect(Collectors.toList());
if (userList.stream().anyMatch(d -> importEmailList.contains(d.getEmail()))) {
List<String> hasEmailList = userList.stream().filter(d -> importEmailList.contains(d.getEmail()))
.map(d -> d.getEmail()).distinct().collect(Collectors.toList());
errorList.add(new KeyValueVo("数据格式错误", "用户邮箱已存在:" + hasEmailList));
}
if (errorList.isEmpty() == false) {
return new ResultInfo(1, "", errorList);
}
// 6.数据持久化
List<User> userList1=new ArrayList<>();
data.getDataList().forEach(d -> {
User user = new User(d.get(2), d.get(0), d.get(1), d.get(3), UserType.User, false
, null, null);
userList1.add(user);
});
userDao.createList(userList1);
return ResultInfo.Success();
} catch (Exception e) {
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
throw new Exception("导入失败：" + e.getMessage());
}
}6.service层导出操作
@Override
public ResultInfo exportData(HttpServletRequest request, HttpServletResponse response) throws Exception {
List<User> userList = userDao.getList(null, null, null);
ArrayList<String> titleList = new ArrayList<String>();
ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
titleList.add("邮箱");
titleList.add("密码");
titleList.add("用户名");
titleList.add("手机号");
userList.forEach(d -> {
ArrayList<String> tempList = new ArrayList<>();
tempList.add(d.getEmail());
tempList.add("");
tempList.add(d.getName());
tempList.add(d.getPhone());
dataList.add(tempList);
});
ExcelOutPut excelOutPut = new ExcelOutPut(titleList, dataList);
boolean b = PoiHelper.createExcel(response, excelOutPut);
System.out.println("成功！");
return null;
}
注：方法通用返回类封装：
@Data
@ApiModel("返回模型")
public class ResultInfo<T> {
@ApiModelProperty("错误码")
private int code;
@ApiModelProperty("提示信息")
private String msg;
@ApiModelProperty("返回值")
private T result;
}
扫一扫
4889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
