java代码通过poi组件合并excel文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
package main.shishi;

import org.apache.commons.compress.archivers.dump.InvalidFormatException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import main.java.ExportExcelUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.swing.*;
import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.*;
import java.nio.charset.Charset;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
import java.util.zip.ZipFile;


public class POIMergeExcelUtil extends JFrame implements ActionListener {
    JButton open = null;

    /**
     * 读取某个文件夹下的所有文件
     */
    public static boolean readfile(List<String> lisst) throws FileNotFoundException, IOException {
        try {
            OutputStream out = new FileOutputStream("C:\Users\conghan\Desktop\new.xls");
            ExportExcelUtils eeu = new ExportExcelUtils();
            HSSFWorkbook workbooks = new HSSFWorkbook();
            Sheet sheet = null;
            List<List<String>> data = new ArrayList<List<String>>();
            List<List<String>> datas = new ArrayList<List<String>>();
            List<List<String>> datasf = new ArrayList<List<String>>();
            List<String> list = new ArrayList<>();
            Object sell = null;
            Object sells = null;
            //第1个sheet
            for (int i = 0; i < lisst.size(); i++) {
                File readfile = new File(lisst.get(i));
                File file = new File(lisst.get(i));
                String region = file.getName().substring(10, 12);
                if (!readfile.isDirectory()) {
                    FileInputStream fileInputStream = new FileInputStream(readfile.getPath());
                    Workbook workbook = new HSSFWorkbook(fileInputStream); //读取excel
                    //循环sheet
                    for (int f = 0; f < 1; f++) {
                        sheet = workbook.getSheetAt(f);//获取sheet
                        //循环行
                        for (int j = 0; j < 1; j++) {
                            Row row = sheet.getRow(j); // 获取行
                            //循环列
                            for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                                if (f == 2 && i == 0) {
                                    Cell cell = row.getCell(k); //获取单元格
                                    sells = (Object) cell;
                                    list.add(sells.toString());
                                }
                            }
                        }
                        for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                            List<String> lista = new ArrayList<>();
                            Row row = sheet.getRow(j); // 获取行
                            for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                                Cell cell = row.getCell(k); //获取单元格
                                sell = (Object) cell;
                                if (sell != null) {
                                    lista.add(sell.toString());
                                }
                            }
                            data.add(lista);
                        }
                        datas = data;
                    }
                } else if (readfile.isDirectory()) {
//                        readfile(filepath + "\" + filelist[i]);
                }
            }
            try {
                String[] headers = new String[list.size()];
                for (int b = 0; b < list.size(); b++) {
                    headers[b] = list.get(b);
                }
                eeu.exportExcel(workbooks, 0, sheet.getSheetName(), headers, data, out);

                data.clear();
                list.clear();
            } catch (Exception e) {
                e.printStackTrace();
            }
            workbooks.write(out);
            out.close();

        } catch (FileNotFoundException e) {
            System.out.println("readfile()   Exception:" + e.getMessage());
        }
        return true;
    }

    public static void main(String[] args) {
        new POIMergeExcelUtil();
    }

    public static List<String> traverseFolder1(String path) {
        int fileNum = 0;
        int folderNum = 0;
        List<String> listdd = new ArrayList<>();
        File file = new File(path);
        if (file.exists()) {
            LinkedList<File> list = new LinkedList<File>();
            File[] files = file.listFiles();
            for (File file2 : files) {
                if (file2.isDirectory()) {
                    list.add(file2);
                    folderNum++;
                } else {
                    listdd.add(file2.getAbsolutePath());
                    fileNum++;
                }
            }
            File temp_file;
            while (!list.isEmpty()) {
                temp_file = list.removeFirst();
                files = temp_file.listFiles();
                for (File file2 : files) {
                    if (file2.isDirectory()) {
                        list.add(file2);
                        folderNum++;
                    } else {
                        listdd.add(file2.getAbsolutePath());
                        fileNum++;
                    }
                }
            }
        } else {
            System.out.println("文件不存在!");
        }
        return listdd;
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 public POIMergeExcelUtil() {
        open = new JButton("open");
        this.add(open);
        this.setBounds(500, 500, 400, 400);
        this.setVisible(true);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        open.addActionListener(this);
    }

    @Override
    public void actionPerformed(ActionEvent e) {
//文件选择器获取文件或者文件夹
        //========================================
        JFileChooser jfc = new JFileChooser();
        FileSystemView fsv = FileSystemView.getFileSystemView();
        jfc.setCurrentDirectory(fsv.getHomeDirectory());
        //JFileChooser.FILES_AND_DIRECTORIES 选择路径和文件
        jfc.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);
//用户选择的路径或文件
        if (jfc.showOpenDialog(POIMergeExcelUtil.this) == JFileChooser.APPROVE_OPTION) {
            File file = jfc.getSelectedFile();
            if (file.isDirectory()) {
                List<String> list = traverseFolder1(file.getAbsolutePath());
                try {
                    readfile(list);
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            } else if (file.isFile()) {
                List<String> list = traverseFolder1(file.getAbsolutePath());
                try {
                    readfile(list);
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
        maisssn();
    }

    public void maisssn() {
        JOptionPane.showMessageDialog(null, "合并完成！！");
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package main.java;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.File;
import java.io.OutputStream;
import java.util.List;

public class ExportExcelUtils {
    /**
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: evan @ 2014-01-09
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle  （sheet的名称）
     * @param headers    （表格的标题）
     * @param result   （表格的数据）
     * @param out  （输出流）
     * @throws Exception
     */
    public void exportExcel(HSSFWorkbook workbook, int sheetNum,
                            String sheetTitle, String[] headers, List<List<String>> result,
                            OutputStream out) throws Exception {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle
               );
        HSSFCellStyle style = workbook.createCellStyle();
        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);

            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (List<String> m : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String str : m) {
                    HSSFCell cell = row.createCell(cellIndex);
                    cell.setCellValue(str.toString());
                    cellIndex++;
                }
                index++;
            }
        }
    }

    public void traverseFolder2(String path) {

        File file = new File(path);
        if (file.exists()) {
            File[] files = file.listFiles();
            if (null == files || files.length == 0) {
                System.out.println("文件夹是空的!");
                return;
            } else {
                for (File file2 : files) {
                    if (file2.isDirectory()) {
                        System.out.println("文件夹:" + file2.getAbsolutePath());
                        traverseFolder2(file2.getAbsolutePath());
                    } else {
                        System.out.println("文件:" + file2.getAbsolutePath());
                    }
                }
            }
        } else {
            System.out.println("文件不存在!");
        }
    }
}