java分割excel文件可用jxl

excel导入是经常使用到的功能，如果文件数据量大的话还是建议分割后导入，java常用的API是poi和jxl，我采用的是jxl,那么让我们来看下怎么用jxl来实现分割。

 

• 需要在pom中导入jxl的包 

<!--excel-->
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

• 我们需要两个实体类 ,分别是excel信息和文件信息

public class FileInfoModel {

    /**
     * 文件在数据库中的Id
     */
    private Long fileId;
    /**
     * 是否上传
     */
    private boolean isUpload;
    /**
     * 存储循环过程中的索引值
     */
    private int forIndex;
    /**
     * 文件全名 xxx.xls
     */
    private String fileFullName;
    /**
     * 文件名 xxx
     */
    private String fileName;
    /**
     * 文件后缀 .xls
     */
    private String fileSuffix;
    /**
     * 文件保存路径 e:\\xx\xx
     */
    private String filePath;
    /**
     * 文件的起始行号
     */
    private int beginRow;
    /**
     * 文件的末尾行号
     */
    private int endRow;

    public FileInfoModel() {
        super();
    }

    public FileInfoModel(String fileFullName) {
        super();
        this.fileFullName = fileFullName;
    }

    public FileInfoModel(String fileName, String fileFullName) {
        super();
        this.fileName = fileName;
        this.fileFullName = fileFullName;
    }

    public FileInfoModel(String fileFullName, String fileName, String fileSuffix) {
        super();
        this.fileFullName = fileFullName;
        this.fileName = fileName;
        this.fileSuffix = fileSuffix;
    }

    public FileInfoModel(String fileFullName, String fileName, String fileSuffix, String filePath) {
        super();
        this.fileFullName = fileFullName;
        this.fileName = fileName;
        this.fileSuffix = fileSuffix;
        this.filePath = filePath;
    }

    public Long getFileId() {
        return fileId;
    }

    public void setFileId(Long fileId) {
        this.fileId = fileId;
    }

    public boolean isUpload() {
        return isUpload;
    }

    public void setUpload(boolean isUpload) {
        this.isUpload = isUpload;
    }

    public int getForIndex() {
        return forIndex;
    }

    public void setForIndex(int forIndex) {
        this.forIndex = forIndex;
    }

    public String getFileFullName() {
        return fileFullName;
    }

    public void setFileFullName(String fileFullName) {
        this.fileFullName = fileFullName;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String getFileSuffix() {
        return fileSuffix;
    }

    public void setFileSuffix(String fileSuffix) {
        this.fileSuffix = fileSuffix;
    }

    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public int getBeginRow() {
        return beginRow;
    }

    public void setBeginRow(int beginRow) {
        this.beginRow = beginRow;
    }

    public int getEndRow() {
        return endRow;
    }

    public void setEndRow(int endRow) {
        this.endRow = endRow;
    }
}

public class ExcelModel {

    /*excel 文件路径*/
    private String excelFilePath;
    /*分割后的文件目录*/
    private String excelChildFileDir;
    /*excel 名称*/
    private String excelName;
    /*sheet 名称*/
    private String sheetName = "Sheet1";
    /*excel 总记录数*/
    private int totalRows;
    /*分割文件后每个文件的记录数*/
    private int fileSize = 3000;
    /*分割后的文件数*/
    private int fileCount;
    /*分割后的文件集合*/
    private List<FileInfoModel> files;
    /*计算总行数时是否去表头*/
    private boolean isRemoveHeader = true;
    /*是否去除重复数据*/
    private boolean isCleareRepeat = false;
    /*导入文件过程中是否出错*/
    private boolean isError;

    public boolean isError() {
        return isError;
    }

    public void setError(boolean isError) {
        this.isError = isError;
    }

    public String getExcelFilePath() {
        return excelFilePath;
    }

    public void setExcelFilePath(String excelFilePath) {
        this.excelFilePath = excelFilePath;
    }

    public boolean isCleareRepeat() {
        return isCleareRepeat;
    }

    public void setCleareRepeat(boolean isCleareRepeat) {
        this.isCleareRepeat = isCleareRepeat;
    }

    public String getExcelChildFileDir() {
        return excelChildFileDir;
    }

    public void setExcelChildFileDir(String excelChildFileDir) {
        this.excelChildFileDir = excelChildFileDir;
    }

    public boolean isRemoveHeader() {
        return isRemoveHeader;
    }

    public void setRemoveHeader(boolean isRemoveHeader) {
        this.isRemoveHeader = isRemoveHeader;
    }

    public int getFileCount() {
        //根据总记录数及分割文件的行数计算文件数量
        fileCount = (int) Math.ceil(this.totalRows / this.fileSize) + 1;
        return fileCount;
    }

    public String getExcelName() {
        return excelName;
    }

    public void setExcelName(String excelName) {
        this.excelName = excelName;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public int getTotalRows() {
        if (this.isRemoveHeader) {
            return totalRows - 1;
        } else {
            return totalRows;
        }
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }

    public int getFileSize() {
        return fileSize;
    }

    public void setFileSize(int fileSize) {
        this.fileSize = fileSize;
    }

    public List<FileInfoModel> getFiles() {
        return files;
    }

    public void setFiles(List<FileInfoModel> files) {
        this.files = files;
    }
}

• 添加接口IExcelOperate

public interface IExcelOperate {

    /**
     * 加载excel文件
     * @param fileName
     */
    void load(String fileName) throws Exception;

    /**
     * 读取excel文件数据
     *
     * @throws Exception
     */
    public <T> List<T> readExcel() throws Exception;

    /**
     * 分割excel
     *
     * @throws Exception
     */
    public void splitExcel() throws Exception;

    /**
     * 多线程分割excel
     */
    public void splitExcelThread() throws Exception;
    /**
     * 关闭文件
     */
    void close();
}

• 让我们来实现接口的方法吧

public class ExcelUtil<T> implements IExcelOperate {
    protected final Log log = LogFactory.getLog(getClass());

    private ExcelModel excelModel;
    private Class tClass;
    private Cell[] titleCell;
    private jxl.Workbook workBook;
    private Sheet sheet;

    public int getRows() {
        if (this.sheet != null) {
            return this.sheet.getRows();
        }
        return 0;
    }

    public ExcelUtil() {
        super();
    }

    public ExcelUtil(ExcelModel excelModel, Class tClass) {
        this.tClass = tClass;
        this.excelModel = excelModel;
    }

    public ExcelModel getExcelModel() {
        return excelModel;
    }

    public void setExcelModel(ExcelModel excelModel) {
        this.excelModel = excelModel;
    }
    
    @Override
    public void load(String fileName) throws Exception {
        try {
            File file = new File(fileName);

            workBook = Workbook.getWorkbook(file);
            this.excelModel.setFiles(new ArrayList<FileInfoModel>());
            //默认读取文件的路径
            String[] ary = file.getName().split("\\.");
            String suffix = ary[1];
            this.excelModel.getFiles().add(new FileInfoModel(file.getName(), fileName, "." + suffix, file.getParent()));

            if (this.excelModel.isCleareRepeat()) {
                this.clearRepeat(file);
                workBook = Workbook.getWorkbook(file);
            }

            if (workBook == null) {
                throw new Exception("读取excel文件出错!");
            }
            sheet = this.excelModel.getSheetName().trim() == "" ? workBook.getSheet(0) : workBook.getSheet(this.excelModel.getSheetName());
            if (sheet == null) {
                sheet = workBook.getSheet(0);
            }
            if (sheet == null) {
                throw new Exception("读取sheet出错!");
            }

            this.excelModel.setTotalRows(sheet.getRows());
            // 用于存储列标题
            titleCell = new Cell[sheet.getColumns()];

            // 将列标题存储存到一个一维数组中
            for (int i = 0; i < titleCell.length; i++) {
                titleCell[i] = sheet.getCell(i, 0);
            }

        } catch (IOException e) {
            workBook.close();
        }
    }
    
       @Override
    public void splitExcel() throws Exception {
        //根据文件数分割 excel，重置文件集合
        this.excelModel.setFiles(new ArrayList<FileInfoModel>());
        //生成Guid作为文件前缀
        UUID uuid = UUID.randomUUID();
        FileInfoModel fileInfoModel = new FileInfoModel();
        for (int i = 1; i <= this.excelModel.getFileCount(); i++) {
            fileInfoModel = getFileInfoModelBySplit(i, uuid);
            this.excelModel.getFiles().add(this.split(fileInfoModel));
        }
    }

    @Override
    public void splitExcelThread() throws Exception {
        //根据文件数分割 excel，重置文件集合
        this.excelModel.setFiles(new ArrayList<FileInfoModel>());
        //生成Guid作为文件前缀
        UUID uuid = UUID.randomUUID();
        //根据文件数分割 excel，重置文件集合
        this.excelModel.setFiles(new ArrayList<FileInfoModel>());
        //工作线程
        ExecutorService executorService = Executors.newFixedThreadPool(this.excelModel.getFileCount());
        FileInfoModel fileInfoModel = new FileInfoModel();
        for (int i = 1; i <= this.excelModel.getFileCount(); i++) {
            fileInfoModel = getFileInfoModelBySplit(i, uuid);
            ExcelThread thread = new ExcelThread(fileInfoModel);
            executorService.execute(thread);
            this.excelModel.getFiles().add(thread.getReturnFile());
        }
        executorService.shutdown();
        while (!executorService.isTerminated()) {
            //检查所有线程都执行完成
        }
    }

  /**
     * 分割excel时获取文件信息
     *
     * @param i
     * @return
     */
    private FileInfoModel getFileInfoModelBySplit(int i, UUID uuid) {

        /*结束行*/
        int endRow = i * this.excelModel.getFileSize();
                 /*起始行*/
        int beginRow = (endRow - this.excelModel.getFileSize()) + 1;
                /*如果结束行超出总记录数，结束行就等于总记录数*/
        if (endRow >= this.excelModel.getTotalRows()) {
            endRow = this.excelModel.getTotalRows();
        }
        //获取文件路径
        String filePath = MessageFormat.format("{0}/{1}_{2}.xls", this.excelModel.getExcelChildFileDir(), uuid, i);
        FileInfoModel fileInfoModel = new FileInfoModel();
        fileInfoModel.setFilePath(filePath);
        fileInfoModel.setBeginRow(beginRow);
        fileInfoModel.setEndRow(endRow);
        fileInfoModel.setForIndex(i - 1);
        return fileInfoModel;
    }

/**
     * 分割excel
     *
     * @param fileInfoModel
     * @return
     */
    private FileInfoModel split(FileInfoModel fileInfoModel) {
        File file = new File(fileInfoModel.getFilePath());
        try {
            jxl.write.WritableWorkbook ww = Workbook.createWorkbook(file);
            WritableSheet ws = ww.createSheet(this.excelModel.getSheetName(), 0);

            //添加表头
            for (int iColumn = 0; iColumn < this.titleCell.length; iColumn++) {
                ws.addCell(new Label(iColumn, 0, this.titleCell[iColumn].getContents()));
            }

            //添加数据到excel中
            int rowIndex = 1;
            for (int iRow = fileInfoModel.getBeginRow(); iRow <= fileInfoModel.getEndRow(); iRow++, rowIndex++) {
                Cell[] cells = this.sheet.getRow(iRow);
                for (int iCell = 0; iCell < cells.length; iCell++) {
                    Cell cell = cells[iCell];
                    //excel 行的索引需要计算
                    ws.addCell(new Label(iCell, rowIndex, cell.getContents()));
                }
            }

            ww.write();
            ww.close();

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String[] ary = file.getName().split("\\.");
        String fileName = ary[0];
        String suffix = ary[1];

        fileInfoModel.setFileFullName(file.getName());
        fileInfoModel.setFileName(fileName);
        fileInfoModel.setFileSuffix("." + suffix);
        return fileInfoModel;
    }

 class ExcelThread implements Runnable {// 任务接口
        FileInfoModel returnFile;

        public FileInfoModel getReturnFile() {
            return returnFile;
        }

        ExcelThread(FileInfoModel fileInfoModel) {
            this.returnFile = fileInfoModel;
        }

        public void run() {
            long beginTime = System.currentTimeMillis();
            this.returnFile = split(this.returnFile);
            System.out.println(MessageFormat.format("分割文件{0},执行耗时{1}秒", this.returnFile.getForIndex(), (System.currentTimeMillis() - beginTime) / 1000f));
        }
    }
}