Excel导入导出

文件操作

获取当前目录下指定后缀的文件

	FileFilter filter = FileUtil.getFileFilter(".zip");
	File[] files = zip_file.listFiles(filter);
	assert files != null;
/**
     * 获取目录下指定后缀的文件列表
     * @param filterStr
     * @return
     */
public static FileFilter getFileFilter(String filterStr){
    //这个文件过滤器会过滤掉文件后缀不是.txt的文件,当返回为false时则丢弃文件
    FileFilter filefilter = new FileFilter() {
        public boolean accept(File file) {
            //if the file extension is .txt return true, else false
            if (file.getName().endsWith(filterStr)) {
                return true;
            }
            return false;
        }
    };
    return filefilter;
}
  • PID:允许的最大值
  • 的顶顶顶顶顶的

java调用grovy脚本

/**
     * 执行 脚本,计算结果
     * @return
     */
    public static Object execGrovy(String script, Object[] param){
        Object result = null;
        GroovyClassLoader classLoader = new GroovyClassLoader();
        Class groovyClass = classLoader.parseClass(script);
        try {
            GroovyObject groovyObject = (GroovyObject) groovyClass.newInstance();
            result = groovyObject.invokeMethod("cal",param);
            return result;
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

导入导出Excel

1、引入依赖包

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.10</version>
        </dependency>

2、导入Excel

/**
     * 导入数据
     *
     * @param inputBean
     * @return
     */
    @RequestMapping(name = "导入数据", value = "importData", method = RequestMethod.POST)
    public SpringIdeDataDictionaryOutputBean importData(SpringIdeDataDictionaryInputBean inputBean) throws Exception {
//        Assert.assertNotEmpty(inputBean.getFileName(), "文件名");
        Assert.assertNotEmpty(inputBean.getFlag(), "全量增量标识");
        Assert.assertNotEmpty(inputBean.getUpload_path(), "上传文件");
        //将文件传送到这个目录下后再读取
        String filePath = FileUtil.findWebDownload() + File.separator + inputBean.getUpload_path();
        ExcelReader workbook_reader = ExcelUtil.getReader(filePath);
        List<Sheet> sheets = workbook_reader.getSheets();
        // 校验模块名是否存在
        List<String> groupNames = this.dictionaryService.checkGroupNames(sheets);
        if(Assert.notEmpty(groupNames)){
            String join = StringUtils.join(groupNames, ",");
            throw new CorsManagerSystemErrorException(join, "以上分组已存在,请重新填写!");
        }
        sheets.forEach(sheet -> {
            ExcelReader reader = new ExcelReader(sheet);
            //读取标题
            String groupName = sheet.getSheetName();
            //设置别名对应类字段
            Map<String, String> map = new HashMap<>();
            map.put("字段名", "field_cname");
            map.put("逻辑名", "field_name");
            map.put("字段类型", "field_type");
            map.put("是否枚举", "enu_yn_flag");
            map.put("选项值", "enu_value");
            map.put("选项代码", "enu_code");
            map.put("选项说明", "enu_bk_expl");
            reader.setHeaderAlias(map);
            //读取值
            List<IdeDataDictAndEnuBean> list = reader.read(1, 2, IdeDataDictAndEnuBean.class);
            int flag = inputBean.getFlag();
            dictionaryService.saveAll(flag, groupName, list);});
        return createOutput();
    }

3、导出Excel

@RequestMapping(name = "导出数据", value = "exportData", method = RequestMethod.POST)
    public SpringIdeDataDictionaryOutputBean exportData(SpringIdeDataDictionaryInputBean inputBean) {
//        Assert.assertNotEmpty(inputBean.getGroupId(), "组Id");
        SpringIdeDataDictionaryOutputBean output = createOutput();
        //查询组信息
        List<IdeDataDictionary> groups = new ArrayList<>();
        if(Assert.notEmpty(inputBean.getGroupId())){
            IdeDataDictionary group = dictionaryService.queryByDicId(inputBean.getGroupId());
            groups.add(group);
        } else {
            groups = this.dictionaryService.selectGroups();
        }

        if(Assert.notEmpty(groups)){
            //导出路径
            String path = "webDownLoad" + File.separator + "dictionary" + File.separator + Constant.DICT_PRE + System.currentTimeMillis() + Constant.XLS_FILE_SUF;
            String full_path = FileUtil.findAppPath() + File.separator + path;
            // 文件存在先删除
            cn.hutool.core.io.FileUtil.del(full_path);
            ExcelWriter writer = ExcelUtil.getWriter(full_path);
            // 导出数据
            for (int i = 0; i < groups.size(); i++) {
                IdeDataDictionary group = groups.get(i);
                //查询组下面得所有数据
                List<IdeDataDictAndEnuBean> dictAndEnuBeanList = dictionaryService.qryDictAndEnus(group.getDic_id());
                List<ExportDataDictionaryInfo> row = new ArrayList<>();
                dictAndEnuBeanList.forEach(data -> {
                    //将查出来的数据复制到导出类里面去
                    ExportDataDictionaryInfo export = new ExportDataDictionaryInfo();
                    BeanUtil.copyProperties(data, export);
                    row.add(export);
                });
                // 需改第一页为空白的bug.
                if(i == 0){
                    writer.renameSheet(group.getGroup_name());
                }else {
                    writer.setSheet(group.getGroup_name());
                }

                //自定义标题别名
                writer.addHeaderAlias("field_cname", "字段名");
                writer.addHeaderAlias("field_name", "逻辑名");
                writer.addHeaderAlias("field_type", "字段类型");
                writer.addHeaderAlias("enu_yn_flag", "是否枚举");
                writer.addHeaderAlias("enu_value", "选项值");
                writer.addHeaderAlias("enu_code", "选项代码");
                writer.addHeaderAlias("enu_bk_expl", "选项说明");

                // 合并单元格后的标题行,使用默认标题样式
//                writer.merge(2, group.getGroup_name());
                // 一次性写出内容,使用默认样式,强制输出标题
                writer.write(row, true);
            }
            groups.forEach(group -> {

            });
            // 关闭writer,释放内存
            writer.close();
            output.setExportDicPath(path);
        }
        return output;
    }

ABC

上一篇:Python操作csv,Excel,word


下一篇:如何把Dataframe保存到EXCEL的不同SHEET中!