Java使用poi操作Excel单元格

1
2
3
4
5
6
7
8
9
10
11
12
 <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</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
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
package com.example.testways.utils;

import lombok.extern.slf4j.Slf4j;
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 org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;

@Slf4j
@Component
public class ExeclUtil {

    public static HSSFWorkbook downLoadCollectionList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //文本居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 标题
        String[] title = {"行人","非机动车","机动车","电动自行车","合计"};
        //声明列对象
        HSSFCell cell = null;
        //合并单元格 第一行  起始行,结束行,起始列,结束列
        //在sheet中添加表头第0行,
        HSSFRow row = sheet.createRow(0);
        //合并第一行 从第一行开始第一行结束，第0列开始 第某列结束
        megerSheet(0, 0, 0, 3,sheet);
        megerSheet(0, 0, 4, 7,sheet);
        megerSheet(0, 0, 8, 10,sheet);
        megerSheet(0, 0, 11, 13,sheet);
        megerSheet(0, 0, 14, 16,sheet);
        int index = 0;
        for (int i = 0; i < title.length; i++) {
            //在第一行第一个单元格
            cell = row.createCell(index);
            //sheet.setColumnWidth(0, 4000);
            //sheet.setDefaultRowHeight((short) 1000);
            // 设置样式为居中
            cell.setCellStyle(style);
            //第一行合并内容
            cell.setCellValue(title[i]);
            //确保为合并的单元格的首列赋值
            if(index >= 8){
                index += 3;
            }else{
                index += 4;
            }
        }
        String[] title1 = {"违法数","通行量","守法率","违法总数","通行量","守法率","违法数","通行量","守法率","头盔佩戴数","通行量","头盔佩戴率","违法数","通行量","守法率"};
        //在sheet中添加表头第1行
        HSSFRow row1 = sheet.createRow(1);
        //合并第一行 从第一行开始第一行结束，第0列开始 第某列结束
        megerSheet(1, 1, 0, 1,sheet);
        megerSheet(1, 1, 4, 5,sheet);
        int index1 = 0;
        for (int i = 0; i < title1.length; i++) {
            //在第2行第一个单元格
            cell = row1.createCell(index1);
            sheet.setColumnWidth(index1, 3000);
            // 设置样式为居中
            cell.setCellStyle(style);
            //第一行合并内容
            cell.setCellValue(title1[i]);
            //确保为合并的单元格的首列赋值
            if(i == 0 || i == 2){
                index1 = i + 2;
            }else{
                if(i == 3){
                    index1 += 2;
                }else{
                    index1 += 1;
                }
            }
        }
        //在sheet中添加表头第1行
        HSSFRow row2 = sheet.createRow(2);
        HSSFRow row3 = sheet.createRow(3);
        HSSFRow row4 = sheet.createRow(4);
        HSSFRow row5 = sheet.createRow(5);
        HSSFRow row6 = sheet.createRow(6);

        String[] title2 = {"闯红灯","闯红灯"};
        String[] title3 = {"不走斑马线","逆行"};
        String[] title4 = {"不走人行道","走机动车道"};
        String[] title5 = {"小计","越线停车"};
        String[] title6 = {"小计","小计"};

        //合并第一行 从第一行开始第一行结束，第0列开始 第某列结束
        megerSheet(5, 6, 0, 0,sheet);
        megerSheet(5, 6, 1, 1,sheet);
        megerSheet(2, 6, 2, 2,sheet);
        megerSheet(2, 6, 3, 3,sheet);
        megerSheet(2, 6, 6, 6,sheet);
        megerSheet(2, 6, 7, 7,sheet);
        megerSheet(2, 6, 8, 8,sheet);
        megerSheet(2, 6, 9, 9,sheet);
        megerSheet(2, 6, 10, 10,sheet);
        megerSheet(2, 6, 11, 11,sheet);
        megerSheet(2, 6, 12, 12,sheet);
        megerSheet(2, 6, 13, 13,sheet);
        megerSheet(2, 6, 14, 14,sheet);
        megerSheet(2, 6, 15, 15,sheet);
        megerSheet(2, 6, 16, 16,sheet);

        createCellAndGetValue(title2,cell,row2,sheet,style);
        createCellAndGetValue(title3,cell,row3,sheet,style);
        createCellAndGetValue(title4,cell,row4,sheet,style);
        createCellAndGetValue(title5,cell,row5,sheet,style);
        createCellAndGetValue(title6,cell,row6,sheet,style);

        long time = System.currentTimeMillis();
        //输出到本地
        //String fileName ="D:\User\违法"+time+".xls";
        //FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        //wb.write(fileOutputStream);
        String fileName =time+"路口交通秩序测评结果.xls";
        response.setContentType("application/octet-stream");
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        // 转码防止乱码
        response.addHeader("Content-Disposition",
                "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
        OutputStream out = response.getOutputStream();
        wb.write(out);
        return wb;
    }

    /**
     * 创建单元格并赋值
     * @param title
     * @param cell
     * @param row
     * @param sheet
     * @param style
     */
    public static void createCellAndGetValue(String[] title, HSSFCell cell, HSSFRow row, HSSFSheet sheet, HSSFCellStyle style){
        int index = 0;
        for (int i = 0; i < title.length; i++) {
            //在第n行第一个单元格
            cell = row.createCell(index);
            sheet.setColumnWidth(index, 4000);
            // 设置样式为居中
            cell.setCellStyle(style);
            //第一行合并内容
            cell.setCellValue(title[i]);
            index += 4;
        }
    }

    /**
     * 合并单元格
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param sheet
     */
    public static void megerSheet(int firstRow, int lastRow, int firstCol, int lastCol,HSSFSheet sheet){
        CellRangeAddress callAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(callAddress);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.example.testways.controller;

import com.example.testways.utils.ExeclUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/operatrExecl")
public class OperateExeclController {

    @GetMapping("/downLoadCollectionList")
    public void downLoadCollectionList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        ExeclUtil.downLoadCollectionList(request,response);
    }
}