精华内容
下载资源
问答
  • 2021-01-08 11:52:26

    前言
    最近接到一个需求,需要把一份37万的excel数据解析并导入数据库,
    分析
    表格格式相对复杂包含很多合并单元格, 不符合通过navicat直接导入的要求, 数据量比较大, 建议一次保存25条,否则会导致内存泄漏;
    代码
    1.引入依赖

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.9</version>
    </dependency>
    

    2.具体操作代码
    2.1.判断指定的单元格是否为合并单元格

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        //获取该sheet所有合并的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //循环判断 该单元格属于哪个合并单元格, 如果能找到对应的,就表示该单元格是合并单元格
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
    

    2.2.获取合并单元格的值

    /**
     * 获取合并单元格的值
     *
     * @param sheet  sheet索引 从0开始
     * @param row    行索引 从0开始
     * @param column 列索引  从0开始
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }
    

    2.3.获取单元格的值(不是合并单元格)

    /**
     * 获取单元格的值  先确定单元格的类型,然后根据类型 取值
     *
     * @param cell 单元格
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) return "";
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }
    

    2.4.解析完一行数据后,判断对象的属性是否都为空

    /**
     * 判断一个对象的属性是否都为空,
     *
     * @param obj 对象
     * @return false : 至少有一个属性不为空, true: 该对象的属性全为空
     */
    public boolean allFieldIsNULL(Object obj) {
        Boolean flag = true;//都为空
        if (null == obj || "".equals(obj)) return flag;
        try {
            Field[] declaredFields = obj.getClass().getDeclaredFields();
            for (Field field : declaredFields) { // 循环该类,取出类中的每个属性
                field.setAccessible(true);// 把该类中的所有属性设置成 public
                Object object = field.get(obj); // object 是对象中的属性
                if (object instanceof CharSequence) { // 判断对象中的属性的类型,是否都是CharSequence的子类
                    if (!Objects.isNull(object)) { // 如果是他的子类,那么就可以用ObjectUtils.isEmpty进行比较
                        flag = false;//不为空
                    }
                } else { //如果不是那就直接用null判断
                    if (!(null == object || "".equals(object))) {
                        flag = false;//不为空
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;//false:不都为空
    }
    

    2.5.解析excel的工具类

    /**
     * 读取excel文件
     *
     * @param
     * @param sheetIndex    sheet页下标:从0开始
     * @param startReadLine 开始读取的行:从0开始
     * @param tailLine      结束行
     */
    public void readExcel(String path, int sheetIndex, int startReadLine, int tailLine) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        //读取excel表中的sheet, 参数为sheet的索引值(从0开始)
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        Boolean save = false;
        //获取该sheet的最后一行数据的索引
        int lastRowNum = sheet.getLastRowNum();
        ArrayList<CmsIndexCategory> list = new ArrayList<>();
        //外循环是循环行,内循环是循环每行的单元格
        for (int i = startReadLine; i <= lastRowNum; i++) {
            CmsIndexCategory indexCategory = new CmsIndexCategory();
            //根据行索引获取行对象(单元格集合)
            row = sheet.getRow(i);
            //遍历行的单元格,并解析
            for (Cell c : row) {
                String returnStr = "";
                String trim = "";
                //设置该单元格的数据的类型为String
                c.setCellType(Cell.CELL_TYPE_STRING);
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                // 判断是否具有合并单元格
                if (isMerge) {
                //如果是合并单元格,就获取合并单元格的值
                    returnStr = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()).toString();
                } else {
                //不是合并单元格直接获取单元格的值
                    returnStr = getCellValue(c).toString();
                }
                if (Objects.nonNull(returnStr) && StringUtils.isNotEmpty(returnStr)) {
                    trim = returnStr.trim();
                    //封装结果集,一行数据封装为一个对象
                    if (c.getColumnIndex() == 0) {
                        indexCategory.setAgencyBrand(trim);
                    } else if (c.getColumnIndex() == 1) {
                        indexCategory.setCompanyName(trim);
                    } else if (c.getColumnIndex() == 2) {
                        indexCategory.setIndustryField(trim);
                    } else if (c.getColumnIndex() == 3) {
                        indexCategory.setDetectionField(trim);
                    } else if (c.getColumnIndex() == 4) {
                        indexCategory.setDetectionObj(trim);
                    } else if (c.getColumnIndex() == 5) {
                        indexCategory.setSampleCategory(trim);
                    } else if (c.getColumnIndex() == 6) {
                        indexCategory.setDetectionRange(trim);
                    } else if (c.getColumnIndex() == 7) {
                        indexCategory.setDetectionItem(trim);
                    } else if (c.getColumnIndex() == 8) {
                        indexCategory.setDetectionStandard(trim);
                    }
                }
            }
            //判断一个对象的属性是否都为空, true:都为空  , false: 不都为空
            if (!allFieldIsNULL(indexCategory)) {
                //该对象不都为空的情况下,添加到集合中
                list.add(indexCategory);
            }
            //一次保存25条数据,最后一次数据不够25条也进行保存
            if (list.size() == 25 || i == lastRowNum) {
                save = this.iCmsIndexCategoryService.saveBatch(list);
                System.out.println("==================================================第------" + (i + 1) + "---------行保存结果为======================================== " + save);
                list.clear();
            }
        }
    }
    

    3.具体实践

    @GetMapping("/excel")
    public String excel2() {
        this.readExcel("C:\\Users\\yangmin\\Desktop\\我要测资料\\特色检测项目\\7.xlsx", 0, 0, 0);
        return "完成";
    }
    
    更多相关内容
  • Java导入复杂excel

    2021-03-16 13:22:33
    Java 导入复杂excel,多表格中有合并以及描述 接口返回类 package com.hs.report.utils; import com.hs.report.enums.ResultCode; import org.apache.commons.lang.StringUtils; import java.io.Serializable; ...

    Java 导入复杂excel,多表格中有合并以及描述

    在这里插入图片描述
    接口返回类

    package com.hs.report.utils;
    
    import com.hs.report.enums.ResultCode;
    import org.apache.commons.lang.StringUtils;
    
    import java.io.Serializable;
    import java.util.HashMap;
    import java.util.Map;
    
    public class Result <T> extends BaseDomain implements Serializable {
        private static final long serialVersionUID = -1117047807265427246L;
        private int status;
        private String msg;
        private T data;
    
        public Result() {
            this.status = ResultCode.SUCCESS.getStatus();
            this.msg = ResultCode.SUCCESS.getMsg();
        }
    
        public Result(T data) {
            this.setData(data);
            this.status = ResultCode.SUCCESS.getStatus();
            this.msg = ResultCode.SUCCESS.getMsg();
        }
    
        public Result(int status, String msg) {
            this.setStatus(status);
            this.setMsg(msg);
        }
    
        public Result(int status, String msg, T data) {
            this.setStatus(status);
            this.setMsg(msg);
            this.setData(data);
        }
    
        public Result<T> setError(int status, String msg) {
            this.setStatus(status);
            this.setMsg(msg);
            return this;
        }
    
        public Result<T> setError(ResultCode errorCode) {
            this.setStatus(errorCode.getStatus());
            this.setMsg(errorCode.getMsg());
            return this;
        }
    
        public boolean isSuccess() {
            return this.getStatus() == ResultCode.SUCCESS.getStatus();
        }
    
        public static  Result instance() {
            return new Result();
        }
    
        public static <T> Result instance(T data) {
            return new Result(data);
        }
    
        public static <T> Result instance(int status, String msg) {
            return new Result(status, msg);
        }
    
        public static <T> Result instance(int status, String msg, T data) {
            return new Result(status, msg, data);
        }
    
        public int getStatus() {
            return this.status;
        }
    
        public void setStatus(int status) {
            this.status = status;
        }
    
        public String getMsg() {
            return this.msg;
        }
    
        public void setMsg(String msg) {
            this.msg = msg;
        }
    
        public void setPeriodIdMsg(Integer periodId){
            this.msg = ResultCode.SUCCESS.getMsg() + "_" + periodId;
        }
    
        public T getData() {
            return this.data;
        }
    
        public void setData(T data) {
            this.data = data;
        }
    
        public Map<String,Object> toJsonMap(){
            Map<String,Object> map = new HashMap<>();
            map.put("data",this.data);
            map.put("msg",this.msg);
            map.put("status",this.status);
            return map;
        }
    
    }
    
    

    实体类

    package com.hs.report.entity.week;
    import lombok.Builder;
    import lombok.Data;
    import java.io.Serializable;
    @Data
    @Builder
    public class SnapshotReq<T> implements Serializable {
        private static final long serialVersionUID = 4403232919132430023L;
        private String type;
        private Integer offset;
    	/**
    	*json字符串
    	**/
        private T content;
    
        private Integer subNo;
    
        private Integer periodId;
    
        private String flowId;
    }
    
    
    

    Controller层

    package com.hs.report.controller.week;
    
    import com.hs.report.entity.week.SnapshotReq;
    import com.hs.report.service.week.ParseExcelForGSService;
    import com.hs.report.service.week.ParseExcelForOTCService;
    import com.hs.report.service.week.ParseExcelForZTService;
    import com.hs.report.utils.Result;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.InputStream;
    import java.util.List;
    
    @Slf4j
    @Api(tags = "导入Excel模板")
    @RestController
    @RequestMapping("/investmentReport/week")
    public class ImportWeekExcelController {
    
        @Autowired
        private ParseExcelForZTService parseExcelForZTService;
    
        @Autowired
        private ParseExcelForGSService parseExcelForGSService;
    
        @Autowired
        private ParseExcelForOTCService parseExcelForOTCService;
    
        @ApiOperation(value = "导入")
        @PostMapping("/import")
        public Result<List<SnapshotReq>> importExcel(@RequestParam("file") MultipartFile file, @RequestParam(value = "type") Integer type) throws Exception {
            log.info("开始解析Excel, type==={}", type);
    
            Result<List<SnapshotReq>> result = new Result<>();
            List<SnapshotReq> snapshotReqs = null;
    
            InputStream inputStream = null;
            Workbook wb = null;
            try{
                inputStream = file.getInputStream();
                String fileName = file.getOriginalFilename();
    
                String fileType = fileName.substring(fileName.lastIndexOf("."));
                if(".xls".equals(fileType)){
                    wb= new HSSFWorkbook(inputStream);
                }else if(".xlsx".equals(fileType)) {
                    wb = WorkbookFactory.create(inputStream);
                }
    
                switch(type){
                    //证投
                    case 1:
                        snapshotReqs = parseExcelForZTService.parseExcelForZT(wb);
                        break;
                    //固收
                    case 2:
                        snapshotReqs = parseExcelForGSService.parseExcelForGS(wb);
                        break;
                    //OTC
                    case 3:
                        snapshotReqs = parseExcelForOTCService.parseExcelForOTC(wb);
                        break;
                }
    
                inputStream.close();
                result.setData(snapshotReqs);
                log.info("结束解析Excel, type==={}", type);
            }catch (Exception ex){
                throw new Exception("导入Excel出现异常,异常信息:" + ex.getMessage());
            }
    
            return result;
        }
    
    
    }
    
    

    Service层

    package com.hs.report.service.impl.week;
    
    import com.hs.report.entity.week.BusinessTypeDO;
    import com.hs.report.entity.week.LossPayfDO;
    import com.hs.report.entity.week.SnapshotReq;
    import com.hs.report.entity.week.WeekLossPayfDO;
    import com.hs.report.service.week.ParseExcelForGSService;
    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.usermodel.Workbook;
    import org.springframework.stereotype.Service;
    
    import java.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.List;
    
    @Service
    public class ParseExcelForGSServiceImpl implements ParseExcelForGSService {
        @Override
        public List<SnapshotReq> parseExcelForGS(Workbook wb) throws InstantiationException, IllegalAccessException  {
            Sheet sheet = wb.getSheetAt(0);
            List<SnapshotReq> result = new ArrayList<>();
            int rowIndex = 0;
    
            //本周重点推进工作:描述
            int weekRowNum = parseTextValue(sheet, 1,rowIndex + 2, 0, result);
    
            //本周重点推进工作:描述
            int lastWeekRowNum = parseTextValue(sheet, 2, weekRowNum + 3, 0, result);
    
            //T0周损益对比:描述
            int weekLossRowNum = parseTextValue(sheet, 3, lastWeekRowNum + 3, 0, result);
    
            //第一个表格内容
            int oneTitleText = parseTableValue(sheet, 4, weekLossRowNum + 4, result, WeekLossPayfDO.class);
    
            //市场状况及交易部门损益
            int marketRowNum = parseTextValue(sheet, 5, weekLossRowNum + 3 + oneTitleText + 3, 0, result);
    
            //第二个表格内容
            int twoTitleText = parseTableValue(sheet, 7, marketRowNum + 4, result, LossPayfDO.class);
    
            //固定收益部损益分析
            int fixedRowNum = parseTextValue(sheet, 10, marketRowNum + 3 + twoTitleText + 4, 0, result);
    
            //第三个表格内容
            int threeTitleText = parseThreeTableValue(sheet, 11, fixedRowNum + 3, result);
    
            //固定收益部
            int dailyRowNum = parseTextValue(sheet, 12, fixedRowNum + 3 + threeTitleText + 3, 0, result);
    
            return result;
        }
    
        //解析文本框内容
        private int parseTextValue(Sheet sheet, int offset, int rowIndex, int cellIndex, List<SnapshotReq> result){
            SnapshotReq snapshotReq = null;
            Cell cell = sheet.getRow(rowIndex).getCell(cellIndex);
            snapshotReq = SnapshotReq.builder().offset(offset).content(cell.getStringCellValue()).build();
            result.add(snapshotReq);
            return rowIndex;
        }
    
        //解析第三表格部分 有合并列
        private <T> int parseThreeTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result) {
            Row row = null;
            Cell cell = null;
            SnapshotReq snapshotReq = null;
            String perPurpose = "";//上一个数据描述
            String purpose = "";//当前数据描述
            boolean flag = true;//用来记录是否是第一次循环
            //判断有几条数据
            int oneTitleText = 0;
            List<Object> dataList = new ArrayList<>();
            for(int i = rowIndex;i < sheet.getLastRowNum();i++){
                BusinessTypeDO businessTypeDO = new BusinessTypeDO();
                row = sheet.getRow(i);
                //判断此行的每一列是否都为空
                if(judgeWhetherNull(row)){
                    oneTitleText++;
                    break;
                }
    
                if(row != null){
                    for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                        cell = row.getCell(j);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        purpose = cell.getStringCellValue();
                        if(j == 0){
    
                            if(StringUtils.isNotEmpty(purpose) && !purpose.equals(perPurpose)){
                                flag = true;
                            }
    
                            if(flag){//如果是第一次循环,将perPurpose初始化,以便后面的第一次判断
                                perPurpose = purpose;
                                flag = false;
                            }
    
                            if(StringUtils.isEmpty(purpose) || purpose.equals(perPurpose)){
                                purpose = perPurpose;
                            }else{
                                purpose = cell.getStringCellValue();
                            }
                        }
    
                        getThreeTitleText(businessTypeDO, purpose, j);
                    }
                    dataList.add(businessTypeDO);
    
                }else{
                    break;
                }
                oneTitleText++;
            }
            snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
            result.add(snapshotReq);
    
            return oneTitleText - 1;
        }
    
    
        //解析表格内容
        private <T> int parseTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result, Class<T> tClass)throws IllegalAccessException, InstantiationException{
            Row row = null;
            Cell cell = null;
            SnapshotReq snapshotReq = null;
            //判断有几条数据
            int oneTitleText = 0;
            List<Object> dataList = new ArrayList<>();
            for(int i = rowIndex;i < sheet.getLastRowNum();i++){
                Object object = tClass.newInstance();
                row = sheet.getRow(i);
                //判断此行的每一列是否都为空
                if(judgeWhetherNull(row)){
                    if(i == rowIndex){
                        oneTitleText = 2;
                    }else{
                        oneTitleText++;
                    }
                    break;
                }
    
                if(row != null){
                    for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                        cell = row.getCell(j);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        generateData(object, cell.getStringCellValue(), j);
                    }
                    dataList.add(object);
    
                }else{
                    break;
                }
                oneTitleText++;
            }
            snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
            result.add(snapshotReq);
    
            return oneTitleText - 1;
        }
    
        private <T> void generateData(T object, String value, int cellIndex){
            if(object instanceof WeekLossPayfDO){
                getOneTitleText((WeekLossPayfDO) object, value, cellIndex);
            }else if(object instanceof LossPayfDO){
                getTwoTitleText((LossPayfDO) object, value, cellIndex);
            }
        }
    
        private void getThreeTitleText(BusinessTypeDO businessTypeDO, String value, int cellIndex) {
            switch(cellIndex){
                case 0:
                    businessTypeDO.setTitle(value);
                    break;
                case 2:
                    businessTypeDO.setSubtitle(getContentStr(value));
                    break;
                case 4:
                    businessTypeDO.setValue(getContentStr(value));
                    break;
            }
        }
    
        private String getContentStr(String content) {
            return StringUtils.isNotEmpty(content) ? content : "";
        }
    
        private boolean judgeWhetherNull(Row row) {
        	if(row == null){
                return true;
            }
            int count = 0;
            //单元格数量
            int rowCount = row.getLastCellNum() - row.getFirstCellNum();
            for (int c = 0; c < rowCount; c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtils.isEmpty((cell+"").trim())){
                    count += 1;
                }
            }
    
            if (count == rowCount) {
                return true;
            }
            return  false;
        }
    
        private void getTwoTitleText(LossPayfDO lossPayfDO, String value, int cellIndex) {
            Double data = null;
            if(cellIndex != 0){
                data = StringUtils.isNotEmpty(value) ? Double.valueOf(value) : 0.0;
            }
            switch (cellIndex){
                case 0:
                    lossPayfDO.setDepName(getContentStr(value));
                    break;
                case 1:
                    lossPayfDO.setRiskExpo(data);
                    break;
                case 2:
                    lossPayfDO.setGsck(data);
                    break;
                case 3:
                    lossPayfDO.setActlMval(data);
                    break;
                case 4:
                    lossPayfDO.setFutrNetPos(data);
                    break;
                case 5:
                    lossPayfDO.setWeekLossPayf(data);
                    break;
                case 6:
                    lossPayfDO.setMthLossPayf(data);
                    break;
                case 7:
                    lossPayfDO.setYearLossPayf(data);
                    break;
            }
        }
    
        private void getOneTitleText(WeekLossPayfDO weekLossPayfDO, String value, int cellIndex) {
            BigDecimal data = null;
            if(cellIndex != 0){
                data = StringUtils.isNotEmpty(value) ? new BigDecimal(value) : null;
            }
            switch (cellIndex){
                case 0:
                    weekLossPayfDO.setDeptName(getContentStr(value));
                    break;
                case 2:
                    weekLossPayfDO.setWeekLoss(data);
                    break;
                case 4:
                    weekLossPayfDO.setLastWeekLoss(data);
                    break;
                case 6:
                    weekLossPayfDO.setYearLoss(data);
                    break;
            }
        }
    }
    
    
    展开全文
  • 主要介绍了Java实现Excel导入导出数据库的方法,结合实例形式分析了java针对Excel的读写及数据库操作相关实现技巧,需要的朋友可以参考下
  • 由于Java并没有表格对象,总要利用集合加实体类去实现(硬编码),如果碰到格式复杂的表格,解析难度大,工作量会成倍增加,代码不仅冗长、且很难通用。 比如要处理这么个场景:数据库表Logistics有3个字段:Shippers...

    一般常规办法:先使用POI或者HSSFWorkbook等第三方类库对其表格数据结构化,再用SQL语句写入数据库。由于Java并没有表格对象,总要利用集合加实体类去实现(硬编码),如果碰到格式复杂的表格,解析难度大,工作量会成倍增加,代码不仅冗长、且很难通用。

    比如要处理这么个场景:数据库表Logistics有3个字段:Shippers、Region、Quantity。解析如下Excel表格,并入库:

     

     

    入库后的效果:

     

     

    Java代码大概要写成这样子:

    ...

    File target = new File(filepath, filename);

    FileInputStream fi = new FileInputStream(target);

    HSSFWorkbook wb = new HSSFWorkbook(fi);

    HSSFSheet sheet = wb.getSheetAt(sheetnum);

    int rowNum = sheet.getLastRowNum() + 1;

    for (int i = startrow; i < rowNum; i++) {

            PageData varpd = new PageData();

            HSSFRow row = sheet.getRow(i);

            int cellNum = row.getLastCellNum();

            ...

            }

    ...

    List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 3, 0, 0);             

    for(int i=0;i<listPd.size();i++){

            pd.put("ET_ID", this.get32UUID());

            ...

            }      

    /*The operation to import the database*/

    mv.addObject("msg","success");  

    ...

    如果有了集算器,这样的问题则会简单很多,它是专业处理结构化数据的语言,能够轻松读取Excel数据,结构化成“序表”后导入数据库。以往需要编写数千行代码才能完成的Excel数据结构化入库工作,现在只需简单的几行就搞定了。比如上面的问题,集算器SPL仅3行:

     

    A

    1

    =file("/workspace/crosstab.xls").xlsimport@t(;1,2).rename(#1:Shippers)

    2

    =A1.pivot@r(Shippers;Region,Quantity)

    3

    =Mysql.update(A2,Logistics)

    其实还有很多类似的结构化问题不太方便,但有集算器SPL的辅助却很简单,感兴趣可以参考:复杂Excel表格导入导出的最简方法10行代码提取复杂Excel数据

    集算器还很容易嵌入到Java应用程序中,Java如何调用SPL脚本有使用和获得它的方法。

    关于集算器安装使用、获得免费授权和相关技术资料,可以参见如何使用集算器

    展开全文
  • java 使用POI导入复杂excel表格

     图片不清晰,请多担待

     

    pom.xml依赖

    <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>${poi.version}</version>
    </dependency>

    上代码:

    import com.ruoyi.common.exception.CustomException;
    import com.ruoyi.common.utils.DateUtils;
    import com.ruoyi.common.utils.StringUtils;
    import com.ruoyi.common.utils.file.FileUtils;
    import com.ruoyi.system.domain.VehicleInfo;
    import com.ruoyi.system.domain.VehicleInsurance;
    import com.ruoyi.system.domain.VehicleTotalmile;
    import com.ruoyi.system.domain.vo.VehicleInfoImportVo;
    import com.ruoyi.system.mapper.sysdict.DictDataMapper;
    import com.ruoyi.system.mapper.vehicle.VehicleInfoMapper;
    import com.ruoyi.system.mapper.vehicle.VehicleInsuranceMapper;
    import com.ruoyi.system.mapper.vehicle.VehicleTotalmileMapper;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.springframework.beans.BeanUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.File;
    import java.io.IOException;
    import java.lang.reflect.Field;
    import java.math.BigDecimal;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.Objects;
    import java.util.UUID;
    
    import static org.apache.poi.ss.usermodel.CellType.*;
    
    @Slf4j
    @Service
    public class VehicleImportService {
    
        @Autowired
        private DictDataMapper dictDataMapper;
        @Autowired
        private VehicleInfoMapper vehicleInfoMapper;
        @Autowired
        private VehicleInsuranceMapper vehicleInsuranceMapper;
        @Autowired
        private VehicleTotalmileMapper vehicleTotalmileMapper;
    
    
        /**
         * 读取excel文件
         *
         * @param path 文件流
         * @param operName 操作人名称
         * @param sheetIndex    sheet页下标:从0开始
         * @param startReadLine 开始读取的行:从0开始
         * @param tailLine      结束行
         */
        public String importVehicleInfo(MultipartFile path, String operName, int sheetIndex, int startReadLine, int tailLine) {
            Workbook wb = null;
            File file = null;
            try {
                file=File.createTempFile(path.getOriginalFilename(), null);
                path.transferTo(file);
                file.deleteOnExit();
                wb = WorkbookFactory.create(file);
            } catch (Exception e) {
                e.printStackTrace();
            }
            //读取excel表中的sheet, 参数为sheet的索引值(从0开始)
            Sheet sheet = wb.getSheetAt(sheetIndex);
            Row row = null;
            Boolean save = false;
            //获取该sheet的最后一行数据的索引
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum < 2){
                throw new CustomException("导入模板不正确!");
            }
            if (lastRowNum == 2){
                throw new CustomException("导入车辆数据不能为空!");
            }
    
            int successNum = 0;
            int failureNum = 0;
            StringBuilder successMsg = new StringBuilder();
            StringBuilder failureMsg = new StringBuilder();
            ArrayList<VehicleInfoImportVo> list = new ArrayList<>();
            //外循环是循环行,内循环是循环每行的单元格
            for (int i = 3; i <= lastRowNum; i++) {
                //车辆信息导入对象
                VehicleInfoImportVo vehicleInfoImportVo = new VehicleInfoImportVo();
    
                //车辆保险对象
                VehicleInsurance vehicleInsurance = new VehicleInsurance();
                //商业保险费
                BigDecimal commercialInsurPrice = BigDecimal.ZERO;
                //承运险
                BigDecimal carrierRisk = BigDecimal.ZERO;
                //交强险保费
                BigDecimal compulsoryInsurPrice = BigDecimal.ZERO;
                //商业保险期限
                String commercialInsurDate = "";
                //承运险期限
                String carrierRiskDate = "";
                //交强险期限
                String compulsoryInsurDate = "";
                //保险号数组
                String[] insurNums = null;
    
                //车辆里程对象
                VehicleTotalmile vehicleTotalmile = new VehicleTotalmile();
                
                //根据行索引获取行对象(单元格集合)
                row = sheet.getRow(i);
                //遍历行的单元格,并解析
                for (Cell c : row) {
                    String returnStr = "";
                    String value = "";
                    //设置该单元格的数据的类型为String
                    c.setCellType(STRING);
                    boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                    // 判断是否具有合并单元格
                    if (isMerge) {
                        //如果是合并单元格,就获取合并单元格的值
                        returnStr = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()).toString();
                    } else {
                        //不是合并单元格直接获取单元格的值
                        returnStr = getCellValue(c).toString();
                    }
                    if (StringUtils.isNotEmpty(returnStr)) {
                        value = returnStr.trim();
                        
                        //封装结果集,一行数据封装为一个对象
                        if (c.getColumnIndex() == 0) {
                            System.out.println("序号:"+value);
                        } else if (c.getColumnIndex() == 1) {
                            System.out.println("车牌号:"+value);
                            vehicleInfoImportVo.setVehicleNo(value);
                        } else if (c.getColumnIndex() == 2) {
                            System.out.println("车牌颜色:"+value);
                            vehicleInfoImportVo.setPlateColor(value);
                        } else if (c.getColumnIndex() == 3) {
                            System.out.println("核定载客数:"+value.replaceAll("人", ""));
                        } else if (c.getColumnIndex() == 4) {
                            System.out.println("车辆厂牌:"+value);
                            vehicleInfoImportVo.setBrand(value);
                        } else if (c.getColumnIndex() == 5) {
                            System.out.println("车辆型号:"+value);
                            vehicleInfoImportVo.setModel(value);
                        } else if (c.getColumnIndex() == 6) {
                            System.out.println("车辆类型:"+value);
                        } else if (c.getColumnIndex() == 7) {
                            System.out.println("车辆所有人:"+value);
                            vehicleInfoImportVo.setOwnerName(value);
                        } else if (c.getColumnIndex() == 8) {
                            System.out.println("车身颜色:"+value);
                            vehicleInfoImportVo.setVehicleColor(value);
                        } else if (c.getColumnIndex() == 9) {
                            System.out.println("发动机号:"+value);
                            vehicleInfoImportVo.setEngineId(value);
                        } else if (c.getColumnIndex() == 10) {
                            System.out.println("车架号:"+value);
                            vehicleInfoImportVo.setVin(value);
                        } else if (c.getColumnIndex() == 11) {
                            System.out.println("保险公司名称:"+value);
                            vehicleInsurance.setInsurCom(value);
                            vehicleInsurance.setVehicleNo(vehicleInfoImportVo.getVehicleNo());
                        } else if (c.getColumnIndex() == 12) {
                            System.out.println("保险号:"+value);
                            insurNums = value.split(";");
                        } else if (c.getColumnIndex() == 13) {
                            System.out.println("车船税:"+value);
                        } else if (c.getColumnIndex() == 14) {
                            System.out.println("商业保险费:"+value);
                            commercialInsurPrice = new BigDecimal(value);
                        } else if (c.getColumnIndex() == 15) {
                            System.out.println("承运险保费:"+value);
                            carrierRisk = new BigDecimal(value);
                        } else if (c.getColumnIndex() == 16) {
                            System.out.println("交强险保费:"+value);
                            compulsoryInsurPrice = new BigDecimal(value);
                        } else if (c.getColumnIndex() == 17) {
                            System.out.println("保险总金额(人民币元):"+value);
                        } else if (c.getColumnIndex() == 18) {
                            System.out.println("承运险保险期限:"+value);
                            carrierRiskDate = value;
                        } else if (c.getColumnIndex() == 19) {
                            System.out.println("交强险保险期限:"+value);
                            compulsoryInsurDate = value;
                        } else if (c.getColumnIndex() == 20) {
                            System.out.println("商业险保险期限:"+value);
                            commercialInsurDate = value;
                        } else if (c.getColumnIndex() == 21) {
                            System.out.println("年审时间:"+value);
                        } else if (c.getColumnIndex() == 22) {
                            System.out.println("行驶公里数:"+value);
                            vehicleTotalmile.setVehicleNo(vehicleInfoImportVo.getVehicleNo());
                            vehicleTotalmile.setTotalMile(new BigDecimal(value));
                        }
                    }
                }
                //判断一个对象的属性是否都为空, true:都为空  , false: 不都为空
                if (!allFieldIsNULL(vehicleInfoImportVo)) {
                    Date date = DateUtils.getNowDate();
                    vehicleInsurance.setUpdateTime(date);
                    vehicleTotalmile.setUpdateTime(date);
                    vehicleInfoImportVo.setUpdateBy(operName);
                    vehicleInfoImportVo.setId(UUID.randomUUID().toString().replaceAll("-",""));
                    try {
                        // 验证是否存在这个车辆
                        VehicleInfo vehicle = vehicleInfoMapper.selectVehicleInfoByVehicleNo(vehicleInfoImportVo.getVehicleNo());
                        if (vehicle == null){
                            vehicleInfoImportVo.setCreateTime(date);
                            VehicleInfo vehicleInfo = new VehicleInfo();
                            // 复制 vehicleInfoImportVo 到 vehicleInfo
                            BeanUtils.copyProperties(vehicleInfoImportVo, vehicleInfo);
                            vehicleInfo.setSeats(Integer.valueOf(vehicleInfoImportVo.getSeats()));
                            if (vehicleInfoMapper.insertVehicleInfo(vehicleInfo) > 0){
                                successNum++;
                                successMsg.append("<br/>" + successNum + "、车辆 " + vehicleInfoImportVo.getVehicleNo() + " 导入成功");
                            } else {
                                log.info("导入车辆信息,插入车辆信息失败,车牌号:{}", vehicleInfoImportVo.getVehicleNo());
                            }
                        } else {
                            vehicleInfoImportVo.setUpdateTime(date);
                            VehicleInfo vehicleInfo = new VehicleInfo();
                            // 复制 vehicleInfoImportVo 到 vehicleInfo
                            BeanUtils.copyProperties(vehicleInfoImportVo, vehicleInfo);
                            vehicleInfo.setSyncflag(0);
                            if (vehicleInfoMapper.updateVehicleInfo(vehicleInfo) > 0){
    							successNum++;
                                successMsg.append("<br/>" + successNum + "、车辆 " + vehicleInfoImportVo.getVehicleNo() + " 更新成功");
                            } else {
                                log.info("导入车辆信息,更新车辆信息失败,车牌号:{}", vehicleInfoImportVo.getVehicleNo());
                            }
                        }
                    } catch (Exception e) {
                        failureNum++;
                        String msg = "<br/>" + failureNum + "、车辆 " + vehicleInfoImportVo.getVehicleNo() + " 导入失败:";
                        failureMsg.append(msg + e.getMessage());
                        log.error(msg, e);
                    }
                }
            }
            if (failureNum > 0) {
                failureMsg.insert(0, "很抱歉,导入失败!共 " + failureNum + " 条数据格式不正确,错误如下:");
                throw new CustomException(failureMsg.toString());
            } else {
                successMsg.insert(0, "恭喜您,数据已全部导入成功!共 " + successNum + " 条,数据如下:");
            }
            return successMsg.toString();
        }
    
        /**
         * 判断指定的单元格是否是合并单元格
         *
         * @param sheet
         * @param row    行下标
         * @param column 列下标
         * @return
         */
        private static boolean isMergedRegion(Sheet sheet, int row, int column) {
            //获取该sheet所有合并的单元格
            int sheetMergeCount = sheet.getNumMergedRegions();
            //循环判断 该单元格属于哪个合并单元格, 如果能找到对应的,就表示该单元格是合并单元格
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress range = sheet.getMergedRegion(i);
                int firstColumn = range.getFirstColumn();
                int lastColumn = range.getLastColumn();
                int firstRow = range.getFirstRow();
                int lastRow = range.getLastRow();
                if (row >= firstRow && row <= lastRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        return true;
                    }
                }
            }
            return false;
        }
    
        /**
         * 获取合并单元格的值
         *
         * @param sheet  sheet索引 从0开始
         * @param row    行索引 从0开始
         * @param column 列索引  从0开始
         * @return
         */
        public static String getMergedRegionValue(Sheet sheet, int row, int column) {
            int sheetMergeCount = sheet.getNumMergedRegions();
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress ca = sheet.getMergedRegion(i);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
                if (row >= firstRow && row <= lastRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        Row fRow = sheet.getRow(firstRow);
                        Cell fCell = fRow.getCell(firstColumn);
                        return getCellValue(fCell);
                    }
                }
            }
            return null;
        }
    
        /**
         * 获取单元格的值  先确定单元格的类型,然后根据类型 取值
         *
         * @param cell 单元格
         * @return
         */
        public static String getCellValue(Cell cell) {
            if (cell == null) return "";
            if (cell.getCellType() == STRING) {
                return cell.getStringCellValue();
            } else if (cell.getCellType() == BOOLEAN) {
                return String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == FORMULA) {
                return cell.getCellFormula();
            } else if (cell.getCellType() == NUMERIC) {
                return String.valueOf(cell.getNumericCellValue());
            }
            return "";
        }
    
        /**
         * 判断一个对象的属性是否都为空,
         *
         * @param obj 对象
         * @return false : 至少有一个属性不为空, true: 该对象的属性全为空
         */
        public static boolean allFieldIsNULL(Object obj) {
            Boolean flag = true;//都为空
            if (null == obj || "".equals(obj)) return flag;
            try {
                Field[] declaredFields = obj.getClass().getDeclaredFields();
                for (Field field : declaredFields) { // 循环该类,取出类中的每个属性
                    field.setAccessible(true);// 把该类中的所有属性设置成 public
                    Object object = field.get(obj); // object 是对象中的属性
                    if (object instanceof CharSequence) { // 判断对象中的属性的类型,是否都是CharSequence的子类
                        if (!Objects.isNull(object)) { // 如果是他的子类,那么就可以用ObjectUtils.isEmpty进行比较
                            flag = false;//不为空
                        }
                    } else { //如果不是那就直接用null判断
                        if (!(null == object || "".equals(object))) {
                            flag = false;//不为空
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return flag;//false:不都为空
        }
    
    
    }
    

    展开全文
  • 此代码为java运用poi3.8插件实现运用模版导入导出复杂excel
  • 数据库连接 操作数据库 导入EXCEL表格 界面设计 还有进度条
  • 项目需要,要实现一个导入导出excel的功能,于是,任务驱动着我学习到了POI和JXL这2个java操作Excel的插件。一、POI和JXL介绍1、POI:是对所有office资源进行读写的一套工具包、属于apache开源组织。poi操作excel:...
  • 支持从数据库中查询到数据,以多级表头形式导入Excel文件中。比如:一级表头:学号、姓名、各科成绩。二级表头:语文、数学、英语(二级表头在各科成绩下面)。
  • 使用poi读取写入复杂excel内容
  • 公司正在使用的员工考勤系统,对打卡机生成的原始数据进行了一系列复杂的处理后生成标准模板。该软件导入Tomcat及能运行。使用了Struts2,框架。对Excel的时间函数进行处理。虽然资源分高了点,但绝对超值,设计公司...
  • java实现excel数据导入

    2021-03-05 20:09:01
    前段时间在 github 上发现了阿里的 EasyExcel 项目,觉得挺不错的,就写了一个简单的方法封装,做到只用一个函数就完成 Excel导入或者导。刚好前段时间更新修复了一些 BUG,就把我的这个封装分享出来,请多多指教...
  • java导入/导出excel

    千次阅读 2022-04-06 20:26:48
    开发中经常会设计到excel的处理,如导出Excel导入Excel到数据库中,操作Excel等。 Apache POl简介是用Java编写的免费开源的跨平台的Java APl,Apache POl提供APl给Java程式对Microsoftoffice (Excel、WORD、...
  • java导入导出excel文件

    2021-07-21 17:26:17
    这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的...,丰富你的文章UML 图表FLowchart流程图导出与导入导出导入...
  • 如何用java 将txt数据导入excel代码如下:import java.io.*;import jxl.*;import jxl.write.*;public class CreateXLS{public static void main(String args[]){try{//打开文件WritableWorkbook book= Workbook....
  • 导入Maven依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>${easyexcel.version}</version> </dependency> ...
  • 初始化readExcel对象 26为excel列数 //文件名称 String file_id = ra.getParameter("file_id"); //文件路径 String filePath = ra.getFileWritePath() + "files/" + file_id; //文件对象 File file = new File...
  • 你只要用缓存,就可能会涉及到缓存与数据库双存储双写,你只要是双写,就一定会有数据一致性的问题,那么你如何解决一致性问题? 面试题剖析 一般来说,如果允许缓存可以稍微的跟数据库偶尔有不一致的情况,也就是说...
  • import java.io.FileOutputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Comparator; import java.util.List; import java.util.UUID; ...
  • java web项目导入excel获取数据,是实用频率非常高的功能,通过做了几个这样的功能之后,现将此功能总结出了,为了以后自己方便使用,也为大家实现此功能做一个参考. 项目框架 1,后台:spring+springmvc+mybatis 2,前台...
  • 本人自主研发的ExcelUtil工具类,主要实现列表数据excel文件互相转换的功能。 作用:快速实现数据导入和导出功能。...优势:只需配置注解即可实现数据导入导出功能,无需熟悉POI,并编写复杂的代码。
  • } } 测试类 String path = "D:\\java-demo\\kuang-poi\\"; @Test public void simpleRead() { String fileName = path+"悠游阅读成长计划-单词部分.xls"; // 这里 需要指定读用哪个class去读,然后读取第一个sheet ...
  • JAVA POI通用Excel导入模板Excel导入模板类 Excel导入模板类 package com.golte.dataform.analysis.controller; import com.alibaba.fastjson.JSON; import com.golte.common.GlobalResponse; import ...
  • Java Aspose导入导出excel

    2021-08-27 16:05:49
    * 导出Excel数据 * @param query * @return * @throws Exception */ public InputStream exportBwClass(BwClass query) throws Exception { // 导出路径 String logPath = "D:" + File.separator + "excel...
  • 由于公司框架的数据表格自带了excel导出功能(N年前的老框架,文档不全BUG贼多),所以只需怼excel导入即可,流程是这样的:选择excel →文件上传→处理数据。文件上传这块做过,不怂,而且现在的SpringMVC框架的...
  • @ApiOperation(value = "全自动导入资源和编目") @PostMapping("/autoExcelToSql") public Response readExcelToList(@RequestPart("file") MultipartFile file) throws IOException, BizException { String ...
  • 定义导入excel模板 定义需要导入EXCEL模板,EXCEL格式变更,只需要变更对应的枚举信息就可以,如果定义多个EXCEL模板, 只要定义多个枚举就行 package org.web.sales.admin.util.excel; import lombok....
  • 根据模板导出excel适用于结构复杂excel表格数据导出,提前定义好excel模板,设置好变量,然后导出数据excel模板中。 在idea中创建一个excel文件 打开创建好的文件province.xls,开始定义模板,设置变量用${}...
  • Java实现Excel文件的导入功能

    千次阅读 2020-12-29 17:00:53
    近期在工作上,遇到了实现Excel文件的导入功能,在此和小伙伴们分享一下过程。 实现Excel文件的导入呢,首先我们需要先上传文件,然后在后端进行解析文件中的内容。这里我们需要用到 poi 的这样一个jar包。 因为我的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,570
精华内容 3,428
关键字:

java导入复杂excel数据

java 订阅