Excel表数据导入到数据库

最新推荐文章于 2024-09-14 18:46:05 发布

holita

最新推荐文章于 2024-09-14 18:46:05 发布

阅读量145

点赞数

文章标签： java excel

本文链接：https://blog.csdn.net/holita/article/details/107411216

版权

HelpOthersRecordController.java

 /**
     * 导入暖心帮扶记录界面
     *
     * @return
     */
    @GetMapping("uploadView")
    public String uploadView() {
        return "helpOthersRecord/upload";
    }

    /**
     * 导入数据
     *
     * @param file
     * @return
     */
    @PostMapping(value = "import")
    @ResponseBody
    public Object uploadAndImport(@RequestParam("file") MultipartFile file) {
        return helpOthersRecordService.importExcelAndExportResult(file);
    }

    /**
     * 下载模板
     *
     * @return
     */
    @GetMapping("download/template")
    public void downloadTemplate(HttpServletResponse response) throws IOException {
        try {
            String title = "暖心帮扶导入模板";
            String extension = "xls";
            String fileName = URLEncoder.encode(title + "." + extension, "UTF-8");
            byte[] bytes = ExcelUtils.buildExcel(title, "时间格式：2020-01-01 10:10:10", HelpOthersRecordForm.class).toByteArray();
            response.reset();
            response.setContentType("application/msexcel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename= " + fileName);
            response.getOutputStream().write(bytes);
            response.getOutputStream().flush();
        } finally {
            StreamUtils.close(response.getOutputStream());
        }
    }

HelpothersRecordForm.java(暖心帮扶导入模板)

public class HelpOthersRecordForm {
    private Long id;//id

    @ExcelColumn(value = "姓名", sort = 1)
    private String peopleName;//姓名

    @ExcelColumn(value = "联系方式", sort = 2)
    private String mobile;//联系方式

    @ExcelColumn(value = "预约时间", sort = 3)
   @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date appointmentTime;//预约时间

    @ExcelColumn(value = "家庭地址", sort = 4)
    private String homeAddress;//家庭地址

    @ExcelColumn(value = "事项", sort = 5)
    private String matter;//事项

    @ExcelColumn(value = "备注", sort = 6)
    private String remark;//备注

    @ExcelColumn(value = "小区", sort = 7)
    private String helpOthersId;//小区

    @ExcelColumn(value = "所属社区", sort = 8)
    private String juwei;//所属社区


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getPeopleName() {
        return peopleName;
    }

    public void setPeopleName(String peopleName) {
        this.peopleName = peopleName;
    }



    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public Date getAppointmentTime() {
        return appointmentTime;
    }

    public void setAppointmentTime(Date appointmentTime) {
        this.appointmentTime = appointmentTime;
    }

    public String getHomeAddress() {
        return homeAddress;
    }

    public void setHomeAddress(String homeAddress) {
        this.homeAddress = homeAddress;
    }

    public String getMatter() {
        return matter;
    }

    public void setMatter(String matter) {
        this.matter = matter;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
    public String getHelpOthersId() {
        return helpOthersId;
    }

    public void setHelpOthersId(String helpOthersId) {
        this.helpOthersId = helpOthersId;
    }

    public String getJuwei() {
        return juwei;
    }

    public void setJuwei(String juwei) {
        this.juwei = juwei;
    }
}

HelpothersRecordService.java(接口）

 Object importExcelAndExportResult(@RequestParam("file") MultipartFile file);

HelpothersRecordServicelmpl.java(接口实现类）

public static final String UploadFilesPath = AttachmentController.class.getResource("/").getPath() + "/../../.." + FileName.UPDATENAME.type;

    private File eleDirFile = new File(UploadFilesPath + "/elevator");

    @Override
    public Object importExcelAndExportResult(MultipartFile targetFile) {
        Map<String, Object> result = new HashMap<>();
        AtomicInteger successTotal = new AtomicInteger();
        AtomicInteger failedTotal = new AtomicInteger();

        try {
            if (!targetFile.isEmpty()) {
                if (ExcelUtils.checkExtension(targetFile)) {
                    Workbook workbook = readData(targetFile.getInputStream(), 1, failedTotal, successTotal);
//                    Sheet sheet = workbook.getSheetAt(0);
//                    CellStyle centerStyle = buildCenterStyle(workbook);
//                    AtomicInteger current = new AtomicInteger();

                    String fileName = "暖心帮扶导入反馈信息_" + System.currentTimeMillis();
                    eleDirFile.mkdirs();
                    File saveFile = new File(eleDirFile.getPath() + "/" + fileName + ".xlsx");
                    FileOutputStream fos = new FileOutputStream(saveFile);
                    try {
                        workbook.write(fos);
                    } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                        StreamUtils.close(fos);
                        StreamUtils.close(workbook);
                    }
                    result.put("successTotal", successTotal.get());
                    result.put("failedTotal", failedTotal.get());
                    result.put("status", true);
                    result.put("msg", "导入成功");

                } else {
                    result.put("status", false);
                    result.put("msg", "不支持的文件类型,导入失败");
                }
            } else {
                result.put("status", false);
                result.put("msg", "文件为空");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("status", false);
            result.put("msg", "导入失败");
        }
        return result;
    }






    public Workbook readData(InputStream inputStream, Integer startRowNum, AtomicInteger failedTotal, AtomicInteger successTotal) {
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            int last = sheet.getLastRowNum();
            for (int i = 3; i <= last; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                HelpOthersRecord jr = new HelpOthersRecord();
                String peopleName = ExcelUtils.auto(row.getCell(0));
                jr.setPeopleName(peopleName);


                String mobile = ExcelUtils.auto(row.getCell(1));
                jr.setMobile(mobile);
                String appointmentTime = ExcelUtils.autoTwo(row.getCell(2));
                try {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date parse = dateFormat.parse(appointmentTime);
                    String format = dateFormat.format(parse);
                    jr.setAppointmentTime(appointmentTime);
                }catch (Exception e){
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    jr.setAppointmentTime(dateFormat.format(new Date()));
                }
                String homeAddress = ExcelUtils.auto(row.getCell(3));
                jr.setHomeAddress(homeAddress);
                String matter = ExcelUtils.auto(row.getCell(4));
                jr.setMatter(matter);
                String remark = ExcelUtils.auto(row.getCell(5));
                jr.setRemark(remark);
                String helpOthersId = ExcelUtils.auto(row.getCell(6));
                String juwei = ExcelUtils.auto(row.getCell(7));
                if(StringUtils.isNotEmpty(helpOthersId)){
                    Map<String,Object> helpOthersBean = helpOthersMapper.selectByCommunity(helpOthersId,juwei);
                    if (helpOthersBean!=null){
                        jr.setHelpOthersId(helpOthersBean.get("ID").toString());
                    }else{
                        jr.setHelpOthersId("43");
                    }
                }else {
                    continue;
                }
                Cell cell = row.createCell(8);

                try {

                    helpOthersRecordMapper.insertHelpOthersRecord(jr);
                    if (jr.isDel()) {
                        failedTotal.getAndIncrement();
                    } else {
                        cell.setCellValue("导入成功");
                        successTotal.getAndIncrement();
                    }

                } catch (Exception e) {
                    cell.setCellValue("导入时出错");
                    e.printStackTrace();
                }
            }
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        return workbook;
    }

ExcelUtils.java(将导入的数据进行检验，尤其是时间数据进行时间格式化）

public static String auto(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value = StringUtils.trimToEmpty(cell.toString());
        if (cell.getCellType() == 0) {
            DecimalFormat df = new DecimalFormat("0");//使用DecimalFormat类对科学计数法格式的数字进行格式化
            if (cell.toString().indexOf(".") > 0) {
                return df.format(cell.getNumericCellValue());
            }
        }
        return value;
    }

    public static String autoTwo(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value = StringUtils.trimToEmpty(cell.toString());
        if (cell.getCellType() == 0) {
            DecimalFormat df = new DecimalFormat("0");//使用DecimalFormat类对科学计数法格式的数字进行格式化
            if (cell.toString().indexOf(".") > 0) {
                return df.format(cell.getNumericCellValue());
            }
        }

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value1 = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value1);
                        value = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        value = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    value = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                value = cell.getBooleanCellValue()+"";;
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                value = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                value = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                value = "ERROR VALUE";
                break;
            default:
                value = "UNKNOW VALUE";
                break;
        }
        return value;
    }

upload.html(导入模态框）

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" lang="en">
<body>
<style>
    .progress-content-box {
        display: none;
        position: absolute;
        top: 0;
        bottom: 0;
        right: 0;
        left: 0;
        justify-content: center;
    }

    .progress-content-box .progress {
        width: 100%;
    }

    .progress-content-box .progress-text {
        position: absolute;
        top: 0;
        left: 10%;
        right: 10%;
        font-size: 16px;
        color: #2F2E2E;
    }
</style>
<div style="overflow: hidden;margin-top:16px;">
    <div id="uploader" class="uploader" data-ride="uploader">
        <div class="uploader-message text-center">
            <div class="content"></div>
            <button type="button" class="close">×</button>
        </div>
        <div class="uploader-files file-list file-list-grid"></div>
        <div>
            <hr class="divider"/>
            <div class="uploader-status pull-right text-muted"></div>
            <button type="button" class="btn btn-link uploader-btn-browse">
                <i class="icon icon-plus"></i> 选择文件
            </button>
            <button type="button" class="btn btn-link uploader-btn-start">
                <i class="icon icon-cloud-upload"></i> 上传并导入
            </button>
            <button type="button" class="btn btn-link btn-download-file" onclick="downloadTemplate()">
                <i class="icon icon-download-alt"></i> 下载Excel模板
            </button>
        </div>
    </div>
</div>
<div class="progress-content-box" id="progress-show">
    <div class="progress progress-striped active">
        <div class="progress-bar progress-bar-success" role="progressbar" aria-valuenow="40" aria-valuemin="0"
             id="progress-bar"
             aria-valuemax="100" style="width: 0%">
            <span class="progress-text" id="progress-text">开始导入数据</span>
        </div>
    </div>
</div>
</body>
<script type="text/javascript" th:inline="javascript">
    /*<![CDATA[*/
    var basePath =/*[[@{/}]]*/;
    var setIntervalObj;
    var currentUploadFileName;

    $('#uploader').uploader({
        autoUpload: false,
        chunk_size: 0,
        lang: 'zh_cn',
        url: basePath + 'helpOthersRecord/import',
        filters: {
            mime_types: [
                {title: 'excel', extensions: 'xls,xlsx'}
            ]
        },
        onBeforeUpload: function (file) {
            currentUploadFileName = file.name;
            // window.clearInterval(setIntervalObj);
            // setIntervalObj = setInterval(showProgress, 1000);
        },
        responseHandler: function (responseObject, file) {
            console.log(responseObject, file)
            window.clearInterval(setIntervalObj);
            $("#progress-show").hide();
            $("#progress-bar").width("0%");
            var obj = eval("(" + responseObject.response + ")");
            var msg = file.name + ":";
            if (obj.status) {
                // if (confirm("成功导入" + obj.successTotal + "条，失败" + obj.failedTotal + "条,确定要下载导入反馈表格吗?")) {
                //     window.location.href = obj.downloadPath;
                // }
                alert("成功导入" + obj.successTotal + "条，失败" + obj.failedTotal + "条");
            } else {
                msg += obj.msg;
                layer.msg(msg);
            }
            reflush();
        }
    });

  
    function downloadTemplate() {
        window.location.href = basePath + "helpOthersRecord/download/template"
    }



    /*]]>*/
</script>
</html>