2021-2-7[ EasyExcel导出简单操作头部样式调整，转码base64 ]

1. EXCEL导出

 //headList excel头部 data数据内容
    public static void exportExcel(HttpServletResponse response, String filename, List<List<String>> headList, List<List<Object>> data) throws IOException {
        String fileName = URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/x-xls");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        response.setHeader("FileName", fileName + ".xlsx");

        OutputStream outputStream = response.getOutputStream();
        ExcelWriter excelwriter = EasyExcel.write(outputStream).registerWriteHandler(setHorizontalCellStyleStrategy()).registerWriteHandler(new ExcelWidthStyleStrategy()).build();
        WriteTable table = new WriteTable();
        table.setHead(headList);
        //数据5000一个sheet
        List<List<List<Object>>> partition = Lists.partition(data, 5000);

        int i = 1;
        for (List<List<Object>> dt : partition) {
            WriteSheet sheet = EasyExcel.writerSheet(i, "sheet" + i).build();
            excelwriter.write(dt, sheet, table);
            i++;
        }
        //支持多个sheet页，重复以下操作就可以
        // WriteSheet sheet1 = EasyExcel.writerSheet(2, "sheet1").build();
        //  excelwriter.write(data,sheet1,table);

        excelwriter.finish();

    }
    /**
    * 样式处理
    * */
    private static HorizontalCellStyleStrategy setHorizontalCellStyleStrategy(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
       /* //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);*/
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
    
    //设置头部单元格宽度
    public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //设置宽度
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.setColumnWidth(cell.getColumnIndex(), 5000);
    }
}

2.EXCEL转base64,base64转EXCEL

private void addUserIdAndAttachment(List<Map<String, Object>> list, List<List<String>> headList, String userId, List<List<Object>> data) {
        ByteArrayOutputStream out = null;
        try {
            out = new ByteArrayOutputStream();
            ExcelWriter excelwriter = EasyExcel.write(out).registerWriteHandler(setHorizontalCellStyleStrategy()).
                    registerWriteHandler(new ExcelWidthStyleStrategy()).build();
            WriteTable table = new WriteTable();
            table.setHead(headList);
            WriteSheet sheet = EasyExcel.writerSheet(1, "sheet0").build();
            excelwriter.write(data, sheet, table);
            excelwriter.finish();
            InputStream inputStream = new ByteArrayInputStream(out.toByteArray());
            String filestr = FileUtil.fileToBase64(inputStream);
            //附件支持多个 put添加
            JSONObject attachment = new JSONObject();
            attachment.put("Customs Clearance Forecast.xlsx", filestr);
            Map<String, Object> mp = new HashMap<>();
            mp.put("attachments", attachment);
            mp.put("userId", userId);
            list.add(mp);
        } catch (Exception e) {
            log.error("数据转换异常：{}", e);
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    log.error("io关闭异常：{}", e);
                }
            }
        }
    }
    
@Slf4j
public class FileUtil {
    /**
     * @return String
     * @description 将文件转base64字符串
     */
    public static String fileToBase64(InputStream inputStream) {
        // 将文件转化为字节数组字符串，并对其进行Base64编码处理
        byte[] data = null;
        // 读取文件字节数组
        try {
            ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
            byte[] buff = new byte[2048];
            int rc = 0;
            while ((rc = inputStream.read(buff, 0, 100)) > 0) {
                swapStream.write(buff, 0, rc);
            }
            data = swapStream.toByteArray();
        } catch (IOException e) {
           log.error("io流异常：{}",e);
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    log.error("io流关闭失败：{}",e);
                }
            }
        }

        return Base64.encodeBase64String(data);
    }
    /**
     * @param outFilePath 输出文件路径,base64 文件编码字符串,outFileName 输出文件名
     * @return String
     * @description BASE64解码成File文件
     */
    public static void base64ToFile(String outFilePath, String base64, String outFileName) {
        File file = null;
        //创建文件目录
        String filePath = outFilePath;
        File dir = new File(filePath);
        if (!dir.exists() && !dir.isDirectory()) {
            dir.mkdirs();
        }
        BufferedOutputStream bos = null;
        java.io.FileOutputStream fos = null;
        try {
            byte[] bytes = Base64.decodeBase64(base64);
            file = new File(filePath + File.separator + outFileName);
            fos = new java.io.FileOutputStream(file);
            bos = new BufferedOutputStream(fos);
            bos.write(bytes);
        } catch (Exception e) {
            log.error("base64转换成文件失败：{}",e);
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    log.error("io流关闭失败：{}",e);
                }
            }
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    log.error("io流关闭失败：{}",e);
                }
            }
        }
    }

}