# Excel操作规范

Thu, Mar 10, 2022 10:21 AM

为了更好的操作Excel, 框架中对Excel中的内容进行了注解化封装，如果不能满足需求， 可以通过poi操作而不是提供的kratos中对应excel操作的组件。
  
 首选定义Bean的数据结构，每一条bean对应Excel中的一行数据，通过@ExcelColumn进行标记
```java
@Getter @Setter
public class T1Bean {
  
  @ExcelColumn(column = "A", title = "ID")
  private String id;

  
  @ExcelColumn(column = "B", title = "NAME")
  private String name;

  
  @ExcelColumn(column = "C", title = "AGE")
  private String age;

  
  @ExcelColumn(column = "D", title = "CONTENT", width = 30)
  private String content;

}

```
  
 写出Excel
 ```java
   public void test1() throws IOException {
    List<T1Bean> list = new ArrayList<>();
    
    for (int i = 0; i < 100; i++) { // 生成100条测试数据
      T1Bean bean = new T1Bean();
      bean.setId(i + "");
      bean.setName("T" + bean.getId());
      bean.setAge(i + 1 + "");
      bean.setContent("测试内容：" + bean.getName());
      
      list.add(bean);
    }
    // 写出到Excel中
    ExcelWriter<T1Bean> ewr = ExcelWriter.EW(T1Bean.class);
    ewr.write("target/t1.xlsx", "T1Bean", list);
  }

}
```
同时我们可以重写ExcelWirter，完成流写出问题。
  
 读取Excel
```java
   public void test2() throws Exception {
    List<T1Bean> list = new ArrayList<>();

    FileInputStream file = new FileInputStream("target/t2.xlsx");
    ExcelReader<T1Bean> err = ExcelReader.create(T1Bean.class);
    err.process(file, (idx, row) -> list.add(row));
  }
```
 
Excel的读取和写出可以更加灵活对ExcelWriter和ExcelReader重载，这里只提供最基本的操作方法。
 
推荐Spring boot框架下通用写法。这里对文件名通过User-Agent进行了修正，已防止浏览器下载时候名字乱码问题。
```java

public class ExcelUploadUtils {
    private ExcelUploadUtils() {
    }

    /**
     * 导出excel, 增加备注标题，如果没有备注内容，以"null"填充
     * @param <T>
     * @param filename
     * @param sheetname
     * @param beans
     * @param beanClass
     * @param remark
     */
    public static <T> void wirte(String filename, String sheetname, List<T> beans, Class<T> beanClass, String remark) {
        // request and response
        ServletRequestAttributes attrs = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes());
        // attrs is nullable
        filename = HttpUtils.reviseFileName(filename, attrs.getRequest().getHeader("User-Agent")); // 修正名字， 防止乱码
        attrs.getResponse().setContentType("application/vnd.ms-excel;charset=UTF-8"); // Excel类型
        attrs.getResponse().setHeader("Content-Disposition", "attachment; filename=" + filename + ".xlsx"); // 导出文件名

        // excel writer to output
        try (OutputStream out = attrs.getResponse().getOutputStream()) {
            IRowBeanParser<T> parser = ExcelBeanFactory.getBeanParser(beanClass);
            ExcelWriterOutputStream<T> exw = new ExcelWriterOutputStream<>(parser);
            if (remark != null) {
                exw.setTitleCreator((sheetName, rowNum, titleStyle, sheet) -> 
                    createRemarkTitle(parser, sheetName, rowNum, titleStyle, sheet, remark), false);
            }
            exw.setWrapTextByContext(true);
            exw.process(out, sheetname, beans);
        } catch (IOException e) {
            throw new ErrorCodeException(200, ShowType.ShowWarn, "B_TENANT_EXCEL-EXPORT", "导出发生异常：" + e.getMessage());
        } catch (Exception e) {
            throw new ErrorCodeException(200, ShowType.ShowError, "B_TENANT_EXCEL-EXPORT", "导出发生异常：" + e.getMessage());
        }
    }

    /**
     * 配置remark
     * @param parser
     * @param sheetName
     * @param rowNum
     * @param titleStyle
     * @param sheet
     * @param value
     * @return
     */
    public static int createRemarkTitle(IRowBeanParser<?> parser, String sheetName, int rowNum, CellStyle titleStyle, Sheet sheet, String value) {
        CellStyle remarkStyle = sheet.getWorkbook().createCellStyle();
        remarkStyle.setWrapText(true);
        remarkStyle.setAlignment(HorizontalAlignment.LEFT);
        remarkStyle.setVerticalAlignment(VerticalAlignment.TOP);

        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellStyle(remarkStyle);
        cell.setCellValue(value);
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, parser.getColumnPropertys().size() - 1);
        sheet.addMergedRegion(cra);

        long lineCnt = value.chars().filter(v -> v == '\n').count() + 1; // 统计换行符
        row.setHeight((short) (lineCnt * 300));

        return rowNum + 1;
        
    }

    /**
     * 导入excel， 增加读取起始行，解决读取不是从第一行读取的问题
     * @param <T>
     * @param filename
     * @param beanClass
     * @param startRow
     * @param sheetName
     * @return
     */
    public static <T> List<T> read(String filename, Class<T> beanClass, Integer startRow, String... sheetName) {
        // request and response
        ServletRequestAttributes attrs = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes());
        if (attrs == null) {
            return Collections.emptyList();
        }
        // attrs is nullable
        MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) attrs.getRequest()
                .getAttribute(MultipartHttpServletRequest.class.getName());
        if (mRequest == null) {
            return Collections.emptyList();
        }
        MultipartFile file = mRequest.getFile(filename);
        if (file == null) {
            return Collections.emptyList();
        }

        try {
            List<T> data = new ArrayList<>();
            ExcelReader<T> exr = ExcelReader.create(beanClass);
            if (startRow != null) {
                exr.setStartRowNum(startRow);
            }
            try (InputStream in = file.getInputStream()) {
                exr.process(in, (idx, row) -> data.add(row), sheetName);
            }
            return data;
        } catch (/* IOException | FileUploadException */ Exception e) {
            throw new ErrorCodeException(200, ShowType.ShowError, "B_TENANT_EXCEL-IMPORT", "导入发生异常：" + e.getMessage());
        }
    }

    /**
     * 导出excel， 增加读取起始行，解决读取不是从第一行读取的问题
     * @param <T>
     * @param filename
     * @param beanClass
     * @param rowCallback
     * @param startRow
     * @param sheetname
     * @throws Exception
     */
    public static <T> void read(String filename, Class<T> beanClass, IRowCallback<T> rowCallback, Integer startRow, String... sheetname)
            throws Exception {
        // request and response
        ServletRequestAttributes attrs = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes());
        if (attrs == null) {
            return;
        }
        // attrs is nullable
        MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) attrs.getRequest()
                .getAttribute(MultipartHttpServletRequest.class.getName());
        if (mRequest == null) {
            return;
        }
        MultipartFile file = mRequest.getFile(filename);
        if (file == null) {
            return;
        }

        ExcelReader<T> exr = ExcelReader.create(beanClass);
        if (startRow != null) {
            exr.setStartRowNum(startRow);
        }
        try (InputStream in = file.getInputStream()) {
            exr.process(in, rowCallback, sheetname);
        }
    }
}

```
使用部分的内容
```java
// 业务中执行Excel导出
//=================================================================================================================
List<DirUserExcelDTO> dtos = new ArrayList<>(); // 获取通讯录内容
SimpleDateFormat sdf = new SimpleDateFormat("yyyMMdd-HHmmss");
ExcelUtils.wirte("通讯录-" + sdf.format(new Date()), "通讯录", dtos, DirUserExcelDTO.class);

// 业务中执行Excel导入
//=================================================================================================================
List<DirUserExcelDTO> users = ExcelUploadUtils.read("file", DirUserExcelDTO.class, "通讯录");
if (users.isEmpty()) {
    return Result.buildFailure("B_TENANT_EXCEL-EMPTY", "导入的Excel为空");
}
users.forEach(v -> log.info(v.getName()));

// 业务中执行Excel导入, 流形式
//=================================================================================================================
try {
    ExcelUploadUtils.read("file", DirUserExcelDTO.class, (idx, row) -> log.info(row.getName()), "通讯录");
} catch (Exception e) {
    throw new ErrorCodeException(200, ShowType.ShowError, "B_TENANT_EXCEL-IMPORT", "导入发生异常：" + e.getMessage());
}
return Result.buildSuccess();

// 定义Bean
public class DirUserExcelDTO /*implements IdxSetter*/ {

    private String code;

    @ExcelColumn(column = "A", title = "序号", width=4)
    private Integer idx;    // 序号
    @ExcelColumn(column = "B", title = "编码", width=13)
    private String ccde;
    @ExcelColumn(column = "C", title = "姓名", width=10)
    private String name;
    @ExcelColumn(column = "D", title = "手机", width=13)
    private String phone;
    @ExcelColumn(column = "E", title = "邮箱", width=16)
    private String email;
    @ExcelColumn(column = "F", title = "邀请码", width=8)
    private String icode;
    @ExcelColumn(column = "G", title = "部门", width=20)
    private String dept;
    @ExcelColumn(column = "H", title = "公司职务", width=8)
    private String post;
    @ExcelColumn(column = "I", title = "特殊身份", width=8)
    private String privilege;
    @ExcelColumn(column = "J", title = "认证状态", width=8)
    private String status;
    
    @ExcelColumn(column = "K", title = "应用直属角色", width=20)
    private String userRoles;
    @ExcelColumn(column = "L", title = "部门授权角色", width=20)
    private String deptRoles;
    @ExcelColumn(column = "M", title = "部门继承角色", width=20)
    private String deptRole0;
}
```