Java实现Excel导入功能

前端vue页面代码

 // 导入数据

    importHandle () {

      this.importVisible = true

      this.$nextTick(() => {

        this.$refs.import.init('/cms/data/import')

      })

    },

导包

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import io.renren.common.exception.RRException;
import io.renren.modules.cms.entity.CmsDataEntity;
import io.renren.modules.cms.service.CmsDataService;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;


import io.renren.common.annotation.SysLog;
import io.renren.common.lang.DateUtils;
import io.renren.common.utils.PageUtils;
import io.renren.common.utils.R;
import io.renren.common.utils.excel.ExcelUtils;
import io.renren.modules.sys.controller.BaseController;
import org.springframework.web.multipart.MultipartFile;

Controller层

/**
 * 导入Cms指标数据信息Excel
 *
 * @param file
 * @return
 * @throws Exception
 */
@SysLog("导入月度数据信息")
@RequestMapping("/import")
@RequiresPermissions("cms:data:import")
public R importExcel(@RequestParam MultipartFile file) throws Exception {
    if (file.isEmpty()) {
        throw new RRException("上传文件不能为空");
    }

    // 首先校验文件格式是否满足要求
    String[] allowedFileSuffix = { "xls", "xlsx" };
    String fileSuffix = FilenameUtils.getExtension(file.getOriginalFilename());
    if (!ArrayUtils.contains(allowedFileSuffix, fileSuffix)) {
        return R.error("请上传xls、xlsx格式数据文件!");
    }

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    // 用getSheetAt(int)方法取得Sheet
    Sheet st = wb.getSheetAt(0);
    // 获得最后一行的行号
    int rownum = st.getLastRowNum();
    // 保存导入过程中出现的错误提示信息
    StringBuffer errorMsg = new StringBuffer();
    // 记录导入开始时间
    long start = System.currentTimeMillis();
    List<CmsDataEntity> list = new ArrayList<>();
    //单独处理某个单元格
    Cell cell;
    for (int i = 1; i <= rownum; i++) {
        Row row = st.getRow(i);
        CmsDataEntity cmsData = new CmsDataEntity();
        if (row.getCell(0) != null) {
            cell = row.getCell(0);
            cell.setCellType(CellType.STRING);
            cmsData.setCityId(StringUtils.trimToEmpty(String.valueOf(cell)));
        }

        if (row.getCell(1) != null) {
            cell = row.getCell(1);
            cell.setCellType(CellType.STRING);
            cmsData.setBizMonth(StringUtils.trimToEmpty(String.valueOf(cell)));
        }

        if (row.getCell(2) != null) {
            cell = row.getCell(2);
            cell.setCellType(CellType.STRING);
            cmsData.setTeamId(StringUtils.trimToEmpty(String.valueOf(cell)));
        }

        if (row.getCell(3) != null) {
            cmsData.setTeamName(StringUtils.trimToEmpty(String.valueOf(row.getCell(3))));
        }

        if (row.getCell(4) != null) {
            cell = row.getCell(4);
            cell.setCellType(CellType.STRING);
            cmsData.setKpiId(StringUtils.trimToEmpty(String.valueOf(cell)));
        }

        if (row.getCell(5) != null) {
            cmsData.setKpiName(StringUtils.trimToEmpty(String.valueOf(row.getCell(5))));
        }

        if (row.getCell(6) != null) {
            cell = row.getCell(6);
            String cellStr = String.valueOf(cell);
            cell.setCellType(CellType.STRING);
            String cellValue = cell.getStringCellValue();
            cmsData.setKpiValue(StringUtils.trimToEmpty(String.valueOf(cell)));
            //存在小数点单独处理
            if(cellValue.indexOf(".") > -1) {
                cellValue = cellStr;
                cmsData.setKpiValue(StringUtils.trimToEmpty(String.valueOf(cellValue)));
            }
        }

        cmsData.preInsert();
        list.add(cmsData);
    }

    if (CollectionUtils.isNotEmpty(list)) {
        cmsDataService.saveObj(list);
    }
    // 记录导入开始时间
    long end = System.currentTimeMillis();
    logger.info("导入耗时：：{}毫秒", (end - start));
    return R.ok("成功导入数据信息:" + rownum + "条");
}

service层

/**
 * 导入Cms指标数据信息
 *
 * @param list
 * @return
 */
void saveObj(List<CmsDataEntity> list);

serviceImpl层

/**
 * 导入Cms指标数据信息
 *
 * @param list
 * @return
 */
@Override
public void saveObj(List<CmsDataEntity> list) {
    for (CmsDataEntity cmsData : list) {
        cmsData.setSourceType("1");
        this.insert(cmsData);
    }
}