java poi 写excel到指定目录

  好记性不如烂笔头，仅以此记录

package com.zsn.test;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;

/**
 * @Author sinong.zhang
 * @Date 2020/7/10 15:25
 * @Version 1.0
 * @description: ${description}
 */
public class WriteExcelTest {


    private static String separator = System.getProperty("file.separator");
    private static final String DIR_PATH = "D:\\test";

    private static final String FILE_NAME = "writeNew-";
    private static final String TXTFILE_SUFFIX = ".xls";
    private static final String SOURCE_EXCEL_PATH =  "D:\\AssetTaxPay.xls";
    private static CellStyle style = null;

    public static void main(String[] args) {
        List<Map<String, Object>> listData =bulidList(); //实际项目中从数据库读出来就行，我就手动加了



        writeDataToFile(null,"2020-07-10");
    }

    /**
     * 
     * @return
     */
    private static List<Map<String, Object>> bulidList() {
        List<Map<String, Object>> listData =new ArrayList<>(); //实际项目中从数据库读出来就行，我就手动加了
        Map<String, Object>  map1 =null;
        for (int i = 0; i < 10; i++) {
            map1=new HashMap<>();
            map1.put("UNIT_NAME","A");
            map1.put("P_CLASS","B");
            map1.put("I_CODE","C");
            map1.put("I_NAME","D");
            map1.put("PRFT_TRD_1",i);
            map1.put("PRFT_TRD_2",i);
            map1.put("TAX_TRD_RATE",i);
            map1.put("TAX_TRD_PAY",i);
            map1.put("PRFT_IR_1",i);
            map1.put("PRFT_IR_2",i);
            map1.put("TAX_IR_RATE",i);
            map1.put("TAX_IR_PAY",i);
            map1.put("SUM_TAX",i);
            listData.add(map1);
        }

        return listData;
    }

    /**
     *
     * @param listData 数据
     * @param bsDate 日期
     */
    private static void writeDataToFile(List<Map<String, Object>> listData, String bsDate) {
        String fileName = FILE_NAME + bsDate.replaceAll("-", "") + TXTFILE_SUFFIX;
        String writeExcelPath = DIR_PATH + separator + fileName;

//        需要写入的列，顺序
        List<String> keyList = new ArrayList<>(Arrays.asList("UNIT_NAME", "P_CLASS", "I_CODE", "I_NAME",
                "PRFT_TRD_1", "PRFT_TRD_2", "TAX_TRD_RATE", "TAX_TRD_PAY",
                "PRFT_IR_1", "PRFT_IR_2", "TAX_IR_RATE", "TAX_IR_PAY", "SUM_TAX"));
//        需要写入的列类型，顺序  ，默认double 小数点后两位
        List<String> formatList = new ArrayList<>(Arrays.asList("String", "String", "String", "String",
                "double", "double", "double", "double",
                "double", "double", "double", "double", "double"));

        try {
            writeExcel(SOURCE_EXCEL_PATH, writeExcelPath, listData, keyList, formatList);
        } catch (Exception e) {
            System.out.println("生成数据文件异常"+ e.getMessage()+ e);
        }

    }


    /**
     * 从sourceExcelPath 复制写入到writeExcelPath
     *
     * @param sourceExcelPath 源文件路径
     * @param writeExcelPath  写入路径
     * @param dataList        数据集合
     * @param keyList         key集合，按顺序依次写入
     * @param formatList      格式集合，按顺序依次写入
     */
    public static void writeExcel(String sourceExcelPath, String writeExcelPath, List<Map<String, Object>> dataList, List<String> keyList, List<String> formatList) throws Exception {
        if (sourceExcelPath.matches("^.+\\.(?i)(xls|xlsx)$")) {//判断是否excel文档
            FileInputStream input = null;
            FileOutputStream output = null;
            try {


                input = new FileInputStream(sourceExcelPath);
                boolean is03Excel = sourceExcelPath.matches("^.+\\.(?i)(xlsx)$") ? true : false;
                Workbook workbook = is03Excel ? new XSSFWorkbook(input) : new HSSFWorkbook(input);
                Sheet sheet = workbook.getSheetAt(0);
                style = workbook.createCellStyle();
//            写入
                writeRows(dataList, keyList, sheet, formatList); //todo 现在只写第一页，后续可以增加多页
                output = new FileOutputStream(writeExcelPath);
                workbook.write(output);
                output.flush();
            } finally {
                if (output != null) output.close();
                if (input != null) input.close();
            }

        }
    }

    /**
     * @param dataList   需要写入的数据
     * @param keyList    key集合
     * @param sheet      excel标签页
     * @param formatList 字段类型
     */
    private static void writeRows(List<Map<String, Object>> dataList, List<String> keyList, Sheet sheet, List<String> formatList) {
        int rowNum = sheet.getPhysicalNumberOfRows();
        Cell cell = null;
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + rowNum);
            for (int j = 0; j < keyList.size(); j++) {
                String key = keyList.get(j);
                Object o = dataList.get(i).get(key);
                cell = row.createCell(j);
                if ("String".equals(formatList.get(j))) {
                    cell.setCellValue(o == null ? "" : o.toString());
                } else if ("double".equals(formatList.get(j))) {
                    if (o != null) {
                        cell.setCellValue(Double.parseDouble(o.toString()));
                    }
                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); //默认格式
                }
                style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右对齐
                style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                cell.setCellStyle(style);

            }
        }
    }

}