背景

新公司会员分销报表管理系统有导出excel表的场景，通过查找，项目中并没有excel表相关工具类，故手写了工具类给大家分享一下，以备不时之需。

方式一（POI方式）

1.该工具类包含导出和导入解析功能，其中导入解析功能需读者根据自身业务情况做进一步封装。
2.工具类中有笔者自己的CommonException异常类，需读者根据自身业务做相应调整和替换。

import com.zzx.commons.exceptions.CommonException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.tomcat.util.http.fileupload.FileUploadException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.List;

public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger("info");
    
    /**
     * 下载execel表模板
     *
     * @param response   HttpServletResponse
     * @param fileName   导出文件名
     * @param list       目标表单
     * @param title      表头数组
     * @param properties 导出属性（与表头对应）
     */
    public static void download(HttpServletResponse response, String fileName, List list, String[] title, String[] properties) {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
        HSSFSheet sheet = workbook.createSheet("sheet1");//创建工作表(Sheet)
        //设置第一列宽（3766）
        HSSFRow row = sheet.createRow(0);// 创建行,从0开始
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle style = workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("@"));
            sheet.setDefaultColumnStyle(i,style);
            HSSFCell cells = row.createCell(i);// 设置单元格内容,重载
            styleOne(workbook, cells).setCellValue(title[i]);
        }
        for(int i = 0; i < list.size() ;i++){
            HSSFRow row_one = sheet.createRow(i+1);
            //反射得到所有属性
            Class cls = list.get(i).getClass();
            Field[] fields = cls.getDeclaredFields();
            styleTwo(workbook, row_one);
            for(int k = 0 ; k < properties.length ; k++) {
                for (Field field : fields) {
                    //得到属性
                    //打开私有访问
                    field.setAccessible(true);
                    //获取属性
                    Object value = null;
                    try {
                        value = field.get(list.get(i));
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    if (properties[k].equals(field.getName())) {
                        row_one.createCell(k).setCellValue(String.valueOf(value));
                    }
                }
            }
        }
        OutputStream outputStream = null;
        try {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            outputStream = response.getOutputStream();
            workbook.write(outputStream);//保存Excel文件
            if (outputStream != null) {
                outputStream.close();//关闭文件流
            }
        } catch (Exception e) {
            logger.info("execel流输出时错误,错误详情：{}", e.getMessage());
            throw new CommonException("execel流输出时错误");
        }
        System.out.println("OK!");
    }


    /**
     * 上传并解析execel表
     *
     * @param file
     * @return
     * @throws IOException
     * @throws FileUploadException
     */
    public static String upLoadExecel(MultipartFile file) {

        byte[] b = new byte[0];
        try {
            b = file.getBytes();
        } catch (IOException e) {
            logger.info("上传文件出错,错误详情：{}", e.getMessage());
            throw new CommonException("上传文件出错");
        }
        InputStream is = new ByteArrayInputStream(b);
        HSSFWorkbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (IOException e) {
            logger.info("该文件为非execel文件,错误详情：{}", e.getMessage());
            throw new CommonException("该文件为非execel文件");
        }
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 循环列Cell
                for (int j = 0; j <= hssfRow.getLastCellNum(); j++) {
                    HSSFCell cell = hssfRow.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println(getCellDate(cell));
                }
            }
        }
        return "数据上传成功！";
    }


    /**
     * 遍历获取相应类型值静态工具类
     *
     * @param cell
     * @return
     */
    private static String getCellDate(Cell cell) {

        String return_string = null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                return_string = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                DecimalFormat format = new DecimalFormat("#,##0.000");
                return_string = String.valueOf(format.format(cell.getNumericCellValue()));
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return_string = String.valueOf(cell.getBooleanCellValue());
            default:
                return_string = "";
                break;
        }
        return return_string;
    }

    
    /**
     * 样式1
     *
     * @param workbook
     * @param cell
     * @return
     */
    private static HSSFCell styleOne(HSSFWorkbook workbook, HSSFCell cell) {
        //创建CellStyle或HSSFCellStyle对象
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置单元格字体位置水平方向
        style.setAlignment(HorizontalAlignment.LEFT);
        //设置单元格字体位置垂直方向
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);   //底部边框样式
        //通过颜色索引设置底部颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //底部边框颜色

        //同理，设置左边样式
        style.setBorderLeft(BorderStyle.THIN);    //左边边框样式
        style.setLeftBorderColor(IndexedColors.BLUE.getIndex());    //左边边框颜色

        //同理，设置右边样式
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREEN.getIndex());
        //最后，设置顶部样式
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BROWN.getIndex());
        //设置字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12); // 字体高度
        font.setFontName(" 宋体 "); // 字体
        style.setFont(font);
        cell.setCellStyle(style);
        return cell;
    }

    /**
     * 样式2
     *
     * @param workbook
     * @param cell
     * @return
     */
    private static HSSFRow styleTwo(HSSFWorkbook workbook, HSSFRow cell) {
        //设置单元格数据格式
        HSSFCellStyle textStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        textStyle.setDataFormat(format.getFormat("#,##0.000"));
        cell.setRowStyle(textStyle);
        return cell;
    }

}

方式二（阿里巴巴EasyExcel）

• pom.xml导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

• 实体类

import com.alibaba.excel.annotation.ExcelProperty;

public class UserEntity {

    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("年龄")
    private Integer age;
    @ExcelProperty("职业")
    private String occupation;
    @ExcelProperty("账户")
    private String account;
    @ExcelProperty("地址")
    private String address;
    
    //gettrt和setter省略。。。。
}

• EasyExcel工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {

    public static void process(String fileName, List target, Class clazs , HttpServletResponse response) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        EasyExcel.write(response.getOutputStream(), clazs).excelType(ExcelTypeEnum.XLS).autoCloseStream(Boolean.TRUE)
                .sheet("用户报表").doWrite(target);
    }
}

• EasyExcelListener类(用于导入)

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.List;

/**
 * @Author: zhuzixin
 * @CreateDate: 2021-07-04 15:09
 */
public class EasyExcelListener<T>  extends AnalysisEventListener<T> {

    private List<T> scoringPlayersBeanList;

    public EasyExcelListener() {
    }

    public EasyExcelListener(List<T> scoringPlayersBeanList) {
        this.scoringPlayersBeanList = scoringPlayersBeanList;
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        System.out.println(t);
        scoringPlayersBeanList.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

• Controller类[导出]

import com.husky.tinelion.entity.UserEntity;
import com.husky.tinelion.utils.ExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@RestController
public class UserReportFormController {

    @GetMapping("/load")
    public void downloadReportData(HttpServletResponse response){
        String fileName = "用户详情报表数据.xls";
        List<UserEntity> userEntities = new ArrayList<>();
        UserEntity userEntity = new UserEntity();
        userEntity.setName("自鑫");
        userEntity.setAccount("9999999999999");
        userEntity.setAddress("成都");
        userEntity.setAge(18);
        userEntity.setOccupation("码农");
        userEntities.add(userEntity);
        try {
            ExcelUtil.process(fileName,userEntities,UserEntity.class,response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

• 导出结果

• Controller类[导入]

@RestController
public class RegionExcelController {

    @Autowired
    private DicLocationProvinceMapper provinceMapper;
    @Autowired
    private DicLocationCityMapper cityMapper;
    @Autowired
    private DicLocationRegionMapper regionMapper;
    @GetMapping("/execute")
    public void executeUploadRegion() throws FileNotFoundException {
        FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\Procedural.life\\Desktop\\tianjing\\regions.xlsx"));

		//此处为easyexcel解析List<Region>实例 
        List<Region> regions = new ArrayList<>();
        EasyExcelListener<Region> easyExcelListener = new EasyExcelListener<>(regions);
        EasyExcel.read(inputStream, Region.class, easyExcelListener).sheet().doRead();

        Map<String, List<Region>> p = regions.stream().collect(Collectors.groupingBy(Region::getProvince));
        Counter counter = new Counter();
        p.forEach((k, v) -> {
            counter.setA(counter.getA() + 1);
            DicLocationProvince province = new DicLocationProvince();
            province.setDicLocationCountryId(1);
            province.setDicLocationProvinceId(counter.getA());
            province.setProvinceName(k);
            provinceMapper.insertSelective(province);
            System.out.println(k + ":" + counter.getA());

            Map<String, List<Region>> c = v.stream().collect(Collectors.groupingBy(Region::getCity));
            c.forEach((m, n) -> {
                counter.setB(counter.getB() + 1);
                DicLocationCity city = new DicLocationCity();
                city.setDicLocationProvinceId(counter.getA());
                city.setDicLocationCountryId(1);
                city.setDicLocationCityId(counter.getB());
                city.setCityName(m);
                cityMapper.insertSelective(city);
                System.out.println(m + ":" + counter.getA()+ ":" + counter.getB());

                n.forEach(co -> {
                    counter.setC(counter.getC() + 1);
                    DicLocationRegion region = new DicLocationRegion();
                    region.setDicLocationCityId(counter.getB());
                    region.setDicLocationRegionId(counter.getC());
                    region.setRegionName(co.getCounty());
                    regionMapper.insertSelective(region);
                    System.out.println(co.getCounty()+ ":" + counter.getA()+ ":" + counter.getB()+ ":" + counter.getC());
                });
            });
        });
    }
}

上面的实例是将一个省市县的excel表解析保存到自己的省市县表，源文件如下：