POI 3.17 + POI-OOXML 3.15导入Excel（含去全角空格）

POI 3.17 + POI-OOXML 3.15导入Excel

ExcelUtil.class

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.google.common.base.CharMatcher;
import org.apache.commons.lang.StringUtils;
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.CellType;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtil<T> {
	
	Class<T> clazz;

	public ExcelUtil(Class<T> clazz) {
		this.clazz = clazz;
	}
	public static final String EXCEL_XLS = "xls";  
	public static final String EXCEL_XLSX = "xlsx";  
	public static final String EXCEL_ET = "et";  
	public static final String EXCEL_EXT = "ext";  
	
	public List<T> importExcel(MultipartFile file,int dataRow) {
		Errors errors = new Errors();
		//定义一个集合保存导入的数据
		List<T> list = new ArrayList<T>();
		// 得到类的所有field.
		Field[] allFields = clazz.getDeclaredFields();
		// 定义一个map用于存放  excel中列的名称和 实体类中属性的对应关系
		Map<String, Field> fieldsMap = new HashMap<String, Field>(allFields.length);
		for (Field field : allFields) {
			// 将有注解的field存放到map中.
			if (field.isAnnotationPresent(ExcelAttribute.class)) {
				ExcelAttribute attr = field.getAnnotation(ExcelAttribute.class);
				//获取类中对应excel上列的名字
				String name = attr.column();
				field.setAccessible(true);// 设置类的私有字段属性可访问.
				fieldsMap.put(name, field);
			}
		}
		InputStream input = null;
		//获取源文件名
		String originalName = file.getOriginalFilename();
		//获取文件后缀
		if(StringUtils.isEmpty(originalName)){
			//System.out.println("源文件名:"+originalName);
		    throw new ErrorException("-1","源文件名不能为空");
		}
		String ext = originalName.substring(originalName.lastIndexOf(".")+1);
		//定义总列数  从title中获取
		int cellNum;
		
		try {
			input = file.getInputStream();
			//根据不同后缀创建Excel工作簿
			Workbook workbook = this.getWorkbook(input, ext);
			Sheet sheet = workbook.getSheetAt(0);
			//获取总行数
			int rows = sheet.getPhysicalNumberOfRows();
			if (rows > dataRow) {// 有数据时才处理 并且除去第一行的title
				
				//通过获取第一行title
				//查询 excel中每一列的列数对应的列名称  并且存入map中  如 1:姓名  2:年龄
				Row title = sheet.getRow(dataRow-1);
				cellNum = title.getPhysicalNumberOfCells();
				Map<Integer, String> titleMap = new HashMap<Integer, String>(cellNum);
				int k = 0;
				for (Cell cell : title) {
					titleMap.put(k, cell.getStringCellValue());
					k++;
				}
				
				// 从第2行开始取数据,默认第一行是表头.
				for (int i = dataRow; i < rows; i++) {
					Row row = sheet.getRow(i);
					if (row==null) {
						break;
					}
					T entity = null;
					for (int j = 0; j < cellNum; j++) {
						//根据excel列号获取列名称
						String colName = titleMap.get(j);
						if(colName!=null&&!"".equals(colName)) {
							//根据列名称获取对应的属性
							Field field = fieldsMap.get(colName);
							//System.out.println(colName);
							if(field == null){
								continue;
							}
							// 取得类型,并根据对象类型设置值.
							Class<?> fieldType = field.getType();
							
							Cell cell = row.getCell(j);
							 
							//是否可为空
							boolean nullable = field.getAnnotation(ExcelAttribute.class).nullable();
							if (cell == null && !nullable) {
								errors.put("-1", "第"+(i+1)+"行的"+colName+"为空");
							}
							//将excel中所有的数据类型按格式先转成String类型
							String c = "";
							if (cell !=null) {
								c = String.valueOf(getCellValue3(cell));
							}
		
							// 如果不存在实例则新建.
							entity = (entity == null ? clazz.newInstance() : entity);
							if (String.class == fieldType) {
								field.set(entity, String.valueOf(c));
							} else if ((Integer.TYPE == fieldType)
									|| (Integer.class == fieldType)) {
								field.set(entity, Integer.parseInt(c));
							} else if ((Long.TYPE == fieldType)
									|| (Long.class == fieldType)) {
								//System.out.println(Long.valueOf(c));
								field.set(entity, Long.valueOf(c));
							} else if ((Double.TYPE == fieldType)
									|| (Double.class == fieldType)) {
								if ("".equals(c)) {
									c = "0.0";
								}
								try {
									field.set(entity, Double.valueOf(c));
								} catch (NumberFormatException e) {
									throw new ErrorException("第"+(i+1)+"行的"+colName+"错误");
								}
								
							} else if (BigDecimal.class == fieldType) {
								field.set(entity, new BigDecimal(c));
							}
						}
					}
					list.add(entity);
				}
			}
 
		} catch (IOException e) {
			throw new ErrorException("-2","上传失败!");
		}catch (IllegalArgumentException e) {
			throw new ErrorException("-2","上传失败!");
		} catch (IllegalAccessException e) {
			throw new ErrorException("-2","上传失败!");
		} catch (InstantiationException e) {
			throw new ErrorException("-2","上传失败!");
		} catch (ErrorException e) {
			throw new ErrorException("-1",e.getErrors());
		} finally {
			try {
			    if(input!=null){
			        input.close();
			    }
			} catch (IOException e) {
				throw new ErrorException("-2","上传失败!");
			}
		}
		if (!errors.isEmpty()) {
			throw new ErrorsException(errors);
		}
		return list;
	}
	
	/**
	 * 根据不同的格式生成不同的workbook
	 * @param input
	 * @param ext
	 * @return
	 * @throws IOException 
	 * @throws Exception
	 */
	private Workbook getWorkbook(InputStream input, String ext) throws ErrorException, IOException {
		Workbook work = null;
		
		if(ExcelUtil.EXCEL_XLS.equals(ext)){
			work=new HSSFWorkbook(input);//2003 版本的excel
		}else if(ExcelUtil.EXCEL_XLSX.equals(ext)) {
			work=new XSSFWorkbook(input);//2007 版本的excel
		}else if(ExcelUtil.EXCEL_ET.equals(ext)) {
			work=new HSSFWorkbook(input);//e
		}else if(ExcelUtil.EXCEL_EXT.equals(ext)) {
			work=new HSSFWorkbook(input);//e
		}else {
			throw new ErrorException("只支持.et,.ext,.xls.,xlsx格式的文件!");
		}
		return work;
	}
	
	
	/**
	 * 描述：对表格中数值进行格式化
	 * @param cell
	 * @return
	 * */
	public Object getCellValue(Cell cell) {
		Object value = null;
		//日期格式化
		//cell.setCellType(CellType.STRING);
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
		if(cell != null ) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = this.StringTrim(cell.getStringCellValue());
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if("General".equals(cell.getCellStyle().getDataFormatString())) {
					//value = cell.getNumericCellValue();
					BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
					value = bd.toPlainString();
				}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					value = sdf.format(cell.getDateCellValue());
				}else if(HSSFDateUtil.isCellDateFormatted(cell)){
					Date date = cell.getDateCellValue();
					value = sdf.format(date);				
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				value = "";
				break;
			default:
				break;
			}
		}		
		return value;
	}
	
	
	public Object getCellValue2(Cell cell) {
        Object value = null;
        //日期格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = this.StringTrim(cell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC: //公式类型
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        //value = cell.getNumericCellValue();
                        BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
    					value = bd.toPlainString();
                    }
                    if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        //value = cell.getNumericCellValue();
                        BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
    					value = bd.toPlainString();
                    } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                        value = sdf.format(cell.getDateCellValue());
                    } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        value = sdf.format(date);
                    } else {
                        //value = cell.getNumericCellValue();
                        BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
    					value = bd.toPlainString();
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
        }
        return value;
    }

	public static String getCellValue3(Cell cell) {
		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}
		// 判断数据的类型
		switch (cell.getCellTypeEnum()) {
			case NUMERIC: // 数字
				//short s = cell.getCellStyle().getDataFormat();
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
					SimpleDateFormat sdf;
					// 验证short值
					if (cell.getCellStyle().getDataFormat() == 14) {
						sdf = new SimpleDateFormat("yyyy/MM/dd");
					} else if (cell.getCellStyle().getDataFormat() == 21) {
						sdf = new SimpleDateFormat("HH:mm:ss");
					} else if (cell.getCellStyle().getDataFormat() == 22) {
						sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
					} else {
						sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					}
					Date date = cell.getDateCellValue();
					cellValue = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
					cell.setCellType(CellType.STRING);
					cellValue = StringTrim(String.valueOf(cell.getRichStringCellValue().getString()));
				}
				break;
			case STRING: // 字符串
				cellValue = StringTrim(String.valueOf(cell.getStringCellValue()));
				break;
			case BOOLEAN: // Boolean
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			case FORMULA: // 公式
				try {
					cellValue = StringTrim(String.valueOf(cell.getStringCellValue()));
				} catch (Exception exception) {
					cellValue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			case BLANK: // 空值
				cellValue = null;
				break;
			case ERROR: // 故障
				cellValue = "非法";
				break;
			default:
				cellValue = "未知";
				break;
		}
		return cellValue;
	}

	/**
	 * @Description: 自定义去空格 excel专用     poi导入的字符串空格ASCll值：160，而空格（Space）的ASCII码值是：32
	 * @Author: Jack.Zhao
	 * @Date: 2020-10-27 14:44:57
	 */
	public static String StringTrim(String str){
		if(null != str) {
			return BcConvertUtil.qjToBj(str).replaceAll("[\\s\\u00A0]+"," ").trim();
		}
		return str;
	}

}

ExcelAttribute.class

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ java.lang.annotation.ElementType.FIELD })
public @interface ExcelAttribute {


	/**
	 * 配置excel中列的名称(title)
	 */
	public abstract String column();
	
	
	/**
	 * 是否能为空
	 */
	public abstract boolean nullable();
	
}

BcConvertUtil.class

import org.apache.commons.lang.StringUtils;

import java.util.regex.Pattern;

/**
 * 字符串半角，全角处理
 * @author TerLee
 */
public class BcConvertUtil {

    /**
     * ASCII表中可见字符从!开始，偏移位值为33(Decimal)
     * 半角!
     */
    static final char DBC_CHAR_START = 33;

    /**
     * ASCII表中可见字符到~结束，偏移位值为126(Decimal)
     *  半角~
     */
    static final char DBC_CHAR_END = 126;

    /**
     * 全角对应于ASCII表的可见字符从！开始，偏移值为65281
     * 全角！
     */
    static final char SBC_CHAR_START = 65281;
    /**
     * 全角对应于ASCII表的可见字符到～结束，偏移值为65374
     * 全角～
     */
    static final char SBC_CHAR_END = 65374;
    /**
     * ASCII表中除空格外的可见字符与对应的全角字符的相对偏移
     * 全角半角转换间隔
     */
    static final int CONVERT_STEP = 65248;

    /**
     * 全角空格的值，它没有遵从与ASCII的相对偏移，必须单独处理
     * 全角空格 12288
     */
    static final char SBC_SPACE = 12288;
    /**
     * 半角空格的值，在ASCII中为32(Decimal)
     * 半角空格
     */
    static final char DBC_SPACE = ' ';

    /**
     * 全角字符转半角字符
     * @return 半角字符，去空格
     */
    public static String qjToBj(String str){
        if (StringUtils.isBlank(str)){
            return "";
        }
        StringBuilder buf = new StringBuilder(str.length());
        char[] ca = str.toCharArray();
        for (char c : ca) {
            // 如果位于全角！到全角～区间内
            if (c >= SBC_CHAR_START && c <= SBC_CHAR_END) {
                buf.append((char) (c - CONVERT_STEP));
            } else if (c == SBC_SPACE) {
                // 如果是全角空格
                buf.append(DBC_SPACE);
            } else {
                // 不处理全角空格，全角！到全角～区间外的字符
                buf.append(c);
            }
        }
        return buf.toString();
    }

    /**
     * 半角字符串转全角字符串
     * @param src 入参
     * @return 全角字符
     */
    public static String bjToQj(String src) {
        if (StringUtils.isBlank(src)){
            return "";
        }
        StringBuilder buf = new StringBuilder(src.length());
        char[] ca = src.toCharArray();
        for (char c : ca) {
            if (c == DBC_SPACE) {
                // 如果是半角空格，直接用全角空格替代
                buf.append(SBC_SPACE);
            } else if ((c >= DBC_CHAR_START) && (c <= DBC_CHAR_END)) {
                // 字符是!到~之间的可见字符
                buf.append((char) (c + CONVERT_STEP));
            } else {
                // 不对空格以及ascii表中其他可见字符之外的字符做任何处理
                buf.append(c);
            }
        }
        return buf.toString();
    }

    /**
     * 判断字符串是否含有全角或者空格
     * @param content 入参
     * @return true：不含有，false:含有
     */
    public static boolean containQjChar(String content){
        if (StringUtils.isBlank(content)){
            return true;
        }
        String spaceChar = " ";
        if (content.contains(spaceChar)){
            return false;
        }
        String pattern = "[\\x00-\\xff]+";
        return Pattern.matches(pattern, content);
    }

    public static void main(String[] args) {
        String content = "111　 ＠ADDＢＴＴ％＾＆";
        String content1 = "111@a123";
        String ret = BcConvertUtil.qjToBj(content);
        boolean a = BcConvertUtil.containQjChar(content1);
        ret = ret.replace(" ", "");
        System.out.println("符合要求? " + ret);
        System.out.println("符合要求? " + a);
    }
}