poi导出

poi导出功能

效果如下：

1.引入依赖

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

2.代码

package com.glm.web.controller.job.test;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class FjFlowTest {
    public static void main(String[] args) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");

            //-----模拟的数据-------
            JSONArray array = new JSONArray();
            for (int i = 0; i < 2; i++) {
                JSONObject jsonObject = new JSONObject();
                Date date = new Date();
                String format = sdf.format(date);
                jsonObject.put("gmtCreate", format);
                jsonObject.put("flow99", 1 + i);
                jsonObject.put("flow139", 22);
                jsonObject.put("flow150", 5);
                jsonObject.put("fail", 2);
                jsonObject.put("successRate", 35 + "%");
                jsonObject.put("avgDealTime", 45 + i);
                array.add(jsonObject);
            }
            //列头
            LinkedHashMap<String, String> headers = new LinkedHashMap<>();
            headers.put("gmtCreate", "时间");
            headers.put("flow99", "99元流量包");
            headers.put("flow139", "139元流量包");
            headers.put("flow150", "150元流量包");
            headers.put("fail", "失败");
            headers.put("successRate", "成功率");
            headers.put("avgDealTime", "平均受理时长");
            //excel保存的路径
            String path = "C:\\Users\\Leon\\Desktop";
            //1.创建工作簿
            Workbook workbook = new XSSFWorkbook();
            //水平居中和垂直居中
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            //背景色
            style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            //2.创建表名
            Sheet sheet = workbook.createSheet("流量包统计2021年");
            sheet.setDefaultColumnWidth(15);//设置列宽
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);//合并单元格，（将第1行到第1行的，第1个格子到第7个格子合并）
            sheet.addMergedRegion(region);
            //3.创建行
            Row row0 = sheet.createRow(0);
            //4.创建单元格
            Cell cell = row0.createCell(0);
            //5.写入数据
            cell.setCellValue("流量包统计2021年");

            Row row1 = sheet.createRow(1);
            Iterator<Map.Entry<String, String>> iterator = headers.entrySet().iterator();
            int firstColumnNum = 0;
            while (iterator.hasNext()){
                Map.Entry<String, String> next = iterator.next();
                String value = next.getValue();
                Cell cell1 = row1.createCell(firstColumnNum);
                cell1.setCellValue(value);
                firstColumnNum++;
            }
            cell.setCellStyle(style);
            for (int i = 0; i < array.size(); i++) {
                Row row = sheet.createRow(i + 2);
                Object o = array.get(i);
                JSONObject jsonObj = (JSONObject) o;
                Set<String> keySet = jsonObj.keySet();
                for (int j=0;j<keySet.size();j++){
                    row.createCell(j);
                }
                for (String key : keySet) {
                    Object o1 =  jsonObj.get(key);
                    Cell cell1 = null;
                    if("gmtCreate".equals(key)){
                        cell1 = row.getCell(0);
                    }else if("flow99".equals(key)){
                        cell1 = row.getCell(1);
                    }else if("flow139".equals(key)){
                        cell1 = row.getCell(2);
                    }else if("flow150".equals(key)){
                        cell1 = row.getCell(3);
                    }else if("fail".equals(key)){
                        cell1 = row.getCell(4);
                    }else if("successRate".equals(key)){
                        cell1 = row.getCell(5);
                    }else if("avgDealTime".equals(key)){
                        cell1 = row.getCell(6);
                    }else{
                        continue;
                    }
                    cell1.setCellValue(o1+"");

                }
            }
            //6.创建流用于输出
            FileOutputStream fileOutputStream = new FileOutputStream(path + "\\流量包统计2021年.xlsx");
            //7.输出
            workbook.write(fileOutputStream);
        } catch (Throwable throwable) {
            throwable.printStackTrace();
        }
    }
}