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
package com.iailab.common.utils;
 
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
 
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
 
/**
 * Excel 导出通用工具类
 *
 * @author PanZhibao
 * @date 2021年06月29日 9:06
 */
public class ExcelUtil {
 
    /**
     * 导出PointValueExcel
     *
     * @param sheetTitle
     * @param title
     * @param list
     * @param response
     * @return
     */
    public static byte[] exportPointValue(String sheetTitle, String[] title, List<Object> list, HttpServletResponse response) {
 
        HSSFWorkbook wb = new HSSFWorkbook();//创建excel表
        HSSFSheet sheet = wb.createSheet(sheetTitle);
        sheet.setDefaultColumnWidth(20);//设置默认行宽
 
        //表头样式（加粗，水平居中，垂直居中）
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
 
        HSSFFont fontStyle = wb.createFont();
 
        cellStyle.setFont(fontStyle);
 
        //标题样式（加粗，垂直居中）
        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setFont(fontStyle);
 
        //字段样式（垂直居中）
        HSSFCellStyle cellStyle3 = wb.createCellStyle();
        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
 
        //数字样式
        HSSFCellStyle cellStyle4 = wb.createCellStyle();
        HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
        cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));//设置数值类型格式为保留两位小数
 
        //创建表头
        HSSFRow row = sheet.createRow(0);
        //row.setHeightInPoints(20);//行高
 
        HSSFCell cell = row.createCell(0);
        cell.setCellValue(sheetTitle);
        cell.setCellStyle(cellStyle);
 
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1)));
 
        //创建标题
        HSSFRow rowTitle = sheet.createRow(1);
        //rowTitle.setHeightInPoints(20);
 
        HSSFCell hc;
        for (int i = 0; i < title.length; i++) {
            hc = rowTitle.createCell(i);
            hc.setCellValue(title[i]);
            hc.setCellStyle(cellStyle2);
        }
 
        byte result[] = null;
 
        try {
            //创建表格数据
            Field[] fields;
            int i = 2;
            for (Object obj : list) {
                fields = obj.getClass().getDeclaredFields();
 
                HSSFRow rowBody = sheet.createRow(i);
                //rowBody.setHeightInPoints(20);
 
                int j = 0;
                for (Field f : fields) {
 
                    f.setAccessible(true);
 
                    Object va = f.get(obj);
                    if (null == va) {
                        va = "";
                    }
 
                    hc = rowBody.createCell(j);
 
                    if (j == 0) {
                        hc.setCellValue(Double.parseDouble(va.toString()));
                        hc.setCellStyle(cellStyle4);
                    } else {
                        hc.setCellValue(va.toString());
                        hc.setCellStyle(cellStyle3);
                    }
 
 
                    j++;
                }
 
                i++;
            }
 
            //设置Http响应头告诉浏览器下载这个附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.close();
            return wb.getBytes();
        } catch (Exception ex) {
            Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            return result;
        } finally{
            try {
                wb.close();
            } catch (IOException ex) {
                Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}