Java实现Excel导入导出

1、Excel导入

总结一下目标，就是要将excel中的数据行、逐一提取，最后得到一个list，这个list的每个元素就是excel的每个数据行的实例，之后的操作就是常规的java逻辑处理了。

可以把这个过程分为三步：

1）接收数据
后台使用 MultipartFile 接收文件数据（这里复习一下，springmvc接收参数的几种方式）。

前端如果是前后端分离项目我们不用管，
如果是jsp项目可以用文件标签传参。

2）提取数据
提取数据的底层是用IO流实现的，我们这里使用封装好的工具类。excel工具类有很多、很多，我一般是使用适配性最好的，不然一会儿springboot项目、一会儿spring项目等等，还得换不同的工具类；效率什么的不是首要考虑项。
使用工具类后，我们就得到了一个List<List>：Excel的每个数据行组成一个List，多个数据行就组成了List<List>

这里有一个小坑，说明一下；有时候你导入的数据，如果是数字比如32位的卡号等等，用工具类提取出来成了科学计数法、或者后面加了小数点；这就说明你用的这个工具类没有将数字类型数据进行处理，你需要在工具类中找到数字类型，添加toText()方法。
当然你也可以使用我后面提供的工具类，这个问题已经作了处理。

3）将list的元素处理成实例对象，方便后续处理
在作转换的时候，还可以加一些校验、限制，比如限制excel导入总行数不得超过多少、限制某列参数不能重复等等。

下面提供一下我用的excel导入工具类，springboot或者spring项目都可以用

public class ImportExeclUtil {    private static int totalRows = 0;// 总行数    private static int totalCells = 0;// 总列数    private static String errorInfo;// 错误信息    /** 无参构造方法 */    public ImportExeclUtil()    {    }    public static int getTotalRows()    {        return totalRows;    }    public static int getTotalCells()    {        return totalCells;    }    public static String getErrorInfo()    {        return errorInfo;    }    /**     *     * 根据流读取Excel文件     *     *     * @param inputStream     * @param isExcel2003     * @return     * @see [类、类#方法、类#成员]     */    public List<List<String>> read(InputStream inputStream, boolean isExcel2003)            throws IOException    {        List<List<String>> dataLst = null;        /** 根据版本选择创建Workbook的方式 */        Workbook wb = null;        if (isExcel2003)        {            wb = new HSSFWorkbook(inputStream);        }        else        {            wb = new XSSFWorkbook(inputStream);        }        dataLst = readDate(wb);        return dataLst;    }    /**     *     * 读取数据     *     * @param wb     * @return     * @see [类、类#方法、类#成员]     */    private List<List<String>> readDate(Workbook wb)    {        List<List<String>> dataLst = new ArrayList<List<String>>();        /** 得到第一个shell */        Sheet sheet = wb.getSheetAt(0);        /** 得到Excel的行数 */        totalRows = sheet.getPhysicalNumberOfRows();        /** 得到Excel的列数 */        if (totalRows >= 1 && sheet.getRow(0) != null)        {            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();        }        /** 循环Excel的行 */        for (int r = 1; r < totalRows; r++)        {            Row row = sheet.getRow(r);            if (row == null)            {                continue;            }            List<String> rowLst = new ArrayList<String>();            /** 循环Excel的列 */            for (int c = 0; c < getTotalCells(); c++)            {                Cell cell = row.getCell(c);                String cellValue = "";                if (null != cell)                {                    // 以下是判断数据的类型                    switch (cell.getCellTypeEnum())                    {                        case NUMERIC: // 数字                            //如果是日期的话                            if(cell != null && HSSFDateUtil.isCellDateFormatted(cell)){                                Date d = cell.getDateCellValue();                                DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");                                String da = formater.format(d);                                cellValue = da;                                break;                            }                            cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());                            break;                        case STRING: // 字符串                            cellValue = cell.getStringCellValue();                            break;                        case BOOLEAN: // Boolean                            cellValue = cell.getBooleanCellValue() + "";                            break;                        case FORMULA: // 公式                            cellValue = cell.getCellFormula() + "";                            break;                        case BLANK: // 空值                            cellValue = "";                            break;                        case ERROR: // 故障                            cellValue = "非法字符";                            break;                        default:                            cellValue = "未知类型";                            break;                    }                }                rowLst.add(cellValue);            }            /** 保存第r行的第c列 */            dataLst.add(rowLst);        }        return dataLst;    }    /**     *     * 根据Excel表格中的数据判断类型得到值     *     * @param cell     * @return     * @see [类、类#方法、类#成员]     */    /*private static String getCellValue(Cell cell)    {        String cellValue = "";        if (null != cell)        {            // 以下是判断数据的类型            switch (cell.getCellType())            {                case HSSFCell.CELL_TYPE_NUMERIC: // 数字                    ;: // 数字                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell))                    {                        Date theDate = cell.getDateCellValue();                        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");                        cellValue = dff.format(theDate);                    }                    else                    {                        DecimalFormat df = new DecimalFormat("0");                        cellValue = df.format(cell.getNumericCellValue());                    }                    break;                case HSSFCell.CELL_TYPE_STRING: // 字符串                    cellValue = cell.getStringCellValue();                    break;                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean                    cellValue = cell.getBooleanCellValue() + "";                    break;                case HSSFCell.CELL_TYPE_FORMULA: // 公式                    cellValue = cell.getCellFormula() + "";                    break;                case HSSFCell.CELL_TYPE_BLANK: // 空值                    cellValue = "";                    break;                case HSSFCell.CELL_TYPE_ERROR: // 故障                    cellValue = "非法字符";                    break;                default:                    cellValue = "未知类型";                    break;            }        }        return cellValue;    }*/    /**     *     * 根据实体成员变量的类型得到成员变量的值     *     * @param realValue     * @param fields     * @param f     * @param cellValue     * @return     * @see [类、类#方法、类#成员]     */    private static Object getEntityMemberValue(Object realValue, Field[] fields, int f, String cellValue)    {        String type = fields[f].getType().getName();        switch (type)        {            case "char":            case "java.lang.Character":            case "java.lang.String":                realValue = cellValue;                break;            case "java.util.Date":                realValue = StringUtils.isBlank(cellValue) ? null : DateUtil.strToDate(cellValue, DateUtil.YYYY_MM_DD);                break;            case "java.lang.Integer":                realValue = StringUtils.isBlank(cellValue) ? null : Integer.valueOf(cellValue);                break;            case "int":            case "float":            case "double":            case "java.lang.Double":            case "java.lang.Float":            case "java.lang.Long":            case "java.lang.Short":            case "java.math.BigDecimal":                realValue = StringUtils.isBlank(cellValue) ? null : new BigDecimal(cellValue);                break;            default:                break;        }        return realValue;    }    /**     *     * 根据路径或文件名选择Excel版本     *     *     * @param filePathOrName     * @param in     * @return     * @throws IOException     * @see [类、类#方法、类#成员]     */    public static Workbook chooseWorkbook(String filePathOrName, InputStream in)            throws IOException    {        /** 根据版本选择创建Workbook的方式 */        Workbook wb = null;        boolean isExcel2003 = ExcelVersionUtil.isExcel2003(filePathOrName);        if (isExcel2003)        {            wb = new HSSFWorkbook(in);        }        else        {            wb = new XSSFWorkbook(in);        }        return wb;    }    static class ExcelVersionUtil    {        /**         *         * 是否是2003的excel，返回true是2003         *         *         * @param filePath         * @return         * @see [类、类#方法、类#成员]         */        public static boolean isExcel2003(String filePath)        {            return filePath.matches("^.+\\.(?i)(xls)$");        }        /**         *         * 是否是2007的excel，返回true是2007         *         *         * @param filePath         * @return         * @see [类、类#方法、类#成员]         */        public static boolean isExcel2007(String filePath)        {            return filePath.matches("^.+\\.(?i)(xlsx)$");        }    }    public static class DateUtil    {        // ======================日期格式化常量=====================//        public static final String YYYY_MM_DDHHMMSS = "yyyy-MM-dd HH:mm:ss";        public static final String YYYY_MM_DD = "yyyy-MM-dd";        public static final String YYYY_MM = "yyyy-MM";        public static final String YYYY = "yyyy";        public static final String YYYYMMDDHHMMSS = "yyyyMMddHHmmss";        public static final String YYYYMMDD = "yyyyMMdd";        public static final String YYYYMM = "yyyyMM";        public static final String YYYYMMDDHHMMSS_1 = "yyyy/MM/dd HH:mm:ss";        public static final String YYYY_MM_DD_1 = "yyyy/MM/dd";        public static final String YYYY_MM_1 = "yyyy/MM";        /**         *         * 自定义取值，Date类型转为String类型         *         * @param date 日期         * @param pattern 格式化常量         * @return         * @see [类、类#方法、类#成员]         */        public static String dateToStr(Date date, String pattern)        {            SimpleDateFormat format = null;            if (null == date)                return null;            format = new SimpleDateFormat(pattern, Locale.getDefault());            return format.format(date);        }        /**         * 将字符串转换成Date类型的时间         * 
         *         * @param s 日期类型的字符串
         *            datePattern :YYYY_MM_DD
         * @return java.util.Date         */        public static Date strToDate(String s, String pattern)        {            if (s == null)            {                return null;            }            Date date = null;            SimpleDateFormat sdf = new SimpleDateFormat(pattern);            try            {                date = sdf.parse(s);            }            catch (ParseException e)            {                e.printStackTrace();            }            return date;        }    }}

再提供一个应用实例：

@ApiOperation(value = "以导入excel方式，上传要申请学分的用户")    @GetMapping(value = "/uuApplyUserInfo")    public AjaxResult uuApplyUserInfo(@RequestParam(value = "files",required = false) MultipartFile files) {        try {            //工具类            ImportExeclUtil readExcelUtil = new ImportExeclUtil();            List<List<String>> read = readExcelUtil.read(files.getInputStream(), true);            if (CollectionUtils.isNotEmpty(read)){                List<ApplyCreditUserDto> importList = read.stream().map(e -> {                    ApplyCreditUserDto importDto = new ApplyCreditUserDto();                    importDto.setUserName(e.get(0));                    importDto.setCreditCardNo(e.get(1));                    importDto.setCreditCardPwd(e.get(2));                    return importDto;                }).collect(Collectors.toList());                if (CollectionUtils.isEmpty(importList)){                    return AjaxResult.error("不能导入空文件");                }                //最多导入1W条                final int maxInt = 10000;                if (importList.size() > maxInt){                    return AjaxResult.error("导入最多修改1W条");                }                List<String> orderIds = importList.stream()                        .map(ApplyCreditUserDto::getUserName)                        .distinct()                        .collect(Collectors.toList());                if (!Objects.equals(orderIds.size(),importList.size())){                    return AjaxResult.error("导入信息中，有用户");                }                //调用业务层                return applyCreditLogService.uuApplyUserInfo(importList);            }else{                return AjaxResult.error("不能导入空文件");            }        } catch (Exception e) {            e.printStackTrace();            return AjaxResult.error("导入失败，更新数据库时报错！报错信息：" + e.toString());        }    }

2、将数据导出为Excel

这里先说明一下这里要做的是把从数据库查到的数据行，导出到excel；和下载一个静态文件（比如下载Excel静态文件模板），是不一样的需求。

1）引入依赖

这里用的是poi导出excel，版本如下

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->      <dependency>          <groupId>org.apache.poi</groupId>          <artifactId>poi</artifactId>          <version>4.1.1</version>      </dependency>

2）自定义注解

为了方便，提高复用效率，这里自定义2个注解；
第一个：@EnableExcel，用来开启Excel表格的导出，用在装导出数据的实体类上；

/** *  标记类是否开启Excel * @Author: Sunlong * @date: 2020/5/10 20:29 */@Target(ElementType.TYPE)@Retention(RetentionPolicy.RUNTIME)public @interface EnableExcel {}

第二个：@ExcelRow，用在装导出数据的实体类的属性上，用来映射字段与excel的对应关系；

/** *  excel 表格 列名注解 * * @author sunlong */@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelRow {    /**     *  Excel 对应列名     * @return     */    String name();    /**     *  excel 列名备注     * @return     */    String note() default "";}

3）代码逻辑—提取导出工具类

a、通过反射获取自定义注解EnableExcel 判断是否开启Excel导出

b、通过反射获取自定义注解ExcelRow 获取列对应的属性

c、把属性对应的列下标取出来，属性名做为key，下标做为value放到map中

d、遍历要导出的数据集合，通过属性描述器PropertyDescriptor获取对应属性下标及属性值并设置到cell单元格中

还是为了方便，已经提高复用效率，我们将上述代码提取成一个工具类，如下：

public class ExportExcelUtils {    /**     *  workbook     * @param titleList     * @return     */    public static HSSFWorkbook getWorkBook(List<String> titleList){        //第一步，创建一个workbook，对应一个Excel文件        HSSFWorkbook wb = new HSSFWorkbook();        // 一个sheet        HSSFSheet sheet = wb.createSheet("sheet1");        HSSFRow rowTitle = sheet.createRow(0); // 第一行 标题        // 设置标题        for (int i = 0; i < titleList.size(); i++) {            HSSFCell cell = rowTitle.createCell(i);            cell.setCellValue(titleList.get(i));        }        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列        /*sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));        sheet.addMergedRegion(new CellRangeAddress(titleList.size()-1,titleList.size()-1,titleList.size()-1,titleList.size()+1));*/        return wb;    }    public static <T> HSSFWorkbook getWorkBook(List<String> titleList , List<T> dataList) throws IntrospectionException, InvocationTargetException, IllegalAccessException {        if (CollectionUtils.isNotEmpty(dataList)) {            T t1 = dataList.get(0);            Class<?> t1Class = t1.getClass();            EnableExcel enableExcel = t1Class.getAnnotation(EnableExcel.class);            if (enableExcel == null) {                throw new IllegalArgumentException("EnableExcel 注解没有在实体类启用");            }            Field[] fields = t1Class.getDeclaredFields();            if (fields != null && fields.length > 0) {                Map<String , Integer> titleMap = new HashMap<>(titleList.size()); // 存放属性名称对应的下标                int fieldExcelSize = 0; // 类中ExcelRow 注解的数量                for (Field field : fields) {                    field.setAccessible(true);                    String fieldName = field.getName();                    ExcelRow excelRow = field.getAnnotation(ExcelRow.class);                    if (excelRow != null) {                        String name = excelRow.name();                        if (StringUtils.isEmpty(name)) {                            throw new IllegalArgumentException("ExcelRow 注解name属性不能为空");                        }                        int index = titleList.indexOf(name.trim());                        if (index != -1) {                            fieldExcelSize++;                            titleMap.put(fieldName , index);                        }                    }                }                if (!(titleList.size() == fieldExcelSize)) {                    throw new IllegalArgumentException("ExcelRow 注解name属性对应的列数不对");                }                HSSFWorkbook workBook = getWorkBook(titleList);                HSSFSheet sheet = workBook.getSheetAt(0);                for (T t : dataList) {                    int lastRowNum = sheet.getLastRowNum();                    HSSFRow row = sheet.createRow(lastRowNum + 1);                    BeanInfo beanInfo = Introspector.getBeanInfo(t.getClass());                    PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();                    for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {                        String fieldName = propertyDescriptor.getName();                        if (titleMap.containsKey(fieldName)) {                            Method readMethod = propertyDescriptor.getReadMethod();                            if (readMethod != null) {                                Class<?> returnType = readMethod.getReturnType();                                String simpleName = returnType.getSimpleName();                                Object invoke = readMethod.invoke(t);                                String value = "";                                // 可以根据不同的类型返回不同的数据                                if ("date".equalsIgnoreCase(simpleName)) {                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                                    if (invoke != null) {                                        value = simpleDateFormat.format(invoke);                                    }                                }                                if (invoke != null && "".equals(value)) {                                    value = invoke.toString();                                }                                row.createCell(titleMap.get(fieldName)).setCellValue(value);                            }                        }                    }                }                return workBook;            }        }        return null;    }}

4）应用实例

创建一个用来装导出数据的类（为了不与项目其他功能冲突，我一般都是新建一个专门做导出的实体类）

@EnableExcel@Datapublic class UserEntity {    @ExcelRow(name = "name")    private String username;    @ExcelRow(name = "pass")    private String password;    @ExcelRow(name = "date")    private Date createDate;}

模拟导出功能：

public class Test {    public static void main(String[] args) throws IllegalAccessException, IntrospectionException, InvocationTargetException, IOException {        List<String> titleList = new ArrayList<>();        titleList.add("name");        titleList.add("pass");        titleList.add("date");        List<UserEntity> userEntities = new ArrayList<>();        for (int i = 0; i < 10; i++) {            UserEntity userEntity1 = new UserEntity();            userEntity1.setUsername("username"+i);            userEntity1.setPassword("password"+i);            userEntity1.setCreateDate(new Date());            userEntities.add(userEntity1);        }        HSSFWorkbook workBook = ExportExcelUtils.getWorkBook(titleList, userEntities);        if (workBook != null) {            File file = new File("D:\\test_export.xlsx");            workBook.write(file);        }            }}