Java实现excel数据导出_java实现excel导出

所需导入包

import static org.apache.poi.ss.usermodel.CellType.STRING;


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

import javax.servlet.http.HttpServletResponse;

import io.renren.modules.cms.entity.CmsDataEntity;
import io.renren.modules.cms.service.CmsDataService;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;

Controller层

/**
 * 导出月度数据列表信息Excel
 *
 * @param params
 * @param response
 */
@RequestMapping("/export")
public void exportExcel(@RequestBody Map<String, Object> params, HttpServletResponse response) {
    // 创建excel
    try {
        // 获取模板Excel数据
        ClassPathResource classPathResource = new ClassPathResource("/template/templateList.xlsx");
        InputStream inputStream = classPathResource.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFCellStyle commonCellStyle = ExcelUtils.createCellStyle(workbook);
        XSSFCellStyle percentCellStyle = ExcelUtils.createCellStyle(workbook);
        percentCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        // 设置标题栏信息
        String dataTag = "清单信息" + DateUtils.getDate();
        // 创建头部栏目
        List<CmsDataEntity> list = cmsDataService.findList(params);

        if (CollectionUtils.isNotEmpty(list)) {
            // 创建具体内容行,具体填充信息见templateList.xls模板
            for (int i = 0; i < list.size(); i++) {
                XSSFRow contentRow = sheet.createRow((i + 1));
                ExcelUtils.createCell(contentRow, 0, commonCellStyle, STRING, StringUtils.trimToEmpty(list.get(i).getKpiName()));
                ExcelUtils.createCell(contentRow, 1, commonCellStyle, STRING, list.get(i).getKpiValue());
                ExcelUtils.createCell(contentRow, 2, commonCellStyle, STRING, list.get(i).getCityName());
                ExcelUtils.createCell(contentRow, 3, commonCellStyle, STRING, list.get(i).getBizMonth());
                ExcelUtils.createCell(contentRow, 4, commonCellStyle, STRING, list.get(i).getBizDay());
                ExcelUtils.createCell(contentRow, 5, commonCellStyle, STRING, list.get(i).getResult());
                ExcelUtils.createCell(contentRow, 6, commonCellStyle, STRING, list.get(i).getReferId());
                ExcelUtils.createCell(contentRow, 7, commonCellStyle, STRING, list.get(i).getBatchNo());
            }
        }

        // 输出Excel数据
        String filename = dataTag + ".xlsx";
        ExcelUtils.writeData(response, workbook, filename);
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}

service层

/**
 * 导出Cms指定指标数据信息
 *
 * @param params
 * @return
 */
List<CmsDataEntity> findList(Map<String, Object> params);

serviceImpl层

/**
 * 导出Cms指定指标数据信息
 *
 * @param params
 * @return
 */
@Override
public List<CmsDataEntity> findList(Map<String, Object> params) {
    String kpiName = Convert.toStr(params.get("kpiName"));
    String cityId = Convert.toStr(params.get("cityId"));
    String teamId = Convert.toStr(params.get("teamId"));
    String bizMonth = Convert.toStr(params.get("bizMonth"));
    List<CmsDataEntity> cmsDataList = this.selectList(new EntityWrapper<CmsDataEntity>().like(StringUtils.isNotBlank(kpiName), "kpi_name", kpiName)
            .eq(StringUtils.isNotBlank(cityId), "city_id", cityId)
            .eq(StringUtils.isNotBlank(teamId), "team_id", teamId)
            .eq(StringUtils.isNotBlank(bizMonth), "biz_month", bizMonth).orderBy("biz_month", false)
            .orderBy("team_id", true).orderBy("kpi_id", true));
    if (CollectionUtils.isNotEmpty(cmsDataList)) {
        // 获取所属地市区域map
        Map<String, String> dictMap = sysDictService.findMap(Constant.SYS_DICT_LOCATION);
        for (CmsDataEntity cmsData : cmsDataList) {
            cmsData.setCityName(dictMap.get(cmsData.getCityId()));
        }
    }
    return cmsDataList;
}

前端vue

// 导出数据

    exportHandle () {

      this.$http({

        url: this.$http.adornUrl(

          `/cms/data/export`

        ),

        responseType: 'blob',

        method: "post",

        data: this.$http.adornData({

          kpiName: this.dataForm.kpiName,

          cityId: this.dataForm.cityId,

          teamId: this.dataForm.teamId,

          bizMonth: this.dataForm.bizMonth

        }, true)

      }).then(res => {

        console.log(res);

        let blob = new Blob([res.data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});

        let fileName = decodeURI(res.headers['filename']);

        if (window.navigator.msSaveOrOpenBlob) {

          navigator.msSaveBlob(blob, fileName)

        } else {

          let link = document.createElement('a')

          link.href = window.URL.createObjectURL(blob)

          link.download = fileName

          link.click()

            //释放内存

          window.URL.revokeObjectURL(link.href)

        }

      }).catch(res => {

         console.log(res)

      });

    }