ExcelUtils

package com.charles.mvctest.utils;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;
import java.util.Map.Entry;


public class RegexMatches {

    private static final String DEFAULT_SHEET_NAME = "2021-12-14";
    private static  int Rowindex =1;

    public static void main(String args[]) {
        String logPath = "D:\\aia\\"; //要读取的log文件存放的目录
        String fileName = "2021-12-14"; // 文件名
        List<String> list = new ArrayList<>();//log目录下的log文件集合
        readLog(list, logPath); //读取log文件

        Map<String, Integer> indexMap = new HashMap<>();
        indexMap.put("trace_id", 0);
        indexMap.put("process_type", 1);
        indexMap.put("mode", 2);
        indexMap.put("user_id", 3);
        indexMap.put("bo_name", 4);
        indexMap.put("policyNo", 5);
        indexMap.put("requestNo", 6);
        indexMap.put("1st_cloud_re", 7);
        indexMap.put("pre_1st_re", 8);
        indexMap.put("1st_perm_re", 9);
        indexMap.put("il_cost", 10);
        indexMap.put("post_1st_re", 11);
        indexMap.put("total cost", 12);
        indexMap.put("db_upd_cost", 13);
        indexMap.put("post_il_cost", 14);

        CreateExcelSample(fileName, indexMap);
        writeExcel(fileName, indexMap,list);


    }


    public static void readLog(List<String> list, String logPath) {
// 给定目录的绝对路径logPath，获取该目录下的所有文件,但不包括其子目录的下的文件
        ArrayList<String> logPathList = new ArrayList<>();
        File file = new File(logPath);
        File[] listFiles = file.listFiles(); //File 类型的数组，返回的是logPath中的文件和目录
        for (int i = 0; i < listFiles.length; i++) {
            if (listFiles[i].isFile()) { //如果是文件，则将该路径添加到logPathList
                logPathList.add(listFiles[i].toString());
            }
        }

// 读取logPathList中的每个log文件
        try {
            for (int i = 0; i < logPathList.size(); i++) {
                FileReader fileReader = new FileReader(logPathList.get(i));
                BufferedReader br = new BufferedReader(fileReader);
                String line = "";
                while ((line = br.readLine()) != null) {
                    if (line.contains("###Request Completed")) {
                        list.add(line);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static JSONObject logProcess(String str) {
        str = str.substring(1);
        String newStr = str.replace("###Request Completed--->", "[");
        String[] temp = newStr.split("\\]\\[");

// 用来存储每一条处理完的log

        JSONObject total = new JSONObject();

        total.put("trace_id", temp[3]);
        total.put("process_type", temp[7]);

        String temp11 = temp[11].substring(1, temp[11].length() - 1);
        JSONObject jsonObject = JSON.parseObject(temp11);
        for (Entry<String, Object> entry : jsonObject.entrySet()) {
            total.put(entry.getKey(), entry.getValue());
        }

        String[] temp12 = temp[12].replace(" ms", "").split(",");
/* HashMap<String, Integer> mapUtils = new HashMapUtils<>();
for (String s : temp12) {
String[] newTemp12 = s.split(":");
mapUtils.put(newTemp12[0], Integer.valueOf(newTemp12[1]));
}*/

        HashMap<String, Object> map = new HashMap<>();
        for (String s : temp12) {
            String[] split = s.split(":");
//当map集合中没有这个key时，value默认为0
            map.put(split[0], ((Integer) map.getOrDefault(split[0], 0) + Integer.parseInt(split[1])));
        }
        for (Entry<String, Object> entry : map.entrySet()) {
            total.put(entry.getKey(), entry.getValue());
        }


        return total;
    }


//    public static void writeTxt(String oneLog, String fileName) {
//        String writePath = "C:\\Users\\E114270\\Documents\\NewLog\\" + fileName + ".txt";
//        File newLog = new File(writePath);
//
//        try {
//            if (!newLog.exists()) {
//                newLog.createNewFile();
//            }
//
 这里为每次写入时不覆盖，则多次执行时会叠加在后面
//            FileWriter successWriter = new FileWriter(newLog, true);
//            BufferedWriter successBufferedWriter = new BufferedWriter(successWriter);
//
//            successBufferedWriter.write(oneLog);
//            successBufferedWriter.flush();
//            successBufferedWriter.newLine();
//            successBufferedWriter.close();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//    }
public static void CreateExcelSample( String fileName, Map<String, Integer> indexMap){
    String noMatchExcelFilePath = "D:\\aia\\NewLog\\" + fileName + ".xlsx";
    File noMatchExcel = new File(noMatchExcelFilePath);

    try {
        if (!noMatchExcel.exists()) {
            noMatchExcel.createNewFile();
        }

        FileOutputStream fileOutputStream = new FileOutputStream(noMatchExcel);
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(fileName);
        createExcelTitle(wb, sheet);
       // outputExcel(sheet, object, indexMap);
//outputExcel(sheet,maps);
        wb.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}


public static void writeExcel(  String fileName, Map<String, Integer> indexMap, List<String> list) {
    String noMatchExcelFilePath = "D:\\aia\\NewLog\\" + fileName + ".xlsx";
    File noMatchExcel = new File(noMatchExcelFilePath);

    try {
        if (!noMatchExcel.exists()) {
            noMatchExcel.createNewFile();

        }
        FileInputStream in = null;
        //FileOutputStream out = null;

        for (int i = 0; i < list.size(); i++) {
            in = new FileInputStream(noMatchExcel);
            XSSFWorkbook wb = new XSSFWorkbook(in);

            // XSSFSheet sheet = wb.createSheet(fileName);
            //"2021-12-14"
            XSSFSheet sheet = wb.getSheet(DEFAULT_SHEET_NAME);
            //XSSFSheet sheet2 = wb.getSheetAt(0);
            FileOutputStream fileOutputStream = new FileOutputStream(noMatchExcel);
            String oneLog = list.get(i); //一条log
            JSONObject object = new JSONObject(logProcess(oneLog)); //处理读取到的每一条log
            createExcelTitle(wb, sheet);
            outputExcel(sheet, object, indexMap);
//outputExcel(sheet,maps)
            //XSSFRow s2 = sheet.createRow(Rowindex++);
            //createCell(s2, 3, "3");
            wb.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            in.close();
            //out.close();
        }

    } catch (IOException e) {
        e.printStackTrace();
    }

}

//
//    public static void writeExcel(JSONObject object, String fileName, Map<String, Integer> indexMap, List<String> list) {
//        String noMatchExcelFilePath = "D:\\aia\\NewLog\\" + fileName + ".xlsx";
//        File noMatchExcel = new File(noMatchExcelFilePath);
//
//        try {
//            if (!noMatchExcel.exists()) {
//                noMatchExcel.createNewFile();
//            }
//            FileInputStream inputStream = new FileInputStream(noMatchExcel);
//            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
//            XSSFSheet sheet = wb.createSheet(fileName);
//            createExcelTitle(wb, sheet);
//
//            outputExcel(sheet, object, indexMap);
//
//            FileOutputStream outputStream = new FileOutputStream("D:\\aia\\NewLog\\" + fileName + ".xlsx");
//            wb.write(outputStream);
//            outputStream.flush();
//            inputStream.close();
//            outputStream.close();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//    }
//

    public static void createExcelTitle(XSSFWorkbook wb, XSSFSheet sheet) {
        XSSFRow firstRow = sheet.createRow(0);
        createCell(wb, firstRow, 0, "trace_id");
        createCell(wb, firstRow, 1, "process_type");
        createCell(wb, firstRow, 2, "mode");
        createCell(wb, firstRow, 3, "user_id");
        createCell(wb, firstRow, 4, "bo_name");
        createCell(wb, firstRow, 5, "policyNo");
        createCell(wb, firstRow, 6, "requestNo");
        createCell(wb, firstRow, 7, "1st_cloud_re");
        createCell(wb, firstRow, 8, "pre_1st_re");
        createCell(wb, firstRow, 9, "1st_perm_re");
        createCell(wb, firstRow, 10, "il_cost");
        createCell(wb, firstRow, 11, "post_1st_re");
        createCell(wb, firstRow, 12, "total_cost");
        createCell(wb, firstRow, 13, "db_upd_cost");
        createCell(wb, firstRow, 14, "post_il_cost");

    }

    private static void createCell(XSSFWorkbook wb, XSSFRow row, int col, String val) {
        XSSFCell cell = row.createCell(col);
        cell.setCellValue(val);
    }


    private static void outputExcel(XSSFSheet sheet, JSONObject object, Map<String, Integer> indexMap) {

            XSSFRow row = sheet.createRow(Rowindex++);
            System.out.println("row "+row);
            for (Entry<String, Object> entry : object.entrySet()) {
                Integer index = indexMap.get(entry.getKey());
                if (index != null) {
                    Object value = entry.getValue();
                    createCell(row, index, value);
                } else {

                }
            }

    }



    private static void createCell(XSSFRow row, Integer index, Object value) {
        XSSFCell cell = row.createCell(index);
        cell.setCellValue(String.valueOf(value));
    }

/*public static void writer(String path, String fileName, String fileType, List<String> list, String titleRow[]) throws IOException {
Workbook wb = null;
String excelPath = "C:\\Users\\E114270\\Documents\\NewLog\\" + fileName + "." + fileType;
File file = new File(excelPath);
Sheet sheet = null;
//创建工作文档对象
if (!file.exists()) {
if (fileType.equals("xls")) {
wb = new HSSFWorkbook();

} else if (fileType.equals("xlsx")) {

wb = new XSSFWorkbook();
}
//创建sheet对象
sheet = (Sheet) wb.createSheet("sheet1");
OutputStream outputStream = new FileOutputStream(excelPath);
wb.write(outputStream);
outputStream.flush();
outputStream.close();

} else {
if (fileType.equals("xls")) {
wb = new HSSFWorkbook();

} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook();

}
}
//创建sheet对象
if (sheet == null) {
sheet = (Sheet) wb.createSheet("sheet1");
}

//添加表头
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
row.setHeight((short) 540);
cell.setCellValue("被保险人员清单"); //创建第一行

CellStyle style = wb.createCellStyle(); // 样式对象
// 设置单元格的背景颜色为淡蓝色
//style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
style.setWrapText(true);// 指定当单元格内容显示不下时自动换行

cell.setCellStyle(style); // 样式，居中

Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 280);
style.setFont(font);
// 单元格合并
// 四个参数分别是：起始行，起始列，结束行，结束列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
sheet.autoSizeColumn(5200);

row = sheet.createRow(1); //创建第二行
for (int i = 0; i < titleRow.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
cell.setCellStyle(style); // 样式，居中
sheet.setColumnWidth(i, 20 * 256);
}
row.setHeight((short) 540);

//循环写入行数据
for (int i = 0; i < list.size(); i++) {
row = (Row) sheet.createRow(i + 2);
row.setHeight((short) 500);
// row.createCell(0).setCellValue(( list.get(i)).getInsuraceUser());

}

//创建文件流
OutputStream stream = new FileOutputStream(excelPath);
//写入数据
wb.write(stream);
//关闭文件流
stream.close();
}*/

}