Excel的导入导出

可以在评论区交流!!!

大数据的导入请参考:https://blog.csdn.net/qq_42992729/article/details/102514065

前端:

html代码:

<div id="choose" style="display: none; height: 100px; 300px" >
                    <form enctype="multipart/form-data" id="batchUpload" class="form-horizontal">
                        <button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;"  type="button" >选择文件</button>
                        <input type="file" name="file"  style="0px;height:0px;" id="uploadEventFile">
                        <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px; 200px;" />
                    </form>
                    <button type="button" class="btn btn-success btn-sm"  id="uploadBtn" >上传</button>
                </div>
                <button type="button" class="layui-btn" id="excelImport"><i class="layui-icon">&#xe654;</i>导入</button>
                <button type="button" class="layui-btn" id="excelExport"><i class="layui-icon">&#xe642;</i>导出</button>

JS代码:

/**
     * 导入
     */
    var excelImport = document.getElementById("excelImport");
    excelImport.onclick = function() {
        layer.open({
            type: 1,
            content: $('#choose'), //这里content是一个DOM，注意：最好该元素要存放在body最外层，否则可能被其它的相对元素所影响

        });
    }
    var User = function() {
        this.init = function() {
            //模拟上传excel
            $("#uploadEventBtn").unbind("click").bind("click", function() {
                $("#uploadEventFile").click();
            });
            $("#uploadEventFile").bind("change", function() {
                $("#uploadEventPath").attr("value",    $("#uploadEventFile").val());
            });
        };
        //点击上传钮
        var uploadBtn = document.getElementById("uploadBtn");
        uploadBtn.onclick = function() {
            var uploadEventFile = $("#uploadEventFile").val();
            if (uploadEventFile == '') {
                alert("请择excel,再上传");
            } else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel
                alert("只能上传Excel文件");
            } else {
                var url = "/phySsPersonInfo/import";

                var formData = new FormData($('form')[0]);
                user.sendAjaxRequest(url, "POST", formData);
            }

        };
        this.sendAjaxRequest = function(url, type, data) {
            $.ajax({
                url : url,
                type : type,
                data : data,
                dataType : "json",
                success : function(result) {
                    alert(result.message);
                    layer.closeAll();
                    $('#SsPersonInfoTable').bootstrapTable('refresh');

                },
                error : function(result) {
                    alert(result.message);
                    layer.closeAll();
                    $('#SsPersonInfoTable').bootstrapTable('refresh');

                },
                cache : false,
                contentType : false,
                processData : false
            });
        };
    };
    var user;
    $(function() {
        user = new User();
        user.init();
    });



    var exportBtn = document.getElementById("excelExport");
    exportBtn.onclick = function() {
        if (confirm("确定导出?")) {
            $.ajax({
                url : "/phySsPersonInfo/exportVillageFile",
                type : "post",
                data:{},
                dataType : "json",
                success : function(result) {
                    alert(result.message);
                },
                error : function(result) {
                    alert(result.message);
                },
                cache : false,
                contentType : false,
                processData : false
            });
        }

    }

后端代码:

Controller层:

/**
     * 导入excl
     */
    //导入excel
     @RequestMapping(value = "/import", method=RequestMethod.POST)
     @ResponseBody
     public Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request, HttpServletResponse response){
         Map<String, Object> map = new HashMap<String, Object>();
         String result = ssPersonInfoService.readExcelFile(file);
         map.put("message", result);
         return map;
     }

    /**
     * 按村为单位导出excel
     */
    @RequestMapping("/exportVillageFile")
    @ResponseBody
    public Map<String, Object> exportVillageFile(HttpServletResponse response,HttpServletRequest request) {
        String result = "";
        try{
            result = ssPersonInfoService.VillageFile(response,request);
        }catch(Exception e){
            e.printStackTrace();
        }
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("message", result);
        return map;
    }

Service接口:

/**
     * 插入数据
     * */
    Integer insertall(SsPersonInfo ssPersonInfo);

    /**
     * 获取所有的镇
     */
    List<Map<String,Object>> getAllTown();

    /**
     * 根据镇获取所有的村
     */
    List<Map<String,Object>> getAllVillage(Map<String,Object> map);

    /**
     * 以村为单位获取人员信息
     */
    List<SsPersonInfo> getPayableInfoByTown(Map<String,Object> map);

Service实现类:

@Override
    public String readExcelFile(MultipartFile file) {
        String result = "";
        //创建处理EXCEL的类
        ReadExcel readExcel = new ReadExcel();
        //解析excel，获取上传的事件单
        List<Map<String, Object>> userList = readExcel.getExcelInfo(file);
        //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
        for(Map<String, Object> user:userList){
            String nativePlace = user.get("nativePlace").toString();
            String [] arr = nativePlace.split(" ");
            SsPersonInfo ssPersonInfo = new SsPersonInfo();
            ssPersonInfo.setIdentityCard(user.get("identityCard").toString());
            ssPersonInfo.setName(user.get("name").toString());
            ssPersonInfo.setNativePlace(user.get("nativePlace").toString());
            ssPersonInfo.setTown(arr[1]);
            ssPersonInfo.setVillage(arr[2]);
            int ret = ssPersonInfoMapper.insertall(ssPersonInfo);
            if(ret == 0){
                result = "插入数据库失败";
            }
        }
        if(userList != null && !userList.isEmpty()){
            result = "上传成功";
        }else{
            result = "上传失败";
        }
        return result;
    }

    /**
     * 获取所有的乡镇/街道
     * @return 所有的乡镇/街道
     */
    @Override
    public List<Map<String, Object>> getAllTown() {
        return ssPersonInfoMapper.getAllTown();
    }

    /**
     * 根据乡镇获取所辖的村/社区
     * @param map 乡镇
     * @return 本镇所有的村
     */
    @Override
    public List<Map<String, Object>> getAllVillage(Map<String, Object> map) {
        return ssPersonInfoMapper.getAllVillage(map);
    }

    /**
     * 用于导出以村为单位的数据
     * @param response 用于输出文件
     */
    @Override
    public String VillageFile(HttpServletResponse response, HttpServletRequest request) {
        String result = "";
        List<Map<String, Object>> allTowns = ssPersonInfoMapper.getAllTown();
        if (allTowns.size()!=0){
            result = "导出成功";
        }
//        String dirPath = request.getSession().getServletContext().getRealPath("/static/download/");
        String dirPath = "D:\study\金现代\冠县社保信息\";
        for (Map<String, Object> town : allTowns){
            //获取乡镇名称
            String Town = (String) town.get("Town");
            //获取乡镇下辖的村
            List<Map<String, Object>> allVillages = ssPersonInfoMapper.getAllVillage(town);
            new Thread(){
                @Override
                public void run() {
                    for (Map<String, Object> village : allVillages){
                        Map<String,Object> param = new HashMap<>(5);
                        String Village = (String) village.get("Village");

                        param.put("Town",Town);
                        param.put("Village",Village);
                        //填入Excel表格中的数据
                        List<SsPersonInfo> ssPersonInfos = ssPersonInfoMapper.getPayableInfoByTown(param);

                        String fileName = Village+"村（社区）社保人员清单.xls";
                        String path = dirPath+Town+"\"+Village+"\";
                        File targetFile = new File(path);
                        if(!targetFile.exists()){//如果文件夹不存在
                            targetFile.mkdirs();
                        }
                        response.setContentType("octets/stream");
                        try {
                            FileOutputStream fos = new FileOutputStream(new File(path+fileName));
                            ByteArrayOutputStream os = new ByteArrayOutputStream();
                            response.addHeader("Content-Disposition",
                                    "attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));
                            HSSFWorkbook wb = new HSSFWorkbook();

                            String sheetName=Village+"村（社区）社保人员清单";
                            HSSFSheet sheet = wb.createSheet(sheetName);

                            /* 设置打印格式 */
                            HSSFPrintSetup hps = sheet.getPrintSetup();
                            hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
                            hps.setLandscape(true);
                            hps.setFitHeight((short) 1);
                            hps.setFitWidth((short) 1);
                            hps.setScale((short) 65);
                            hps.setFooterMargin(0);
                            hps.setHeaderMargin(0);
                            sheet.setMargin(HSSFSheet.TopMargin, 0.3);
                            sheet.setMargin(HSSFSheet.BottomMargin, 0);
                            sheet.setMargin(HSSFSheet.LeftMargin, 0.3);
                            sheet.setMargin(HSSFSheet.RightMargin, 0);


                            //创建第一行
                            HSSFRow row = sheet.createRow((short) 0);
                            HSSFCell cell ;
                            row.setHeightInPoints(40);

                            HSSFFont font = wb.createFont();
                            font.setFontName("宋体");
                            //粗体显示
                            font.setBold(true);
                            font.setFontHeightInPoints((short) 16);
                            cell = row.createCell(0);
                            cell.setCellValue("身份证");
                            cell = row.createCell(1);
                            cell.setCellValue("姓名");
                            cell = row.createCell(2);
                            cell.setCellValue("籍贯");
                            cell = row.createCell(3);
                            cell.setCellValue("镇");
                            cell = row.createCell(4);
                            cell.setCellValue("村");
                            sheet.setColumnWidth(0, 4096);
                            sheet.setColumnWidth(1, 4096);
                            sheet.setColumnWidth(2, 4096);
                            sheet.setColumnWidth(3, 4096);
                            sheet.setColumnWidth(4, 4096);
                            //设置列值-内容
                            for (int i = 0; i < ssPersonInfos.size(); i++) {
                                row = sheet.createRow(i + 1);
                                row.setHeightInPoints(20);
                                SsPersonInfo ssPersonInfo = ssPersonInfos.get(i);

                                cell = row.createCell(0);
                                cell.setCellValue(ssPersonInfo.getIdentityCard());
                                cell = row.createCell(1);
                                cell.setCellValue(ssPersonInfo.getName());
                                cell = row.createCell(2);
                                cell.setCellValue(ssPersonInfo.getNativePlace());
                                cell = row.createCell(3);
                                cell.setCellValue(ssPersonInfo.getTown());
                                cell = row.createCell(4);
                                cell.setCellValue(ssPersonInfo.getVillage());
                            }

                            wb.write(os);
                            InputStream excelStream = new ByteArrayInputStream(os.toByteArray());
                            //写入目标文件
                            byte[] buffer = new byte[1024*1024];
                            int byteRead = 0;
                            while((byteRead= excelStream.read(buffer))!=-1){
                                fos.write(buffer, 0, byteRead);
                                fos.flush();
                            }
                            fos.close();
                            excelStream.close();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
                }.start();

        }
        return result;
        
    }

Mapper接口:

/**
     * 插入数据
     * */
    Integer insertall(SsPersonInfo ssPersonInfo);

    /**
     * 获取所有的镇
     */
    List<Map<String,Object>> getAllTown();

    /**
     * 根据镇获取所有的村
     */
    List<Map<String,Object>> getAllVillage(Map<String,Object> map);

    /**
     * 以村为单位获取人员信息
     */
    List<SsPersonInfo> getPayableInfoByTown(Map<String,Object> map);

Mapper.xml:　

<!--获取所有镇-->

<select id="getAllTown" resultType="map">
        SELECT DISTINCT town Town FROM ss_person_info
    </select>

<!--根据镇获取所有的村-->

<select id="getAllVillage" resultType="map" parameterType="map"> SELECT DISTINCT village Village FROM ss_person_info WHERE town=#{Town} </select> 

<!--根据镇和村获取一个村的人员信息-->

<select id="getPayableInfoByTown" parameterType="map" resultMap="BaseResultMap"> select identity_card,name,native_place,town,village from ss_person_info WHERE town=#{Town} and village=#{Village} </select>

实体类:

@TableName("ss_person_info")
public class SsPersonInfo extends Model<SsPersonInfo> {

    private static final long serialVersionUID = 1L;

    /**
     * 身份证
     */
    @TableField("identity_card")
    private String identityCard;
    /**
     * 姓名
     */
    @TableField("name")
    private String name;
    /**
     * 籍贯
     */
    @TableField("native_place")
    private String nativePlace;
    /**
     * 镇
     */
    @TableField("town")
    private String town;
    /**
     * 村
     */
    @TableField("village")
    private String village;


    public String getIdentityCard() {
        return identityCard;
    }

    public void setIdentityCard(String identityCard) {
        this.identityCard = identityCard;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getNativePlace() {
        return nativePlace;
    }

    public void setNativePlace(String nativePlace) {
        this.nativePlace = nativePlace;
    }

    public String getTown() {
        return town;
    }

    public void setTown(String town) {
        this.town = town;
    }

    public String getVillage() {
        return village;
    }

    public void setVillage(String village) {
        this.village = village;
    }

    @Override
    protected Serializable pkVal() {
        return this.serialVersionUID;
    }

    @Override
    public String toString() {
        return "SsPersonInfo{" +
        "identityCard=" + identityCard +
        ", name=" + name +
        ", nativePlace=" + nativePlace +
        ", town=" + town +
        ", village=" + village +
        "}";
    }
}

PS.根据实体类改动service层和自定义类的内容

自定义读取Excel类:

     *
     * @param is      输入流
     * @param isExcel2003   excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel里面客户的信息
     */
    private List<Map<String, Object>> readExcelValue(Workbook wb) {
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            // 循环Excel的列
            Map<String, Object> map = new HashMap<String, Object>();
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 1) {
                        // 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
                        if (cell.getCellTypeEnum() == NUMERIC) {
                            String identityCard = String.valueOf(cell.getNumericCellValue());
                            map.put("identityCard", identityCard.substring(0, identityCard.length() - 2 > 0 ? identityCard.length() - 2 : 1));// 身份证
                        } else {
                            map.put("identityCard", cell.getStringCellValue());// 身份证
                        }
                    } else if (c == 2) {
                        if (cell.getCellTypeEnum() == NUMERIC) {
                            String name = String.valueOf(cell.getNumericCellValue());
                            map.put("name",name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));// 姓名
                        } else {
                            map.put("name",cell.getStringCellValue());// 姓名
                        }
                    } else if (c == 17) {
                        if (cell.getCellTypeEnum() == NUMERIC) {
                            String nativePlace = String.valueOf(cell.getNumericCellValue());
                            map.put("nativePlace", nativePlace.substring(0, nativePlace.length() - 2 > 0 ? nativePlace.length() - 2 : 1));// 籍贯
                        } else {
                            map.put("nativePlace", cell.getStringCellValue());// 籍贯
                        }
                    }
                    }
            }
            // 添加到list
            userList.add(map);
        }
        return userList;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\.(?i)(xlsx)$");
    }

}

最终效果:

导入:

导出:

 

 

 按照镇,村结构导出!!!