导出导出excel

导入导出excel文件

• maven配置
• 导出excel文件
• 导入excel文件

maven配置

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

导出excel文件

1.工具类 ExportExcelUtils

package com.XXX.common.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

/**
 * 导出
 */
public class ExportExcelUtils {


    /**
     * 导出
     * @param reportName
     * @param recordList
     * @param request
     * @param response
     * @param rowName
     */
    public static void exportCreateReport(String reportName, List<Map<String, Object>> recordList,
                                          HttpServletRequest request, HttpServletResponse response, String[] rowName) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            // 创建sheet页
            HSSFSheet sheet = wb.createSheet(reportName);
            // 定义样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            HSSFRow row = sheet.createRow(0);

            for (int n = 0; n < rowName.length; n++) {
                row.createCell(n).setCellValue(rowName[n]);
                row.getCell(n).setCellStyle(style);
            }
            for (int i = 0; i < recordList.size(); i++) {
                HSSFRow rows = sheet.createRow(i + 1);
                Map<String, Object> record = recordList.get(i);
                for (int j = 0; j < rowName.length; j++){
                    rows.createCell(j)
                            .setCellValue(record.get(rowName[j])+"");
                    rows.getCell(j).setCellStyle(style);

                }
            }
            for (int i = 0; i < rowName.length; i++){
                sheet.setColumnWidth(i, 5000);
            }
            // 文件下载
            String fileName = reportName + ".xls";
            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0
                    ||request.getHeader("User-Agent").toUpperCase().indexOf("TRIDENT") > 0) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(), "ISO-8859-1");
            }
            String headStr = "attachment; filename=\"" + fileName + "\"";
            response.setContentType("APPLICATION/OCTET-STREAM");
            response.setHeader("Content-Disposition", headStr);
            OutputStream out;

            out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {

        }
    }
}

2.controller

	@GetMapping("/export")
	public RespBean export(@RequestParam Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) {
		List<Map<String, Object>> list = XXXService.exportList(params);
		if (!list.isEmpty()) {
			List<Map<String, Object>> excelList = new ArrayList<>();
			for (Map<String, Object> mDo : list) {
				Map<String, Object> map = new HashMap<>();
				map.put("部门", mDo.get("deptName") == null ? "" : mDo.get("deptName"));
				map.put("标题", mDo.get("title") == null ? "" : mDo.get("title"));
				excelList.add(map);
			}
			try {
				String fileName = "市场反馈信息";
				String[] title = new String[]{"部门", "标题"};
				ExportExcelUtils.exportCreateReport(fileName, excelList, request, response, title);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
			}
			return null;
		} else {
			return RespBean.fail().setMsg("未查询到数据！");
		}
	}

主要方法已结束。

导入excel文件

1.controller

	@ResponseBody
	@PostMapping("/importBatch")
	//@RequiresPermissions("api:file:add")
	public RespBean importBatch(MultipartFile file, HttpServletResponse response) {
		if(file == null){
			return RespBean.fail().setMsg("未获取到文件！");
		}
		try {
			ExcelImportResult<ComposingPlanExDO> importExcelMore = ExcelUtils.importExcelMore(file.getInputStream(),
					0, 1, true, ComposingPlanExDO.class, null);
			List<ComposingPlanExDO> composingPlanExDOS = importExcelMore.getList();
			if (composingPlanExDOS != null && composingPlanExDOS.size() > 0) {
				int res = composingPlanService.batchUpdate(composingPlanExDOS);
				if(res <= 0){
					return RespBean.fail().setMsg("匹配数据失败！");
				}
			}
			if (importExcelMore.isVerfiyFail()) {
				return RespBean.success().setCode(RespMessEnum.ESP_CODE_0000010.getRespCode())
						.setMsg(RespMessEnum.ESP_CODE_0000010.getRespContent());
			}
			return RespBean.success();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return RespBean.fail();
	}

2.工具类ExcelUtils

package com.tianxing.common.utils;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;

import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.NoSuchElementException;

public class ExcelUtils {

	// @描述：是否是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)$");
	}

	/**
	 * 验证EXCEL文件
	 * 
	 * @param filePath
	 * @return
	 */
	public static boolean validateExcel(String filePath) {
		if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
			return false;
		}
		return true;
	}

	/**
	 * excel 导入
	 *
	 * @param file      excel文件
	 * @param pojoClass pojo类型
	 * @param <T>
	 * @return
	 */
	public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
		return importExcel(file, 1, 1, pojoClass);
	}

	/**
	 * excel 导入
	 *
	 * @param file       excel文件
	 * @param titleRows  标题行
	 * @param headerRows 表头行
	 * @param pojoClass  pojo类型
	 * @param <T>
	 * @return
	 */
	public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
		return importExcel(file, titleRows, headerRows, false, pojoClass);
	}

	/**
	 * excel 导入
	 *
	 * @param file       上传的文件
	 * @param titleRows  标题行
	 * @param headerRows 表头行
	 * @param needVerfiy 是否检验excel内容
	 * @param pojoClass  pojo类型
	 * @param <T>
	 * @return
	 */
	public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
		if (file == null) {
			return null;
		}
		try {
			return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
		} catch (Exception e) {
			throw new IOException(e.getMessage());
		}
	}

	/**
	 * excel 导入
	 *
	 * @param inputStream 文件输入流
	 * @param titleRows   标题行
	 * @param headerRows  表头行
	 * @param needVerfiy  是否检验excel内容
	 * @param pojoClass   pojo类型
	 * @param <T>
	 * @return
	 */
	public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
		if (inputStream == null) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headerRows);
		params.setSaveUrl("/excel/");
		params.setNeedSave(true);
		params.setNeedVerfiy(needVerfiy);
		try {
			return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
		} catch (NoSuchElementException e) {
			throw new IOException("excel文件不能为空");
		} catch (Exception e) {
			throw new IOException(e.getMessage());
		}
	}
	
	/**
	 * excel 导入
	 *
	 * @param inputStream 文件输入流
	 * @param titleRows   标题行
	 * @param headerRows  表头行
	 * @param needVerfiy  是否检验excel内容
	 * @param pojoClass   pojo类型
	 * @param <T>
	 * @return
	 */
	public static <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Integer titleRows, Integer headerRows, 
			boolean needVerfiy, Class<T> pojoClass, IExcelVerifyHandler verifyHandler) throws IOException {
		if (inputStream == null) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headerRows);
		params.setSaveUrl("/excel/");
		params.setNeedSave(true);
		params.setNeedVerfiy(needVerfiy);
		if (!ObjectUtils.isEmpty(verifyHandler)) {
			params.setVerifyHandler(verifyHandler);
		}
		try {
			return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
		} catch (NoSuchElementException e) {
			throw new IOException("excel文件不能为空");
		} catch (Exception e) {
			throw new IOException(e.getMessage());
		}
	}

}

主要方法已结束。