java根据excel某些格子特定内容归类文件（定值单分类工具）

 

 

package com.xxx.controller;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.sun.star.lib.uno.helper.WeakAdapter;
import com.xxx.core.util.FileUtil;
import com.xxx.base.core.util.string.StringUtil;

/**
 * @author 38962
 *
 */
public class DzdClassificationUtil {

    public static void main(String[] args)   {
        System.out.println("+++++++++++++++++++++++start+++++++++++++++++++++++");


        
        String allPath = "C:\\定值单_new\\普通定值单\\1";
        String samePath1 = "C:\\定值单_new\\通过代码分类出来的单\\";
        
        try {
            new DzdClassificationUtil().getListValListCircuit(allPath, samePath1);
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        System.out.println("+++++++++++++++++++++++end+++++++++++++++++++++++");
    }

    /**
     * 
     *
     * @param allPath
     * @param samePath1
     * @throws IOException 
     */
    public void getListValListCircuit(String allPath, String samePath1) throws IOException {

        //按照厂家名称进行分类然后看下哪些可以合并手动合并文件夹
        List<File> fileList = com.ytd.ebos.platform.util.FileUtil.getOnlyFile(allPath);
        
        //创建文件夹
        File fa = new File(samePath1 + File.separator + "执行移动后_报错的文件");
        if (!fa.exists()) {
            fa.mkdir();
        }
        //创建文件夹
        File notExcel = new File(samePath1 + File.separator + "非excel文件");
        if (!notExcel.exists()) {
            notExcel.mkdir();
        }
        
        String name = "";
        String absolutePath = "";
//        for (File file : fileList) {
        int j =0;
        for (int i=0;i<fileList.size();i++) {
            File file = fileList.get(i);
            Workbook wb = null;
            try {
                name = file.getName().trim();
                absolutePath = file.getPath();
                wb = WorkbookFactory.create(file);
                if (StringUtil.endsWith(absolutePath, "xlsx") || StringUtil.endsWith(absolutePath, "xls")) {
                    String changJia = getChangJia(wb);
                    if (changJia != null && !"".equals(changJia)) {
                        File f = new File(samePath1 + File.separator + changJia);
                        if (!f.exists()) {
                            f.mkdir();
                        }
//                        FileUtil.copyFile(absolutePath, f.getPath() + File.separator + name);
                        wb.close();
                        System.gc();
                        moveFile(file,new File(f.getPath() + File.separator + name));
                        j++;
                    } else {
//                        System.err.println("不符合筛选条件的单");
                    }
                  
                } else {
                     wb.close();
                     System.gc();
                     moveFile(file,new File(notExcel.getPath() + File.separator + name));
                }
            } catch (Exception e) {
                e.printStackTrace();
                if (name != "" && absolutePath != null) {
                    if (wb!=null) {
                        wb.close();
                    }
                    System.gc();
                    //moveFile(file,new File(fa.getPath() + File.separator + name));
                }
            } 
        }
        System.out.println("移动的数量="+j);
        System.err.println();
    }


    public void moveFile(File file,File file2) throws IOException {
        System.out.println("移动文件：从路径 " + file.getAbsolutePath() + " 移动到路径 " + file2.getAbsolutePath());
        if (file.isFile()) {
            if (file2.exists()) {
                System.out.println("文件已存在");
            } else {
                file.renameTo(file2);
                System.out.println("移动文件成功");
            }
        }
    }
    
    
//    public static void moveFile(File file) throws IOException {
//
//        String toPath = "E:\\111\\" + file.getName();
//        File file1 = new File(toPath);
//        System.out.println("移动文件：从路径 " + file.getName() + " 移动到路径 " + toPath);
//
//        if (file.isFile()) {
//            File toFile = new File(toPath + "\\" + file.getName());
//            if (toFile.exists()) {
//                System.out.println("文件已存在");
//            } else {
//                file.renameTo(file1);
//                System.out.println("移动文件成功");
//            }
//        }
//    }
    
    
    /**
     * @param src
     * @param dest   moveFile("D:/temp/test.txt", "D:/temp1/test.txt");
     */
    private static void moveFile(String src, String dest ) {
        Path result = null;
        try {
           result = Files.move(Paths.get(src), Paths.get(dest));
        } catch (IOException e) {
           System.out.println("Exception while moving file: " + e.getMessage());
        }
        if(result != null) {
           System.out.println("文件已成功移动。");
        }else{
           System.out.println("文件移动失败。");
        }
     }
    
    
    /**
     * @param workbook
     * @return 根据文档具体格子，是什么划分。可以返回厂家 也可以自定义。但一般是返回自定义格式名称作为一个文件夹因为很多厂家的格式一样
     */
    public String getChangJia(Workbook workbook) {
        String changJia = "";
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet != null) {
            
            Row row1 = sheet.getRow(1);
               Cell c = row1.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c.setCellType(CellType.STRING);
               String v1 = c.getStringCellValue().trim();
               
               Cell c11 = row1.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c11.setCellType(CellType.STRING);
               String v11 = c11.getStringCellValue().trim();
               
               
               Row row2 = sheet.getRow(2);
               Cell c2 = row2.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c2.setCellType(CellType.STRING);
               String v2 = c2.getStringCellValue().trim();
               
               Cell c21 = row2.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c21.setCellType(CellType.STRING);
               String v21 = c21.getStringCellValue().trim();
               
               Row row3 = sheet.getRow(3);
               Cell c3 = row3.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c3.setCellType(CellType.STRING);
               String v3 = c3.getStringCellValue().trim();
               
               Cell c31 = row3.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c31.setCellType(CellType.STRING);
               String v31 = c31.getStringCellValue().trim();
               
               
               
               Row row4 = sheet.getRow(4);
               Cell c4 = row4.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c4.setCellType(CellType.STRING);
               String v4 = c4.getStringCellValue().trim();
               
               Cell c41 = row4.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c41.setCellType(CellType.STRING);
               String v41 = c41.getStringCellValue().trim();
               
               
               
               Row row5 = sheet.getRow(5);
               Cell c5 = row5.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c5.setCellType(CellType.STRING);
               String v5 = c5.getStringCellValue().trim();
               
               Cell c51 = row5.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c51.setCellType(CellType.STRING);
               String v51 = c51.getStringCellValue().trim();
               
               Cell c52 = row5.getCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c52.setCellType(CellType.STRING);
               String v52 = c52.getStringCellValue().trim();
               
               Cell c53 = row5.getCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c53.setCellType(CellType.STRING);
               String v53 = c53.getStringCellValue().trim();
               
               Cell c54 = row5.getCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c54.setCellType(CellType.STRING);
               String v54 = c54.getStringCellValue().trim();
               
               Cell c55 = row5.getCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c55.setCellType(CellType.STRING);
               String v55 = c55.getStringCellValue().trim();
               
               Cell c56 = row5.getCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c56.setCellType(CellType.STRING);
               String v56 = c56.getStringCellValue().trim();
               
               
               Row row6 = sheet.getRow(6);
               Cell c6 = row6.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c6.setCellType(CellType.STRING);
               String v6 = c6.getStringCellValue().trim();
               
               Cell c61 = row6.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c61.setCellType(CellType.STRING);
               String v61 = c61.getStringCellValue().trim();
               
               Cell c62 = row6.getCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c62.setCellType(CellType.STRING);
               String v62 = c62.getStringCellValue().trim();
               
               Cell c63 = row6.getCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c63.setCellType(CellType.STRING);
               String v63 = c63.getStringCellValue().trim();
               
               Cell c64 = row6.getCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c64.setCellType(CellType.STRING);
               String v64 = c64.getStringCellValue().trim();
               
               Cell c65 = row6.getCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c65.setCellType(CellType.STRING);
               String v65 = c65.getStringCellValue().trim();
               
               Cell c66 = row6.getCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c66.setCellType(CellType.STRING);
               String v66 = c66.getStringCellValue().trim();
               
               
               Row row7 = sheet.getRow(7);
               Cell c7 = row7.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c7.setCellType(CellType.STRING);
               String v7 = c7.getStringCellValue().trim();
               
               Cell c71 = row7.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c71.setCellType(CellType.STRING);
               String v71 = c71.getStringCellValue().trim();
               
               Cell c72 = row7.getCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c72.setCellType(CellType.STRING);
               String v72 = c72.getStringCellValue().trim();
               
               Cell c73 = row7.getCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c73.setCellType(CellType.STRING);
               String v73 = c73.getStringCellValue().trim();
               
               Cell c74 = row7.getCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c74.setCellType(CellType.STRING);
               String v74 = c74.getStringCellValue().trim();
               
               Cell c75 = row7.getCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c75.setCellType(CellType.STRING);
               String v75 = c75.getStringCellValue().trim();
               
               Cell c76 = row7.getCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c76.setCellType(CellType.STRING);
               String v76 = c76.getStringCellValue().trim();
                                     
               Row row8 = sheet.getRow(8);
               Cell c8 = row8.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c8.setCellType(CellType.STRING);
               String v8 = c8.getStringCellValue().trim();
               
               Cell c81 = row8.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c81.setCellType(CellType.STRING);
               String v81 = c81.getStringCellValue().trim();
               
               Cell c82 = row8.getCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c82.setCellType(CellType.STRING);
               String v82 = c82.getStringCellValue().trim();
               
               Cell c83 = row8.getCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c83.setCellType(CellType.STRING);
               String v83 = c83.getStringCellValue().trim();
               
               Cell c84 = row8.getCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c84.setCellType(CellType.STRING);
               String v84 = c84.getStringCellValue().trim();
               
               Cell c85 = row8.getCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c85.setCellType(CellType.STRING);
               String v85 = c85.getStringCellValue().trim();
               
               
               Cell c86 = row8.getCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
               c86.setCellType(CellType.STRING);
               String v86 = c86.getStringCellValue().trim();
               
//               start13
//               if (v1.contains("定值单编号")
//                       &&v2.contains("被保护设备")
//                       &&v3.contains("终端厂家及型号")
//                       &&v4.contains("CT变比")
//                       &&v61.equals("功能")
//                       
//                       ) {
//                   changJia = "G列-定值单编号-被保护设备-终端厂家及型号-CT变比-功能";
//               }
           
               
//               start18
//               if (
//                       v2.contains("定值单编号")&&
//                       v3.contains("被保护设备")&&
//                       v4.contains("编发日期")&&
//                       v5.contains("终端型号")&&
//                       v6.equals("序号")
//                       
//                       ) {
//                       changJia = "G列-定值单编号-被保护设备-编发日期-终端型号-序号";
//                    }
               
               
        
    if (
            v2.contains("定值单编号")&&
            v3.contains("安装间隔")&&
            v4.contains("终端型号")&&
            v5.contains("CT变比")
            
            ) {
        changJia = "G列-定值单编号-安装间隔-终端型号-CT变比";
    }
               
               
               

               
               
//               start37
//             if (  
//                     (v6.contains("序号")
//                              &&v61.contains("定值名称")
//                              &&v63.contains("整定范围及步长")
//                              &&v64.contains("整定值")
//                              &&v66.contains("备注")
//                             )
//                             ||
//                             (v7.contains("序号")
//                      &&v71.contains("定值名称")
//                      &&v73.contains("整定范围及步长")
//                      &&v74.contains("整定值")
//                      &&v76.contains("备注")
//                     )
//                     ||(v8.contains("序号")
//                      &&v81.contains("定值名称")
//                      &&v83.contains("整定范围及步长")
//                      &&v84.contains("整定值")
//                      &&v86.contains("备注")
//              )
//              ) {
//           changJia = "G列-序号-定值名称-整定范围及步长-整定值-备注（混杂系列，子表列值同）";
//        }
             
               
               
//               start0
               //抽离 包含出线，进线的单，这些单不用录入
//                   if (
//               (v4.contains("进线")||v4.contains("出线"))||
//               (v5.contains("进线")||v5.contains("出线"))
//               
//               ) {
//               changJia = "包含出线或者进线的单(开关站)";
//            }
               
               
        }else{
            System.out.println("sheet 大小为 0 ");
        }
        return changJia;
    }

}