Java将一个excel拆分为多个excel

110 阅读 0 评论 73 点赞

我是靠谱客的博主霸气黑裤，最近开发中收集的这篇文章主要介绍Java将一个excel拆分为多个excel，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

package hotelPro;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcel {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    public static void writeExcel(List<List<String>> dataList, int cloumnCount, String finalXlsxPath) {
        OutputStream out = null;
        try {
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);
            /**
             * 删除原有数据，除了属性列
             */
            int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
            System.out.println("原始数据总行数，除属性列：" + rowNumber);
            for (int i = 1; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);
                sheet.removeRow(row);
            }
            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(finalXlsxPath);
            workBook.write(out);
            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < dataList.size(); j++) {
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 1);
                // 得到要插入的每一条记录
                List<String> dataMap = dataList.get(j);
                String cell1 = dataMap.get(0).toString();
                String cell2 = dataMap.get(1).toString();
                String cell3 = dataMap.get(2).toString();
                String cell4 = dataMap.get(3).toString();
                String cell5 = dataMap.get(4).toString();
                String cell6 = dataMap.get(5).toString();
                String cell7 = dataMap.get(6).toString();

                // for (int k = 0; k <= columnNumCount; k++) {
                // 在一行内循环
                Cell first = row.createCell(0);
                first.setCellValue(cell1);

                Cell second = row.createCell(1);
                second.setCellValue(cell2);

                Cell third = row.createCell(2);
                third.setCellValue(cell3);
                Cell four = row.createCell(3);
                four.setCellValue(cell4);
                Cell five = row.createCell(4);
                five.setCellValue(cell5);
                Cell six = row.createCell(5);
                six.setCellValue(cell6);
                Cell seven = row.createCell(6);
                seven.setCellValue(cell7);
                // }
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(finalXlsxPath);
            workBook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("数据导出成功");
    }

    /**
     * 判断Excel的版本,获取Workbook
     * 
     * @param in
     * @param filename
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * 根据fileType不同读取excel文件
     *
     * @param path
     * @param path
     * @throws IOException
     */
    @SuppressWarnings({ "resource", "deprecation" })
    public static List<List<String>> readExcel(String path) {
        String fileType = path.substring(path.lastIndexOf(".") + 1);
        // return a list contains many list
        List<List<String>> lists = new ArrayList<List<String>>();
        // 读取excel文件
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            // 获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                return null;
            }

            // 读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            // 第一行为标题
            for (Row row : sheet) {
                ArrayList<String> list = new ArrayList<String>();
                for (Cell cell : row) {
                    // 根据不同类型转化成字符串
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    list.add(cell.getStringCellValue());
                }
                lists.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }

    public static void main(String[] args) throws IOException {
        String path = "F:\Data\out.xlsx";
        List<List<String>> lists = readExcel(path);
        int size = lists.size();
        //每个一万条
        int a = size / 10000;
        for (int i = 0; i < a + 1; i++) {
            System.out.println("---------------------------" + i);
            List<List<String>> list;
            if (i == a) {
                list = lists.subList(i * 1000, size+a);
            } else {
                list = lists.subList(i * 1000, (i + 1) * 1000);
            }
            list.add(0, lists.get(0));
            String p = "split_" + i + ".xlsx";
            WriteExcel.writeExcel(list, list.size(), "F:\Data\" + p);
        }

        System.out.println("-------------");
    }

}