本地json文件批量转excel文件多对多生成

 String dateFormat = "yyyy-MM-dd";
        LocalDate localDateStart = Jdk8DateFormatter.strToLocalDate("2022-10-01", dateFormat);
        LocalDate localDateEnd = Jdk8DateFormatter.strToLocalDate(new SimpleDateFormat().format(new Date()), dateFormat);
        int diff = localDateEnd.getDayOfYear() - localDateStart.getDayOfYear();//总天数
        String time = localDateStart.format(DateTimeFormatter.ofPattern(dateFormat));
        String FirstDay = time.replaceAll("-", "");
        //第一天
        JSONToExcelUtil.toExcelByLocalJSONFile(ExcelConstant.resourcePath+FirstDay+".json",ExcelConstant.savePath,FirstDay);
        LocalDate temp = localDateStart;
        for (int i = 0; i < diff; i++) {
            temp = temp.plusDays(1);
            String format = temp.format(DateTimeFormatter.ofPattern(dateFormat));
            String NextDay = format.replaceAll("-", "");
            //第二天
            JSONToExcelUtil.toExcelByLocalJSONFile(ExcelConstant.resourcePath+NextDay+".json",ExcelConstant.savePath,NextDay);
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

package com.qiyunsoft.meteo.util;

import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.Date;

/**
 * 参考jdk8 doc https://docs.oracle.com/javase/tutorial/datetime/iso/period.html
 * stackoverflow https://stackoverflow.com/questions/25776787/java-simpledateformat-format-issue-with-yyyy/25777559#25777559
 *
 * @version 0.1
 * @auth
 * @time
 * @since 0.1
 */
public class Jdk8DateFormatter {

    public static String NORMAL_FORMATTER = "yyyy-MM-dd HH:mm:ss";

    /**
     * 标准 yyyy-MM-dd HH:mm:ss
     * @param date
     * @return
     */
    public static String dateFormatter(Date date){
        return dateFormatter(date,NORMAL_FORMATTER);
    }

    public static String dateFormatter(Date date,String formatter){
        return dateToLocalDateTime(date).format(DateTimeFormatter.ofPattern(formatter));
    }

    /**
     * LocalDate转date
     *
     * @param localDate
     * @return
     */
    public static Date localDateToDate(LocalDate localDate){
        ZoneId zoneId = ZoneId.systemDefault();
        Date result = localDateToDate(localDate,zoneId);
        return result;
    }

    /**
     * LocalDate转date
     *
     * @param localDate
     * @param zoneId 时区
     * @return
     */
    public static Date localDateToDate(LocalDate localDate, ZoneId zoneId){
        Date result = Date.from(localDate.atStartOfDay().atZone(zoneId).toInstant());
        return result;
    }

    /**
     * LocalDateTime转Date
     *
     * @param localDateTime
     * @return
     */
    public static Date localDateTimeToDate(LocalDateTime localDateTime){
        ZoneId zoneId = ZoneId.systemDefault();
        Date result = localDateTimeToDate(localDateTime,zoneId);
        return result;
    }

    /**
     * LocalDateTime转Date
     *
     * @param localDateTime
     * @param zoneId 时区
     * @return
     */
    public static Date localDateTimeToDate(LocalDateTime localDateTime,ZoneId zoneId){
        Date result = Date.from(localDateTime.atZone(zoneId).toInstant());
        return result;
    }

    /**
     * Date转LocalDate
     *
     * @param date
     * @return
     */
    public static LocalDate dateToLocalDate(Date date){
        ZoneId zoneId = ZoneId.systemDefault();
        LocalDateTime localDateTime = dateToLocalDateTime(date,zoneId);
        return localDateTime.toLocalDate();
    }

    /**
     * Date转LocalDate
     *
     * @param date
     * @param zoneId 时区
     * @return
     */
    public static LocalDate dateToLocalDate(Date date,ZoneId zoneId){
        LocalDateTime localDateTime = dateToLocalDateTime(date,zoneId);
        return localDateTime.toLocalDate();
    }

    /**
     * Date转LocalDateTime
     *
     * @param date
     * @return
     */
    public static LocalDateTime dateToLocalDateTime(Date date){
        ZoneId zoneId = ZoneId.systemDefault();
        Instant instant = date.toInstant();
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant,zoneId);
        return localDateTime;
    }

    /**
     * Date转LocalDateTime
     *
     * @param date
     * @return
     */
    public static LocalDateTime dateToLocalDateTime(Date date, ZoneId zoneId){
        Instant instant = date.toInstant();
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant,zoneId);
        return localDateTime;
    }

    /**
     * String转LocalDate
     *
     * @param date
     * @return
     */
    public static LocalDate strToLocalDate(String date){
        DateTimeFormatter formatter1 = DateTimeFormatter.BASIC_ISO_DATE;
        LocalDate localDate = LocalDate.parse(date,formatter1);
        return localDate;
    }

    /**
     * String转LocalDateTime
     *
     * @param date
     * @return
     */
    public static LocalDateTime strToLocalDateTime(String date){
        DateTimeFormatter formatter1 = DateTimeFormatter.ISO_DATE_TIME;
        LocalDateTime localDateTime = LocalDateTime.parse(date,formatter1);
        return localDateTime;
    }

    /**
     * String转LocalDate
     *
     * @param date
     * @param formatter
     * @return
     */
    public static LocalDate strToLocalDate(String date, String formatter){
        DateTimeFormatter formatter1 = DateTimeFormatter.ofPattern(formatter);
        LocalDate localDate = LocalDate.parse(date,formatter1);
        return localDate;
    }

    /**
     * String转LocalDateTime
     *
     * @param date
     * @param formatter
     * @return
     */
    public static LocalDateTime strToLocalDateTime(String date, String formatter){
        DateTimeFormatter formatter1 = DateTimeFormatter.ofPattern(formatter);
        LocalDateTime localDateTime = LocalDateTime.parse(date,formatter1);
        return localDateTime;
    }

    public static Date strToDate(String date, String formatter){
        DateTimeFormatter formatter1 = DateTimeFormatter.ofPattern(formatter);
        LocalDateTime localDateTime = LocalDateTime.parse(date,formatter1);
        return localDateTimeToDate(localDateTime);
    }

    // -------------------- 两个日期相差的时间 ----------------------------------//

    public static long diffSeconds(LocalDateTime start,LocalDateTime end){
        return ChronoUnit.SECONDS.between(start,end);
    }

    public static long diffMinutes(LocalDateTime start,LocalDateTime end){
        return ChronoUnit.MINUTES.between(start,end);
    }

    public static long diffHours(LocalDateTime start,LocalDateTime end){
        return ChronoUnit.HOURS.between(start,end);
    }

    public static long diffDays(LocalDateTime start,LocalDateTime end){
        return ChronoUnit.DAYS.between(start,end);
    }

    public static long diffMonths(LocalDateTime start,LocalDateTime end){
        return ChronoUnit.MONTHS.between(start,end);
    }

    public static long diffYears(LocalDateTime start,LocalDateTime end){
        return ChronoUnit.YEARS.between(start,end);
    }

    // -------------------- 日期加上指定时间 ----------------------------------//
    public static Date plusDays(LocalDateTime dateTime,int days){
        return plusDays(dateTime,days,ZoneId.systemDefault());
    }

    public static LocalDateTime plusDays(Date date,int days){
        LocalDateTime dateTime = dateToLocalDateTime(date);
        Date temp = plusDays(dateTime,days,ZoneId.systemDefault());
        return dateToLocalDateTime(temp);
    }

    public static Date plusDays(LocalDateTime dateTime,int days,ZoneId zoneId){
        LocalDateTime temp = dateTime.plusDays(days);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date plusMonths(LocalDateTime dateTime,int months){
        return plusMonths(dateTime,months,ZoneId.systemDefault());
    }

    public static Date plusMonths(LocalDateTime dateTime,long months,ZoneId zoneId){
        LocalDateTime temp = dateTime.plusMonths(months);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date plusYears(LocalDateTime dateTime,long years){
        return plusYears(dateTime,years,ZoneId.systemDefault());
    }

    public static Date plusYears(LocalDateTime dateTime,long years,ZoneId zoneId){
        LocalDateTime temp = dateTime.plusYears(years);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date plusSeconds(LocalDateTime dateTime,long seconds){
        return plusSeconds(dateTime,seconds,ZoneId.systemDefault());
    }

    public static Date plusSeconds(LocalDateTime dateTime,long seconds,ZoneId zoneId){
        LocalDateTime temp = dateTime.plusSeconds(seconds);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date plusMinutes(LocalDateTime dateTime,long minutes){
        return plusMinutes(dateTime,minutes,ZoneId.systemDefault());
    }

    public static Date plusMinutes(Date date,long minutes){
        LocalDateTime dateTime = Jdk8DateFormatter.dateToLocalDateTime(date);
        return plusMinutes(dateTime,minutes,ZoneId.systemDefault());
    }

    public static Date plusMinutes(LocalDateTime dateTime,long minutes,ZoneId zoneId){
        LocalDateTime temp = dateTime.plusMinutes(minutes);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date plusHours(LocalDateTime dateTime,long hours){
        return plusHours(dateTime,hours,ZoneId.systemDefault());
    }

    public static Date plusHours(LocalDateTime dateTime,long hours,ZoneId zoneId){
        LocalDateTime temp = dateTime.plusHours(hours);
        return localDateTimeToDate(temp,zoneId);
    }

    // -------------------- 日期减去指定时间 ----------------------------------//
    public static Date minusSeconds(LocalDateTime dateTime,long seconds){
        return minusSeconds(dateTime,seconds,ZoneId.systemDefault());
    }

    public static Date minusSeconds(LocalDateTime dateTime,long seconds,ZoneId zoneId){
        LocalDateTime temp = dateTime.minusSeconds(seconds);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date minusMinutes(LocalDateTime dateTime,long minutes){
        return minusMinutes(dateTime,minutes,ZoneId.systemDefault());
    }

    public static Date minusMinutes(LocalDateTime dateTime,long minutes,ZoneId zoneId){
        LocalDateTime temp = dateTime.minusMinutes(minutes);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date minusHours(LocalDateTime dateTime,long hours){
        return minusHours(dateTime,hours,ZoneId.systemDefault());
    }

    public static Date minusHours(LocalDateTime dateTime,long hours,ZoneId zoneId){
        LocalDateTime temp = dateTime.minusHours(hours);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date minusDays(Date date,int days){
        LocalDateTime dateTime = dateToLocalDateTime(date);
        return minusDays(dateTime,days);
    }

    public static Date minusDays(LocalDateTime dateTime, long days){
        return minusDays(dateTime,days, ZoneId.systemDefault());
    }

    public static Date minusDays(LocalDateTime dateTime,long days,ZoneId zoneId){
        LocalDateTime temp = dateTime.minusDays(days);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date minusMonths(LocalDateTime dateTime,long months){
        return minusMonths(dateTime,months,ZoneId.systemDefault());
    }

    public static Date minusMonths(LocalDateTime dateTime,long months,ZoneId zoneId){
        LocalDateTime temp = dateTime.minusMonths(months);
        return localDateTimeToDate(temp,zoneId);
    }

    public static Date minusYears(LocalDateTime dateTime,long years){
        return minusYears(dateTime,years,ZoneId.systemDefault());
    }

    public static Date minusYears(LocalDateTime dateTime,long years,ZoneId zoneId){
        LocalDateTime temp = dateTime.minusYears(years);
        return localDateTimeToDate(temp,zoneId);
    }
}

package com.qiyunsoft.meteo.util;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.JSONWriter;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
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.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

/**
 *  uni
 *  2022/07/05~2022/07/06
 *  将 JSON 转化为 Excel的工具类
 */
public class JSONToExcelUtil {

    /**
     * 读取绝对路径下的json文件
     * @param resourcePath json文件的绝对路径
     * @return json文件格式化后的字符串
     */
    public static String readJSONFile(String resourcePath) {
        try{
            // 1. 创建文件流
            File file = new File(resourcePath);
            // 2. 使用 common-lang3工具包, 以 UTF-8 格式读取文件, 转为字符串
            String str = FileUtils.readFileToString(file, "UTF-8");
            JSONObject jsonObject = JSONObject.parseObject(str);
            // 3. 将字符串转为标准的JSON格式的字符串
            return JSONObject.toJSONString(jsonObject, JSONWriter.Feature.WriteMapNullValue);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 创建 Sheet
     * @param layer 当前Sheet所在JSON中的层级
     * @param workbook 工作台 ( excel表格的主体 )
     * @param sheetName 当前页的名称
     * @param jsonArray JSON数组
     */
    public static void createSubSheet(int layer, XSSFWorkbook workbook, String sheetName, JSONArray jsonArray){
        // 创建新的 sheet
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 存储每个字段
        Map<String, Integer> map = new HashMap<>();
        // 统计当前的列
        int cellCount = 0;
        // 创建第一行
        XSSFRow firstRow = sheet.createRow(0);
        // 获取每一项
        for (int row = 1; row <= jsonArray.size(); row++) {
            JSONObject jsonObject = jsonArray.getJSONObject(row - 1);
            // 创建行
            XSSFRow currentRow = sheet.createRow(row);
            if(jsonObject != null){
                // 遍历每个KV
                for (String cellName : jsonObject.keySet()) {
                    // 列不存在时, 则创建列
                    if (!map.containsKey(cellName)) {
                        // 第一行创建列
                        XSSFCell firstRowCell = firstRow.createCell(cellCount);
                        firstRowCell.setCellValue(cellName);
                        map.put(cellName, cellCount++);
                    }
                    // 设置单元格
                    XSSFCell cell = currentRow.createCell(map.get(cellName));
                    // 获取 Value
                    String cellValue = JSON.toJSONString(jsonObject.get(cellName));

                    // 如果V为数组则递归创建sheet
                    if(JSON.isValidArray(cellValue)){
                        String subCellName = sheetName + "-" + cellName;
                        cell.setCellValue(subCellName);
                        createSubSheet(layer + 1, workbook,subCellName, jsonObject.getJSONArray(cellName));
                    }
                    else{
                        cell.setCellValue(jsonObject.getString(cellName));
                    }
                }
            } else{ // Value为一个数组
                JSONArray array = jsonArray.getJSONArray(row - 1);
                // 遍历数组
                if(array != null && array.size() > 0){
                    for (int i = 1; i <= array.size(); i++) {
                        JSONObject obj = array.getJSONObject(i - 1);
                        // 遍历 obj
                        for (String cellName : obj.keySet()) {
                            // 若列不存在则添加
                            if(!map.containsKey(cellName)){
                                XSSFCell cell = firstRow.createCell(cellCount);
                                map.put(cellName, cellCount++);
                                cell.setCellValue(cellName);
                            }
                            // 分情况讨论
                            String cellValue = obj.getString(cellName);
                            XSSFCell cell = currentRow.createCell(map.get(cellName));
                            // 如果值是JSON对象, 则递归创建
                            if(JSON.isValidObject(cellValue)){
                                String subSheetName = sheetName + "-" + cellName;
                                cell.setCellValue(subSheetName);
                                createSubSheet(layer+1, workbook, subSheetName , JSONObject.parseObject(cellValue));
                            } else if(JSON.isValidArray(cellValue)){
                                String subSheetName = sheetName + "-" + cellName;
                                cell.setCellValue(subSheetName);
                                createSubSheet(layer+1, workbook, subSheetName , JSONArray.parseArray(cellValue));
                            } else {
                                cell.setCellValue(cellValue);
                            }
                        }
                    }
                } else {
                    firstRow.createCell(0).setCellValue(sheetName);
                    XSSFCell cell = currentRow.createCell(cellCount);
                    cell.setCellValue(jsonArray.getString(row-1));
                }
            }
        }
    }

    /**
     * 创建 Sheet
     * @param layer 当前Sheet所在JSON中的层级
     * @param workbook 工作台 ( excel表格的主体 )
     * @param sheetName 当前页的名称
     * @param jsonObject JSON对象
     */
    public static void createSubSheet(int layer, XSSFWorkbook workbook, String sheetName, JSONObject jsonObject){
        // 创建新的 sheet
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 存储每个字段
        Map<String, Integer> map = new HashMap<>();
        // 统计当前的列
        int cellCount = 0;
        // 创建第一行
        XSSFRow fistRow = sheet.createRow(0);
        // 记录行数
        int row = 1;
        // 获取每一项
        // 创建行
        XSSFRow currentRow = sheet.createRow(row);
        // 遍历每个KV
        for (String cellName : jsonObject.keySet()) {
            // 列不存在时, 则创建列
            if (!map.containsKey(cellName)) {
                // 第一行创建列
                XSSFCell firstRowCell = fistRow.createCell(cellCount);
                firstRowCell.setCellValue(cellName);
                map.put(cellName, cellCount++);
            }
            // 设置单元格
            XSSFCell cell = currentRow.createCell(map.get(cellName));
            // 获取 Value
            String cellValue = JSON.toJSONString(jsonObject.get(cellName));
            // 如果V为对象则递归创建sheet
            if(JSON.isValidObject(cellValue)){
                String subCellName = "Sheet" + layer + "-" + sheetName + "-" + cellName;
                cell.setCellValue(subCellName);
                createSubSheet(layer + 1, workbook,subCellName, JSON.parseObject(cellValue));

            } else if(JSON.isValidArray(cellValue)){
                String subCellName = "Sheet" + layer + "-" + sheetName + "-" + cellName;
                cell.setCellValue(subCellName);
                createSubSheet(layer + 1, workbook,subCellName, JSON.parseArray(cellValue));
            }
            else{
                cell.setCellValue(jsonObject.getString(cellName));
            }
        }
    }

    /**
     * 将格式化的JSON字符串导出为Excel
     * @param jsonStr 格式化后的JSON字符串
     * @param savePath Excel保存路径
     * @param excelName Excel名称
     */
    public static void toExcelByString(String jsonStr, String savePath, String excelName){
        assert JSON.isValid(jsonStr) : "字符串: " + jsonStr + " 不是标准的JSON字符串";
        toExcelByJSONObject(JSONObject.parseObject(jsonStr),savePath, excelName);
    }

    /**
     * 将普通的Java对象导出为JSON文件
     * @param obj   Java对象
     * @param savePath  Excel保存路径
     * @param excelName Excel名称
     */
    public static void toExcelByObject(Object obj, String savePath, String excelName){
        String jsonStr = JSON.toJSONString(obj, JSONWriter.Feature.WriteMapNullValue);
        JSONObject jsonObject = JSONObject.parseObject(jsonStr);
        toExcelByJSONObject(jsonObject, savePath, excelName);
    }

    /**
     * 将本地的JSON文件导出为 Excel
     * @param resourcePath JSON文件的绝对路径
     * @param savePath  保存的路径
     * @param excelName 保存的Excel名称
     */
    public static void toExcelByLocalJSONFile(String resourcePath, String savePath, String excelName){
        // 1. 获取标准的 JSON 字符串
        String jsonStr = readJSONFile(resourcePath);
        // 验证字符串是否合法
        assert JSON.isValid(jsonStr) : "路径:[" + resourcePath + "] 的json文件不符合标准的JSON格式";
        toExcelByString(jsonStr, savePath, excelName);
    }

    /**
     * 将JSONObject转化导出到 Excel
     * 这里遵循递归导出，当遇到数组时会调用 createSheet创建新的页面。
     * @param jsonObject    JSON对象
     * @param savePath      Excel保存路径
     * @param excelName     Excel名称
     */
    public static void toExcelByJSONObject(JSONObject jsonObject,  String savePath, String excelName){
        try(XSSFWorkbook workbook = new XSSFWorkbook()){
            // 获取当前的Sheet
            XSSFSheet sheet = workbook.createSheet("sheet");
            // 获取第一行
            XSSFRow firstRow = sheet.createRow(0);
            // 记录Key所在的列
            Map<String, Integer> map = new HashMap<>();
            // 记录列数
            int cellCount = 0;
            // 遍历 JSON的key
            XSSFRow currentRow = sheet.createRow(1);
            for (String key : jsonObject.keySet()) {
                // 先处理列
                if(!map.containsKey(key)){  // 当列不存在则添加
                    map.put(key, cellCount);
                    XSSFCell cell = firstRow.createCell(cellCount++);
                    cell.setCellValue(key);
                }
                XSSFCell currentCell = currentRow.createCell(map.get(key));
                String jsonStr = jsonObject.getString(key);
                // 如果 Value为数组 则创建新的 Sheet
                if(JSON.isValidArray(jsonStr)){
                    String subSheetName = "Sheet-" + key;
                    createSubSheet(1, workbook, subSheetName, jsonObject.getJSONArray(key));
                    currentCell.setCellValue(subSheetName);
                } else if(JSON.isValidObject(jsonStr)){ // 如果当前 value 仍然是一个JSON对象
                    String subSheetName = "Sheet-" + key;
                    createSubSheet(1, workbook, subSheetName, jsonObject.getJSONObject(key));
                    currentCell.setCellValue(subSheetName);
                }
                else {
                    // 特殊处理空值
                    if(StringUtils.isEmpty(jsonStr))
                        currentCell.setCellValue("null");
                    else
                        currentCell.setCellValue(jsonStr);
                }
            }
            save(workbook, savePath, excelName);
        } catch (IOException ex) {
            throw new RuntimeException(ex);
        }
    }
    /**
     * 将 Excel对象保存到本地
     * @param workbook Excel对象
     * @param path Excel文件路径
     * @param excelName excel名称
     */
    public static void save(Workbook workbook, String path, String excelName){
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(path +"/" + excelName +".xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            System.out.println("保存完毕. 保存位置为[ " + path + "/" + excelName + " ]");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }



}

posted @ 2022-10-21 14:37 花心大萝卜li 阅读(228) 评论(0) 收藏举报

刷新页面返回顶部

花心大萝卜li

本地json文件批量转excel文件 多对多生成

公告

本地json文件批量转excel文件多对多生成