excel批量轉csv文件

CSVCreateService

package com.fhs.createcsvtool.service;

import com.fhs.createcsvtool.util.DateUtil;
import com.fhs.createcsvtool.util.FileUtil;
import com.fhs.createcsvtool.vo.ExcelFileInfoVO;
import com.fhs.createcsvtool.vo.ProcessorVO;
import lombok.extern.log4j.Log4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;
@Log4j
public class CSVCreateService extends BaseService {
    private ProcessorVO m_processorVO;

    public CSVCreateService(ProcessorVO processorVO)
    {
        m_processorVO=processorVO;
    }

    /**
     * CSVファイル作成
     */
    public void CreateCSV()
    {

        //(4)読込対象Excelファイルリストを取得
        //入荷計画
        if ("1".equals(m_processorVO.getCode()))
        {
            ProcessorVO processorVO1=m_processorVO;
            FileUtil fileUtil=new FileUtil();
            fileUtil.ReadInput(processorVO1);
            createWorking(processorVO1);
        }
            //部品マスター
        if("2".equals(m_processorVO.getCode()))
        {
            ProcessorVO processorVO2=m_processorVO;
            FileUtil fileUtil=new FileUtil();
            fileUtil.ReadInput(processorVO2);
            createWorking(processorVO2);

        }
            //SMT照合マスター
        if("3".equals(m_processorVO.getCode()))
        {
            ProcessorVO processorVO3=m_processorVO;
            FileUtil fileUtil=new FileUtil();
            fileUtil.ReadInput(processorVO3);
            createWorking(processorVO3);


        }
    }
    private void createWorking( ProcessorVO processorVO)
    {
        List<String> excelNameFileList;
        List<String> SheetNameList;
        List<String> ExcelData;
        String directoryPath_Working=processorVO.getDataFolder().getRootPath()+processorVO.getDataFolder().getWorking()+"\\";
        String directoryPath_Backup=processorVO.getDataFolder().getRootPath()+processorVO.getDataFolder().getBackup()+"\\";
        String directoryPath_Error=processorVO.getDataFolder().getRootPath()+processorVO.getDataFolder().getError()+"\\";
        String directoryPath_Trasfer=processorVO.getDataFolder().getRootPath()+processorVO.getDataFolder().getTransfer()+"\\";
        String savePath= directoryPath_Trasfer;
        //文件列表

        excelNameFileList=getExcelFileNameList(processorVO);
        //(5)ファイルを作業用フォルダへ移動
        for(String filename:excelNameFileList)
        {
            //文件路径
            String filePath_Working=directoryPath_Working+filename;
            // 文件名对应的sheet列表
            SheetNameList=getExcelSheetNameList(processorVO,directoryPath_Working,filename);
            //所有sheet表拼接的文本
            ExcelData=readAndCheckExcelData(filePath_Working,SheetNameList,filename);
            //创建csv文件
            if(createCSVFile(savePath,ExcelData,filename)) {
                log.info(filename + "文件处理完成");
                FileUtil fileUtil=new FileUtil();
                //保存到Trasfer
                fileUtil.RemoveFile(filePath_Working,directoryPath_Trasfer);
                //从Trasfer复制到Backup
                try {
                    fileUtil.CopyFile(directoryPath_Trasfer+filename,directoryPath_Backup+filename);
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
            else
            {
                log.info(filename + "文件处理失败");
                FileUtil fileUtil=new FileUtil();
                //保存到error
                fileUtil.RemoveFile(filePath_Working,directoryPath_Error);

                //从error复制到Backup
                try {
                    fileUtil.CopyFile(directoryPath_Error+filename,directoryPath_Backup+filename);
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }

        }

    }
    /**
     * 読込対象Excelファイルリストを取得
     * @param processorVO
     * @return
     */
    private List<String> getExcelFileNameList(ProcessorVO processorVO)
    {
        List<String>Working_ExcelFileNameList=new ArrayList<String>();
        FileUtil fileUtil=new FileUtil();
        String Working_directoryPath=processorVO.getDataFolder().getRootPath()+processorVO.getDataFolder().getWorking();
        String configInfo=processorVO.getExcelFileInfo().getFileName();
        File[] files =fileUtil.FileSortBytime(Working_directoryPath);
        Working_ExcelFileNameList=fileUtil.getFileNameListByConfig(files,configInfo);

        return Working_ExcelFileNameList;
    }



    /**
     * 読込対象Excelファイルのシートリストを取得
     * @param processorVO
     * @return
     */
    //默认获取文件夹中所有的excel；sheet，可能需要改成单excel多sheet。
    private List<String> getExcelSheetNameList(ProcessorVO processorVO, String directoryPath,String filename)
    {
        List<String>SheetNameList=new ArrayList<>();
        try {
            //实际路径
            String filePath=directoryPath+filename;
            //储存到实体类。
            ExcelFileInfoVO excelFileInfoVO=processorVO.getExcelFileInfo();
            excelFileInfoVO.setFilePath(filePath);
            processorVO.setExcelFileInfo(excelFileInfoVO);
            //打开xlsx文件
            if (filePath!=null&&filename.endsWith(".xlsx"))
            {
                XSSFWorkbook sheets = new XSSFWorkbook(new FileInputStream(filePath));
                int sheetsNumber = sheets.getNumberOfSheets();
                if (sheetsNumber>0){
                for (int i = 0; i <sheetsNumber ; i++) {
                    String sheetname=sheets.getSheetAt(i).getSheetName();
                    SheetNameList.add(sheetname);
                }
                filePath=null;
            }
                else {
                    log.info(filename+":没有sheet表");
                }
            }
            //打开xls文件
            if(filePath!=null&&filename.endsWith(".xls"))
            {
                HSSFWorkbook sheets=new HSSFWorkbook(new FileInputStream(filePath));
                int sheetsNumber = sheets.getNumberOfSheets();
                if (sheetsNumber>0){
                for (int i = 0; i <sheetsNumber ; i++) {
                    String sheetname=sheets.getSheetAt(i).getSheetName();
                    SheetNameList.add(sheetname);
                }
                    filePath=null;
                }
                else {
                    log.info(filename+":没有sheet表");
                }

            }
            return SheetNameList;
        } catch (IOException e) {
            log.info(filename+"无法打开");
           return null;
        }
    }

    /**
     * 7)CSVデータを抽出・チェック
     * @param
     * @return
     */
    private List<String> readAndCheckExcelData(String filePath,List<String> SheetNameList,String filename)
    {
        List<String> excelData=new ArrayList<>();
        DateUtil dateUtil=new DateUtil();
        //读xls文件
        if (filename.endsWith(".xls")){
            for (String sheetname:SheetNameList){

            String data=dateUtil.readXls(filePath,sheetname,filename);
                if (data!=null) {
                    excelData.add(data);
                }
            }
        }
        //读xlsx文件
        if (filename.endsWith(".xlsx"))
        {
            for (String sheetname:SheetNameList){
                String data=dateUtil.readXlsx(filePath,sheetname,filename);
                if (data!=null) {
                    excelData.add(data);
                }
            }
        }
        if (excelData.size()!=0)
        {
        return excelData;
        }
        else {
            log.info(filename+"无数据");
            return null;
        }
    }

    /**
     * 8)CSVファイル作成
     * @param
     */
    private boolean createCSVFile(String savePath ,List<String> ExcelData,String filename)
    {
      FileUtil fileUtil=new FileUtil();
      return fileUtil.WriteFile(savePath,ExcelData,filename);

    }

    /**
     * ファイル退避処理
     * @param processorVO
     */
    private void backFile(ProcessorVO processorVO)
    {

    }

}

CSVTransferService

package com.fhs.createcsvtool.service;

import com.fhs.createcsvtool.util.FileUtil;
import com.fhs.createcsvtool.vo.ProcessorVO;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.List;

public class CSVTransferService extends BaseService {
    public void TransferCSV(List<ProcessorVO>processorList)
    {
        long tid = Thread.currentThread().getId();
        System.out.println("CSVTransferService88=" + tid + "=" + new Date());
        FileUtil fileUtil=new FileUtil();
        for (ProcessorVO processor:processorList)
        {
            if ("1".equals(processor.getCode()))
            {
            fileUtil.MoveFileToWes(processor);
            }
            if ("2".equals(processor.getCode()))
            {
                fileUtil.MoveFileToWes(processor);
            }
            if ("3".equals(processor.getCode()))
            {
                fileUtil.MoveFileToWes(proce