excel 导入

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**

• ImportRepositoryService 导入数据库处理逻辑
  */
  public class ImportRepositoryService {

  /**

  • 打印日志
    */

  private static final Logger logger = LoggerFactory
  .getLogger(ImportRepositoryService.class);

  /**

  • 写入数据库的方法类
    */
    private ImportRepositoryMapper importRepositoryMapper;

  /**

  • 数据库中表的列数
    */
    private int line;

  /**

  • 上传文件的路径
    */
    private String path;

  /**

  • 对应的实体类
    */
    private String type;

  /*对应表中列名的数组
  *
  /
  private String[] lineWord;
  /

  • 空格
    /
    private static final String[] SPACE_LIST={" “,” “,” "};
    /*
  • 空字符串
    */
    private static final String[] EMPTY_LIST={"","",""};

  /**

  • 文件的解析方法
  • @param str str
  • @return String
    */
    @SuppressWarnings(value = { “rawtypes”, “unchecked” })
    public String xlsMethod(String str) {
    File file = new File(path);
    Class<? extends Object> clazz = null;
    try (FileInputStream fs = FileUtils.openInputStream(file)) {
    Workbook workbook = null;
    if (XzzfTableConsts.XLS.equals(str)) {
    workbook = new HSSFWorkbook(fs);
    }
    if (XzzfTableConsts.XLSX.equals(str)) {
    workbook = new XSSFWorkbook(fs);
    }
    Sheet hs = workbook.getSheetAt(0);
    int first = hs.getFirstRowNum();
    int last = hs.getLastRowNum();
    List list = new ArrayList();
    for (int j = first + 1; j <= last; j++) {
    Row row = hs.getRow(j);
    if (row == null) {
    continue;
    //return XzzfTableConsts.EMPTY;
    }
    int firstCellNum = row.getFirstCellNum();
    int lastCellNum = row.getLastCellNum();
    int table = lastCellNum - firstCellNum + 1;
    clazz = (Class<? extends Object>) getClazz(clazz);
    Object t = creatObject(clazz);
    for (int k = firstCellNum; k < lastCellNum; k++) {
    if (line != table) {
    return XzzfTableConsts.MISTAKE;
    }
    Cell cell = row.getCell(k);
    if (!putValue(cell, k, t, clazz)) {
    return XzzfTableConsts.CHECKDATE;
    }
    }
    list.add(t);
    }
    return addInfo(list);
    } catch (IOException e) {
    logger.error(“读取文件时候，输入输出流发生的异常{}”, e.getMessage());
    }
    return XzzfTableConsts.EMPTY;
    }

  /**

  • 获得实体类
  • @param clazz 对应的表实体类
  • @return 实体类
    */
    public Object getClazz(Class<? extends Object> clazz) {
    try {
    clazz = Class.forName(type);
    } catch (ClassNotFoundException e) {
    logger.error(“类找不到错误{}”, e.getMessage());
    }
    return clazz;
    }

  /**

  • 构建传来实体类的对象的方法
  • @param clazz 对应的表实体类
  • @return 返回实体类的一个对象
    */
    public Object creatObject(Class<? extends Object> clazz) {
    Object t = null;
    try {
    t = clazz.newInstance();
    } catch (InstantiationException | IllegalAccessException
    | NullPointerException e) {
    logger.error(“根据{}，找不到对应的实体类或实体对象创建失败{}”, type, e.getMessage());
    return t;
    }
    String cid = CommonUtils.getUuid();
    Method method = setInfo(lineWord[0], cid, clazz);
    try {
    method.invoke(t, cid);
    } catch (IllegalAccessException | IllegalArgumentException
    | InvocationTargetException e) {
    logger.error(“主键的值没有写进去该实体类对象中{}”, e.getMessage());
    }
    return t;
    }

  /**

  • 把值放到数组中的方法

  • @param cell 传入excel表格

  • @param k 数组下标

  • @param t 对应的表实体类对象

  • @param clazz 对应的表实体类

  • @return 返回判断值是否符合日期要求
    */
    public boolean putValue(Cell cell, int k, Object t,
    Class<? extends Object> clazz) {
    if (cell == null) {
    return true;
    }
    String value = getDate(cell);
    boolean b = checkDate(value, k + 1,clazz);
    if (!b) {
    return false;
    }
    Method method = setInfo(lineWord[k + 1], value, clazz);
    try {
    // 日期替换 时间错误
    // String values= StringUtils.replaceEach(value,SPACE_LIST,EMPTY_LIST);
    // method.invoke(t, values);

     method.invoke(t, value);
    

    } catch (IllegalAccessException | IllegalArgumentException
    | InvocationTargetException e) {
    logger.error(“主键的值没有写进去该实体类对象中{}”, e.getMessage());
    }
    return true;
    }

  /**
  *

  • 文件写入实体类的方法
  • @param columnName 某个set方法名
  • @param value 读到的值
  • @param clazz 对应的实体类
  • @return 返回该set方法
    */
    public Method setInfo(String columnName, String value,
    Class<? extends Object> clazz) {
    Method[] allMethods = clazz.getDeclaredMethods();
    for (Method method : allMethods) {
    if (method.getName().equalsIgnoreCase(“set” + columnName)) {
    return method;
    }
    }
    return null;
    }

  /**

  • 表格中改成String类型读取
  • @param cell 单元格
  • @return String类型的日期信息和数字信息
    */
    public String getDate(Cell cell) {
    if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
    Date d = cell.getDateCellValue();
    DateFormat formater = new SimpleDateFormat(
    “yyyy-MM-dd HH:mm:ss”);
    return formater.format(d);
    }
    double val = cell.getNumericCellValue();
    return String.valueOf(val);
    }
    return cell.getStringCellValue();
    }

  /**
  *

  • 对日期数字进行判断
  • @param value 单元格内值
  • @param k 表示数值的下标
  • @param clazz clazz
  • @return 返回判断结果
    */
    public boolean checkDate(String value, int k,Class<? extends Object> clazz) {
    Field field=null;
    try {
    field = clazz.getDeclaredField(lineWord[k]);
    } catch (NoSuchFieldException | SecurityException e) {
    logger.error(“判断日期出问题”, e);
    }
    Class<? extends Object> date = field.getType();
    if( date == Date.class) {
    String zz = XzzfTableConsts.DATE;
    Pattern p = Pattern.compile(zz);
    Matcher m = p.matcher(value);
    return m.matches();
    }
    // if (lineWord[k].startsWith(XzzfTableConsts.DATE_BEGIN)) {
    // String zz = XzzfTableConsts.DATE;
    // Pattern p = Pattern.compile(zz);
    // Matcher m = p.matcher(value);
    // return m.matches();
    // }
    return true;
    }

  /**

  • 将list中的值写入数据库中
  • @param list excle表中的数值行集合
  • @return 判断信息
    */
    @SuppressWarnings(“rawtypes”)
    private String addInfo(List list) {
    if (CollectionUtils.isEmpty(list)) {
    return XzzfTableConsts.EMPTY;
    }
    Method me = null;
    try {
    me = importRepositoryMapper.getClass().getDeclaredMethod(
    “insertInfo” + type.substring(type.lastIndexOf(’.’) + 1),
    List.class);
    me.invoke(importRepositoryMapper, list);
    } catch (NoSuchMethodException | SecurityException
    | IllegalAccessException | IllegalArgumentException
    | InvocationTargetException e) {
    logger.error(“没有该对象或实体类，或者没有对应的方法{}”, e.getMessage());
    }
    return XzzfTableConsts.RESULT;
    }

  /**

  • @return the importRepositoryMapper
    */
    public ImportRepositoryMapper getImportRepositoryMapper() {
    return importRepositoryMapper;
    }

  /**

  • @param importRepositoryMapper the importRepositoryMapper to set
    */
    public void setImportRepositoryMapper(
    ImportRepositoryMapper importRepositoryMapper) {
    this.importRepositoryMapper = importRepositoryMapper;
    }

  /**

  • @return the line
    */
    public int getLine() {
    return line;
    }

  /**

  • @param line the line to set
    */
    public void setLine(int line) {
    this.line = line;
    }

  /**

  • @return the path
    */
    public String getPath() {
    return path;
    }

  /**

  • @param path the path to set
    */
    public void setPath(String path) {
    this.path = path;
    }

  /**

  • @return the type
    */
    public String getType() {
    return type;
    }

  /**

  • @param type the type to set
    */
    public void setType(String type) {
    this.type = type;
    }

  /**

  • @return the lineWord
    */
    public String[] getLineWord() {
    return lineWord;
    }

  /**

  • @param lineWord the lineWord to set
    */
    public void setLineWord(String[] lineWord) {
    this.lineWord = lineWord;
    }
    }