java 导入excel表格（批量导入），下载excel模板，导出表格

1.导入excel表格（批量导入）
如图，给id=83和id=84的老师导入工作时间。

导入的excel模板如下
注意导入excel表格时关于日期时间类的的数据要设置对应的格式。

后台接口

    /**
     * 导入老师时间
     * @param file
     * @param request
     * @return
     */
    @PostMapping(value = "/import")
    public JSONResult<String> importTeacher(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request) {
        JSONResult result = null;
        String fileName = file.getOriginalFilename();
        try {
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                return new JSONResult(HttpServletResponse.SC_INTERNAL_SERVER_ERROR,"上传文件格式不正确!");
            }
            teacherService.batchImportTeacherInfo(fileName,file,getCurrentUserId(request));
            result = new JSONResult(Constant.APP_RESULT_SUCCESS_NO_DATA, "导入老师时间成功.");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("addChannelInfo={}", e);
            result = new JSONResult(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.getMessage());
        }
        return result;
    }

    /**
     * 批量导入老师时间
     * @param fileName
     * @param file
     * @param currentUserId
     * @throws Exception
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public void batchImportTeacherInfo(String fileName, MultipartFile file, Long currentUserId) throws Exception {
        List<TeacherTimeEntity> teacherTimeEntities = this.importExcelData(fileName, file, currentUserId);
        for (TeacherTimeEntity excelEntity : teacherTimeEntities) {
            teacherTimeService.insertSelective(excelEntity);
        }
    }

    /**
     * 导入excel数据
     * @param fileName
     * @param file
     * @param userId
     * @return
     * @throws Exception
     */
    public List<TeacherTimeEntity> importExcelData(String fileName, MultipartFile file, Long userId) throws Exception {
        boolean isExcel2003 = true;
        List<TeacherTimeEntity> channelExcelEntities = new ArrayList<TeacherTimeEntity>();
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            throw new Exception("sheet不存在，请确认！");
        }
        TeacherTimeEntity channelExcelEntity;
        logger.info("行数" + sheet.getLastRowNum());
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            channelExcelEntity = new TeacherTimeEntity();
            // 老师名称
            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            String name = row.getCell(0).getStringCellValue();

            // 老师id
            String id = null;
            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            id = row.getCell(1).getStringCellValue();
            TeacherEntity teacherEntity = dao.getById(Long.valueOf(id));
            if (teacherEntity == null){
                throw new Exception("导入失败(第" + (r) + "行,老师ID"+id+"不存在)");
            }

            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 开始时间
            String startTime = null;
            if ( row.getCell(2).getCellType()==0){
                if (HSSFDateUtil.isCellDateFormatted(row.getCell(2))){
                    startTime = formater.format(row.getCell(2).getDateCellValue());
                }
            }

            // 结束时间
            String endTime = null;
            if ( row.getCell(3).getCellType()==0){
                if (HSSFDateUtil.isCellDateFormatted(row.getCell(3))){
                    endTime = formater.format(row.getCell(3).getDateCellValue());
                 }
            }
            
            channelExcelEntity.setTeacherId(Long.valueOf(id));
            channelExcelEntity.setStartTime(formater.parse(startTime));
            channelExcelEntity.setEndTime(formater.parse(endTime));
            channelExcelEntity.setCreateId(userId);
            channelExcelEntity.setCreateTime(new Date());
            channelExcelEntities.add(channelExcelEntity);
        }
        return channelExcelEntities;
    }

注意在处理日期类型时要先判断是不是日期类型。

            // 开始时间
            String startTime = null;
            if ( row.getCell(2).getCellType()==0){
                if (HSSFDateUtil.isCellDateFormatted(row.getCell(2))){
                    startTime = formater.format(row.getCell(2).getDateCellValue());
                }
            }

相关的jar包引入

导入数据库成功

后台框架采用的是antd design，调用后台接口代码如下。

                               <Upload {...uploadOption}>
                                  <Button style={{ marginLeft: 8 }}>
                                    <Icon type="upload" /> 导入老师时间
                                  </Button>
                                </Upload>

    const uploadOption = {
      action: `${apiUri}/teacher/import?dir=channel`,
      accept:"application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      showUploadList: false,
      onChange : this.changeFile,
      beforeUpload (file, fileList) {
        // 不能大于 1M=1*1024*1024(B) = 1048576
        if (file.size > 1048576) {
          message.warning("Picture size should not exceed 1M.");
          fileList.length = 0;
          return false;
        }
        return true;
      }
    };

  changeFile = ( {file, fileList} ) => {
    switch (file.status) {
      case 'uploading' : break;
      case 'done' :
        const {response} = file;
        console.info('response',response);
        if (response.statusCode === 0) {
          message.success('导入成功');
        }else {
          message.error(response.message);
        }
        break;
      case 'error' : break;
      case 'removed' : break;
      default : break;
    }
  };

2.下载excel模板
将需要下载的模板放在项目能直接访问到的文件夹下面。

 <Button style={{marginLeft:8}} href={`${excelOrigin}/doc/time.xlsx`} target="_blank">
         下载excel模版
 </Button>

href里面写能访问到excel模板的项目的路径

3.导出表格


后台接口

    /**
     * 导出 重点词汇
     * @param dto
     * @param request
     * @param response
     */
    @GetMapping("/reportExcel")
    public void reportExcel(KeyWordsEntity dto, HttpServletRequest request, HttpServletResponse response) {
        SXSSFWorkbook workbook = null;
        try {
            //开始展开导出操作
            List<KeyWordsEntity> data = keyWordsService.reportExcel(dto);
            //内存中保留 1000 条数据，以免内存溢出，其余写入 硬盘
            workbook = new SXSSFWorkbook(1000);
            String sheetName = "重点词汇";
            Sheet sheet = workbook.createSheet(sheetName);
            Row row = sheet.createRow(0);
            // 标题
            String[] headers = {"序号", "单词", "单词中文", "句子", "句子中文"};
            //创建单元格格式，并设置居中
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中
            //设置字体
            Font font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            titleStyle.setFont(font);

            // 设置样式
            Cell cell = null;
            for (int i = 0; i < headers.length; i++) {//表头部分
                cell = row.createCell(i);
                cell.setCellValue(headers[i]);
                sheet.setDefaultColumnWidth(20);
                cell.setCellStyle(titleStyle);
            }
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中

            for (int i = 0; i < data.size(); i++) {
                KeyWordsEntity channelDto = data.get(i);
                row = sheet.createRow((i + 1));//创建一行Excel
                //序号
                cell = row.createCell(0);
                cell.setCellValue((i + 1));
                cell.setCellStyle(cellStyle);
                //单词
                cell = row.createCell(1);
                cell.setCellValue(channelDto.getWord());
                cell.setCellStyle(cellStyle);
                // 单词中文
                cell = row.createCell(2);
                cell.setCellValue(channelDto.getWordChinese());
                cell.setCellStyle(cellStyle);
                // 句子
                cell = row.createCell(3);
                cell.setCellValue(channelDto.getSentence());
                cell.setCellStyle(cellStyle);
                // 句子中文
                cell = row.createCell(4);
                cell.setCellValue(channelDto.getSentenceChinese());
                cell.setCellStyle(cellStyle);
            }
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/ms-excel; charset=UTF-8");
            String filename = "重点词汇.xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("UTF-8"), "iso-8859-1")); // 设置文件的名称
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            workbook.dispose();
        }
    }

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

前端：react + Ant.design
页面

  <Button style={{ marginLeft: 8 }} type="primary" onClick={this.report}>导出表格</Button>

  report = () => {
    const { dispatch, form } = this.props;
    const parms = qs.parse(window.location.search);
    const id = parseInt(parms.id, 10);
    form.validateFields((err, fieldsValue) => {
      const values = {
        cellId: id,
        ...fieldsValue,
      };
      dispatch({
        type: 'course/reportExcel',
        payload: values,
      });
    });
  };

model

    *reportExcel({ payload }, { call }) {
      yield call(reportExcel, payload);
    },

service

export async function reportExcel(params) {
  window.open(`${apiUri}/course/reportExcel?${stringify(params)}`);
}