excel多sheet导出工具类——java

excel多sheet导出工具类

1、多个sheet导出工具类：

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入、导出
 */
public class ExcelDataHandleUtil {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    private final static String SUFFIX_XLS = ".xls";
    private final static String SUFFIX_XLSX = ".xlsx";

    /**
     * 解析Excel数据
     */
    public static List<Map<String, Object>> analysisExcel(MultipartFile file, String[] keyList) throws Exception {

        // 1、用HSSFWorkbook打开或者创建“Excel文件对象”
        Workbook workbook = null;
        //获得文件名
        String fileName = file.getOriginalFilename();
        // 判断后缀
        if (fileName.endsWith(XLS)) {
            //2003
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            //2007
            workbook = new XSSFWorkbook(file.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }

        // 2、用HSSFWorkbook对象返回或者创建Sheet对象
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();// 指的行数，一共有多少行
        if (rows == 0) {
            throw new Exception("请填写数据");
        }
        List<Map<String, Object>> list = new ArrayList<>();
        // 3、用Sheet对象返回行对象，用行对象得到Cell对象
        for (int i = 1; i <= rows; i++) {
            // 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                // 4、对Cell对象读写。
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < keyList.length; j++) {
                    map.put(keyList[j], getCellValue(row.getCell(j)));
                }
                list.add(map);
            }
        }
        return list;
    }

    /**
     * 导出excel文件2007
     */
    public static void exportExcel(HttpServletResponse response, List<Map> list, String[] keyList, String[]
            columnNameList, String fileName) throws Exception {
        // 第一步，创建一个webbook，对应一个Excel文件
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workbook.createSheet("Sheet1");

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

//        HSSFCell cell = row.createCell(0);
        XSSFCell cell = row.createCell(0);

        // 列头
        for (int i = 0; i < columnNameList.length; i++) {
            cell.setCellValue(columnNameList[i]);
            cell.setCellStyle(style);
            cell = row.createCell(i + 1);
        }

        // 数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < keyList.length; j++) {
                String key = keyList[j];
                // 第四步，创建单元格，并设置值
                if (map.get(key) != null && StringUtils.isNotBlank(map.get(key).toString())) {
                    row.createCell(j).setCellValue(String.valueOf(map.get(key)));
                }
            }
        }
        // 第六步,输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        // SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
        // String fileName =  df.format(new Date());// new Date()为获取当前系统时间
        response.setHeader("Content-disposition", "attachment; filename=" +
                URLEncoder.encode(fileName, "UTF-8") + SUFFIX_XLSX);
        response.setContentType("application/msexcel");
        workbook.write(output);
        output.close();
    }

    /**
     * 导出excel文件2003
     */
    public static void exportExcelXLS(HttpServletResponse response, List<Map> list, String[] keyList, String[]
            columnNameList, String fileName) throws Exception {
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

        HSSFCell cell = row.createCell(0);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 列头
        for (int i = 0; i < columnNameList.length; i++) {
            cell.setCellValue(columnNameList[i]);
            cell.setCellStyle(style);
            cell = row.createCell(i + 1);
        }

        // 数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < keyList.length; j++) {
                String key = keyList[j];
                // 第四步，创建单元格，并设置值
                if (map.get(key) != null && StringUtils.isNotBlank(map.get(key).toString())) {
                    row.createCell(j).setCellValue(String.valueOf(map.get(key)));
                }
            }
        }
        // 第六步,输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
        //String fileName = df.format(new Date());// new Date()为获取当前系统时间
        response.setHeader("Content-disposition", "attachment; filename=" +
                URLEncoder.encode(fileName, "UTF-8") + SUFFIX_XLS);
        response.setContentType("application/msexcel");
        workbook.write(output);
        output.close();
    }

    /**
     * 多个sheet导出
     * 导出excel文件2003
     */
    public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
                                   String sheetTitle, List<Map<String, Object>> list, String[] keyList, String[]
                                           columnNameList) {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        HSSFRow row = sheet.createRow(0);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        HSSFCell cell = row.createCell(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 13);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 列头
        for (int i = 0; i < columnNameList.length; i++) {
            cell.setCellValue(columnNameList[i]);
            cell.setCellStyle(style);
            row.setHeight((short) (30 * 20));
            cell = row.createCell(i + 1);
        }

        // 数据
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                Map<String, Object> map = list.get(i);
                for (int j = 0; j < keyList.length; j++) {
                    String key = keyList[j];
                    // 第四步，创建单元格，并设置值
                    if (map.get(key) != null && StringUtils.isNotBlank(map.get(key).toString())) {
                        row.createCell(j).setCellValue(String.valueOf(map.get(key)));
                    }
                }
            }
        }
    }

    /**
     * 获得Cell内容
     *
     * @param cell 单元格
     * @return 数据
     */
    private static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("##0.####").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

}

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
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294

2、工具类使用

 @ApiOperation(value = "资产中心-资产包-导出")
    @ApiResponse(code = 200, message = "返回结果")
    @RequestMapping(value = "/export", method = RequestMethod.POST)
    public void export(@RequestBody AssetPackageVersionSearchDto dto, HttpServletResponse response)
            throws IOException {

        if (dto.getProjectMainId() == null) {
            throw new LogicException(500, "项目id不能为空");
        }
        // 导出查询
        AssetPackageVersionResultDto resultDto = this.assetPackageClient.queryDetail(dto).getResult();

 		// 获取枚举数据，进行枚举数据匹配
        Map<String, String> operateStatusMap = getOperateStatus();
        for (AssetUnitResultDto assetUnitResultDto : resultDto.getAssetUnitList()) {
            // 资产状态替换
            if (operateStatusMap.containsKey(assetUnitResultDto.getOperateStatus().toString())) {
                assetUnitResultDto.setOperateStatusName(
                        operateStatusMap.get(assetUnitResultDto.getOperateStatus().toString()));
            }
        }

        try {

            String s1 = JSON.toJSONString(resultDto.getAssetPackageList());
            String s2 = JSON.toJSONString(resultDto.getAssetUnitList());
            // 资产包
            List<Map<String, Object>> assetPackageList = JSONObject.parseObject(s1, new TypeReference<List<Map<String,
                    Object>>>() {
            });
            // 资产单元
            List<Map<String, Object>> assetUnitList = JSONObject.parseObject(s2, new TypeReference<List<Map<String,
                    Object>>>() {
            });

            // 资产包字段名
            String[] assetPackageKey = {"assetPackageCode", "assetPackageName", "firstFormatName", "buildingArea",
                    "selfBuildingArea", "saleableBuildingArea", "matchingArea", "governmentRepurchaseArea",
                    "otherArea", "assetLocation", "remark"};
            // 资产包表格列头-顺序需要一致
            String[] assetPackageColumnName = {"资产包编码", "资产包名称", "汇总业态", "总建筑面积(㎡)",
                    "自持建筑面积(㎡)", "可售建筑面积(㎡)", "配套面积(㎡)", "政府回购面积(㎡)",
                    "其他面积(㎡)", "资产包坐落", "备注"};

            // 资产单元字段名
            String[] assetUnitKey = {"assetUnitName", "assetUnitCode", "buildingName", "assetPackageName",
                    "assetPackageCode", "operateStatusName", "firstFormatName", "buildingFormatName",
                    "secondFormatName",
                    "buildingArea", "selfBuildingArea", "saleableBuildingArea", "matchingArea",
                    "governmentRepurchaseArea", "otherArea", "remark"};
            // 资产单元表格列头-顺序需要一致
            String[] assetUnitColumnName = {"资产单元名称", "资产单元编码", "所属楼栋", "所属资产包名称",
                    "所属资产包编码", "资产状态", "汇总业态", "建筑业态", "营销业态", "总建筑面积(㎡)",
                    "自持建筑面积(㎡)", "可售建筑面积(㎡)", "配套面积(㎡)", "政府回购面积(㎡)",
                    "其他面积(㎡)", "备注"};

            // 导出
            HSSFWorkbook workbook = new HSSFWorkbook();
            ExcelDataHandleUtil.exportExcel(workbook, 0, "资产包", assetPackageList,
                    assetPackageKey, assetPackageColumnName);
            ExcelDataHandleUtil.exportExcel(workbook, 1, "资产单元", assetUnitList,
                    assetUnitKey, assetUnitColumnName);

            // 定义文件名称
            String fileName = "资产包数据导出" + DateUtils.dateTimeNow();
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String dlfileName = URLEncoder.encode(fileName, "utf-8").replaceAll("\\+", "%20");
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + dlfileName + ".xls");
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            throw new LogicException(500, "导出失败,请重试!");
        }

    }

    /**
     * 获取资产状态
     *
     * @return 数据
     */
    public Map<String, String> getOperateStatus() {
        DicCodeVo dicCodeVo = DicCodeVo.init()
                // 资产状态
                .addDic("assetPackageOperateStatus", DictPathConstant.ASSET_PACKAGE_OPERATE_STATUS);
        Map<String, String> map = Maps.newHashMap();
        dicCodeVo.getDic().entrySet().forEach(d -> {
            // 资产状态
            if ("assetPackageOperateStatus".equals(d.getKey())) {
                d.getValue().getChildren().forEach(v -> {
                    map.put(v.getValue(), v.getName());
                });
            }
        });
        return map;
    }

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