package com.ruoyi.web.controller.tool;
import com.ruoyi.common.core.redis.RedisCache;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.enumVo.FileNameEnum;
import com.ruoyi.web.core.config.FileConfigProperties;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;
import javax.annotation.Resource;
import java.io.*;
import java.util.Collection;
import java.util.List;
@Component
public class FileDataUtil {
@Resource
FileConfigProperties fileConfigProperties;
@Resource
RedisCache redis;
/**
* 读取Excel文件数据
*
* @param fileNameVo 文件类型
* @return 文件数据
* @throws FileNotFoundException 文件不存在
*/
public List<?> getFileDataList(FileNameEnum fileNameVo) throws FileNotFoundException {
String fileName = fileNameVo.getFileName();
File file = ResourceUtils.getFile(fileConfigProperties.getFilePath() + fileName);
String fileLastTime = String.valueOf((file.lastModified()));
String dataListKey = fileName + "Data";
if (!redis.hasKey(fileName) || !redis.getCacheObject(fileName).equals(fileLastTime)) {
redis.setCacheObject(fileName, fileLastTime);
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
ExcelUtil<?> util = new ExcelUtil<>(fileNameVo.getEntityClass());
List<?> dataList = util.importExcel(fileInputStream);
if (!dataList.isEmpty()) {
redis.setCacheObject(dataListKey, dataList);
}
return dataList;
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
// 处理关闭文件流时可能出现的异常
e.printStackTrace();
}
}
}
} else {
return redis.getCacheObject(dataListKey);
}
}
/**
* 加载数据
*
* @param dataList 数据集
* @param file 文件
*/
public <T> void loadFileData(FileNameEnum file, List<T> dataList) throws Exception {
try {
dataList.clear();
dataList.addAll((Collection<? extends T>) getFileDataList(file));
} catch (Exception e) {
throw new FileNotFoundException("加载文件数据时发生错误: " + e.getMessage());
}
if (dataList.isEmpty()) {
throw new FileNotFoundException("文件数据为空");
}
}
/**
* 写入文件数据
*
* @param fileName 文件名
* @param dataList 数据集
*/
public void writeFileData(String fileName, List<String> dataList) {
String filePath = fileConfigProperties.getFilePath() + fileName;
try (FileInputStream fis = new FileInputStream(filePath);
Workbook workbook = new XSSFWorkbook(fis);
FileOutputStream fos = new FileOutputStream(filePath)
) {
Sheet sheet = workbook.getSheet("Sheet1");
// 定位到最后一行
int lastRowNum = sheet.getLastRowNum();
Row row = sheet.getRow(lastRowNum);
//获取最后一行数据的序号值 并加1
double rowIdDouble = Double.parseDouble(row.getCell(0).toString());
int newRowId = (int) Math.round(rowIdDouble) + 1;
// 创建新的行,并在最后一行的下一行追加数据
Row newRow = sheet.createRow(lastRowNum + 1);
newRow.createCell(0).setCellValue(newRowId);
for (int i = 0; i < dataList.size(); i++) {
newRow.createCell(i + 1).setCellValue(dataList.get(i));
}
// 保存Excel文件
workbook.write(fos);
} catch (IOException e) {
// 处理异常
System.out.println("对文件写入数据发生错误:" + e.getMessage());
}
}
/*
* 更新文件数据
*
* @param fileName 文件名
* @param rowIndex 行索引(索引从0开始) 标题也占一列 也就是 id是多少就是更新第几行
* @param columnIndex 第几列
* @param new
*/
public void updateFileData(String fileName, int rowIndex, int columnIndex, String newValue) {
String filePath = fileConfigProperties.getFilePath() + fileName;
try (FileInputStream fis = new FileInputStream(filePath);
Workbook workbook = new XSSFWorkbook(fis);
FileOutputStream fos = new FileOutputStream(filePath)
) {
Sheet sheet = workbook.getSheet("Sheet1");
Row row = sheet.getRow(rowIndex);//要更新的行的索引,(索引从0开始)
columnIndex = columnIndex - 1;//(索引从0开始)
if (row != null) {
Cell cell = row.getCell(columnIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell == null) {
cell = row.createCell(columnIndex);
}
cell.setCellValue(newValue); // 设置新的单元格值
}
// 保存Excel文件
workbook.write(fos);
} catch (IOException e) {
// 处理异常
System.out.println("对文件数据进行更新发生错误:" + e.getMessage());
}
}
}
java对excel文件里面的数据进行追加或者写入,修改等工具类
最新推荐文章于 2024-08-01 04:44:47 发布