Json按指定格式转excel文件（多sheet页）

依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>fr.opensagres.xdocreport</groupId>
            <artifactId>fr.opensagres.poi.xwpf.converter.pdf-gae</artifactId>
            <version>2.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.6.1</version>
        </dependency>

代码

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.file.FileReader;
import com.alibaba.fastjson.JSONObject;
import com.zdb.test.bean.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;


public class EposJson2Excel {
    private static final String BASE_PATH = "xxxxxx";
    private static final String EXCELNAME = "_ABU.xlsx";
    private static Map<String, Integer> localTionMap = new HashMap();
    static{
        localTionMap.put("getTotalDistriCost", 0);
        localTionMap.put("getDeathBenefit", 3);

        localTionMap.put("getYear",0);
        localTionMap.put("getAge", 1);
        localTionMap.put("getGuaranteed", 2);
        localTionMap.put("getNonguaranteedLow", 3);
        localTionMap.put("getTotalLow", 4);
        localTionMap.put("getNonguaranteedHigh", 5);
        localTionMap.put("getTotalHigh", 6);

        localTionMap.put("getYearlyPrmPaidLow", 2);
        localTionMap.put("getDeductionLow", 3);
        localTionMap.put("getTotalSurrenderLow", 4);
        localTionMap.put("getYearlyPrmPaidHigh", 5);
        localTionMap.put("getDeductionHigh", 6);
        localTionMap.put("getTotalSurrenderHigh", 7);

    }

    public static void main(String[] args) throws IOException, InvocationTargetException, IllegalAccessException {
        FileReader fileReader = FileReader.create(new File(BASE_PATH + "ADS.json"));
        BufferedReader bufferedReader = fileReader.getReader();
        int line = 1;
        String context = null;
        String json = "";
        //json内容转化为Map集合通过遍历集合来进行封装
        while ((context = bufferedReader.readLine()) != null) {
            //Context就是读到的json数据
            json += context;
            line++;
        }


       //  EposPDFSuppIllustrationsData data = JSONUtil.toBean(json, EposPDFSuppIllustrationsData.class);
        Object data = JSONObject.parse(json);
        /*Object data = result.get("data");
        data = data == null ? result : data;*/
        EposPDFSuppIllustrationsData eposPDFSuppIllustrationsData = new EposPDFSuppIllustrationsData();
        BeanUtil.copyProperties(data, eposPDFSuppIllustrationsData);
        File filetemp = getFile(BASE_PATH + "temp.xlsx"); // 临时文件
        File file = getFile(BASE_PATH + DateUtil.formatDate(new Date()) + EXCELNAME);
        file.delete();
     
        List<Deduction> deductionList = eposPDFSuppIllustrationsData.getDeductionList();
        createExcel(wk, deductionList, "deductionList", Deduction.class);
        outPutExcel(file, wk);
        filetemp.delete();
    }

    private static void createExcel(Workbook wk, List dataList, String sheetName, Class clazz) throws InvocationTargetException, IllegalAccessException {
        if(dataList==null||dataList.size()<=0){return;}
        Sheet wkSheet = wk.createSheet(sheetName);
        List<Method> getmethods =null;
        if(clazz!=null){
            Method[] declaredMethods = clazz.getDeclaredMethods();
            getmethods = Arrays.asList(declaredMethods).stream().filter(p -> p.getName().startsWith("get")).collect(Collectors.toList());
            Collections.sort(getmethods, Comparator.comparing(p -> p.getName()));
        }
        for (int i = 0; i < dataList.size() + 1; i++) {
            // wkSheet.setColumnWidth(i,252*20+323);
            wkSheet.autoSizeColumn(1,true);
            Row row = wkSheet.createRow(i);
            if(getmethods==null){
                Cell cell = row.createCell(0);
                if(i==0){cell.setCellValue(sheetName);continue;}
                cell.setCellValue(dataList.get(i-1).toString());
                continue;
            }
            for (int j = 0; j < getmethods.size(); j++) {
                int localtion = localTionMap.get(getmethods.get(j).getName());
                Cell cell = row.createCell(localtion);
                if (i == 0) {
                    cell.setCellValue(getmethods.get(j).getName().replace("get", ""));
                } else {
                    Object invoke = getmethods.get(j).invoke(dataList.get(i - 1));
                    invoke = invoke == null ? "" : invoke;
                    cell.setCellValue(invoke.toString());
                }
            }
        }
        System.out.println(sheetName + " is created!");
    }


    /**
     * 创建文件
     *
     * @param fileName
     * @return
     */
    public static File getFile(String fileName) {
        File file = new File(fileName);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return file;
    }

    /**
     * 写出excel文件
     *
     * @param file
     * @param wk
     */
    public static void outPutExcel(File file, Workbook wk) {
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(file);
            wk.write(out);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                if (null != wk)
                    wk.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取excel文件可编辑的工作簿
     *
     * @param file
     * @return
     */
    public static Workbook getWk(File file) {
        Workbook wk = new HSSFWorkbook();
        if (file.exists()) {
            wk.createSheet("temp");
            outPutExcel(file, wk);
            try {
                wk = WorkbookFactory.create(file);
                wk.removeSheetAt(0);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } finally {
            }
        }
        return wk;
    }

}

/**
 * 将FILE_PATH目录下的EXCEl文件的第rowNum+1行全都设置为小写
 */
public class ChangeExcel {
    private static final String FILE_PATH = "E:\\zdb_wk\\test\\";
    private static final int rowNum = 4;

    public static void main(String[] args) {
        List<String> fileNames = FileUtil.listFileNames(FILE_PATH);
        fileNames.forEach(f -> {
            System.out.println(f + "========================================================================" + f);
            Workbook wk = null;
            FileInputStream fin = null;
            try {
                fin = new FileInputStream(FILE_PATH + f);
                wk = WorkbookFactory.create(fin);
                for (Sheet sheet : wk) {
                    for (Row row : sheet) {
                        if (row.getRowNum() == rowNum) {
                            for (int i = 0; i < row.getLastCellNum(); i++) {
                                Cell cell = row.getCell(i);
                                if (null != cell) {
                                    String s = cell.getStringCellValue().toLowerCase();
                                    cell.setCellValue(s);
                                } else {
                                    System.out.println(f + "--  " + sheet.getSheetName() + "存在空单元格" + "index = " + i);
                                }
                            }
                            continue;
                        }
                    }
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (fin != null)
                        fin.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            FileOutputStream out = null;
            try {
                out = new FileOutputStream(FILE_PATH + f);
                wk.write(out);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (out != null)
                        out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });
    }
}