个人中心
Erlo

java 导入dbf和excel解析

2025-07-15 10:29:23 发布   44 浏览  
页面报错/反馈
已收藏 点赞

新生导入配置表

点击查看代码 
package com.test.server.utils;

import com.linuxense.javadbf.DBFReader;
import lombok.extern.slf4j.Slf4j;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: dbf工具类
 */
@Slf4j
public class DBFUtils {

    public static List> readDbf(byte[] bytes) {
        List> list = new ArrayList();
        try {
            //读取dbf文件,防止中文乱码
            DBFReader reader = new DBFReader(new ByteArrayInputStream(bytes), StandardCharsets.ISO_8859_1);
            list = readDbfStream(reader);
        } catch (Exception e) {
            log.error("读取dbf文件失败", e);
        }
        return list;
    }

    //读取dbf文件转换为List>
    public static List> readDbf(String filePath) {
        List> list = new ArrayList();
        try {
            //读取dbf文件
            DBFReader reader = new DBFReader(new FileInputStream(filePath));
            list = readDbfStream(reader);
        } catch (Exception e) {
            log.error("读取dbf文件失败", e);
        }
        return list;
    }

    private static List> readDbfStream(DBFReader reader) {
        List> list = new ArrayList();
        try {
            //获取字段数量
            int fieldCount = reader.getFieldCount();
            //获取记录数量
            int recordCount = reader.getRecordCount();
            //遍历记录
            for (int i = 0; i  map = new HashMap();
                //遍历字段
                for (int j = 0; j  {
    private HSSFWorkbook workbook;//excel 对象
    private String title; //表格标题
    private int colWidth = 20; //单元格宽度
    private int rowHeight = 20;//单元格行高度
    private HSSFCellStyle styleHead; //表头样式
    private HSSFCellStyle styleBody; //主体样式
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //日期格式化,默认yyyy-MM-dd HH:mm:ss

    /**
     * 无参数 初始化 对象
     */
    public ExcelTool(){
        this.title="sheet1";
        this.workbook = new HSSFWorkbook();
        init(0);
    }

    /**
     * 有参数 初始化 对象
     * @param title
     * @param colWidth
     * @param rowHeight
     * @param dateFormat
     */
    public ExcelTool(String title,int colWidth,int rowHeight,String dateFormat){
        this.colWidth = colWidth;
        this.rowHeight = rowHeight;
        this.title = title;
        this.workbook = new HSSFWorkbook();
        this.sdf = new SimpleDateFormat(dateFormat);
        init(0);
    }

    public ExcelTool(String title,int colWidth,int rowHeight){
        this.colWidth = colWidth;
        this.rowHeight = rowHeight;
        this.title = title;
        this.workbook = new HSSFWorkbook();
        init(0);
    }
    public ExcelTool(String title,int colWidth,int rowHeight,int flag){
        this.colWidth = colWidth;
        this.rowHeight = rowHeight;
        this.title = title;
        this.workbook = new HSSFWorkbook();
        init(flag);
    }
    public ExcelTool(String title){
        this.title = title;
        this.workbook = new HSSFWorkbook();
        init(0);
    }
    /**ExcelTool 属性 get、set 方法 开始*/
    public int getColWidth() {
        return colWidth;
    }

    public void setColWidth(int colWidth) {
        this.colWidth = colWidth;
    }

    public int getRowHeight() {
        return rowHeight;
    }

    public void setRowHeight(int rowHeight) {
        this.rowHeight = rowHeight;
    }

    public HSSFWorkbook getWorkbook() {
        return this.workbook;
    }

    public void setWorkbook(HSSFWorkbook workbook) {
        this.workbook = workbook;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }


    public HSSFCellStyle getStyleHead() {
        return styleHead;
    }


    public void setStyleHead(HSSFCellStyle styleHead) {
        this.styleHead = styleHead;
    }


    public HSSFCellStyle getStyleBody() {
        return styleBody;
    }


    public void setStyleBody(HSSFCellStyle styleBody) {
        this.styleBody = styleBody;
    }
    /**ExcelTool 属性 get、set 方法 结束*/
    //内部统一调用的样式初始化
    private void init(int styleFlag){
        this.styleHead = this.workbook.createCellStyle();
        this.styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        this.styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        this.styleHead.setRightBorderColor(HSSFColor.BLACK.index);
        this.styleHead.setBottomBorderColor(HSSFColor.BLACK.index);
        if (styleFlag == 1) {
            this.styleBody = this.workbook.createCellStyle();
            this.styleBody.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中ALIGN_CENTER
            this.styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            this.styleBody.setRightBorderColor(HSSFColor.BLACK.index);
            this.styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
            this.styleBody.setBorderRight((short) 1);// 边框的大小
            this.styleBody.setBorderBottom((short) 1);// 边框的大小
        } else {
            this.styleBody = this.workbook.createCellStyle();
            this.styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中ALIGN_CENTER
            this.styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            this.styleBody.setRightBorderColor(HSSFColor.BLACK.index);
            this.styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
            this.styleBody.setBorderRight((short) 1);// 边框的大小
            this.styleBody.setBorderBottom((short) 1);// 边框的大小
        }
    }
    /**
     * 导出表格 无返回
     * @param listTpamscolumn 表头数据
     * @param datas 行内数据
     * @param FilePath 保存路径
     * @param flag
     * @param rowFlag
     * @throws Exception
     */
    public HSSFWorkbook exportExcel(List listTpamscolumn, List datas, String FilePath, boolean flag, boolean rowFlag) throws Exception{
        splitDataToSheets(datas, listTpamscolumn,flag,rowFlag);
        save(this.workbook,FilePath);
        return this.workbook;
    }

    /**
     * 返回workbook
     * @param listTpamscolumn 表头数据
     * @param datas 行内数据
     * @param flag 是否写入行内数据
     * @return
     * @throws Exception
     */
    public HSSFWorkbook exportWorkbook(List listTpamscolumn, List datas , boolean flag) throws Exception{
        splitDataToSheets(datas, listTpamscolumn,flag,false);
        return this.workbook;
    }
    /**
     * 导出表格 有返回值
     * @param listTpamscolumn 表头数据
     * @param datas 行内数据
     * @param flag  只输出表头数据
     * @param rowFlag
     * @return
     * @throws Exception
     */
    public InputStream exportExcel(List listTpamscolumn, List datas, boolean flag, boolean rowFlag) throws Exception {
        splitDataToSheets(datas, listTpamscolumn,flag,rowFlag);
        return save(this.workbook);
    }
    /**
     * 导出Excel,适用于web导出excel
     * @param sheet excel
     * @param data 行内数据
     * @param listTpamscolumn 表头数据
     * @param flag 只输出表头数据
     * @param rowFlag 输出展示数据的结构(表头下面行的数据)
     * @throws Exception
     */
    private void writeSheet(HSSFSheet sheet, List data, List listTpamscolumn, boolean flag, boolean rowFlag) throws Exception {
        sheet.setDefaultColumnWidth(colWidth);
        sheet.setDefaultRowHeightInPoints(rowHeight);
        sheet = createHead(sheet, listTpamscolumn.get(0).getTotalRow(), listTpamscolumn.get(0).getTotalCol());
        createHead(listTpamscolumn,sheet,0);
        if(flag)//控制是否 bug修复：每次写入行数据时，总是漏第一个条数据 rowIndex 错误
            writeSheetContent(listTpamscolumn,data,sheet, listTpamscolumn.get(0).getTotalRow()+1,rowFlag);
    }
    /**
     *  拆分sheet，因为每个sheet不能超过65535，否则会报异常
     * @param data 行内数据
     * @param listTpamscolumn 表头数据
     * @param flag 只输出表头数据
     * @param rowFlag  输出展示数据的结构(表头下面行的数据)
     * @throws Exception
     */
    private void splitDataToSheets(List data, List listTpamscolumn, boolean flag, boolean rowFlag)throws Exception{
        int dataCount = data.size();
        int maxColumn = 65535;
        int pieces = dataCount/maxColumn;
        for(int i = 1; i  subList = data.subList((i-1)*maxColumn, i*maxColumn);
            writeSheet(sheet,subList, listTpamscolumn,flag,rowFlag);
        }
        HSSFSheet sheet = this.workbook.createSheet(this.title+(pieces+1));
        writeSheet(sheet, data.subList(pieces*maxColumn, dataCount), listTpamscolumn,flag,rowFlag);
    }

    /**
     * 把数据写入到单元格
     * @param listTpamscolumn 表头数据
     * @param datas 行内数据
     * @param sheet 工作表（excel分页）
     * @throws Exception
     * void
     */
    private void writeSheetContent(List listTpamscolumn, List datas, HSSFSheet sheet, int rowIndex, boolean rowFlag) throws Exception{
        HSSFRow row = null;
        List  listCol=new ArrayList();
        rowFlag=false;
        if(rowFlag){//暂时没有用 后面扩展用
            for (int i = 0, index = rowIndex; i  temp= (List)t;
            if(j>=temp.size()) return;
            text=String.valueOf(  temp.get(j).get(finame)==null?"": temp.get(j).get(finame));
        }
        HSSFRichTextString richString = new HSSFRichTextString(text);
        cell.setCellValue(richString);
    }

    /**
     * 把column的columnList整理成一个list 过滤表头的脏数据
     * @param list 表头数据
     * @param listCol 返回新的list
     * @return
     * List
     */
    private void getColumnList(List list, List listCol){
        for(int i = 0; i  listChilren = list.get(i).getListTpamscolumn();
            if(listChilren.size() > 0){
                getColumnList( listChilren, listCol);
            }
        }
    }

    /**
     * 保存Excel到InputStream，此方法适合web导出excel
     * @param workbook
     * @return
     */
    private InputStream save(HSSFWorkbook workbook) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
            InputStream bis = new ByteArrayInputStream(bos.toByteArray());
            return bis;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 保存excel到本机指定的路径
     * @param workbook
     * @param filePath
     * @throws IOException
     */
    private void save(HSSFWorkbook workbook, String filePath){
        File file = new File(filePath);
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        FileOutputStream fOut = null;
        try {
            fOut = new FileOutputStream(file);
            workbook.write(fOut);
            fOut.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {if(null!=fOut) fOut.close();} catch (Exception e1) { }
    }

    /**
     * 创建行
     * @param row Excel对应的行
     * @param tpamscolumn 当前单元格属性
     * @param v
     * @param j
     * @return
     * @throws Exception
     */
    public int createRowVal(HSSFRow row, Column tpamscolumn, T v, int j) throws Exception{
        //遍历标题
        if(tpamscolumn.getListTpamscolumn() != null && tpamscolumn.getListTpamscolumn().size() > 0){
            for(int i = 0; i  {
                if(k.equals(tpamscolumn.getFieldName()) && !tpamscolumn.isHasChilren()){
                    value[0] =val;
                }
            });
        }else {
            Class> cls = v.getClass();// 拿到该类
            Field[] fields=cls.getDeclaredFields();// 获取实体类的所有属性，返回Field数组
            for(int i=0;i listTpamscolumn, HSSFSheet sheetCo, int rowIndex){
        HSSFRow row = sheetCo.getRow(rowIndex);
//        if(row == null)row = sheetCo.createRow(rowIndex);
        int len = listTpamscolumn.size();//当前行 有多少列
        for(int i = 0; i  c){
                endC--;
            }
            HSSFCell cell = row.getCell(c);
//            if( null == cell)cell = row.createCell(c);

            HSSFRichTextString text = new HSSFRichTextString(tpamscolumn.getContent());
            cell.setCellStyle(this.styleHead); //设置表头样式
            cell.setCellValue(text);
            // 合并单元格
            CellRangeAddress cra = new CellRangeAddress(r,endR,c,endC);
            sheetCo.addMergedRegion(cra);

            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(1, cra,sheetCo,this.workbook); // 下边框
            RegionUtil.setBorderLeft(1, cra, sheetCo,this.workbook); // 左边框
            RegionUtil.setBorderRight(1, cra, sheetCo,this.workbook); // 有边框

            if(tpamscolumn.isHasChilren()){
                rowIndex=r+1;
                createHead( tpamscolumn.getListTpamscolumn(), sheetCo,rowIndex);
            }
        }
    }


    /**
     * 转换成column对象
     *  支持List的数据结构:map String ，只能是单级的数据
     * @param list 需要转换的数据
     * @return
     */
    public List columnTransformer(List  list){
        List lc=new ArrayList();
        if(list.get(0)instanceof Map) {
            final int[] i = {1};
            for (Map m :(List>)list ) {
                m.forEach((k, val) -> {
                    Column tpamscolumn = new Column();
                    tpamscolumn.setId(String.valueOf(i[0]));
                    tpamscolumn.setPid("0");
                    tpamscolumn.setContent(k);
                    tpamscolumn.setFieldName(val);
                    lc.add(tpamscolumn);
                    i[0]++;
                });
            }
        } else {
            int i = 1;
            for(String s:(List)list) {
                Column tpamscolumn = new Column();
                tpamscolumn.setId(String.valueOf(i));
                tpamscolumn.setPid("0");
                tpamscolumn.setContent(s);
                tpamscolumn.setFieldName(null);
                lc.add(tpamscolumn);
                i++;
            }
        }
        setParm(lc,"0");//处理一下
        List s = TreeTool.buildByRecursive(lc,"0");
        setColNum(lc,s,s);
        return s;
    }

    /**
     * 转换成column对象 返回tree数据结构
     * 支持：List、某个具体对象（entity）数据的转换
     * @param list 需要转换的数据
     * @param id 当前节点id 字段的名称  主键
     * @param pid 父节点id 字段的名称
     * @param content 填写表头单元格内容的 字段名称
     * @param fielName 填写行内数据对的 字段名称
     * @param rootid  rootid的值
     * @return
     * @throws Exception
     */
    public List columnTransformer(List list , String id, String pid, String content, String fielName,String rootid) throws Exception {
        List lc=new ArrayList();
        if(list.get(0) instanceof Map){
            for(Map m:(List)list) {
                Column tpamscolumn = new Column();
                m.forEach((k, val) -> {//java8 以上的遍历方式
                    if (id.equals(k))
                        tpamscolumn.setId(String.valueOf(val));
                    if (pid.equals(k)) tpamscolumn.setPid((String) val);
                    if (content.equals(k)) tpamscolumn.setContent((String) val);
                    if (fielName.equals(k) && fielName != null) tpamscolumn.setFieldName((String) val);
                });
                lc.add(tpamscolumn);
            }
        }else {
            for (T t : list) {//反射
                Column tpamscolumn = new Column();
                Class cls = t.getClass();
                Field[] fs=cls.getDeclaredFields();
                for (int i = 0; i  s = TreeTool.buildByRecursive(lc,rootid);
        setColNum(lc,s,s);
        return s;
    }
    /**
     * 设置基础的参数
     * @param list
     */
    public static void setParm(List list,String rootid){
        int row = 0;//excel第几行
        int rLen = 0; //excel 跨多少行
        int totalRow = TreeTool.getMaxStep(list);
        int totalCol = TreeTool.getDownChilren(list,rootid);
        for(int i=0;i list, List treeList,List flist){
//        int col = pcIndex;//excel第几列
//        int cLen ;//xcel跨多少列
        List new_list = new ArrayList();//新的遍历list
        for(int i = 0;i 0){
                new_list.addAll(poit.getListTpamscolumn());
            }
        }
        if(new_list.size() > 0){
            setColNum( list,new_list,flist);
        }
    }
//========上部分是导出excel的使用（生成excel），下部分是解析excel，由于excel导入==================================================================================================================================

    /**
     * 根据HSSFCell类型设置数据
     * @param cell 单元格
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) { // 判断当前Cell的Type

                case HSSFCell.CELL_TYPE_NUMERIC:  // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);
                    }
                    else { // 如果是纯数字
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case HSSFCell.CELL_TYPE_STRING:  // 如果当前Cell的Type为STRIN
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:  // 默认的Cell值
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbookType(InputStream inStr, String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(".xls".equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(".xlsx".equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("导入格式错误");
        }
        return wb;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getStringCellValue(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue().trim();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue()).trim();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case Cell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }
    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i = firstRow && row = firstColumn && column = firstRow && row = firstColumn && column >的数据结构
     * @param  fileUrl 文件路径
     * @param  sheetNum 工作表（第几分页[1,2,3.....]）
     * @return  List>
     */
    public  List> getExcelValues(String fileUrl, int sheetNum) throws Exception{
        List> values = new ArrayList>();
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = sheetNum-1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
        //int sheetCount1= workbook.getNumberOfSheets();
        Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
        int rowNum = sheet.getLastRowNum();//有多少行
        for (int i = 1; i  list = new ArrayList();
            int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
            for (int j = 0; j >>的数据结构
     * @param  sheetNum  工作表（第几分页[1,2,3.....]）
     * @return List>>
     */
    public static List> getExcelMapVal(InputStream is, int sheetNum) throws Exception{
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
        //int sheetCount1= workbook.getNumberOfSheets();
        Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
        int rowNum = sheet.getLastRowNum();//有多少行
        Row rowTitle = sheet.getRow(0);//第i行
        int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
        List> list = new ArrayList();
        for (int i = 1; i  map=new HashMap();
            for (int j = 0; j >>的数据结构
     * @param  fileUrl  文件路径
     * @param  sheetNum  工作表（第几分页[1,2,3.....]）
     * @return List>>
     */
    public  List>> getExcelMapVal(String fileUrl, int sheetNum) throws Exception{
        List>> values = new ArrayList>>();
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
        //int sheetCount1= workbook.getNumberOfSheets();
        Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
        int rowNum = sheet.getLastRowNum();//有多少行
        Row rowTitle = sheet.getRow(0);//第i行
        int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
        for (int i = 1; i > list = new ArrayList>();
            for (int j = 0; j  map=new HashMap();
                Cell cell = row.getCell(j);
                Cell cellTitle = rowTitle.getCell(j);
                String cellValue;
                String cellKey=getStringCellValue(cellTitle);
                boolean isMerge = false;
                if (cell != null) {
                    isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
                }
                //判断是否具有合并单元格
                if (isMerge) {
                    cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
                } else {
                    cellValue = getStringCellValue(cell);
                }
                map.put(cellKey,cellValue);
                list.add(map);
            }
            values.add(list);
        }
        return values;
    }

    /**
     * 获取当前excel的工作表sheet总数
     * @param fileUrl
     * @return
     * @throws Exception
     */
    public int hasSheetCount(String fileUrl)throws Exception{
        File file = new File(fileUrl);
        InputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount= workbook.getNumberOfSheets();
        return sheetCount;
    }
}





resource接口

@RequestMapping(value = "/importDbf", method=RequestMethod.POST)
    Result> importDbf(@ApiParam(value = "dbf文件", required = true) @RequestBody MultipartFile file,
                        @RequestParam(value = "genNo") String genNo,
                        @RequestParam(value = "importType") String importType);
				
@RequestMapping(value = "/importExcel", method=RequestMethod.POST)
    Result> importExcel(@ApiParam(value = "excel文件", required = true) @RequestBody MultipartFile file,
                          @RequestParam(value = "genNo") String genNo,
                          @RequestParam(value = "importType") String importType);
 
				

controller类

    @Autowired
    private NewStudentImportSettingService newStudentImportSettingService;

    @Autowired
    private HttpServletRequest request;
	
 @Override
    public Result> importDbf(MultipartFile file,@RequestParam(value = "genNo")String genNo,@RequestParam(value = "importType") String importType) {
        if (file == null) {
            throw new ServiceException(SimpleErrorCode.ParamsError);
        }
        List> list = new ArrayList();
        try {
            list = DBFUtils.readDbf(file.getBytes());
        } catch (Exception e) {
            log.error("读取文件失败", e);
            throw new ServiceException(2,"读取文件失败");
        }
        if (CollectionUtils.isEmpty(list)) {
            throw new ServiceException(2,"文件内无数据");
        }
        ImportMessageVo resultVo = new ImportMessageVo();
        resultVo = newStudentImportSettingService.importNewStuData(list,genNo,importType);
        StringBuilder resultMessages = new StringBuilder(); // 导入结果信息
        String message = "导入数据完成，总处理数据" + resultVo.getTotalCount() + "条，成功" + resultVo.getSuccessCount() + "条，失败" + resultVo.getFailCount() + "条;";
        resultMessages.append(message).append("rn").append(resultVo.getImportFailMessages());
        return Result.buildSuccessResult(resultMessages);
    }
	
	
	
    @Override
    public Result> importExcel(MultipartFile file,
                                 @RequestParam(value = "genNo")String genNo,
                                 @RequestParam(value = "importType") String importType) {
        if (file == null) {
            throw new ServiceException(SimpleErrorCode.ParamsError);
        }
        List> list = new ArrayList();
        try {
            list = ExcelTool.getExcelMapVal(file.getInputStream(),1);
        } catch (Exception e) {
            log.error("读取文件失败",e);
            throw new ServiceException(2,"读取文件失败");
        }
        if (CollectionUtils.isEmpty(list)) {
            throw new ServiceException(2,"文件内无数据");
        }
        ImportMessageVo resultVo = new ImportMessageVo();
        resultVo = newStudentImportSettingService.importNewStuData(list,genNo,importType);
        StringBuilder resultMessages = new StringBuilder(); // 导入结果信息
        String message = "导入数据完成，总处理数据" + resultVo.getTotalCount() + "条，成功" + resultVo.getSuccessCount() + "条，失败" + resultVo.getFailCount() + "条;";
        resultMessages.append(message).append("rn").append(resultVo.getImportFailMessages());
        return Result.buildSuccessResult(resultMessages);
    }
	
	
service接口

	 ImportMessageVo importNewStuData(List> data,String genNo,String importType);
	 
service实现类
 

	  @Override
    public ImportMessageVo importNewStuData(List> data,String genNo,String importType) {
 
        //读取dbf映射配置
        NewStudentImportSettingDto searchDto = new NewStudentImportSettingDto();
        searchDto.setImportType(importType);
        searchDto.setTableName(Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey());
        List newStudentImportSettingVoList = newStudentImportSettingMapper.select(searchDto);
        if (CollectionUtils.isEmpty(newStudentImportSettingVoList)) {
            throw new ServiceException(2,"请先配置导入字段映射");
        }
        Map mappingMap = newStudentImportSettingVoList.stream().collect(
                java.util.stream.Collectors.toMap(
                        NewStudentImportSettingVo::getFileFieldCode,
                        NewStudentImportSettingVo::getLibraryTableCode
                )
        );

        ImportMessageVo importMessageVo = new ImportMessageVo();
        int totalCount = data.size();
        AtomicInteger successCount = new AtomicInteger(0);
        AtomicInteger insertSuccessCount = new AtomicInteger(0);
        AtomicInteger updateSuccessCount = new AtomicInteger(0);
        AtomicInteger failCount = new AtomicInteger();
        AtomicReference importFailMessages = new AtomicReference(new StringBuilder());
        AtomicInteger currentRow = new AtomicInteger(1);


        for (Map map : data) { 
            StringBuilder sql = new StringBuilder("insert into " + Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey() + " (");
            StringBuilder updateSql = new StringBuilder("update " + Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey() + " set ");
            Boolean updateStuFlag = false;// 是否修改 否
            try {
                Map studentNoFilterMap = new HashMap();
                // 判断是否已经存在
                Boolean kshValNull = false;
                String ksh = "";
                Boolean sfzjhValNull = false;
                String sfzjh = "";
                for (Map.Entry entry : map.entrySet()) {
                    if (mappingMap.containsKey(entry.getKey()) ){
                        studentNoFilterMap.put(mappingMap.get(entry.getKey()).trim(), entry.getValue().toString().trim());
                        String val =entry.getValue().toString().trim();
                        if("KSH".equals(mappingMap.get(entry.getKey()))){
                            if(StringUtils.isBlank(val)) {
                                kshValNull = true;
                                continue;
                            }else{
                                ksh = val;
                            }
                        }
                        if( "SFZJH".equals(mappingMap.get(entry.getKey())) ){
                            if(StringUtils.isBlank(val)){
                                sfzjhValNull = true;
                                continue;
                            }else{
                                sfzjh = val;
                            }

                        } 
                    }
                }

                // 对比数据库
                if (!studentNoFilterMap.containsKey("KSH")) {
                    failCount.getAndIncrement();
                    importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：excel中必须包含 导入字段映射到考生编号(KSBH)字段的这一列").append("n");
                    currentRow.getAndIncrement();
                    continue;
                }
                if (!studentNoFilterMap.containsKey("SFZJH")) {
                    failCount.getAndIncrement();
                    importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：excel中必须包含 导入字段映射到身份证件号(SFZJH)字段的这一列").append("n");
                    currentRow.getAndIncrement();
                    continue;
                }
                if(kshValNull ){
                    failCount.getAndIncrement();
                    importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 映射到考生编号(KSBH)这一列的值不能为空").append("n");
                    currentRow.getAndIncrement();
                    continue;
                }
                if( sfzjhValNull ){
                    failCount.getAndIncrement();
                    importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 映射到身份证件号(SFZJH)这一列的值不能为空").append("n");
                    currentRow.getAndIncrement();
                    continue;
                }

                // 判断数据库是否已经存在考生号
                List countExist = newStudentImportSettingMapper.selectBySFZH(sfzjh);
                if(!CollectionUtils.isEmpty(countExist) ){
                    if(countExist.size()>1) {
                        failCount.getAndIncrement();
                        importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 系统已存在" + countExist.size() + "条该身份证相同的数据，导入失败").append("n");
                        currentRow.getAndIncrement();
                        continue;
                    }else if(countExist.size()==1){
                        if(!ksh.equals(countExist.get(0).getKsH())){
                            failCount.getAndIncrement();
                            importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 系统已存在" + countExist.size() + "条该身份证相同的,但考生号不同数据，导入失败").append("n");
                            currentRow.getAndIncrement();
                            continue;
                        }else{
                            // 拼接修改
                            updateStuFlag = true;
                        }
                    }
                } else{

                }

                int res = 0;
                if (!updateStuFlag) {
                    for (Map.Entry entry : map.entrySet()) {
                        //只有映射的字段才会插入
                        if (mappingMap.containsKey(entry.getKey())){

                            sql.append(mappingMap.get(entry.getKey()).trim()).append(",");
                        }
                    }
                    sql = new StringBuilder(sql.substring(0, sql.length() - 1));
                    sql.append(") values (");
                    for (Map.Entry entry : map.entrySet()) {

                        if (mappingMap.containsKey(entry.getKey()) ){
                            String val =entry.getValue().toString().trim();
                            sql.append("'").append(val).append("',");
                        }
                    } 
                    sql.deleteCharAt(sql.lastIndexOf(","));
                    sql.append(")");
                    res =  publicMapper.executeSQLInsert(sql.toString());
                    if (res == 0){
                        failCount.getAndIncrement();
                        importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：插入数据失败").append("n");
                        currentRow.getAndIncrement();
                    }else{

                        successCount.getAndIncrement();
                        insertSuccessCount.getAndIncrement();
                        importFailMessages.get().append("第").append(currentRow.get()).append("行导入成功").append("n");
                        currentRow.getAndIncrement();
                    }
                }else{
                    // 修改
                    for (Map.Entry entry : map.entrySet()) {

                        if (mappingMap.containsKey(entry.getKey()) ){

                            String val =entry.getValue().toString().trim();

                            updateSql.append(mappingMap.get(entry.getKey()).trim()).append("=") ;
                            updateSql.append("'").append(val).append("',");
                        }
                    }
                    updateSql.deleteCharAt(updateSql.lastIndexOf(","));
                    updateSql.append(" where SFZJH = ")
                            .append("'").append(sfzjh).append("'");
                   res = publicMapper.executeSQLInsert(updateSql.toString());
                    if (res == 0){
                        failCount.getAndIncrement();
                        importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：插入数据失败").append("n");
                        currentRow.getAndIncrement();
                    }else{

                        successCount.getAndIncrement();
                        updateSuccessCount.getAndIncrement();
                        importFailMessages.get().append("第").append(currentRow.get()).append("行导入修改成功").append("n");
                        currentRow.getAndIncrement();
                    }
                }




            } catch (Exception e) {
                log.error("导入数据失败", e); 
                failCount.getAndIncrement();
                importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：").append(e.getMessage()).append("n");
            }
        }
        importMessageVo.setTotalCount(totalCount);
        importMessageVo.setSuccessCount(successCount.get());
        importMessageVo.setUpdateSuccessCount(updateSuccessCount.get());
        importMessageVo.setFailCount(failCount.get());
        if(insertSuccessCount.get()>=1){
            importFailMessages.get().append("n").append("其中新增了"+insertSuccessCount+"条");
        }
        if(updateSuccessCount.get()>=1){
            importFailMessages.get().append("n").append("其中修改了"+updateSuccessCount+"条");
        }
        importMessageVo.setImportFailMessages(importFailMessages.get());
        return importMessageVo;
    }

登录查看全部

参与评论

昵称 登录
发布评论

评论留言

还没有评论留言，赶紧来抢楼吧~~

@2025 by 唯宠科技 蜀ICP备18000267号
浏览 2966.31 万次 点击这里给我发消息

手机查看

 返回顶部

给这篇文章打个标签吧~

棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认