springboot整合poi实现excel导出_spring boot 导出 excel hssfworkbook workbook = new h

小白简易入门

 //使用poi导出EXCEL
    @Test
    public void bbb() throws IOException {
        //创建Excel对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建工作表单
        HSSFSheet sheet = workbook.createSheet("对象报表");
//创建HSSFRow对象 （行）
        HSSFRow row = sheet.createRow(0);

//创建HSSFCell对象 （单元格）
        HSSFCell cell = row.createCell(0);

//设置单元格的值
        cell.setCellValue("单元格中的中文");

        //输出Excel文件
        FileOutputStream output = new FileOutputStream("c:\\workbook.xls");

        workbook.write(output);

        output.flush();
    }


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

项目实战

  private HSSFWorkbook downloadRealTimeData(List<RtdDataQuery> rtdDataQueryList, List<Object> cellTitleList) throws FileNotFoundException {
        HSSFWorkbook workBook = new HSSFWorkbook();//创建工作薄
        //输出Excel文件
        FileOutputStream output = new FileOutputStream("c:\\workbook1.xls");
        //创建新文件夹
//        File thePath = new File(saveExportPath);
//        //判断文件夹是否存在
//        if (!thePath.exists()) {
//            thePath.mkdirs();
//        }
        try {
            int count = 0;
            //判断当前时间段是否有数据
            for (int y = 0; y < rtdDataQueryList.size(); y++) {

//                workBook.setSheetName(count,   rtdDataQueryList.get(y).getArrayofkeyvalue().getK());//工作簿名称 时间段
                HSSFSheet sheet = workBook.createSheet();//创建表


                workBook.setSheetName(0, "数据报表");//工作簿名称
                HSSFFont font = workBook.createFont();  //字体对象
                font.setColor(HSSFFont.COLOR_NORMAL);
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                HSSFCellStyle cellStyle = workBook.createCellStyle();//创建格式
                cellStyle.setFont(font);
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                //创建第1行标题
                //创建字体格式 加粗
                HSSFFont font1 = workBook.createFont();  //字体对象
                font1.setColor(HSSFFont.COLOR_NORMAL);
                font1.setFontHeightInPoints((short) 30);
                font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                HSSFCellStyle cellStyle1 = workBook.createCellStyle();//创建格式
                cellStyle1.setFont(font);

                HSSFRow titleRow1 = sheet.createRow((short) 0);//第1行标题
                HSSFCell cell1 = titleRow1.createCell(0);
                cell1.setCellStyle(cellStyle1);
                titleRow1.setHeightInPoints(30);
                HSSFRow titleRow2 = sheet.createRow((short) 1);//第2行标题
                titleRow2.setHeightInPoints(30);
                HSSFCell cell2 = titleRow2.createCell(0);
                cell2.setCellStyle(cellStyle1);
                HSSFRow titleRow3 = sheet.createRow((short) 2);//第3行标题
                titleRow3.setHeightInPoints(30);
                HSSFCell cell3 = titleRow3.createCell(0);
                cell3.setCellStyle(cellStyle1);
                /*标题  企业 、站点、mn号*/
                cell3.setCellValue(rtdDataQueryList.get(0).getMn());
                /*根据站点id查询到企业、站点名称*/
                EnterprisePointQuery enterprisePointQuery = new EnterprisePointQuery();
                enterprisePointQuery.setId(rtdDataQueryList.get(0).getPointId());
                List<EnterprisePointQuery> enterprisePointQueries = enterprisePointVoMapper.enterprisePointSearch(enterprisePointQuery);
                if (enterprisePointQueries != null && enterprisePointQueries.size() > 0) {
                    cell1.setCellValue(enterprisePointQueries.get(0).getEnterpriseName());//设置值-企业名称
                    cell2.setCellValue(enterprisePointQueries.get(0).getName());//站点名称
                }

                //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 20));
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 20));
                sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 20));
                //创建第7行因子名称
                HSSFRow titleRow4 = sheet.createRow((short) 3);//第4行标题
                System.out.println("因子数量" + cellTitleList.size());
                for (int i = 0; i < cellTitleList.size(); i++) {//创建第4行标题单元格
                    HSSFCell cell = titleRow4.createCell(i, 0);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(cellTitleList.get(i).toString());

                }

                //从第二行开始写入数据
                //注：此处如果数据过多，会抛出java.lang.IllegalStateException异常：The maximum number of cell styles was exceeded.
                //You can define up to 4000 styles in a .xls workbook。这是是由于cell styles太多create造成，故一般可以把cellstyle设置放到循环外面

                int countI = 0;
                HSSFCellStyle style = workBook.createCellStyle();//创建格式

                //从当前时间段第一条数据开始遍历
//                for(int x=1;y<rtdDataQueryList.size();y++,x++){
                if (rtdDataQueryList != null && !rtdDataQueryList.isEmpty()) {


                    for (int i = 0; i < rtdDataQueryList.size(); i++, y++) {//一行数据

                        HSSFRow row = sheet.createRow((short) i + 4);
                        HSSFCell cellz1 = row.createCell(0, 0);// 在上面行索引0的位置创建单元格
                        cellz1.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型

                        //监测时间
                        cellz1.setCellValue(rtdDataQueryList.get(i).getArrayofkeyvalue().getK());
                        //遍历因子名称 得到实时值/平均值

                        Object v = rtdDataQueryList.get(i).getArrayofkeyvalue().getV();
                        //把object转换为OBjectjson
                        org.json.JSONObject jsonObject = new org.json.JSONObject(v.toString());
//=========={烟气温度={"Rtd":"25.2400","Flag":"N"}, 烟气流速={"Rtd":"0.0000","Flag":"N"}, 烟气压力={"Rtd":"0.0000","Flag":"N"}, 废气={"Rtd":"18.5200","Flag":"N"}, 烟气湿度={"Rtd":"0.9500","Flag":"N"}, 非甲烷总烃={"Rtd":"14.8100","Flag":"N"}}

                        Iterator<String> iterator = jsonObject.keys();
                        int countJ = 0;
                        while (iterator.hasNext()) {//countj个因子
                            countJ++;
                            countI = countJ;//第i列段
                            HSSFCell cell = row.createCell(countJ, 0);// 在上面行索引+1的位置创建单元格
                            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                            cell.setCellStyle(style);
                            cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
                            //根据键遍历值
                            Object o = jsonObject.get(iterator.next());

                            //object转为jsonObJECT再转为map
                            Map mapObject = JSONObject.parseObject(o.toString(), Map.class);
                            /*判断是否为实时数据*/
                            System.out.println("objecct" + o + "map" + mapObject.get("Rtd"));
                            if (rtdDataQueryList.get(i).getFlag().contains("d2011")) {
                                cell.setCellValue(mapObject.get("Rtd").toString());
                            } else {//非实时数据，取平均值属性

                                cell.setCellValue(mapObject.get("Avg").toString());

                            }
                        }


                    }
//                }
                    //判断当前数据是不是最后一个 或者 判断当前数据是不是当前时间段最后一个
//                    if (y == rtdDataQueryList.size() - 1 || !(rtdDataQueryList.get(y).getDataTime()).equals(rtdDataQueryList.get(y + 1).getDataTime())) {
//                        //新建一页
//                        count++;
//                        //退出循环
//                        break;
//                    }
                }

                sheet.autoSizeColumn((short) 0);
                sheet.setColumnWidth(0, sheet.getColumnWidth(0) * 15 / 10);
                sheet.autoSizeColumn((short) countI);
                sheet.setColumnWidth(countI, sheet.getColumnWidth(countI) * 15 / 10);


            }

            workBook.write(output);
            System.out.println("数据导出成功");
            output.flush();
            return workBook;


        } catch (Exception e) {
            e.printStackTrace();
            return 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
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