Excel表格导入导出

Maven依赖

<!--Excel表格导入导出-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.17</version>
</dependency>

表格数据

Excel表格导出:

1.导出到本地

public void fun() throws Exception {
        //操创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet("人员列表");
        //创建行,参数表示索引,行数索引位
        Row row = sheet.createRow(5);
        //创建单元格,参数表示索引,列数索引位
        Cell cell = row.createCell(5);
        //设置单元格值
        cell.setCellValue("我爱我的祖国");

        /**
         * 设置样式
         */
        //设置行的高度
        row.setHeightInPoints(50);
        //设置行中某个单元格的宽度
        sheet.setColumnWidth(5,30*256);
        //设置单元格各样式
        CellStyle cellStyle = workbook.createCellStyle();
        
        //设置边框样式
        cellStyle.setBorderTop(BorderStyle.DASHED);
        cellStyle.setBorderBottom(BorderStyle.DASH_DOT);
        cellStyle.setBorderLeft(BorderStyle.HAIR);
        cellStyle.setBorderLeft(BorderStyle.DOUBLE);
        cellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());

        //设置单元格文字位置
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格字体
        Font font = workbook.createFont();
        font.setFontName("华文隶书");
        font.setFontHeightInPoints((short) 20);
        font.setBold(true);
        font.setColor(IndexedColors.RED.getIndex());
        cellStyle.setFont(font);
        //设置单元格样式
        cell.setCellStyle(cellStyle);

        //输出到本机
        FileOutputStream outputStream = new FileOutputStream("F:\\导出Excel.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

2.<a>标签Excel导出

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@GetMapping("export")
    public ResponseEntity<byte[]> export() throws Exception {
        //获取所有员工信息
        List<User> list = iUserService.list();
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("用户信息");

        //创建标题行
        Row titleRow = sheet.createRow(0);
        String[] title = {"用户id", "用户名称", "用户密码", "用户地址", "用户手机", "创建时间"};

        for (int i = 0; i < title.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(title[i]);
        }

        for (int i = 0; i < list.size(); i++) {
            User user = list.get(i);
            Row row = sheet.createRow(i);

            Cell cellId = row.createCell(0);
            cellId.setCellValue(user.getId());

            Cell cellName = row.createCell(1);
            cellName.setCellValue(user.getUserName());

            Cell cellPass = row.createCell(2);
            cellPass.setCellValue(user.getUserPwd());

            Cell cellAddress = row.createCell(3);
            cellAddress.setCellValue(user.getUserAddress());

            Cell cellPhone = row.createCell(4);
            cellPhone.setCellValue(user.getUserPhone());

            Cell cellTime = row.createCell(5);
            cellTime.setCellValue(sdf.format(user.getAddTime()));


        }

        ByteArrayOutputStream out = new ByteArrayOutputStream();

        workbook.write(out);
        byte[] bytes = out.toByteArray();

        HttpHeaders httpHeaders = new HttpHeaders();
        httpHeaders.setContentDispositionFormData("attachment", URLEncoder.encode("用户信息表.xlsx", "utf-8"));
        ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(bytes, httpHeaders, HttpStatus.OK);

        workbook.close();
        out.close();

        return responseEntity;
    }

Excel表格导入:

1.本地读取

public void fun5() throws Exception {
        //加载文件
        Workbook workbook = new XSSFWorkbook("C:\\Users\\用户信息表.xlsx");
        //获得sheet
        //workbook.getSheetAt(0);同下
        Sheet sheet = workbook.getSheet("用户信息");

        //获取单元格信息
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());//用户id

        //在表格中日期和数字都默认为是数字
        Row row1 = sheet.getRow(1);
        Cell cell1 = row1.getCell(4);
        Cell cell2 = row1.getCell(5);
        System.out.println(cell1.getNumericCellValue());//8000.0

        //日期得使用日期获取
        System.out.println(cell2.getDateCellValue());//Thu Apr 01 00:00:00 CST 2021
        System.out.println(DateUtil.isCellDateFormatted(cell2));//true,表示这个列是否是日期格式的

    }



 public void fun6() throws Exception {
        Workbook workbook = new XSSFWorkbook("C:\\Users\\JAVASM\\Desktop\\用户信息表.xlsx");
        Sheet sheet = workbook.getSheet("用户信息");

        //获取一共多少行,值为索引
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);

            //获取一行一共有多少个单元格,值为个数,不是索引
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);

                //获取单元格值
                if (cell!=null){
                    //使用枚举判断单元格属性
                    CellType cellTypeEnum = cell.getCellTypeEnum();
                    switch (cellTypeEnum){
                        case BOOLEAN:
                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case STRING:
                            System.out.println(cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)){
                                System.out.println(cell.getDateCellValue());
                            }else {
                                System.out.println(cell.getNumericCellValue());
                            }
                            break;

                    }
                }
            }

            System.out.println("--------------");
        }
    }

2.上传读取导入Excel表格

/**
     * 上传导入表格
     */
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    //#表示后面不带0,0表示后面带0
    DecimalFormat decimalFormat = new DecimalFormat("#");
    @PostMapping("import")
    public AxiosResult<Void> importExcel(@RequestPart Part file) throws Exception {
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheetAt = workbook.getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();

        for (int i = 1; i <= lastRowNum; i++) {
            Row row = sheetAt.getRow(i);
            short lastCellNum = row.getLastCellNum();
            Object[] objects = new Object[lastCellNum];

            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    Object callValue = getCallValue(cell);
                    objects[j] = callValue;
                }
            }
            User user = new User();
            user.setId(Long.parseLong(decimalFormat.format(objects[0])));
            user.setUserName(objects[1].toString());
            user.setUserPwd(objects[2].toString());
            user.setUserAddress(objects[3].toString());
            user.setUserPhone(objects[4].toString());
            user.setAddTime(sdf.parse(objects[5].toString()));

            System.out.println(Arrays.toString(objects));
            System.out.println("-----------------------");

            //存入信息
            iUserService.save(user);
        }

        return AxiosResult.success();
    }

    /**
     * 获取到对应的Cell的值
     */
    public Object getCallValue(Cell cell) {
        CellType cellTypeEnum = cell.getCellTypeEnum();
        Object obj = null;
        switch (cellTypeEnum) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    obj = cell.getDateCellValue();
                } else {
                    obj = cell.getNumericCellValue();
                }
                break;
            case FORMULA:
                obj = cell.getCellFormula();
                break;
        }
        return obj;
    }

<label style="margin: 0" class="btn btn-danger btn-sm"><input type="file" style="display: none" @change="chooseExcel"/>导入Excel</label>

let vue = new Vue({
    el: "#app",

    created() {
        this.findAll();
    },
   
        /**
         * 导入表格,文件上传
         */
        chooseExcel(e) {
            console.log(e)
            //1.通过事件对象e拿到文件
            let file = e.target.files[0];
            //2.上传文件
            let formData = new FormData();
            //3.加入formData,前面是key,后面是文件
            formData.append("file",file);
            console.log(file)
            //4.请求,值,对象
            axios.post(`user/import`,formData,{headers:{'content-Type':'multipart/form-data'}}).then(response=>{
                console.log("进去了")
                //成功刷新列表
                this.findAll();
            })
        }
    }
})

模板导出Java代码示例：

模板文件位置：resources下

 

//正常运营 导出模板路径 导出模板名
    private final String exportNormalStoreDataPath = "/xxxMapper/exportNormalStoreData.xlsx";
    private final String normal = "正常";
    private final String exportNormalStoreName = "正常运营店铺信息.xlsx";
    //停止运营 导出模板路径 导出模板名
    private final String exportStopStoreDataPath = "/xxxMapper/exportStopStoreData.xlsx";
    private final String stop = "停止";
    private final String exportStopStoreName = "停止运营店铺信息.xlsx";

@GetMapping(value = "/exportStoreData")
    public Result<?> exportStoreData(@Validated({ValidatedGroup.QueryGroup.class}) BusinessStoreSearchListRequest businessStoreSearchListRequest, HttpServletResponse response) {
        //查询条件
        String storeName = businessStoreSearchListRequest.getStoreName();
        String isFunction = businessStoreSearchListRequest.getIsFunction();
        //查询条件
        QueryWrapper<PurchaseStore> storeQueryWrapper = new QueryWrapper<>();
        storeQueryWrapper.lambda().eq(PurchaseStore::getIsValid, CommonConstant.VALID);//有效
        storeQueryWrapper.lambda().eq(PurchaseStore::getIsFunction, isFunction);
        storeQueryWrapper.lambda().like(storeName != null, PurchaseStore::getStoreName, storeName);
        //获取门店信息
        List<PurchaseStore> list = businessPurchaseStoreService.list(storeQueryWrapper);
        List<BusinessStoreInfoVO> businessStoreInfoVOList = new ArrayList<>();
        for (PurchaseStore purchaseStore : list) {
            BusinessStoreInfoVO businessStoreInfoVO = new BusinessStoreInfoVO();
            businessStoreInfoVO.setStoreId(purchaseStore.getId());//店铺id
            businessStoreInfoVO.setStoreName(purchaseStore.getStoreName());//名称
            businessStoreInfoVO.setAddress(purchaseStore.getAddress());//地址
            businessStoreInfoVO.setIsFunction(purchaseStore.getIsFunction());//是否运营
            businessStoreInfoVO.setCreationTime(purchaseStore.getCreationTime());//创建时间
            businessStoreInfoVO.setOperateEndTime(purchaseStore.getOperateEndTime());//运营结束时间
            businessStoreInfoVOList.add(businessStoreInfoVO);
        }
        try {
            //导出 正常运营
            if (isFunction != null && isFunction.equals(CommonConstant.VALID)) {
                downloadExcel(response, businessStoreInfoVOList, exportNormalStoreDataPath, exportNormalStoreName);
            }
            //导出 停止运营
            if (isFunction != null && isFunction.equals(CommonConstant.INVALID)) {
                downloadExcel(response, businessStoreInfoVOList, exportStopStoreDataPath, exportStopStoreName);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return Result.Error(ReturnCode.DOWNLOAD_FAILED);
        }
        return Result.OK(ReturnCode.DOWNLOAD_SUCCEED);
    }

    /**
     * 封装数据
     *
     * @param response response
     * @param businessStoreInfoVOList 数据
     * @param filePath 模板文件路径
     * @param fileName 下载文件名称
     */
    private void downloadExcel(HttpServletResponse response, List<BusinessStoreInfoVO> businessStoreInfoVOList, String filePath, String fileName) throws Exception {
        ClassPathResource cpr = new ClassPathResource(filePath);
        InputStream is = cpr.getInputStream();
        Workbook workbook = WorkbookFactory.create(is);
        Sheet sheet0 = workbook.getSheetAt(0);
        int i = 1;
        for (BusinessStoreInfoVO storeInfoVO : businessStoreInfoVOList) {
            Row row = sheet0.createRow(i + 1);
            int j = 0;
            row.createCell(j++).setCellValue(i++);
            row.createCell(j++).setCellValue(storeInfoVO.getStoreName());
            row.createCell(j++).setCellValue(storeInfoVO.getAddress());
            String isFunction = storeInfoVO.getIsFunction();
            if (isFunction.equals(CommonConstant.VALID)) {
                row.createCell(j++).setCellValue(normal);
                row.createCell(j++).setCellValue(TimeUtil.getDateStr(storeInfoVO.getCreationTime()));
            }
            if (isFunction.equals(CommonConstant.INVALID)) {
                row.createCell(j++).setCellValue(stop);
                row.createCell(j++).setCellValue(TimeUtil.getDateStr(storeInfoVO.getOperateEndTime()));
            }

        }
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载文件
     *
     * @param fileName 文件名
     * @param response response
     * @param workbook 工作簿
     */
    private void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        response.setCharacterEncoding(CommonConstant.CODE_UTF8);
        response.setHeader(CommonConstant.CONTENT_TYPE, CommonConstant.APPLICATION_EXECL);
        response.setHeader(CommonConstant.CONTENT_DISPOSITION,
                CommonConstant.ATTACHMENT_FILENAME + URLEncoder.encode(fileName, CommonConstant.CODE_UTF8) + CommonConstant.END_STR);
        OutputStream output = response.getOutputStream();
        workbook.write(output);
        output.close();
        workbook.close();
    }