页面上将excel导入数据到数据库

从页面读取excel导入到数据库,工作中需要在页面增加功能,读取excel,将数据存入数据库,在此记录一下

依赖

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

前台页面使用的是layui,也可以导入的同时携带额外的参数

layui.use('upload', function () {
    var uu = layui.upload.render({
        elem: '#test1',
        url: '/data/import/import1',
        auto: false,
        //,multiple: true
        accept: 'file', //普通文件
        exts: 'xls|xlsx', //只允许excel
        bindAction: '#test2',
        data: {},
        before: function (obj) {
            // 此种方式可以携带额外的参数
            this.data = {
                siteCode: $("#siteCode").val(),
                radio: $("input[type='radio'][name='radio']:checked").val(), 
                checkbox: $("input[type='checkbox'][name='checkbox']").is(':checked'), 
            }
        },
        done: function(res, index, upload){
            layer.msg(res.msg)
        }
    });

Controller层代码

// 数据导入
    @RequestMapping("/import1")
    @ResponseBody
    public R importExcel(MultipartFile file,@RequestParam Map<String,Object> map) {
        R r = new R();
        try {
            String str = dataImportService.importExcel(file,map);
            r.put("msg", str);
        } catch (Exception e) {
            r.put("msg", "导入失败");
        }
        return r;
    }

Service层代码

 public String importExcel(MultipartFile myFile, Map<String,Object> map) throws IOException {
        Workbook workbook = null;
        String fileName = myFile.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            // 2003版本excel
            workbook = new HSSFWorkbook(myFile.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
        	// 2007以后的excel
            workbook = new XSSFWorkbook(myFile.getInputStream());
        } else {
            return "文件格式错误";
        }
        int sheets = workbook.getNumberOfSheets();  // 获取sheet个数
        Sheet sheet0 = workbook.getSheetAt(0);
        int rows = sheet0.getPhysicalNumberOfRows(); // 获取有效行数
        if (rows == 0) {
            return "请填写有效数据";
        }
        List<ParamId> allParamName = dataImportDao.findAllParamName();  // 查出所有ID和名称
        List<ExcelData> excelData = ImpUtils.import1(sheet0,sheet1,rows,siteCode,allParamName);
        // 如果数据太多,我们可以将集合均分成若干份,循环插入
        List<List<ExcelData>> lists = splitList(excelData, 2448);
for (int i = 0; i < lists.size(); i++) {
            List<ExcelData> list = lists.get(i);
            // 删除库中相同数据,避免重复
             dataImportDao.removeSame(siteCode, start, end);
             // 插入数据
            dataImportDao.insertImport1(list); 
        }
        return "导入成功";
    }

调用的读取excel封装成对象集合的方法

    public static List<ExcelData> import1(Sheet sheet0, Sheet sheet1, int rows, String siteCode, List<ParamId> ids) {
        ArrayList<ExcelData> list = new ArrayList<>();
        // 第一行一般是表头
        Row row0 = sheet0.getRow(0);
        for (int i = 1; i < rows + 1; i++) {
            Row row = sheet0.getRow(i);
            if (row != null) {
            // 我这里读取一百列数据
                for (int j = 1; j < 100; j++) {
                    ExcelData excelData = new ExcelData();
                    excelData.setSiteCode(siteCode);    // 站点编码
                    excelData.setDate(getCellValue(row.getCell(0)));  // 数据时间
                   //  Cell headCell = row0.getCell(j); // 取出行头
                    // String head = getCellValue(headCell);
                    // 取出第I行J列的值
					String cellValue = getCellValue(row.getCell(j));
					// 设置进对象
					excelData.setPpbValue(cellValue);
					// 将每个对象装进集合
                    list.add(excelData);
                }
            }
        }
        return list;
    }
	// 从单元格取值
    private static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:  // 数字
                    value = cell.getNumericCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

至此导入数据到数据库就完成啦,若有遗漏,欢迎指出