常用的POI格式设置以及使用POI生成EXCEL文件并转为MultipartFile类型上传到服务器

1. 常用的POI格式设置以及使用POI生成EXCEL文件并转为MultipartFile类型上传到服务器

        // 根据数据设置文档大小
        Integer dataCount = purGoodsModelDtoMap.size() + 12;
        //创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个工作表
        HSSFSheet sheet = workbook.createSheet("表格");

        // 设置列宽
        Integer width = 1820;
        sheet.setColumnWidth(0, 600);
        sheet.setColumnWidth(1, width);
       
        PrintSetup ps = sheet.getPrintSetup();
        //设置用纸
        ps.setPaperSize(PrintSetup.A4_PAPERSIZE);
        // 调整为一页
        sheet.setAutobreaks(true);

        // 创建表格大小
        POIUtil.createAllRow(sheet, 0, dataCount, false);
        int rowIndex = 0;
        int colIndex = 0;

        // 创建行
        HSSFRow row = sheet.getRow(rowIndex);
        row.setHeight((short) 700);
        
        // 创建单元格
        HSSFCell cell = row.createCell(colIndex + 1);

        // 单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        Font baseFont = workbook.createFont();
        baseFont.setFontName("华文中宋");
        baseFont.setFontHeightInPoints((short)20);
        baseFont.setBold(true);
        // 设置字体
        cellStyle.setFont(baseFont);
        // 水平方向居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 允许换行
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);
        // 设置文件标题单元格
        POIUtil.setCellValue("aa\r\nbb", cell);
        // 单元格合并
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 2, colIndex + 1, colIndex + 6));

        // 左对齐\右对齐 华文宋体 11号
        HSSFCellStyle cellStyleCommon = workbook.createCellStyle();
        HSSFCellStyle cellStyleCommonRight = workbook.createCellStyle();
        Font baseFontCommon = workbook.createFont();
        baseFontCommon.setFontName("华文宋体");
        baseFontCommon.setFontHeightInPoints((short)11);
        baseFontCommon.setBold(false);
        cellStyleCommon.setFont(baseFontCommon);
        cellStyleCommon.setAlignment(HorizontalAlignment.LEFT);
        cellStyleCommonRight.setFont(baseFontCommon);
        cellStyleCommonRight.setAlignment(HorizontalAlignment.RIGHT);
        
        // 华文宋体 12号，设置表格边框
        HSSFCellStyle cellStyleTable = workbook.createCellStyle();
        Font tableFontCommon = workbook.createFont();
        tableFontCommon.setFontName("华文宋体");
        tableFontCommon.setFontHeightInPoints((short)12);
        tableFontCommon.setBold(false);
        cellStyleTable.setFont(tableFontCommon);
        cellStyleTable.setAlignment(HorizontalAlignment.LEFT);
        cellStyleTable.setBorderBottom(BorderStyle.MEDIUM);
        cellStyleTable.setBorderLeft(BorderStyle.MEDIUM);
        cellStyleTable.setBorderTop(BorderStyle.MEDIUM);
        cellStyleTable.setBorderRight(BorderStyle.MEDIUM);

        // 文件名
        String fileName = storeOut.getStoreId().toString() + ".xls";

        // 生成文件
        try {
            uploadFile(fileName, workbook, storeOut);
        } catch (IOException e) {
            log.info("上传附件失败 ->{}",e);
        }

private void uploadFile(String fileName, HSSFWorkbook workbook, PurStoreOut storeOut) throws IOException {
        // 处理文件
        CloseableHttpClient httpClient = HttpClients.createDefault();
        try {
            // 需要将workbook通过流对象转为MultipartFile，不然直接转为workbook的字节生成的文件无法打开
            FileItemFactory factory = new DiskFileItemFactory(16, null);
            FileItem fileItem = factory.createItem("textField", "text/plain", true, fileName);
            OutputStream os = fileItem.getOutputStream();
            workbook.write(os);
            os.close();
            MultipartFile multipartFile = new CommonsMultipartFile(fileItem);

            // 请求文件上传服务
            HttpEntity reqEntity =
                    MultipartEntityBuilder.create().addPart("token", new StringBody(fileUploadToken, ContentType.TEXT_PLAIN))
                            .addPart("resize", new StringBody("false", ContentType.TEXT_PLAIN))
                            .addPart("file", new ByteArrayBody(multipartFile.getBytes(), fileName)).build();
            HttpPost post = new HttpPost(fileUploadUrl + "/file_upload");
            post.setEntity(reqEntity);
            post.setConfig(RequestConfig.custom().setSocketTimeout(10000).setConnectTimeout(30000).build());
            HttpResponse response = httpClient.execute(post);

            // 获取上传结果
            HttpEntity entity = response.getEntity();
            String result = EntityUtils.toString(entity, "UTF-8");
            post.releaseConnection();
            log.info("文件上传结果={}", result);

            JSONObject res = JSON.parseObject(result);
            FileServiceResp uploadResp = JSONObject.toJavaObject(res, FileServiceResp.class);
            if (res.getInteger("code") == 1) {
                log.info("上传成功, url->{}", uploadResp.getUrl());
            }

            // 获取文件名 保存到数据库
            
        } catch (Exception e) {
            log.info("文件生成异常->{}",e);
        } finally {
            httpClient.close();
        }
    }