java-导出数据到excle表格（动态表头和静态表头）

两种类型（1.动态生成列数和列名 2.自定义列名）
1.动态表头（把需要的实体类换成自己的就行了）

@RequestMapping(value = “/historyExport”)
public String historyExport(HttpServletResponse response, String ncode, String from, String to, HttpServletRequest request) throws IOException {

    if (StringUtils.isBlank(ncode)) {
        log.error("设备号为空");
        return "redirect:../index";
    }
    if (StringUtils.isBlank(from)) {
        log.error("起始时间为空");
        return "redirect:../index";
    }
    if (StringUtils.isBlank(to)) {
        log.error("结束时间为空");
        return "redirect:../index";
    }
    //验证设备是否属于用户。
    SysUserBean user = ControllerHelper.getInstance(dao).getLoginUser();
    if (user == null) {
        log.error("用户未登录");
        return "redirect:../doLogin";
    }
    if (user.getLevel() > 0) {
        UserDeviceBean bean = dao.fetch(UserDeviceBean.class, Cnd.where("user_name", "=", user.getUserName()).and("ncode", "=", ncode).and("is_del", "=", "0"));
        if (bean == null) {
            throw new ServiceException("权限不足");
        }
    }

    Timestamp fromTime = Timestamp.valueOf(from);
    Timestamp toTime = Timestamp.valueOf(to);
    Timestamp timestamp = new Timestamp(System.currentTimeMillis() - (6 * 30 * 24 * 60 * 60 * 1000l));
    DeviceBean deviceBean = dao.fetch(DeviceBean.class, ncode);
    if (deviceBean == null) {
        throw new ServiceException("设备不存在");
    }
    if (fromTime.getTime() < timestamp.getTime()) {
        fromTime = timestamp;
    }
    if (deviceBean.getCreatTime() != null && fromTime.getTime() < deviceBean.getCreatTime().getTime()) {
        fromTime = deviceBean.getCreatTime();
    }
    Condition cnd = Cnd.where("Drecord_time", ">", fromTime).and("Drecord_time", "<", toTime).and("Dcode", "=", ncode).desc("Did");
    List<NetDevicedata> list = SplitTableHelper.queryForDate(NetDevicedata.class, cnd, fromTime, toTime, 1, 50000);

// Sql sql = Sqls.create(“SELECT a.Nname,a.Ncode,a.Nserson_type,b.dsensor_data Nsensor_data,b.drecord_time Nrecord_time FROM netdevicedata b,network a where b.drecord_time>= @from and b.drecord_time<= @to and a.ncode=b.dcode and a.ncode=@ncode”);
// sql.params().set(“from”, fromtime);
// sql.params().set(“to”, totime);
// sql.params().set(“ncode”, ncode);
// this.sqlCallBack(sql);
// dao.execute(sql);
// List list = sql.getList(DeviceBean.class);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(ncode + “历史数据”);
String fileName = ncode + “历史数据”;//设置要导出的文件的名字
//新增数据行，并且设置单元格数据
int rowNum = 1;

    Cookie[] cookies = request.getCookies();
    String language = "";
    if (cookies != null) {
        for (Cookie cookie : cookies) {
            String name = cookie.getName();
            if (Constant.COOKIE_LANG.equals(name)) {
                language = cookie.getValue();
                break;
            }
        }
    }

    int headerCount = 4;
    String[] typeArr = {};
    if (!StringUtils.isEmpty(deviceBean.getType())) {
        typeArr = deviceBean.getType().split("/");
        headerCount += typeArr.length * 2;
    }

    String[] headers = new String[headerCount];
    if (language.equals("en")) {
        headers[0] = "Number";
        headers[1] = "Device Name";
        headers[2] = "Device Number";
        headers[3] = "Receive Time";
        int j = 0;
        try {
            for (int i = 4; i < headers.length; i += 2) {
                if (j < typeArr.length) {
                    String enHeader = "";
                    SensorTypeBean sensorTypeBean = dao.fetch(SensorTypeBean.class, Cnd.where("tname", "=", typeArr[j].trim()));
                    if (sensorTypeBean != null) {
                        if (StringUtils.isBlank(sensorTypeBean.getEnName())) {
                            enHeader = getEnHeaderStr(typeArr[j].trim());
                            sensorTypeBean.setEnName(enHeader);
                            dao.update(sensorTypeBean);
                        } else {
                            enHeader = sensorTypeBean.getEnName();
                        }
                    } else {
                        enHeader = getEnHeaderStr(typeArr[j].trim());
                    }

                    headers[i] = enHeader;
                    headers[i + 1] = "unit";
                    j++;
                }
            }
        } catch (Exception e) {
            log.warn(e.getMessage());
        }
    } else {
        headers[0] = "序号";
        headers[1] = "设备名称";
        headers[2] = "设备编号";
        headers[3] = "接收时间";
        int j = 0;
        for (int i = 4; i < headers.length; i += 2) {
            if (j < typeArr.length) {
                headers[i] = typeArr[j];
                headers[i + 1] = "单位";
                j++;
            }
        }
    }
    //headers表示excel表中第一行的表头
    HSSFRow row = sheet.createRow(0);
    //在excel表中添加表头
    for (int i = 0; i < headers.length; i++) {
        HSSFCell cell = row.createCell(i);
        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
        cell.setCellValue(text);
        cell.getCellStyle().setAlignment(HorizontalAlignment.CENTER);
        switch (i) {
            case 1:
                cell.getSheet().setColumnWidth(i, 5000);
                break;
            case 2:
                cell.getSheet().setColumnWidth(i, 5000);
                break;
            default:
                cell.getSheet().setColumnWidth(i, 3000);
        }
    }

    //在表中存放查询到的数据放入对应的列
    for (NetDevicedata netDevicedata : list) {
        HSSFRow row1 = sheet.createRow(rowNum);
        String time = netDevicedata.getDrecordTime().toString();
        row1.createCell(0).setCellValue(rowNum);
        row1.createCell(1).setCellValue(deviceBean.getName());
        row1.createCell(2).setCellValue(netDevicedata.getDeviceNumber());
        row1.createCell(3).setCellValue(time.substring(0, time.lastIndexOf(".")));

        String[] datas = {};
        if (!StringUtils.isEmpty(deviceBean.getType())) {
            datas = netDevicedata.getSensorData().split("\\|");

            int j = 0;
            for (int i = 4; i < headers.length; i += 2) {
                if (j < typeArr.length) {
                    try {//改模越界问题
                        String[] datasStr = datas[j].trim().split("\\s+");
                        row1.createCell(i).setCellValue(datasStr[0]);
                        if (datasStr.length < 2) {
                            if (language.equals("zh")) {
                                row1.createCell(i + 1).setCellValue("无");
                            } else {
                                row1.createCell(i + 1).setCellValue("none");
                            }
                        } else {
                            row1.createCell(i + 1).setCellValue(datasStr[1]);
                        }
                    } catch (Exception e) {

// log.info(e.toString(),e);
}
j++;
}
}
}
rowNum++;
}
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/octet-stream”);
response.setHeader(“Content-disposition”, “attachment;filename=” + new String(fileName.getBytes(“gbk”), “iso8859-1”) + “.xls”);
//response.setHeader(“Content-disposition”, “attachment;filename=” + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());

    return null;
}

private String getEnHeaderStr(String cnHeaderStr) throws Exception {

    String enHeader = TranslationUtils.getTranslationStr(cnHeaderStr);
    if (StringUtils.isBlank(enHeader)) {
        return cnHeaderStr;
    }
    return enHeader;
}

public static String getAnalysisData(String type, String value) {

    if (StringUtils.isEmpty(value)) {
        return "";
    }
    String[] types = {};
    if (!StringUtils.isEmpty(type)) {
        types = type.split("/");
    }
    String[] datas = value.split("\\|");
    String data = "";
    for (int j = 0; j < datas.length; j++) {
        if (types.length > j) {
            data += types[j].replace(" ", "") + "·" + datas[j].replace(" ", "");
        } else {
            data += datas[j];
        }
        if (j < datas.length - 1) {
            data += " | ";
        }
    }
    return data;
}

效果图
![效果图](https://img-blog.csdnimg.cn/20200810112513924.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dvZFN1cmUwOTE0,size_16,color_FFFFFF,t_70)


静态表头，这个就比较简单了，需要几列自己设置
    @RequestMapping(value = "/historyExport")
    @ResponseBody
    public String historyExport(HttpServletResponse response, HttpServletRequest request) throws IOException {


        //验证设备是否属于用户。
        SysUserBean user = ControllerHelper.getInstance(dao).getLoginUser();
        if (user == null) {
            log.error("用户未登录");
            return "redirect:../doLogin";
        }
     List<DeviceBean> deviceList=new ArrayList<>();
        List<UserDeviceBean> list1 = dao.query(UserDeviceBean.class, Cnd.where("user_name", "=", user.getUserName()).and("is_del", "=", "0"));
        for (int i=0;i<list1.size();i++){
            DeviceBean deviceBean=dao.fetch(DeviceBean.class,Cnd.where("Ncode","=",list1.get(i).getDeviceNumber()));
            if (null!=deviceBean){
                deviceList.add(deviceBean);
            }
        }

//        Sql sql = Sqls.create("SELECT a.Nname,a.Ncode,a.Nserson_type,b.dsensor_data Nsensor_data,b.drecord_time Nrecord_time FROM netdevicedata b,network a where  b.drecord_time>= @from and b.drecord_time<= @to and a.ncode=b.dcode and a.ncode=@ncode");
//        sql.params().set("from", fromtime);
//        sql.params().set("to", totime);
//        sql.params().set("ncode", ncode);
//        this.sqlCallBack(sql);
//        dao.execute(sql);
//        List<DeviceBean> list = sql.getList(DeviceBean.class);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(user.getUserName() + "用户设备历史数据");
        String fileName = user.getUserName() + "设备数据";//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据
        int rowNum = 1;

        Cookie[] cookies = request.getCookies();
        String language = "";
        if (cookies != null) {
            for (Cookie cookie : cookies) {
                String name = cookie.getName();
                if (Constant.COOKIE_LANG.equals(name)) {
                    language = cookie.getValue();
                    break;
                }
            }
        }

        int headerCount = 1;
        String typeArr = "";
        if (null!=(deviceList)) {
            for (DeviceBean deviceBean:deviceList){
                typeArr = deviceBean.getType();
            }

        }

        String[] headers = new String[6];
        if (language.equals("en")) {
            headers[0] = "Number";
            headers[1] = "Device Name";
            headers[2] = "Device Number";
            headers[3] = "Receive Time";
            headers[4] = "Receive Data";
            headers[5] = "unit";


        } else {
            headers[0] = "序号";
            headers[1] = "设备名称";
            headers[2] = "设备编号";
            headers[3] = "接收时间";
            headers[4] = "接收数据";
            headers[5] = "单位";


        }
        //headers表示excel表中第一行的表头
        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.getCellStyle().setAlignment(HorizontalAlignment.CENTER);
            switch (i) {
                case 1:
                    cell.getSheet().setColumnWidth(i, 5000);
                    break;
                case 2:
                    cell.getSheet().setColumnWidth(i, 5000);
                    break;
                default:
                    cell.getSheet().setColumnWidth(i, 3000);
            }
        }

        //在表中存放查询到的数据放入对应的列
        for (DeviceBean deviceBean1 : deviceList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            String time = deviceBean1.getTime().toString();
            row1.createCell(0).setCellValue(rowNum);
            row1.createCell(1).setCellValue(deviceBean1.getName());
            row1.createCell(2).setCellValue(deviceBean1.getDeviceNumber());
            row1.createCell(3).setCellValue(time.substring(0, time.lastIndexOf(".")));
            row1.createCell(4).setCellValue(deviceBean1.getData());
            row1.createCell(5).setCellValue(deviceBean1.getType());

            String[] datas = {};

            rowNum++;
        }
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls");
        //response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());

        return null;
    }
    private String getEnHeaderStr(String cnHeaderStr) throws Exception {

        String enHeader = TranslationUtils.getTranslationStr(cnHeaderStr);
        if (StringUtils.isBlank(enHeader)) {
            return cnHeaderStr;
        }
        return enHeader;
    }

效果图：
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200810112848478.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dvZFN1cmUwOTE0,size_16,color_FFFFFF,t_70)