json/excel文件上传下载工具方法汇总-爱代码爱编程

浏览器下载json文件

    @Operation(summary = "设备模型导出(带分组)")
    @PostMapping("/export")
    public void exportWithGroup(HttpServletRequest request, HttpServletResponse response) {
        var user = SecurityUtils.getCurrentUser();
        bizTypeService.exportWithGroup(request, response, user);
    }
    
     @Override
    public void exportWithGroup(HttpServletRequest request, HttpServletResponse response, SecurityUser user) {
        // 1. 导出的数据
        ExportDto exportDto = new ExportDto();
        exportDto.setTypes(types);
        exportDto.setGroups(groups);
        // 2. 下载
        PrintWriter writer = null;
        try {
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("content-disposition", "download;filename*=utf-8''" + URLEncoder.encode("device_model_type.json", "UTF-8"));
            writer = response.getWriter();
            writer.write(JacksonUtils.writeValueAsString(exportDto)); // 转成string
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            writer.close();
        }
    }

浏览器下载excel文件【Workbook】

详见ExcelUtils

     @Override
    public void exportRunningRecords(
  HttpServletRequest request, HttpServletResponse response) {
        // 1.  查询数据     
        List<RunningRecordsDto> data = resultDto.getItems();
        // 2. 导出数据
        List<String> headers = new ArrayList<>();
        headers.add("设备名称");
        headers.add("设备编号");
        headers.add("所属模型");
        headers.add("安装位置");
        headers.add("服务范围");
        headers.add("运行次数");
        headers.add("运行时长");
        List<List<Object>> dataList = new ArrayList<>();
        for (RunningRecordsDto item : data) {
            List<Object> list = new ArrayList<>();
            list.add(item.getName());
            list.add(item.getCode());
            list.add(item.getTypeName());
            list.add(item.getLocationName());
            list.add(item.getServiceArea());
            list.add(item.getCounts());
            list.add(item.getDuration());
            dataList.add(list);
        }
        try {
        // 最后一个参数说明
        // false: 普通excel文件，无任何格式
        // true: excel文件第一行为黄色背景，最后一列字体为红色
            ExcelUtils.generateCreateExcel(headers, dataList, "设备运行记录.xlsx", request, response, true);
        } catch (Exception e) {
            log.error("设备运行记录导出异常", e);
        }
    }

public class ExcelUtils {
    /**
     * 导出最精简的excel.
     *
     * @param headers       (不是必填) excel头列 比如: 姓名  年龄  性别 ..
     * @param datas         (必填) 数据列(请确保和列的顺序保持一致)
     * @param fileName      生成excel的文件名称，如果不传则默认为随机生成
     * @param request       HttpServletRequest
     * @param response      HttpServletResponse
     * @param isCustomStyle 是否自定义样式
     * @throws IOException io异常
     */
    public static void generateCreateExcel(List<String> headers, List<List<Object>> datas, String fileName,
                                           HttpServletRequest request, HttpServletResponse response, Boolean isCustomStyle) throws Exception {
        Workbook workbook = generateWorkBook(headers, datas, isCustomStyle);
        // 文件名处理一下
        //设置编码
        fileName = new String(((StringUtils.isBlank(fileName) ? UUID.randomUUID().toString() : fileName) + "."
                + XSSFWorkbookType.XLSX.getExtension()).getBytes("UTF-8"), "ISO-8859-1");

        String userAgent = request.getHeader("User-Agent");
        if (userAgent.toUpperCase().contains("MSIE") || userAgent.toUpperCase().contains("RV:11")) {
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.displayName());
        } else {
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        }
        response.reset(); // 清空response
        response.setContentType(HttpHeaderEnum.FILE_DOWNLOAD_XLSL.getValue());
        response.setHeader("content-disposition", "attachment;filename=" + fileName);
        response.setCharacterEncoding("UTF-8");
        // 文件流输出
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new IOException("could not write to response. cause: ", e);
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
    }

    /**
     * 生成一个workbook.
     *
     * @param headers       (不是必填) excel头列 比如: 姓名  年龄  性别 ..
     * @param data          数据列(请确保和列的顺序保持一致)
     * @param isCustomStyle 是否自定义样式
     * @return workbook对象
     */
    public static Workbook generateWorkBook(List<String> headers, List<List<Object>> data, Boolean isCustomStyle) {
        XSSFWorkbook book = null;
        // 先创建一发book,并建一个sheet
        book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet();
        CellStyle cellStyle1 = book.createCellStyle();
        CellStyle cellStyle2 = book.createCellStyle();
        if (isCustomStyle) {
            // 设置第一行背景颜色为黄色
            cellStyle1.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 设置最后一列字体为红色
            Font font = book.createFont();
            font.setColor(IndexedColors.RED.getIndex());
            cellStyle2.setFont(font);
            cellStyle2.setAlignment(HorizontalAlignment.LEFT);
            cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        // 设置自适应列宽
        List<Integer> maxCalls = getMaxCall(headers, data);
        for (int i = 0, j = maxCalls.size(); i < j; i++) {
            // 最大列宽设置
            if (maxCalls.get(i) > 30) {
                sheet.setColumnWidth(i, 30 * 256);
            } else {
                sheet.setColumnWidth(i, maxCalls.get(i) * 256);
            }
        }

        // 是否有头，如果有则先把表头建好
        if (CollectionUtils.isNotEmpty(headers)) {
            // 创建第一行，表头行
            XSSFRow titleRow = sheet.createRow(0);
            XSSFCell titleCell = null;
            XSSFRichTextString titleText = null;
            // 把表头放到第一个行里面
            for (int i = 0; i < headers.size(); i++) {
                titleCell = titleRow.createCell(i);
                // 给创建的单元格里面设值
                titleText = new XSSFRichTextString(headers.get(i));
                titleCell.setCellValue(titleText);
                if (isCustomStyle) {
                    titleRow.getCell(i).setCellStyle(cellStyle1);
                }
            }
        }
        // 处理内容
        if (CollectionUtils.isNotEmpty(data)) {
            // 如果有列表头则row重1开始。否则重0开始
            int dataRowIdx = CollectionUtils.isNotEmpty(headers) ? 1 : 0;
            XSSFRow dataRow = null;
            XSSFCell dataCell = null;
            List<Object> dataList = null;
            // 开始处理行和单元格
            for (int i = 0; i < data.size(); i++) {
                // 创建内容的行
                dataRow = sheet.createRow(i + dataRowIdx);
                dataList = data.get(i);
                // 将内容放到对应的行中
                for (int j = 0; j < dataList.size(); j++) {
                    // 有多少个内容就有多少个单元格
                    dataCell = dataRow.createCell(j);
                    // 设值单元格的值
                    setValue(book, sheet, dataCell, dataList.get(j));
                }
            }
        }
        if (isCustomStyle) {
            int lastColumnIndex = headers.size() - 1;
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                if (row != null) {
                    XSSFCell cell = row.getCell(lastColumnIndex);
                    if (cell != null) {
                        cell.setCellStyle(cellStyle2);
                    }
                }
            }
        }
        return book;
    }

浏览器导入json文件【ObjectMapper】

   
    @Operation(summary = "设备模型导入(带分组)")
    @PostMapping("/import")
    public ApiResultDto<BizTypeImportResDto> importWithGroup(MultipartFile file) {
        var user = SecurityUtils.getCurrentUser();
        return bizTypeService.importWithGroup(file, user);
    }

	@Override
    @Transactional(rollbackFor = Exception.class)
    public ApiResultDto<BizTypeImportResDto> importWithGroup(MultipartFile file, SecurityUser user) {
        // 1. 读取文件内容
        ObjectMapper objectMapper = new ObjectMapper();
        ExportModelDto exportModelDto = null;
        try {
            exportModelDto = objectMapper.readValue(file.getInputStream(), ExportModelDto.class);
        } catch (IOException e) {
            return PagingResultDto.failed(ApiErrorCode.DATA_CORRUPTION, "json数据格式不正确");
        }
   }

浏览器导入excel文件【Workbook】

	 @Operation(summary = "批量新增-导入excel")
    @PostMapping("/batchAdd")
    public ApiResultDto<?> batchAdd(MultipartFile file) {
        SecurityUser user = SecurityUtils.getCurrentUser();
        return locationService.batchAdd(file, user);
    }

	// 具体实现
	@Transactional(rollbackFor = Exception.class)
    public ApiResultDto<?> batchAdd(MultipartFile file, SecurityUser user) {
        UUID projectId = SecurityUtils.getCurrentUser().getProjectId();
        // 1. EXCEL文件校验
        Workbook workbook;
        try {
            String originalFilename = file.getOriginalFilename();
            if (StringUtils.isBlank(originalFilename)) {
                return ApiResultDto.failed(ApiErrorCode.VALID_FAILED, "文件名称有误");
            }
            String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
            if (StringUtils.equals(ModelConstants.EXCEL_POSTFIX, suffix)) {
                workbook = new XSSFWorkbook(file.getInputStream());
            } else if (StringUtils.equals(ModelConstants.EXCEL_POSTFIX_XLS, suffix)) {
                workbook = new HSSFWorkbook(file.getInputStream());
            } else {
                return ApiResultDto.failed(ApiErrorCode.BAD_REQUEST, "文件格式错误，支持xls和xlsx格式文件");
            }
        } catch (Exception e) {
            log.error("errMsg", e);
            return ApiResultDto.failed(ApiErrorCode.BUSINESS_FAILURE, "文件读取失败");
        }
        // 2. 获取EXCEL文件内容
        Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
        List<LocationDto> dtos = new ArrayList<>();
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            final String type = ExcelUtils.getStringCellValue(workbook, row.getCell(0));
            String bName = ExcelUtils.getStringCellValue(workbook, row.getCell(1));
            String bSerialNum = ExcelUtils.getStringCellValue(workbook, row.getCell(2));
            String fName = ExcelUtils.getStringCellValue(workbook, row.getCell(3));
            String fSerialNum = ExcelUtils.getStringCellValue(workbook, row.getCell(4));      
            LocationDto dto = new LocationDto();
            dto.setRemark(remark);
            dto.setSort(StringUtils.isNotBlank(sort) ? Integer.valueOf(sort) : null);
            dto.setArea(Objects.nonNull(area) ? Double.valueOf(area) : null);
            dtos.add(dto);
        }
        // 3. 具体业务
        return ApiResultDto.success();
    }

ResourceLoader读取类路径下单个json

@Slf4j
@Component
public class ProjectDataBaseInitTask implements ApplicationRunner {

   /**
    * 资源加载器.
    */
   @Autowired
   private ResourceLoader resourceLoader;

   @Override
   public void run(ApplicationArguments args) {
       // 1. 读取json文件
       InitDbDataDto initDbData = getInitDbDataFromPath("classpath:init/init_property.json");
       if (Objects.isNull(initDbData)) {
           return;
       }
   }

   /**
    * 读取json文件.
    *
    * @param path 文件路径.
    * @return 初始化数据类.
    */
   private InitDbDataDto getInitDbDataFromPath(String path) {
       if (StringUtils.isEmpty(path)) {
           return null;
       }
       Resource resource = resourceLoader.getResource(path);
       if (!resource.exists()) {
           log.error("初始化数据不存在");
           return null;
       }
       InitDbDataDto initDbData = null;
       try {
           InputStream inputStream = resource.getInputStream();
            // 判断当前可读取的字节数
            if (inputStream.available() == 0) {
                continue;
            }
           ObjectMapper objectMapper = new ObjectMapper();
           initDbData = objectMapper.readValue(inputStream, InitDbDataDto.class);
       } catch (IOException e) {
           log.error("读取json文件错误");
           throw new RuntimeException(e);
       }
       return initDbData;
   }
}

ResourceLoader读取类路径下所有json文件

	/**
     * 读取classpath:init文件夹下所有json文件.
     *
     * @param path 文件路径.
     * @return 初始化数据类.
     */
    private List<InitDbDataDto> getInitDataFromPath(String path) {
        List<InitDbDataDto> initDbDataDtos = new ArrayList<>();
        // 1. 读取类路径指定文件夹下所有文件
        PathMatchingResourcePatternResolver resourceLoader = new PathMatchingResourcePatternResolver();
        Resource[] resources;
        try {
            resources = resourceLoader.getResources(path);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        if (Objects.isNull(resources)) {
            return initDbDataDtos;
        }
        // 2. 解析数据ObjectMapper
        for (Resource resource : resources) {
            InitDbDataDto initDbData = null;
            try {
                InputStream inputStream = resource.getInputStream();
                 // 判断当前可读取的字节数
                if (inputStream.available() == 0) {
                    continue;
                }
                ObjectMapper objectMapper = new ObjectMapper();
                initDbData = objectMapper.readValue(inputStream, InitDbDataDto.class);
                initDbDataDtos.add(initDbData);
            } catch (IOException e) {
                log.error("读取json文件错误");
                throw new RuntimeException(e);
            }
        }
        return initDbDataDtos;
    }
    List<InitDbDataDto> initDbDatas = getInitDataFromPath("classpath:init/*");