本人开发过程中遇到这样一个需求,导入上文双表头excel。我尝试用过easyPoi,但是由于二级表头名称一样(养老保险----养老保险),故失败。故自己先了一个工具,解决思路:
1.原生POI读出双表头名(代扣个人款项-养老保险、公司承担款项-养老保险)、对应列数(第几列)
2.手动写一个注解,用来标识excel某一列对应实体类某个字段,作用类似于easyPoi中的@excel
/**
* 个人承担养老保险
*/
@ExcelMerge(columnName = "代扣个人款项-养老保险", filed = "endowmentIns")
private BigDecimal endowmentIns = BigDecimal.ZERO;
3.对比表头和实体类的注解,利用反射创建实体类并赋值
具体代码如下:
1.注解
package com.accfun.fssc.common.anno;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by yyc on 2018/12/21.
*/
@Target({ElementType.FIELD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelMerge {
String columnName();
String filed();
}
2.实体类
/**
* 职员姓名
*/
@ExcelMerge(columnName = "姓名", filed = "cPsnName")
private String cPsnName;
/**
* 部门编码
*/
private String cDeptCode;
/**
* 部门名称
*/
@ExcelMerge(columnName = "部门", filed = "cDeptName")
private String cDeptName;
/**
* 个税起征点
*/
@ExcelMerge(columnName = "个税免征额", filed = "taxThreshold")
private BigDecimal taxThreshold = BigDecimal.ZERO;
/**
* 基本工资
*/
@ExcelMerge(columnName = "工资总额-工资", filed = "basicSalary")
private BigDecimal basicSalary = BigDecimal.ZERO;
3.工具类
package com.accfun.fssc.common.utils;
import com.accfun.easyui.framework.poi.excel.entity.result.ExcelImportResult;
import com.accfun.fssc.common.anno.ExcelMerge;
import com.accfun.fssc.invoice.model.ExpSalary;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
/**
* Created by yyc on 2018/11/28.
*/
@Slf4j
public class ExcelUtil {
/**
* 原生poi获取excel指定行、列的值
* @param model
* @param sheet
* @param row
* @param cell
* @return
*/
public static String getTitleValue(ExcelImportResult<?> model, int sheet, int row, int cell){
String value = "";
if(model.getWorkbook().getSheetAt(sheet) != null &&
model.getWorkbook().getSheetAt(sheet).getRow(row) != null &&
model.getWorkbook().getSheetAt(sheet).getRow(row).getCell(cell) != null){
value = model.getWorkbook().getSheetAt(sheet).getRow(row).getCell(cell).getStringCellValue();
}
return value;
}
public static List<Object> readExcel(Sheet sheet, Class clazz,int titleRow){
List<Object> list = new ArrayList<>();
//获取标题栏
Map<String,Integer> category = opposite(getCategory(sheet,titleRow));
//遍历标题栏
Iterator<Map.Entry<String, Integer>> entries = category.entrySet().iterator();
while (entries.hasNext()){
Map.Entry<String, Integer> entry = entries.next();
log.info("excel:Key: " + entry.getKey() + ", Value: " + entry.getValue());
Integer value =entry.getValue();
}
for(int i = titleRow+1;i<=sheet.getLastRowNum();i++) {
Row row = sheet.getRow(i);
if(row.getCell(0)==null||StringUtils.isBlank(getCellStringValue(row.getCell(0)))){
return list;
}
Object model = buildTransModel(clazz,row,category);
list.add(model);
}
return list;
}
public static Object buildTransModel(Class clazz,Row row,Map<String,Integer> category){
//获取类
Object model = null;
try {
model = clazz.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
//获取字段
Field[] fields = clazz.getDeclaredFields();
Method method;
for(Field field : fields){
if(field.isAnnotationPresent(ExcelMerge.class)){
ExcelMerge annotion = field.getAnnotation(ExcelMerge.class);
//字段名
String fileName = field.getName();
//注解名
String columnName = annotion.columnName();
//字段类型
Class filedClazz = field.getType();
//获取字段对应excel哪一列
Integer index = category.get(columnName);
if(index == null){
continue;
}
Object value = null;
//根据类型获取excel里的值
if("java.math.BigDecimal".equals(filedClazz.getName())){
value = getVal(row.getCell(index));
}else if("java.lang.String".equals(filedClazz.getName())){
value = getCellStringValue(row.getCell(index));
}else if("java.util.Date".equals(filedClazz.getName())){
value = getValDate(row.getCell(index));
}
method = getMethod(model.getClass(),convertSetter(fileName),filedClazz);
invoke(model,method,value);
//log.info(valStr);
}
}
return model;
}
/**
* 获取每一空,返回BigDecimal
* @param cell
* @return
*/
private static BigDecimal getVal(Cell cell){
String value = getCellStringValue(cell);
if(StringUtils.isBlank(value)){
value = "0";
}
return new BigDecimal(value);
}
/**
* 获取每一空,返回Date
* @param cell
* @return
*/
private static Date getValDate(Cell cell){
Date date = null;
if (cell != null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC:
double dateDouble = cell.getNumericCellValue();
date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(dateDouble);
break;
case XSSFCell.CELL_TYPE_STRING:
String value = String.valueOf(cell.getRichStringCellValue());
try {
date = DateUtils.setDate(value);
} catch (ParseException e) {
e.printStackTrace();
}
break;
}
}
return date;
}
/**
* 获取每一空,返回字符串
* @param cell
* @return
*/
public static String getCellStringValue(Cell cell) {
String cellValue = "";
if(cell == null){
return cellValue;
}
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING://字符串类型
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue="";
break;
case XSSFCell.CELL_TYPE_NUMERIC: //数值类型
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA: //公式
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
cellValue="";
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
break;
case XSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
}
public static Map<Integer,String> getCategory(Sheet sheet,int titleRow){
Map<Integer,String> category = new HashMap<Integer, String>();
if(sheet == null){
return category;
}
int mergedRegions = sheet.getNumMergedRegions();
Row row2 = sheet.getRow(0);
for(int j = 0 ; j < mergedRegions; j++ ){
CellRangeAddress rangeAddress = sheet.getMergedRegion(j);
int firstRow = rangeAddress.getFirstColumn();
int lastRow = rangeAddress.getLastColumn();
category.put(firstRow, lastRow+"-"+row2.getCell(firstRow).toString());
}
//便利每一行
for( int rowNum = titleRow ; rowNum <= titleRow ; rowNum++ ){
System.out.println();
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
short lastCellNum = row.getLastCellNum();
String cate = "";
Integer maxIndex = 0;
//遍历每一空
for( int col = 0 ; col < lastCellNum ; col++ ){
Cell cell = row.getCell(col);
int columnIndex = cell.getColumnIndex();
if(cell == null || "".equals(cell.toString())){
String value = category.get(columnIndex);
category.put(columnIndex, StringUtils.isNotBlank(value)?value.substring(value.indexOf("-")+1):"");
continue;
}
String string = category.get(columnIndex);
if(string != null && !string.equals("")){
String[] split = string.split("-");
if(split.length<2){
continue;
}
cate = split[1];
maxIndex = Integer.parseInt(split[0]);
category.put(columnIndex,cate+"-"+cell.toString());
}else {
//如果当前便利的列编号小于等于合并单元格的结束,说明分类还是上面的分类名称
if(columnIndex<=maxIndex){
if(StringUtils.isBlank(cate)){
category.put(columnIndex,cell.toString());
}else{
category.put(columnIndex,cate+"-"+cell.toString());
}
}else {
if(StringUtils.isBlank(cate)){
category.put(columnIndex,cell.toString());
}else{
category.put(columnIndex,cate+"-"+cell.toString());
}
}
}
}
}
return category;
}
/**
* 把Map钟key value对换
* @param category
* @return
*/
private static Map<String,Integer> opposite(Map<Integer,String> category){
Map<String,Integer> map = new HashMap<>();
Iterator<Map.Entry<Integer, String>> entries = category.entrySet().iterator();
while (entries.hasNext()){
Map.Entry<Integer, String> entry = entries.next();
map.put(entry.getValue(),entry.getKey());
}
return map;
}
/**
* 获取get方法
*
* @param fieldName
* @return
*/
private static String convertGetter(String filedName){
return "get".concat(convertGetSet(filedName));
}
/**
* 获取set方法
*
* @param fieldName
* @return
*/
private static String convertSetter(String fieldName) {
return "set".concat(convertGetSet(fieldName));
}
/**
* 构建get set 方法体(不带 set get)
*
* @param fieldName
* @return
*/
private static String convertGetSet(String fieldName){
char c = fieldName.charAt(0);
char c1 = fieldName.charAt(1);
if(Character.isLowerCase(c)&&Character.isLowerCase(c1)){
return String.valueOf(c).toUpperCase().concat(fieldName.substring(1));
}
return fieldName;
}
/**
*
* @param clazz
* @param fieldName
* @param parameterTypes
* @return
*/
private static Method getMethod(Class clazz,String fieldName,Class<?>...parameterTypes){
Method method = null;
try {
method = clazz.getMethod(fieldName,parameterTypes);
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return method;
}
/**
* 无参执行方法
*
* @param o 需先实例化
* @param method
* @return
*/
private static void invoke(Object o, Method method, Object value){
try {
method.invoke(o,value);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
4.读取excel
@RequestMapping(path = "importInXls", method = RequestMethod.POST)
public PcApiJson importInXls(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setLastOfInvalidRow(1);
try {
List<ExpSalary> list = importDate(file, request);
if (list == null || list.size() <= 0) {
throw new AccfunMessageException("年月不能为空");
}
salaryService.importInXls(list);
} catch (AccfunMessageException e) {
return new PcApiJson(PcApiJson.PcJsonCode.FAIL, e.getMessage());
} catch (Exception e) {
e.printStackTrace();
return new PcApiJson(PcApiJson.PcJsonCode.FAIL, e.getMessage());
}
return new PcApiJson(PcApiJson.PcJsonCode.SUCCESS, "导入成功");
}
public List<ExpSalary> importDate(MultipartFile file, HttpServletRequest request) throws Exception {
List<ExpSalary> list = new ArrayList<>();
//不确定excel是03版本还是07版本
Workbook wbs = null;
try {
wbs = new XSSFWorkbook(file.getInputStream());
}catch (Exception e){
wbs = new HSSFWorkbook(file.getInputStream());
}
Sheet childSeet = wbs.getSheetAt(0);
List<Object> list2 = ExcelUtil.readExcel(childSeet, ExpSalary.class,1);
String showSalaryEditionName = childSeet.getSheetName();
String showSalaryEdition = "";
if("简易版工资表".equals(showSalaryEditionName)){
showSalaryEdition = "1";
}else if("详细版工资表".equals(showSalaryEditionName)){
showSalaryEdition = "2";
}else{
throw new AccfunMessageException("Excel工作簿名称错误");
}
for (Object model : list2) {
ExpSalary salary = (ExpSalary) model;
salary.setShowSalaryEdition(showSalaryEdition);
list.add(salary);
}
return list;
}