JAVA导出EXCEL
1.MAVEN依赖
<!--导出导入excel-->
<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>
2.代码实现
@RequestMapping
public Object export(QdUserBean qdUserBean) {
Assert.notNull(qdUserBean.getCodes(), "用户codes不能为空");
List<QdUserBean> qdUserBeans = qdUserService.findAll(QdUserBean.builder().codes(qdUserBean.getCodes()).build());
Workbook workbook = new XSSFWorkbook();
if (CollectionUtils.isNotEmpty(qdUserBeans)) {
excelUser(qdUserBeans, workbook);
}
String excelUrl = null;
try {
String name = UUID.randomUUID().toString().replace("-", "") + ".xlsx";
String filePath = name;
logger.info("文件名:" + name);
FileOutputStream outputStream = new FileOutputStream(filePath);
workbook.write(outputStream);
outputStream.close();
File inputFile = new File(filePath);
FileInputStream inputStream = new FileInputStream(inputFile);
byte[] fileBytes = IOUtils.toByteArray(inputStream);
long fileSize = inputFile.length();
inputStream.close();
NasFileBean nasFileBean = new NasFileBean();
nasFileBean.setData(fileBytes);
nasFileBean.setSize(fileSize);
nasFileBean.setType("xlsx");
nasFileBean.setName(name);
nasFileBean.setCategoryFlag("COMMON");
nasFileBean = this.fileRecordService.uploadFile(nasFileBean);
workbook.close();
if (nasFileBean != null) {
excelUrl = fileHost + nasFileBean.getCode();
} else {
return ResponseBean.serverError("操作失败!");
}
} catch (IOException e) {
e.printStackTrace();
return ResponseBean.serverError("文件处理失败!");
} catch (Exception e) {
e.printStackTrace();
return ResponseBean.serverError(e.getMessage());
}
return ResponseBean.success("操作成功!").addData("url", excelUrl);
}
public static void excelUser(List<QdUserBean> qdUserBeans, Workbook workbook) {
Sheet sheet = workbook.createSheet("用户信息");
Row headerRow = sheet.createRow(0);
int columnIndex = 0;
List<String> head = new ArrayList<>();
head.add("ID");
head.add("昵称");
head.add("头像");
head.add("手机号码");
head.add("最近登录时间");
head.add("登录IP");
head.add("所在城市");
head.add("注册时间");
head.add("用户状态");
for (String name : head) {
int columnWidth = 5000;
Cell cell = headerRow.createCell(columnIndex);
cell.setCellValue(name);
sheet.setColumnWidth(columnIndex, columnWidth);
columnIndex++;
}
int rowIndex = 1;
for (QdUserBean qdUserBean : qdUserBeans) {
Row dataRow = sheet.createRow(rowIndex);
int totalColumns = headerRow.getLastCellNum();
for (int colIndex = 0; colIndex < totalColumns; colIndex++) {
Cell cell = headerRow.getCell(colIndex);
String columnName = cell.getStringCellValue();
Cell dataCell = dataRow.createCell(colIndex);
try {
Object value = null;
Field field = null;
if (columnName.equals("ID")) {
field = qdUserBean.getClass().getDeclaredField("userId");
}
if (columnName.equals("昵称")) {
field = qdUserBean.getClass().getDeclaredField("nickname");
}
if (columnName.equals("头像")) {
field = qdUserBean.getClass().getDeclaredField("portrait");
}
if (columnName.equals("手机号码")) {
field = qdUserBean.getClass().getDeclaredField("phone");
}
if (columnName.equals("最近登录时间")) {
field = qdUserBean.getClass().getDeclaredField("lastLoginTime");
}
if (columnName.equals("登录IP")) {
field = qdUserBean.getClass().getDeclaredField("ip");
}
if (columnName.equals("所在城市")) {
field = qdUserBean.getClass().getDeclaredField("address");
}
if (columnName.equals("注册时间")) {
field = qdUserBean.getClass().getDeclaredField("createTime");
}
if (columnName.equals("用户状态")) {
field = qdUserBean.getClass().getDeclaredField("status");
}
field.setAccessible(true);
value = field.get(qdUserBean);
if (value != null) {
if (value instanceof String) {
if (value.equals("normal")) {
value = "正常";
}
if (value.equals("freeze")) {
value = "冻结";
}
dataCell.setCellValue((String) value);
} else if (value instanceof Number) {
dataCell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof Date) {
dataCell.setCellValue((Date) value);
}
}
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
}
rowIndex++;
}
}