Excel/word导入导出（域）

导入
```
@ApiOperation(value = "导入文件", notes = "导入文件")
  @RequestMapping(value = "/importFile", method = {RequestMethod.POST})
  public ResultBean<Boolean> importFile(HttpServletRequest request, HttpServletResponse response) {
      MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
      MultipartFile multipartFile = multipartHttpServletRequest.getFile("file");
      try {
          return ResultUtil.success(kxDictionaryService.importFile(multipartFile));
      } catch (IOException e) {
          return ResultUtil.error();
      }
  }
```
```
@Override
  public boolean importFile(MultipartFile multipartFile) throws IOException {
      // 临时文件
      File tempFile = new File(customConfigProperty.getTempDirPath()+ File.separator + "康熙管理临时文件.xlsx");
      multipartFile.transferTo(tempFile);
      EasyExcel.read(tempFile, KxDictionaryExcel.class, new ExcelDataListener<KxDictionaryExcel>(kxDictionaryMapper)).headRowNumber(1).sheet(0).doRead();
      return true;
  }
```
```java public class ExcelDataListener extends AnalysisEventListener {

private Logger logger = LoggerFactory.getLogger(getClass());

// 每隔1000条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 private static final int BATCH_COUNT = 1000;

List list = new ArrayList();

private ExcelMapper excelMapper;

/**
- 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 */ public ExcelDataListener(ExcelMapper excelMapper) { this.excelMapper = excelMapper; }
  
  /**
- 这个每一条数据解析都会来调用 */ @Override public void invoke(T data, AnalysisContext context) { logger.info(“解析到一条数据:{}”, JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) {
```
 saveData();
 // 存储完成清理 list
 list.clear();
```
  } }
  
  /**
- 所有数据解析完成了都会来调用 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); logger.info(“所有数据解析完成！”); }
  
  /**
- 加上存储数据库 */ private void saveData() { logger.info(“{}条数据，开始存储数据库！”, list.size()); excelMapper.saveExcelData(list); logger.info(“存储数据库成功！”); }

}


- 导出excel
```java
@ApiOperation(value = "导出Excel")
    @RequestMapping(value="/exportExcel", method = {RequestMethod.POST})
    public void exportExcel(@RequestBody String[] ary){
        try {
            kxDictionaryService.exportExcel(Arrays.asList(ary));
        } catch (Exception e) {
            logger.error("导出Excel数据异常",e);
            e.printStackTrace();
        }
    }

@Override
    public void exportExcel(List<String> list) {
        List<KxDictionary> data = baseMapper.selectBatchIds(list);
        // 临时文件路径
        File tempFilePath = new File(customConfigProperty.getTempDirPath());
        if(!tempFilePath.exists()) {
            tempFilePath.mkdirs();//创建目录
        }
        // 临时文件
        String temporaryFileName = customConfigProperty.getTempDirPath() + File.separator + "康熙管理.xlsx";
        // 模板文件
        String templateFileName = FileUtils.getPath() + File.separator + "康熙管理模板.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(temporaryFileName).withTemplate(templateFileName).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(data, writeSheet);
        // 关闭流
        excelWriter.finish();
        try {
            FileDownLoad.download(temporaryFileName);
        } catch (BaseException e) {
            e.printStackTrace();
        }
    }

导出word

@ApiOperation(value = "导出word")
  @RequestMapping(value="/exportWord", method = {RequestMethod.POST})
  public void exportWord(@RequestBody AncientTones entity){
      try {
          searchIndexService.exportWord(entity);
      } catch (Exception e) {
          logger.error("导出word数据异常",e);
          e.printStackTrace();
      }
  }

@Override
  public void exportWord(AncientTones entity) {
      if (!StringUtils.isEmpty(entity.getYears())){
          entity.setCoding(this.handle(entity.getYears()));
      }
      List<AncientTones> data = searchIndexMapper.queryExcelData(entity);
      List<Map<String, Object>> list = new ArrayList<>();
      String str = "";
      for (int i = 0; i < data.size(); i++) {
          str = (i+1) + "、" + data.get(i).getChinesePrefix();
          if (!StringUtil.isBlank(data.get(i).getAntiCut())){
              str += "，" + data.get(i).getAntiCut();
          }
          if (!StringUtil.isBlank(data.get(i).getCitation())){
              str += "。" + data.get(i).getCitation();
          }
          Map<String, Object> map = new HashMap<>();
          map.put("contexts",str);
          list.add(map);
      }
      Map<String, Object> dataMap = new HashMap<>();
      dataMap.put("list",list);
      String name = "古音检索记录.doc";
      try {
          InputStream templateStream = new ClassPathResource("template/古代音读.doc").getInputStream();
          InputStream asposeLicenseXmlStream = new ClassPathResource("license.xml").getInputStream();
          String path = customConfigProperty.getBaseFilePath() + name;
          boolean success = WordTempateUtil.wordAsAspose(asposeLicenseXmlStream, templateStream, path, "", dataMap);
          FileDownLoad.download(path);
      } catch (IOException | BaseException e) {
          e.printStackTrace();
          throw new RuntimeException("获取文件内容失败！" + e.getMessage());
      }
  }

```java package com.sw.anc.utils;

import com.aspose.words.Document; import com.aspose.words.License; import org.apache.commons.lang.StringUtils; import org.springframework.core.io.ClassPathResource;

import java.io.File; import java.io.InputStream; import java.util.Map;

/**

@author 王健
@version 1.0.0
@description word导出工具类
@since 2019/12/19 1.0.0 */ public class WordTempateUtil {

/**
- @param stream 许可证文件流
- @return boolean 是否成功
- @create 王健 2019/12/19
- @description 检测许可 */ public static boolean getLicense(InputStream stream) { boolean result = false; License license = new License(); try {
```
 license.setLicense(stream);
 result = true;
```
  } catch (Exception e) {
```
 e.printStackTrace();
```
  } return result; }
  
  /**
- @return java.io.InputStream 许可证文件流
- @create 王健 2019/12/19
- @description 获取许可证文件流 */ public static InputStream getAsposeLicenseXmlStream() { InputStream asposeLicenseXmlStream = null; try {
```
 asposeLicenseXmlStream = new ClassPathResource("lib/license.xml").getInputStream();
```
  } catch (Exception e) {
```
 e.printStackTrace();
```
  } return asposeLicenseXmlStream; }
  
  /**
- @param licenseStream 许可证文件流
- @param templateStream 模板文件流
- @param destdoc 存储位置
- @param ImagePath 图片路径
- @param map 数据
- @return boolean 是否转换成功
- @create 王健 2019/12/19
- @description 将数据填充至模板 */ public static boolean wordAsAspose(InputStream licenseStream, InputStream templateStream, String destdoc, String ImagePath, Map map) { if (!getLicense(licenseStream)) {
```
 return false;
```
  } try {
```
 Document doc = new Document(templateStream);
 new WordTemplateParser(doc, map, ImagePath).parse();
 File file = new File(destdoc);
 String parent = file.getParent();
 if (StringUtils.isNotEmpty(parent)) {
     File parentFile = new File(parent);
     if (!parentFile.exists()) {
         parentFile.mkdirs();
     }
 }
 if (!file.exists()) {
     file.createNewFile();
 }
 doc.save(destdoc);
 return true;
```
  } catch (Exception e) {
```
 e.printStackTrace();
 throw new RuntimeException("归档发生异常!");
```
  } }

}

```java
package com.sw.anc.utils;
import com.aspose.words.*;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.sw.common.util.UUIDUtil;
import org.apache.commons.lang.StringUtils;
import java.util.*;
import java.util.List;
/**
*@author 王健
*@since 2019/12/19 1.0.0
*@version 1.0.0
*@description word模板转换类
*/
public class WordTemplateParser {
    private Document doc;
    private Map<String, Object> data = Maps.newHashMap();
    private String[] fieldNames;
    private String imageFolder;
    /**
 * @create 王健 2019/12/19
 * @param doc 文档
 * @param data 数据
 * @param imageFolder 图片文件夹
 * @description 构造函数
 */
    public WordTemplateParser(Document doc, Map<String, Object> data, String imageFolder) {
        super();
        this.doc = doc;
        this.data = data;
        this.imageFolder = imageFolder;
        try {
            this.fieldNames = this.doc.getMailMerge().getFieldNames();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * @create 王健 2019/12/19
     * @description 模板解析函数
     */
    public void parse() throws Exception {
        this.doc.getMailMerge().setFieldMergingCallback(new AsposeWordMergeHandle("Image", this.imageFolder));
        this.mergeExecute(Arrays.asList(this.fieldNames), this.data);
        this.buildTables();
    }
    /**
     * @create 王健 2019/12/19
     * @param fieldNameList 字段集合
     * @param data 数据
     * @description 合并域数据
     */
    private void mergeExecute(List<String> fieldNameList, Map<String, Object> data) throws Exception {
        List<String> executeFieldNameList = Lists.newArrayList();
        List<Object> valueList = Lists.newArrayList();
        for (int i = 0; i < fieldNameList.size(); i++) {
            String fieldName = fieldNameList.get(i);
            if(fieldName.startsWith("Table")) continue;
            if(fieldName.startsWith("Image")) {
                fieldName = fieldName.substring("Image".length() + 1).trim();
                valueList.add(this.getMapValueByKey(fieldName.split(":")[0], data, String.class));
            }
            else {
                valueList.add(this.getMapValueByKey(fieldName, data, String.class));
            }
            executeFieldNameList.add(fieldName);
        }
        this.doc.getMailMerge().execute(executeFieldNameList.toArray(new String[executeFieldNameList.size()]), valueList.toArray(new Object[valueList.size()]));
    }
    /**
     * @create 王健 2019/12/19
     * @description 合并表格数据
     */
    @SuppressWarnings("unchecked")
    private void buildTables() throws Exception {
        Map<String, List<String>> tableNameChildrenMap = this.fieldNameGroup(Arrays.asList(this.fieldNames));
        Set<String> keySet = tableNameChildrenMap.keySet();
        for (String tableName : keySet) {
            DataTable dataTable = new DataTable(tableName);
            DataSet dataSet = new DataSet();
            dataSet.getTables().add(dataTable);
            this.buildDataTable(dataSet, dataTable, tableNameChildrenMap.get(tableName), "", this.getMapValueByKey(tableName, this.data, List.class));
            this.doc.getMailMerge().executeWithRegions(dataSet);
        }
    }
    /**
     * @create 王健 2019/12/19
     * @param dataSet 数据集
     * @param dataTable 数据表格
     * @param fieldNameList 字段集合
     * @param parent 父级
     * @param data 数据
     * @description 递归创建表格数据
     */
    @SuppressWarnings("unchecked")
    private void buildDataTable(DataSet dataSet, DataTable dataTable, List<String> fieldNameList, String parent, List<Map<String, Object>> data) throws Exception {
        addTableColumn(dataTable, fieldNameList);
        addTableColumnData(dataTable, parent, data);
        if(data == null || data.isEmpty()) return;
        dataSet.getTables().add(dataTable);
        Map<String, List<String>> tableNameChildrenMap = fieldNameGroup(fieldNameList);
        Set<String> keySet = tableNameChildrenMap.keySet();
//        boolean hasTableMerge = false;
        for (String tableName : keySet) {
            DataTable dataTableChild = dataSet.getTables().get(tableName);
            if(dataSet.getTables().get(tableName) == null) {
                dataTableChild = new DataTable(tableName);
                dataSet.getTables().add(dataTableChild);
            }
            for (Map<String, Object> map : data) {
                buildDataTable(dataSet, dataTableChild, tableNameChildrenMap.get(tableName), (String)map.get("id"), this.getMapValueByKey(tableName, map, List.class));
                // 关联数据并渲染
                if(dataTableChild.getRows().getCount() == 0) continue;
            }
        }
        dataSet.getTables().forEach(dataTableChild -> {
            dataSet.getRelations().add(new DataRelation(dataTable.getTableName() + "-relation-" + UUIDUtil.getUUID(), dataTable, dataTableChild, new String[] {"id"}, new String[] {"parent"}));
        });
    }
    /**
     * @create 王健 2019/12/19
     * @param dataTable 数据表格
     * @param columnNameList 列集合
     * @description 添加表格的列
     */
    private void addTableColumn(DataTable dataTable, List<String> columnNameList) {
        dataTable.getColumns().add("id"); // 主键
        dataTable.getColumns().add("guid");
        dataTable.getColumns().add("parent"); // 父节点
        dataTable.getColumns().add("row_index"); // 行号
        int tableDeep = 0;
        for (String columnName : columnNameList) {
            if(columnName.startsWith("TableStart")) {
                tableDeep ++;
            }
            else if(columnName.startsWith("TableEnd")) {
                tableDeep --;
            }
            else {
                if(tableDeep == 0) {
                    if(columnName.startsWith("Image")) {
                        columnName = columnName.substring("Image".length() + 1).trim();
                    }
                    dataTable.getColumns().add(columnName);
                }
            }
        }
    }
    /**
     * @create 王健 2019/12/19
     * @param fieldNameList 字段集合
     * @return java.util.Map<java.lang.String,java.util.List<java.lang.String>> 转换结果
     * @description 转换字段数据
     */
    private Map<String, List<String>> fieldNameGroup(List<String> fieldNameList) {
        Map<String, List<String>> tableNameChildrenMap = Maps.newHashMap();
        String tableName = null;
        boolean hasTableStartSign = false;
        for (int i = 0; i < fieldNameList.size(); i++) {
            String fieldName = fieldNameList.get(i);
            if(hasTableStartSign) {
                if(fieldName.startsWith("TableEnd")
                        && fieldName.substring("TableEnd".length() + 1).equals(tableName)) {
                    hasTableStartSign = false; // 标志table结束
                }
                else {
                    tableNameChildrenMap.get(tableName).add(fieldName);
                }
            }
            else {
                if(fieldName.startsWith("TableStart")) {
                    tableName = fieldName.substring("TableStart".length() + 1);
                    if(!tableNameChildrenMap.containsKey(tableName)) {
                        tableNameChildrenMap.put(tableName, Lists.newArrayList());
                    }
                    hasTableStartSign = true;
                }
            }
        }
        return tableNameChildrenMap;
    }
    /**
     * @create 王健 2019/12/19
     * @param dataTable 数据表格
     * @param parent 父级
     * @param data 数据
     * @description 添加表格数据
     */
    private void addTableColumnData(DataTable dataTable, String parent, List<Map<String, Object>> data) throws Exception {
        if(data == null || data.isEmpty()) {
//            DocumentBuilder builder = new DocumentBuilder(this.doc);
//            builder.moveToMergeField(dataTable.getTableName());
//            builder.insertHtml("aaaa");
            DataRow dataRow = dataTable.newRow();
            if(!StringUtils.isBlank(parent)) {
                String id = UUIDUtil.getUUID();
                dataRow.set(0, id); // id
                dataRow.set(2, parent); // parent
            }
            dataTable.getRows().add(dataRow);
            return;
        }
        int rowIndex = 0; // 行号
        for (Map<String, Object> map : data) {
            rowIndex ++;
            map.put("id", UUIDUtil.getUUID());
            map.put("parent", parent);
            map.put("row_index", rowIndex); // 设置行号
            DataRow dataRow = dataTable.newRow();
            DataColumnCollection dataColumnCollection = dataTable.getColumns();
            Iterator<DataColumn> iterator = dataColumnCollection.iterator();
            int index = 0;
            while(iterator.hasNext()) {
                DataColumn dataColumn = iterator.next();
                String[] columnNameSplit = dataColumn.getColumnName().split(":");
                dataRow.set(index, map.get(columnNameSplit[0]));
                index ++;
            }
            dataTable.getRows().add(dataRow);
        }
    }
    /**
     * @create 王健 2019/12/19
     * @param fieldName 字段名
     * @param map 数据集合
     * @param clazz 类型
     * @return T 返回结果
     * @description 转换数据
     */
    @SuppressWarnings("unchecked")
    private <T> T getMapValueByKey(String fieldName, Map<String, Object> map, Class<T> clazz) {
        if(map.containsKey(fieldName)) return ((T) map.get(fieldName));
        return null;
    }
}

Excel/word导入导出 （域）