根据上传的日志文件,删除指定的行
一个高端的程序员不是来搞office 的是用来写程序的,就上周,公司安排的任务,不知道谁的程序写成这样,出现那么多bug,搞的我们小组10个人都在搞excel 回家就直接写了一个工具,和朋友一起写的哈哈哈
需求:
一个日志文件,一个excel ;日志文件里面有很多需要删除的行数
就想知道你看到这个要删除的这么多是什么心情。
写了一个小小的工具呢,仅供参考
配置文件 :config.properties
#起始的excel文件名数字比如:luoyang_20220804_$.xlsx 就是示例文件名中的$所在位置的数字 文件名:luoyang_20220804_3.xlsx 则为3
start=1
#结束的excl文件名数字 具体参考起始的
end=3
#日志文件存放的磁盘路径 路径请用\\分割 如:D:\\excel\\log 错误示范:D:\excel\log 快捷方式:先复制好路径然后全局将\₩��换为\\
logPath=D:\\excel\\log
#日志文件全称 记住带后缀!
logName=luoyang.log
#需要被处理的excel的文件路径同样需要\\分割
sourceFilePath=D:\\excel\\xlsx
#需要被处理的excel文件前缀比如:luoyang_20220804_$.xlsx 则取$前面的文字 示例:luoyang_20220804_3.xlsx 取:luoyang_20220804_
preFileName=luoyang_20220804_
#文件的后缀名,不用加点(.) 比如 luoyang_20220804_3.xlsx 就是xlsx
fileFormat=xlsx
#处理后的文件存放路径,同样需要\\分割
resFilePath=D:\\excel\\res
ExcelDelete.class
package com.ling.tool.excel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.*;
import java.util.*;
/**
* @author dyl
* @title: ExcelDelete
* @projectName ling-tool
* @description: 根据上传的日志文件,删除指定的行
* @date 2022/8/5 20:47
*/
public class ExcelDelete {
private static int start = 0;
private static int end = 0;
private static String logPath = null;
private static String logName = null;
private static String sourceFilePath = null;
private static String preFileName = null;
private static String fileFormat = null;
private static String resFilePath = null;
public static void main(String[] args) throws IOException {
//加载配置
getConfig();
//Log文件路径
// String textPath = "D:\\excel\\log\\luoyang.log";
String logFullPath = logPath + "\\" + logName;
Map<String, Set<Integer>> delMap = loadTextErrorContent(logFullPath, start, end + 1);
//EXCEL文件路径
// String basePath = "D:\\excel\\xlsx\\luoyang_20220804_%d.xlsx";
String basePath = sourceFilePath + "\\" + preFileName + "%d." + fileFormat;
//目标文件存放路径(删除后)
// String baseResPath = "D:\\excel\\res\\luoyang_20220804_%d.xlsx";
String baseResPath = resFilePath + "\\" + preFileName + "%d." + fileFormat;
for (int i = start; i <= end; i++) {
String filePath = String.format(basePath, i);
String fileName = filePath.substring(filePath.indexOf(preFileName));
Workbook excel = loadxlsx(filePath);
Set<Integer> delNums = delMap.get(fileName);
System.out.println(fileName);
if (delNums != null) delExcelByRowNum(excel.getSheetAt(0), delNums);
writeExcel(excel, String.format(baseResPath, i));
}
}
//加载配置文件
public static void getConfig() throws IOException {
String jarPath = ExcelDelete.class.getProtectionDomain().getCodeSource().getLocation().getPath();
int index = jarPath.lastIndexOf("/");
String configPrePath = jarPath.substring(0, index);
Properties properties = new Properties();
FileReader fileReader = new FileReader(configPrePath + "/config.properties");
properties.load(fileReader);
start = Integer.parseInt(properties.getProperty("start").trim());
end = Integer.parseInt(properties.getProperty("end").trim());
logPath = properties.getProperty("logPath").trim();
logName = properties.getProperty("logName").trim();
sourceFilePath = properties.getProperty("sourceFilePath").trim();
preFileName = properties.getProperty("preFileName").trim();
fileFormat = properties.getProperty("fileFormat").trim();
resFilePath = properties.getProperty("resFilePath").trim();
for (Object key : properties.keySet()) {
System.out.println("加载出的key:" + key + ",加载出的value:" + properties.getProperty(key.toString()));
}
fileReader.close();
}
//生成每个文件所需要删除的行数集
public static Map<String, Set<Integer>> loadTextErrorContent(String textFilePath, int start, int end) throws IOException {
//装载需要删除行的结果的map,key:文件名 value:需要删除的行总和 Set是为了去重(其实不影响)
Map<String, Set<Integer>> map = new HashMap<>();
// String judgeStr = URLEncoder.encode("短信子端口数据库中已存在", "UTF-8");
String lineStr;
FileReader fileReader = new FileReader(textFilePath);
BufferedReader br = new BufferedReader(fileReader);
while ((lineStr = br.readLine()) != null) {
if (LogLineStrIsBlank(lineStr)) {
continue;
}
//取出当前行对应的文件名
int preIndex = lineStr.indexOf("fileName:") + "fileName:".length();
int fixIndex = lineStr.indexOf(".xlsx") + ".xlsx".length();
String fileName = lineStr.substring(preIndex, fixIndex);
//判断是否是目标文件 是否是错误的数据
if (!checkIsFile(fileName, start, end) || !lineStr.contains("短信子端口数据库中已存在")) continue;
//从map中取出之前装载的结果 如果为空创建新的
Set<Integer> delRows = map.computeIfAbsent(fileName, k -> new HashSet<>());
//取出响应的json
int index = lineStr.indexOf("xlsx:") + "xlsx:".length();
String jsonStr = lineStr.substring(index);
//将响应json转为jsonObj
JSONObject jsonObject = JSONObject.parseObject(jsonStr);
//取出错误行数的数组信息
JSONArray rowArr = jsonObject.getJSONObject("data").getJSONObject("errorList").getJSONArray("【子端口】短信子端口数据库中已存在");
/*//获取错误结果行并且将其加入map中
if (fileName.equals("20220804_5_145.xlsx")) {
System.out.println(fileName);
}*/
for (int i = 0; i < rowArr.size(); i++) {
JSONObject rowJson = rowArr.getJSONObject(i);
Integer row = rowJson.getInteger("row");
delRows.add(row);
}
}
return map;
}
//检查是否是目标文件
public static boolean checkIsFile(String fileName, int start, int end) {
String[] split = fileName.split("_");
String[] res = split[split.length - 1].split("\\.");
int num = Integer.parseInt(res[0]);
return num >= start && num < end;
}
//加载excel
public static Workbook loadxlsx(String filePath) throws IOException {
InputStream fis = null;
fis = new FileInputStream(filePath);
Workbook workbook = null;
if (filePath.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fis);
} else if (filePath.endsWith(".xls") || filePath.endsWith(".et")) {
workbook = new HSSFWorkbook(fis);
}
fis.close();
return workbook;
}
//删除excel指定行
public static void delExcelByRowNum(Sheet sheet, Set<Integer> rows) {
if (rows.size() > 500) {
return;
}
ArrayList<Integer> delRows = new ArrayList<>();
for (Integer row : rows) {
row--;
int resRow = getResRow(delRows, row);
delRows.add(row);
Row delRow = sheet.getRow(resRow);
sheet.removeRow(delRow);
if (resRow + 1 <= sheet.getLastRowNum()) {
sheet.shiftRows(resRow + 1, sheet.getLastRowNum(), -1);
}
}
}
//获取需要被删除的行数上移后所在的行数
public static int getResRow(List<Integer> delRows, int row) {
int resRow = row;
for (Integer delRow : delRows) {
if (delRow < row) {
resRow--;
}
}
return resRow;
}
//生成excel
public static void writeExcel(Workbook excel, String path) throws IOException {
FileOutputStream fos = new FileOutputStream(path);
excel.write(fos);
excel.close();
fos.close();
}
//读取的日志行数据是否为空
public static boolean LogLineStrIsBlank(String lineStr) {
if (StringUtils.isBlank(lineStr) || lineStr.equals("\n") || lineStr.equals("\r") || lineStr.equals("\r\n")) {
return true;
}
return false;
}
}
注意一下:依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.10</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.78</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>