晴风

做了一个需求,我已经从数据库里跑出来了需要的数据放到excel里了,现在又要在区县的基础上细分到镇与街道,不想手动分了,写了个java程序来跑,毕竟不止是一个区县呢...
之前写的Excel工具类也正好派上用场.

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 根据户籍地址分镇,街道
 * @author 晴风
 */
public class ExcelFenSheet {
    //读文件路径
    public static final String wbName = "C:\\Users\\Clearwind\\Desktop\\xxxxx\\桐城市\\xxxxxxx.xlsx";
    public static final String dataSheetName = "zjhmcx_temp3";
    //写文件路径
    public static final String outputPath = "C:\\Users\\Clearwind\\Desktop\\xxxxx\\桐城市";
    //所有镇,街道
    public static final String[] strings = {
            "鲟鱼",
            "孔城",
            "范岗",
            "青草",
            "金神",
            "唐湾",
            "双港",
            "嬉子湖",
            "新渡",
            "大关",
            "吕亭",
            "黄甲",
            "文昌",
            "龙眠",
            "龙腾"
    };

    public static void main(String[] args) throws Exception{
        //初始化输出文件
        String outputFile = outputPath + "\\" + initOutputFile();

        Workbook wb = ExcelUtil.getWorkBook(wbName);
        Workbook wbNew = ExcelUtil.getWorkBook(outputFile);
        //取sheet
        Sheet st = wb.getSheet(dataSheetName);
        //sheet内数据行数
        int rowNumbs = st.getPhysicalNumberOfRows();

        //遍历sheet
        for (int i = 1; i < rowNumbs; i++) {
            //初始化hjdz
            String hjdz = "";
            //取第i行数据
            List<String> line = ExcelUtil.getOneRowToList(st, i);
            System.out.println("正在处理第" + i + "行...");
            //简单处理hjdz可能为空的问题
            if (line.size() < 3){
                hjdz = "";
            } else {
                hjdz = line.get(2);
            }

            //分值写入
            boolean isContain = false;
            for (String str : strings){
                //找到匹配的值
                if (hjdz.contains(str)) {
                    isContain = true;
                    //写数据到对应的sheet
                    writeSheet(wbNew,line,str);
                }
            }
            //均不匹配,写到other sheet
            if (!isContain) {
                //System.out.println("什么也没找到...");
                writeSheet(wbNew,line,"other");
            }
        }

        //保存更改
        try {
            wbNew.write(new FileOutputStream(outputFile));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void writeSheet(Workbook wb,List<String> data,String sheetName) {
        Sheet sheet = wb.getSheet(sheetName);
        //取得最后一行索引
        int lastRowNum = sheet.getLastRowNum();
        //加表头
        if (lastRowNum == 0) {
            ArrayList<String> head = new ArrayList<>();
            head.add("zjhm");
            head.add("xm");
            head.add("hjdz");
            ExcelUtil.writeALine(sheet,head,0);
        }
        //写入一行
        ExcelUtil.writeALine(sheet,data,++lastRowNum);
        System.out.println("writeSheetName:" + sheetName + ", rowIndex:" + lastRowNum);
    }

    public static String initOutputFile() throws Exception {
        //时间戳文件名
        String fileName = "out_"+System.currentTimeMillis()+".xlsx";
        File fileDir = new File(outputPath);
        if (fileDir.exists()) {
            //创建Workbook对象
            Workbook wb = new XSSFWorkbook();
            //遍历sheetName并创建
            for (String sheetName : strings) {
                wb.createSheet(sheetName);
            }
            //用于保存未匹配到的记录
            wb.createSheet("other");
            //保存文件
            wb.write(new FileOutputStream(outputPath + "\\" + fileName));
        } else {
            throw new RuntimeException("输出文件夹不存在...");
        }

        return  fileName;
    }
}