Java实现Excel导入导出操作_cellstart

java 对excel的导入及导出

最近在做对excel的导入导出，在平常的工作中，导入导出excel数据是常见的需求，今天就简单的记录一下Java是如何来实现这个功能的，感兴趣或者正好大家在工作中遇到了可以了解下。

首先我们能引入导入导出所需要用到Maven依赖，因为返回用到了阿里巴巴的Json包所也引入fastjson包及文件上传的包

<!-- POI -->
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>5.2.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.0</version>
</dependency>
<!-- JSON -->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>fastjson</artifactId>
     <version>1.2.83</version>
</dependency>
<!-- 文件上传 -->
<dependency>
    <groupId>org.apache.httpcomponents</groupId>
    <artifactId>httpmime</artifactId>
    <version>4.5.13</version>
</dependency>
 <!--HTTP-->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>4.0.1</version>
    <scope>provided</scope>
</dependency>
<!-- springframework -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>5.3.18</version>
</dependency>

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

导入Controller层

@ResponseBody
@RequestMapping(value="import")
public JSONArray import(@RequestPart("file")MultipartFile file) throws Exception{
    JSONArray array = ExcelUtil.readMultipartFile(file);
    System.out.println("导入数据为:" + array);
    return array;
}
1
2
3
4
5
6
7

然后导入解析为对象（基础）创建需要的一个与导入表格对应的Java实体对象

ExcelUtils（导入导出工具类）

package com./*自己包名路径*/.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

public class ExcelUtil {
    
    private static final String XLSX = ".xlsx";
    private static final String XLS = ".xls";
    private static final String ROW_NUM = "rowNum";
    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();
    
    public static JSONArray readFile(File file) throws Exception {
        return readExcel(null, file);
    }
    private static JSONArray readExcel(MultipartFile mFile, File file) throws IOException {
        boolean fileNotExist = (file == null || !file.exists());
        if (mFile == null && fileNotExist) {
            return new JSONArray();
        }
        // 解析表格数据
        InputStream in;
        String fileName;
        if (mFile != null) {
            // 上传文件解析
            in = mFile.getInputStream();
            fileName = getString(mFile.getOriginalFilename()).toLowerCase();
        } else {
            // 本地文件解析
            in = new FileInputStream(file);
            fileName = file.getName().toLowerCase();
        }
        Workbook book;
        if (fileName.endsWith(XLSX)) {
            book = new XSSFWorkbook(in);
        } else if (fileName.endsWith(XLS)) {
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
            book = new HSSFWorkbook(poifsFileSystem);
        } else {
            return new JSONArray();
        }
        JSONArray array = read(book);
        book.close();
        in.close();
        return array;
    }
    
    private static String getString(String s) {
        if (s == null) {
            return "";
        }
        if (s.isEmpty()) {
            return s;
        }
        return s.trim();
    }
    
    private static JSONArray read(Workbook book) {
        // 获取 Excel 文件第一个 Sheet 页面
        Sheet sheet = book.getSheetAt(0);
        return readSheet(sheet);
    }
    
    private static JSONArray readSheet(Sheet sheet) {
        // 首行下标
        int rowStart = sheet.getFirstRowNum();
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = getCellValue(headRow.getCell(j));
            if (val != null && val.trim().length() != 0) {
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return (JSONArray) Collections.emptyList();
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, 1);
            for (int i : keyMap.keySet()) {
                obj.put(keyMap.get(i), "");
            }
            array.add(obj);
            return array;
        }
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, i + 1);
            StringBuilder sb = new StringBuilder();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getCellValue(eachRow.getCell(k));
                    // 所有数据添加到里面，用于判断该行是否为空
                    sb.append(val);
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.length() > 0) {
                array.add(obj);
            }
        }
        return array;
    }
    
    private static String getCellValue(Cell cell) {
        // 空白或空
        if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        }
        // String类型
        if (cell.getCellTypeEnum() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }
        // 数字类型
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            // 科学计数法类型
            return NUMBER_FORMAT.format(cell.getNumericCellValue()) + "";
        }
        // 布尔值类型
        if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        // 错误类型
        return cell.getCellFormula();
    }
    
private static void export(HttpServletResponse response, File file, String 		fileName, String sheetName, List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
        // 整个 Excel 表格 book 对象
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 每个 Sheet 页
        Sheet sheet = book.createSheet(sheetName);
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        // 设置表头背景色（灰色）
        CellStyle headStyle = book.createCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        // 设置表身背景色（默认色）
        CellStyle rowStyle = book.createCellStyle();
        rowStyle.setAlignment(HorizontalAlignment.CENTER);
        rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置表格列宽度（默认为15个字节）
        sheet.setDefaultColumnWidth(15);
        // 创建合并算法数组
        int rowLength = sheetDataList.size();
        int columnLength = sheetDataList.get(0).size();
        int[][] mergeArray = new int[rowLength][columnLength];
        for (int i = 0; i < sheetDataList.size(); i++) {
            // 每个 Sheet 页中的行数据
            Row row = sheet.createRow(i);
            List<Object> rowList = sheetDataList.get(i);
            for (int j = 0; j < rowList.size(); j++) {
                // 每个行数据中的单元格数据
                Object o = rowList.get(j);
                int v = 0;
                if (o instanceof URL) {
                    // 如果要导出图片的话, 链接需要传递 URL 对象
                    setCellPicture(book, row, patriarch, i, j, (URL) o);
                } else {
                    Cell cell = row.createCell(j);
                    if (i == 0) {
                        // 第一行为表头行，采用灰色底背景
                        v = setCellValue(cell, o, headStyle);
                    } else {
                        // 其他行为数据行，默认白底色
                        v = setCellValue(cell, o, rowStyle);
                    }
                }
                mergeArray[i][j] = v;
            }
        }
        // 合并单元格
        mergeCells(sheet, mergeArray);
        // 设置下拉列表
        setSelect(sheet, selectMap);
        // 写数据
        if (response != null) {
            // 前端导出
            try {
                write(response, book, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            // 本地导出
            FileOutputStream fos;
            try {
                fos = new FileOutputStream(file);
                ByteArrayOutputStream ops = new ByteArrayOutputStream();
                book.write(ops);
                fos.write(ops.toByteArray());
                fos.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    
public static void export(HttpServletResponse response, String fileName,List<List<Object>> sheetDataList) {
        export(response, fileName, fileName, sheetDataList, null);
    }
    
public static void export(HttpServletResponse response, String fileName, String sheetName, List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap){
        export(response, null, fileName, sheetName, sheetDataList, selectMap);
    }  
    
}
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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243

动态导出Controller

这种方式十分灵活，表中的数据，完全自定义设置。

@ResponseBody
@RequestMapping(value="export")
public void export(@RequestPart("file")MultipartFile file) throws Exception{
    // 表头数据
    List<Object> head = Arrays.asList("姓名","年龄","性别");
    // 用户1数据
    List<Object> user1 = new ArrayList<>();
    user1.add("111");
    user1.add(60);
    user1.add("男");
    // 用户2数据
    List<Object> user2 = new ArrayList<>();
    user2.add("222");
    user2.add(28);
    user2.add("女");
    // 将数据汇总
    List<List<Object>> sheetDataList = new ArrayList<>();
    sheetDataList.add(head);
    sheetDataList.add(user1);
    sheetDataList.add(user2);
    // 导出数据
    ExcelUtil.export(response,"用户表", sheetDataList);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23