excel导入（双表头）

2023-11-24 14:41:31

 

    本人开发过程中遇到这样一个需求，导入上文双表头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 readExcel(Sheet sheet, Class clazz,int titleRow){List list = new ArrayList<>();//获取标题栏Map category = opposite(getCategory(sheet,titleRow));//遍历标题栏Iterator> entries = category.entrySet().iterator();while (entries.hasNext()){Map.Entry 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 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 getCategory(Sheet sheet,int titleRow){Map category = new HashMap();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 opposite(Map category){Map map = new HashMap<>();Iterator> entries = category.entrySet().iterator();while (entries.hasNext()){Map.Entry 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 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 importDate(MultipartFile file, HttpServletRequest request) throws Exception {List 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 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;} 

 
                        

                        

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！

                        
                        

                    

                    



                    


    
    




                    

    

    




                    

    

        
        

            
相关文章

        

                

            
Duilib中list控件支持ctrl和shif多行选中的实现

        

                

            
[ICML2015]Batch Normalization:Accelerating Deep Network Training by Reducing Internal Covariate Shif

        

                

            
win10系统 微软输入法 于eclipse ctrl+shif+f冲突间接处理办法

        

                

            
Codeforces Round #259 (Div. 2) B. Little Pony and Sort by Shif

        

                

            
读LDD3，内存映射与DMA--PAGE_SHIF…

        

                

            
VMware虚拟机安装XP【要先分区，再设置BOOT 启动CD，shif+上移】

        

                

            
更换iBus五笔的左与右Shif

        

                

            
sublime ctrl+shif+f 没用解决办法

        

                

            
idea 对 ctrl + z 的撤销 是 ctrl + shif + z

        

                

            
计算机最早的设计师应用于,计算机应用基础选择题doc.doc

        

                

            
win10自带截图神器：Win+Shift+S

        

                

            
Python基础之文件目录操作

        

                

            
python简述目录_Python基础之文件目录操作(示例代码)

        

                

            
tp5 如何做数据采集

        

                

            
任务2-7(服务器字体+阿里巴巴矢量库)

        

                

            
html标签（1)：h1~h6,p,br,pre,hr

        

                

            
TI 电量计介绍与芯片选型指南

        

                

            
几款TI电源芯片简介

        

                

            
TI DSP芯片C2000系列读取FLASH数据

        

                

            
德州仪器(Ti)平台嵌入式开发基础

        

                

            
TI三相电机智能栅极驱动芯片特点分类

        

                

            
省选模拟（12.08） T3 圈圈圈圈圈圈圈圈

        

                

            
Hadoop生态圈技术栈（上）

        

                

            
大数据开发基础入门与项目实战（三）Hadoop核心及生态圈技术栈之6.Impala交互式查询

        

                

            
小猿圈之Linux下Mysql 操作命令

        

                

            
大数据Hadoop生态圈常用面试题

        

                

            
大数据开发基础入门与项目实战（三）Hadoop核心及生态圈技术栈之4.Hive DDL、DQL和数据操作

        

                

            
备战Noip2018模拟赛11（B组）T3 Monogatari 物语

        

                

            
【智能优化算法-圆圈搜索算法】基于圆圈搜索算法Circle Search Algorithm求解单目标优化问题附matlab代码

        

                

            
NYOJ 78 圈水池

        

                

            
递归问题 跑道 汽车 绕圈问题 Python实现

        

                

            
Hadoop生态圈（三）：MapReduce