小功能实现（十一）使用ExcelUtil和e-iceblue将Excel文件导入到数据库中

一、前言

• 初始：起因是需要通过Excel表格的形式将数据导入到数据库中，但因为以前的人使用了ExcelUtil这个工具类，本着少些改动的原则，就没有用POI的方式操作Excel表，而是用原有的工具类进行操作
• 整体功能：从前端接收Excel文件，由于ExcelUtil只支持xls格式，所以如果是xlsx格式，还需要使用e-iceblue转换格式，然后将Excel文件中的数据取出，并导入到数据库中
• 不足：使用e-iceblue转换格式的过程中会生成一个冗余的中间文件，而且我尝试了很多种办法都没办法删掉它；转换过程需要一定时间；需要更新pom.xml，而且容易报错
• 后续优化：尝试使用POI的方式操作Excel表导入；批量插入，别一条一条插

二、步骤

• ExcelUtil工具类

package com.ev_image.gdwebgl.tools;




import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * excel 工具类
 *
 * @since 0.1.0
 */

public class ExcelUti {


    /**
     * @param listMap      多数据源
     * @param out       导出流
     * @MethodName : listToExcel
     * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小）
     */
    public static <T> void listMapToExcel(
            Map<Map<String,List>,LinkedHashMap> listMap,
            OutputStream out
    ) throws Exception {


        if (listMap == null || listMap.size() == 0) {
            return;
            // throw new Exception("数据源中没有任何数据");
        }
        //创建工作簿并发送到OutputStream指定的地方
        WritableWorkbook wwb;
        try {
            wwb = Workbook.createWorkbook(out);
            for (Map.Entry<Map<String,List>, LinkedHashMap> entry : listMap.entrySet()) {
                System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
                Map<String,List>  obj=entry.getKey();
                LinkedHashMap fieldMap=entry.getValue();
                for (Map.Entry<String, List> item : obj.entrySet())
                {
                    List list=item.getValue();
                    String sheetName=item.getKey();
//                    WritableSheet sheet = wwb.createSheet(sheetName, 0);
//                    fillSheet(sheet, list, fieldMap, 0, list.size() - 1);
                    double sheetNum = Math.ceil(list.size() / new Integer(65535).doubleValue());

                    //2.创建相应的工作表，并向其中填充数据
                    for (int i = 0; i < sheetNum; i++) {
                        //如果只有一个工作表的情况
                        if (1 == sheetNum) {
                            WritableSheet sheet = wwb.createSheet(sheetName, i);
                            fillSheet(sheet, list, fieldMap, 0, list.size() - 1);
                            //有多个工作表的情况
                        } else {
                            WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);

                            //获取开始索引和结束索引
                            int firstIndex = i * 65535;
                            int lastIndex = (i + 1) * 65535 - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * 65535 - 1;
                            //填充工作表
                            fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
                        }
                    }
                }
            }
            wwb.write();
            wwb.close();

        } catch (Exception e) {
            e.printStackTrace();
            //如果是Exception，则直接抛出
            if (e instanceof Exception) {
                throw (Exception) e;

                //否则将其它异常包装成Exception再抛出
            } else {
                throw new Exception("导出Excel失败");
            }
        }
    }


    /**
     * @param list      数据源
     * @param fieldMap  类的英文属性和Excel中的中文列名的对应关系
     *                  如果需要的是引用对象的属性，则英文属性使用类似于EL表达式的格式
     *                  如：list中存放的都是student，student中又有college属性，而我们需要学院名称，则可以这样写
     *                  fieldMap.put("college.collegeName","学院名称")
     * @param sheetName 工作表的名称
     * @param sheetSize 每个工作表中记录的最大个数
     * @param out       导出流
     * @MethodName : listToExcel
     * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小）
     */
    public static <T> void listToExcel(
            List<T> list,
            LinkedHashMap<String, String> fieldMap,
            String sheetName,
            int sheetSize,
            OutputStream out
    ) throws Exception {


        if (list == null || list.size() == 0) {
            return;
            // throw new Exception("数据源中没有任何数据");
        }

        if (sheetSize > 65535 || sheetSize < 1) {
            sheetSize = 65535;
        }

        //创建工作簿并发送到OutputStream指定的地方
        WritableWorkbook wwb;
        try {
            wwb = Workbook.createWorkbook(out);


            //因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条
            //所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
            //1.计算一共有多少个工作表
            double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue());

            //2.创建相应的工作表，并向其中填充数据
            for (int i = 0; i < sheetNum; i++) {
                //如果只有一个工作表的情况
                if (1 == sheetNum) {
                    WritableSheet sheet = wwb.createSheet(sheetName, i);
                    fillSheet(sheet, list, fieldMap, 0, list.size() - 1);

                    //有多个工作表的情况
                } else {
                    WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);

                    //获取开始索引和结束索引
                    int firstIndex = i * sheetSize;
                    int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * sheetSize - 1;
                    //填充工作表
                    fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
                }
            }
            if (sheetNum == 0) {
                WritableSheet sheet = wwb.createSheet(sheetName, 0);
                fillSheet(sheet, list, fieldMap, 0, list.size() - 1);
            }

            wwb.write();
            wwb.close();

        } catch (Exception e) {
            e.printStackTrace();
            //如果是Exception，则直接抛出
            if (e instanceof Exception) {
                throw (Exception) e;

                //否则将其它异常包装成Exception再抛出
            } else {
                throw new Exception("导出Excel失败");
            }
        }

    }

    /**
     * @param list     数据源
     * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系
     * @param out      导出流
     * @throws Exception
     * @MethodName : listToExcel
     * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，工作表大小为2003支持的最大值）
     */
    public static <T> void listToExcel(
            List<T> list,
            LinkedHashMap<String, String> fieldMap,
            String sheetName,
            OutputStream out
    ) throws Exception {

        listToExcel(list, fieldMap, sheetName, 65535, out);

    }


    /**
     * @param list      数据源
     * @param fieldMap  类的英文属性和Excel中的中文列名的对应关系
     * @param sheetSize 每个工作表中记录的最大个数
     * @param response  使用response可以导出到浏览器
     * @throws Exception
     * @MethodName : listToExcel
     * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小）
     */
    public static <T> void listToExcel(
            List<T> list,
            LinkedHashMap<String, String> fieldMap,
            String sheetName,
            int sheetSize,
            HttpServletResponse response
    ) throws Exception {

        //设置默认文件名为当前时间：年月日时分秒
        String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();

        //设置response头信息
        response.reset();
        response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");

        //创建工作簿并发送到浏览器
        try {

            OutputStream out = response.getOutputStream();
            listToExcel(list, fieldMap, sheetName, sheetSize, out);

        } catch (Exception e) {
            e.printStackTrace();

            //如果是Exception，则直接抛出
            if (e instanceof Exception) {
                throw (Exception) e;

                //否则将其它异常包装成Exception再抛出
            } else {
                throw new Exception("导出Excel失败");
            }
        }
    }


    /**
     * @param list     数据源
     * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系
     * @param response 使用response可以导出到浏览器
     * @throws Exception
     * @MethodName : listToExcel
     * @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值）
     */
    public static <T> void listToExcel(
            List<T> list,
            LinkedHashMap<String, String> fieldMap,
            String sheetName,
            HttpServletResponse response
    ) throws Exception {

        listToExcel(list, fieldMap, sheetName, 65535, response);
    }

    /**
     * @param in           ：承载着Excel的输入流
     * @param entityClass  ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
     * @param fieldMap     ：Excel中的中文列头和类的英文属性的对应关系Map
     * @param uniqueFields ：指定业务主键组合（即复合主键），这些列的组合不能重复
     * @return ：List
     * @throws Exception
     * @MethodName : excelToList
     * @Description : 将Excel转化为List
     */
    public static <T> List<T> excelToList(
            InputStream in,
            String sheetName,
            Class<T> entityClass,
            LinkedHashMap<String, String> fieldMap,
            String[] uniqueFields
    ) throws Exception {

        //定义要返回的list
        List<T> resultList = new ArrayList<T>();

        try {

            //根据Excel数据源创建WorkBook
            Workbook wb = Workbook.getWorkbook(in);
            //获取工作表
            Sheet sheet = wb.getSheet(sheetName);
//            Sheet sheet = wb.getSheet(0);

            //获取工作表的有效行数
            int realRows = 0;
            for (int i = 0; i < sheet.getRows(); i++) {

                int nullCols = 0;
                for (int j = 0; j < sheet.getColumns(); j++) {
                    Cell currentCell = sheet.getCell(j, i);
                    if (currentCell == null || "".equals(currentCell.getContents().toString())) {
                        nullCols++;
                    }
                }

                if (nullCols == sheet.getColumns()) {
                    break;
                } else {
                    realRows++;
                }
            }


            //如果Excel中没有数据则提示错误
            if (realRows <= 1) {
                throw new Exception("Excel文件中没有任何数据");
            }


            Cell[] firstRow = sheet.getRow(0);

            String[] excelFieldNames = new String[firstRow.length];

            //获取Excel中的列名
            for (int i = 0; i < firstRow.length; i++) {
                excelFieldNames[i] = firstRow[i].getContents().toString().trim();
            }

            //判断需要的字段在Excel中是否都存在
            boolean isExist = true;
            List<String> excelFieldList = Arrays.asList(excelFieldNames);
            for (String cnName : fieldMap.keySet()) {
                if (!excelFieldList.contains(cnName)) {
                    isExist = false;
                    break;
                }
            }

            //如果有列名不存在，则抛出异常，提示错误
            if (!isExist) {
                throw new Exception("Excel中缺少必要的字段，或字段名称有误");
            }


            //将列名和列号放入Map中,这样通过列名就可以拿到列号
            LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
            for (int i = 0; i < excelFieldNames.length; i++) {
                colMap.put(excelFieldNames[i], firstRow[i].getColumn());
            }


            //判断是否有重复行
            //1.获取uniqueFields指定的列
            Cell[][] uniqueCells = new Cell[uniqueFields.length][];
            for (int i = 0; i < uniqueFields.length; i++) {
                int col = colMap.get(uniqueFields[i]);
                uniqueCells[i] = sheet.getColumn(col);
            }

            //2.从指定列中寻找重复行
            for (int i = 1; i < realRows; i++) {
                int nullCols = 0;
                for (int j = 0; j < uniqueFields.length; j++) {
                    String currentContent = uniqueCells[j][i].getContents();
                    Cell sameCell = sheet.findCell(currentContent,
                            uniqueCells[j][i].getColumn(),
                            uniqueCells[j][i].getRow() + 1,
                            uniqueCells[j][i].getColumn(),
                            uniqueCells[j][realRows - 1].getRow(),
                            true);
                    if (sameCell != null) {
                        nullCols++;
                    }
                }

                if (nullCols == uniqueFields.length) {
                    throw new Exception("Excel中有重复行，请检查");
                }
            }

            //将sheet转换为list
            for (int i = 1; i < realRows; i++) {
                //新建要转换的对象
                T entity = entityClass.newInstance();

                //给对象中的字段赋值
                for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
                    //获取中文字段名
                    String cnNormalName = entry.getKey();
                    //获取英文字段名
                    String enNormalName = entry.getValue();
                    //根据中文字段名获取列号
                    int col = colMap.get(cnNormalName);

                    //获取当前单元格中的内容
                    String content = sheet.getCell(col, i).getContents().toString().trim();

                    //给对象赋值
                    setFieldValueByName(enNormalName, content, entity);
                }

                resultList.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
            //如果是Exception，则直接抛出
            if (e instanceof Exception) {
                throw (Exception) e;

                //否则将其它异常包装成Exception再抛出
            } else {
                e.printStackTrace();
                throw new Exception("导入Excel失败");
            }
        }
        return resultList;
    }





    /*<-------------------------辅助的私有方法----------------------------------------------->*/

    /**
     * @param fieldName 字段名
     * @param o         对象
     * @return 字段值
     * @MethodName : getFieldValueByName
     * @Description : 根据字段名获取字段值
     */
    private static Object getFieldValueByName(String fieldName, Object o) throws Exception {

        Object value = null;
        Field field = getFieldByName(fieldName, o.getClass());

        if (field != null) {
            field.setAccessible(true);
            value = field.get(o);
        } else {
            throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
        }

        return value;
    }

    /**
     * @param fieldName 字段名
     * @param clazz     包含该字段的类
     * @return 字段
     * @MethodName : getFieldByName
     * @Description : 根据字段名获取字段
     */
    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        //拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();

        //如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }

        //否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }

        //如果本类和父类都没有，则返回空
        return null;
    }


    /**
     * @param fieldNameSequence 带路径的属性名或简单属性名
     * @param o                 对象
     * @return 属性值
     * @throws Exception
     * @MethodName : getFieldValueByNameSequence
     * @Description :
     * 根据带路径或不带路径的属性名获取属性值
     * 即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.name等
     */
    private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {

        Object value = null;

        //将fieldNameSequence进行拆分
        String[] attributes = fieldNameSequence.split("\\.");
        if (attributes.length == 1) {
            value = getFieldValueByName(fieldNameSequence, o);
        } else {
            //根据属性名获取属性对象
            Object fieldObj = getFieldValueByName(attributes[0], o);
            String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
            value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
        }
        return value;

    }


    /**
     * @param fieldName  字段名
     * @param fieldValue 字段值
     * @param o          对象
     * @MethodName : setFieldValueByName
     * @Description : 根据字段名给对象的字段赋值
     */
    private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {

        Field field = getFieldByName(fieldName, o.getClass());
        if (field != null) {
            field.setAccessible(true);
            //获取字段类型
            Class<?> fieldType = field.getType();

            //根据字段类型给字段赋值
            if (String.class == fieldType) {
                field.set(o, String.valueOf(fieldValue));
            } else if ((Integer.TYPE == fieldType)
                    || (Integer.class == fieldType)) {
                field.set(o, Integer.parseInt(fieldValue.toString()));
            } else if ((Long.TYPE == fieldType)
                    || (Long.class == fieldType)) {
                field.set(o, Long.valueOf(fieldValue.toString()));
            } else if ((Float.TYPE == fieldType)
                    || (Float.class == fieldType)) {
                field.set(o, Float.valueOf(fieldValue.toString()));
            } else if ((Short.TYPE == fieldType)
                    || (Short.class == fieldType)) {
                field.set(o, Short.valueOf(fieldValue.toString()));
            } else if ((Double.TYPE == fieldType)
                    || (Double.class == fieldType)) {
                field.set(o, Double.valueOf(fieldValue.toString()));
            } else if (Character.TYPE == fieldType) {
                if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
                    field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
                }
            } else if (BigDecimal.class == fieldType) {
                field.set(o, BigDecimal.valueOf(Double.parseDouble(fieldValue.toString())));
            } else if (Date.class == fieldType) {
                field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
            } else {
                field.set(o, fieldValue);
            }
        } else {
            throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
        }
    }


    /**
     * @param ws
     * @MethodName : setColumnAutoSize
     * @Description : 设置工作表自动列宽和首行加粗
     */
    private static void setColumnAutoSize(WritableSheet ws, int extraWith) {
        //获取本列的最宽单元格的宽度
        for (int i = 0; i < ws.getColumns(); i++) {
            int colWith = 0;
            for (int j = 0; j < ws.getRows(); j++) {
                String content = ws.getCell(i, j).getContents().toString();
                int cellWith = content.length();
                if (colWith < cellWith) {
                    colWith = cellWith;
                }
            }
            //设置单元格的宽度为最宽宽度+额外宽度
            ws.setColumnView(i, colWith + extraWith);
        }

    }

    /**
     * @param sheet      工作表
     * @param list       数据源
     * @param fieldMap   中英文字段对应关系的Map
     * @param firstIndex 开始索引
     * @param lastIndex  结束索引
     * @MethodName : fillSheet
     * @Description : 向工作表中填充数据
     */
    private static <T> void fillSheet(
            WritableSheet sheet,
            List<T> list,
            LinkedHashMap<String, String> fieldMap,
            int firstIndex,
            int lastIndex
    ) throws Exception {

        //定义存放英文字段名和中文字段名的数组
        String[] enFields = new String[fieldMap.size()];
        String[] cnFields = new String[fieldMap.size()];

        //填充数组
        int count = 0;
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            enFields[count] = entry.getKey();
            cnFields[count] = entry.getValue();
            count++;
        }
        //填充表头
        for (int i = 0; i < cnFields.length; i++) {
            Label label = new Label(i, 0, cnFields[i]);
            sheet.addCell(label);
        }

        if (list != null && list.size() != 0) {

            //填充内容
            int rowNo = 1;
            for (int index = firstIndex; index <= lastIndex; index++) {
                //获取单个对象
                T item = list.get(index);
                for (int i = 0; i < enFields.length; i++) {
                    Object objValue = getFieldValueByNameSequence(enFields[i], item);
                    String fieldValue = objValue == null ? "" : objValue.toString();
                    Label label = new Label(i, rowNo, fieldValue);
                    sheet.addCell(label);
                }

                rowNo++;
            }
        }

        //设置自动列宽
        setColumnAutoSize(sheet, 5);
    }

}

• 在pom.xml中导入e-iceblue

	<dependencies>
		<dependency>
			<groupId>e-iceblue</groupId>
			<artifactId>spire.xls</artifactId>
			<version>12.9.1</version>
		</dependency>
	</dependencies>

	<repositories>
		<repository>
			<id>com.e-iceblue</id>
			<name>e-iceblue</name>
			<url>https://repo.e-iceblue.cn/repository/maven-public/</url>
		</repository>
	</repositories>

• 若配置的仓库不起作用，在maven的setting.xml文件中找到如下位置，在mirrorOf位置的*后面加上!com.e-iceblue

  <mirror>
    <id>aliyunmaven</id>
    <mirrorOf>*,!com.e-iceblue</mirrorOf>
    <name>阿里云公共仓库</name>
    <url>https://maven.aliyun.com/repository/public</url>
  </mirror>

• 导入主体代码

@Override
    public Object insertCableLineInfoByExcel(MultipartFile file){
        try{
            //初始化需要的参数
            InputStream inputStream=file.getInputStream();

            //若后缀是xlsx，则需要先转换成xls
            if (file.getOriginalFilename().endsWith("xlsx")){
                Workbook workbook=new Workbook();
                workbook.loadFromHtml(inputStream);
                workbook.saveToFile(file.getOriginalFilename().substring(0,file.getOriginalFilename().indexOf("."))+".xls", ExcelVersion.Version97to2003);
                File fileTemp=new File(file.getOriginalFilename().substring(0,file.getOriginalFilename().indexOf("."))+".xls");
                inputStream=new FileInputStream(fileTemp);
                //删除临时excel
//                FileUtil.del(fileTemp.getPath());
            }
            //sheet名称
            String sheetname="Sheet1";
            EvCablelineinfor evCablelineinfor=new EvCablelineinfor();

			//对应关系
            LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
            fieldMap.put("编号", "id");
            fieldMap.put("线路编号", "loopid");
            fieldMap.put("线路名称", "loopname");
            fieldMap.put("电缆名称", "cablelinename");
            fieldMap.put("起始设备", "startdevice");
            fieldMap.put("终止设备", "enddevice");
            fieldMap.put("电缆长度", "cablelength");
            fieldMap.put("弧线类型", "arctype");
            fieldMap.put("运行状态", "runstatus");
            fieldMap.put("覆盖类型", "covertype");

			//主键
            String[] uniqueFields=new String[1];
            uniqueFields[0]="编号";

			//转换成List
            List list = ExcelUti.excelToList(inputStream,sheetname,evCablelineinfor.getClass(),fieldMap,uniqueFields);
            System.out.println(list.size());
            int isSuccess=0;
            //插入
            for(int i=0;i<list.size();i++){
                isSuccess=evCablelineinforMapper.insertSelective((EvCablelineinfor) list.get(i));
            }
            if(isSuccess<1){
                return ResultUtil.error("excel表格导入失败");
            }
            return ResultUtil.success("excel表格导入成功");
        } catch (Exception e){
            return ResultUtil.error("excel表格格式报错");
        }
    }

参考

• https://blog.csdn.net/LabDNirvana/article/details/104497709