基于SSM框架的Excel工具类

最新推荐文章于 2024-05-09 17:08:00 发布

BoomGred

最新推荐文章于 2024-05-09 17:08:00 发布

阅读量1.3k

点赞数

分类专栏： java服务端开发文章标签： Excel导出导入工具

本文链接：https://blog.csdn.net/Gred01/article/details/79042425

版权

java服务端开发专栏收录该内容

18 篇文章 0 订阅

订阅专栏

前言

本次提供Excel工具类导入和导出的功能，是本人在借鉴网上的部分代码的基础上搭出一个公用化的工具。如有不妥之处，麻烦指明，本人不胜感激。

Excel导出

实现思路
- 通过Apache提供POI包读取Excel信息，首先读取指定字段名表头，其次从指定行数开始读取数据，然和对应的字段名映射到Map，最后返回一个List。在获取读取结果后通过调用Map映射到实体类工具，将Map中对应的值映射到实体中。这样子，只要规定好Excel模板和指定好表头读取行数和数据读取开始索引，就可以达到一个公用化的目的了。

实现步骤
- 导入Maven依赖

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>3.17</version>
        </dependency>

定义上传Excel格式
接收MultiPartFile类型的数据
调用ExcelUtil 工具

ExcelUtils utils = new ExcelUtils();
//字段名读取开始索引
utils.setFieldReadIndex(4);
//数据读取开始索引
utils.setDataReadIndex(6);
List<Map> maps = utils.getExcelInfo(file.getOriginalFilename(), file);

接收到List结果在MapToBeanUtils中映射。

for(Map map :maps){
    //这里Bean是要用来接收的自定义实体类
    Bean obj = MapToBeanUtils.getModel(map,Bean.class);
    //处理相关业务逻辑    
}

Excel导出

实现思路
- 借助自定义注解在导出实体类的字段标注该字段对应的表头名称，再通过ExcelUtils工具解析实体类数据和其字段注解来生成Excel。
实现步骤
- 导入Maven依赖

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>3.17</version>
        </dependency>

自定义注解

@Target(ElementType.FIELD)
@Documented
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {

    String title();
}

导出实体类引用注解形式

@ExcelColumn(title="uuid")
    private String uuid;

引用工具类

//这里Bean是要用来导出的自定义实体类
ExcelUtils.export(Bean.class, list, DateUtil.format(new Date(),DateUtil.INT_STRING_FORMAT), response);

源码

ExcelUtils.java

package com.demo.util;

import com.andacx.core.log.LogFactory;
import com.andacx.tool.Asserts;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

/**
 * <p>@description:【Excel写入和导出工具】</p>
 * <p>@author:【Boomer】</p>
 * <p>@date:【2018/1/11 11:18】</p>
 **/
public class ExcelUtils {


    private static final Logger LOGGER = LogFactory.get(ExcelUtils.class);

    /*** 总行数 **/
    private int totalRows = 0;
    /*** 总条数 **/
    private int totalCells = 0;
    /*** 数据读取开始索引 **/
    private int dataReadIndex = 3;
    /*** 字段名读取开始索引 **/
    private int fieldReadIndex = 2;

    public ExcelUtils() {
    }


    public int getTotalRows() {
        return totalRows;
    }

    public int getTotalCells() {
        return totalCells;
    }


    public int getDataReadIndex() {
        return dataReadIndex;
    }

    public void setDataReadIndex(int dataReadIndex) {
        this.dataReadIndex = dataReadIndex;
    }

    public int getFieldReadIndex() {
        return fieldReadIndex;
    }

    public void setFieldReadIndex(int fieldReadIndex) {
        this.fieldReadIndex = fieldReadIndex;
    }

    /**
     * <p>Description:【验证EXCEL文件】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param filePath
     * @return
     **/
    public boolean validateExcel(String filePath) {
        Asserts.notNull(filePath, "文件名不是excel格式");
        Asserts.isTrue((isExcel2003(filePath) || isExcel2007(filePath)), "文件名不是excel格式");
        return true;
    }

    /**
     * <p>Description:【是否是2003的excel，返回true是2003】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param filePath
     * @return
     **/
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+.(xls)$");
    }

    /**
     * <p>Description:【是否是2007的excel，返回true是2007】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param filePath
     * @return
     **/
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+.(xlsx)$");
    }


    /**
     * <p>Description:【读EXCEL文件，获取客户信息集合】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param fileName         
     * @param Mfile 
     * @return
     **/
    public List<Map> getExcelInfo(String fileName, MultipartFile Mfile) {

        //把spring文件上传的MultipartFile转换成CommonsMultipartFile类型
        CommonsMultipartFile cf = (CommonsMultipartFile) Mfile;
        //获取本地存储路径
        File fileFolder = new File(Sysutils.getUploadFilePath());
        if (!fileFolder.exists()) {
            fileFolder.mkdirs();
        }

        //新建一个文件
        File uploadFile = new File(Sysutils.getUploadFilePath() + System.currentTimeMillis() + ".xlsx");
        //将上传的文件写入新建的文件中
        try {
            cf.getFileItem().write(uploadFile);
        } catch (Exception e) {
            e.printStackTrace();
        }

        //初始化信息的集合
        List<Map> list = new ArrayList<>();
        //初始化输入流
        InputStream is = null;
        try {
            //验证文件名是否合格
            if (!validateExcel(fileName)) {
                return null;
            }
            //根据新建的文件实例化输入流
            is = new FileInputStream(uploadFile);
            //根据excel里面的内容读取客户信息
            list = getExcelInfo(is, isExcel2003(fileName));
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * <p>Description:【根据excel里面的内容读取客户信息】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param is          输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     **/
    public List<Map> getExcelInfo(InputStream is, boolean isExcel2003) {
        List<Map> list;
        try {
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb;
            //当excel是2003时
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {//当excel是2007时
                wb = new XSSFWorkbook(is);
            }
            //读取Excel里面客户的信息
            list = readExcelValue(wb);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        } catch (NumberFormatException e) {
            throw new RuntimeException("读取错误，请确认Excel是否包含错误字符");
        }
        return list;
    }

    /**
     * <p>Description:【读取Excel里面客户的信息】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param wb
     * @return
     **/
    private List<Map> readExcelValue(Workbook wb) {
        //得到第一个shell
        Sheet sheet = wb.getSheetAt(0);

        //得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();

        //得到Excel的列数(前提是有行数)
        if (totalRows >= this.dataReadIndex && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        List<Map> list = new ArrayList<>();
        Map tempMap;
        //获取列数据对应的字段
        Row title = sheet.getRow(this.fieldReadIndex);
        List<String> header = new ArrayList<>();
        //循环Excel的列
        for (int c = 0; c < this.totalCells; c++) {
            Cell cell = title.getCell(c);
            header.add(cell.getStringCellValue());
        }

        //循环Excel行数,从第二行开始。标题不入库
        for (int r = this.dataReadIndex; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            tempMap = new HashMap(this.totalCells);

            //循环Excel的列
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    cell.setCellType(CellType.STRING);
                    tempMap.put(header.get(c), cell.getStringCellValue());
                }
            }
            //添加客户
            list.add(tempMap);
        }
        return list;
    }

    /**
     * <p>Description:【导出到file】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param clz
     * @param data
     * @param filePath
     * @param <T>
     * @throws IOException
     **/
    public static <T> void export(Class<T> clz, List<? extends T> data, String filePath) throws IOException {
        FileOutputStream out = new FileOutputStream(filePath);
        getBook(clz, data).write(out);
        out.close();
    }

    /**
     * <p>Description:【导出到httpServletResponse】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param clz
     * @param data
     * @param fileName
     * @param response
     * @param <T>
     **/
    public static <T> void export(Class<T> clz, List<? extends T> data, String fileName, HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx" + "\"");
            getBook(clz, data).write(response.getOutputStream());
        } catch (Exception e) {
            throw new RuntimeException("系统异常");
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                LOGGER.error("流关闭异常：" + e.getMessage());
            }
        }
    }

    /**
     * <p>Description:【生成Excel】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param clz
     * @param data
     * @return
     **/
    private static <T> SXSSFWorkbook getBook(Class<T> clz, List<? extends T> data) {
        if (null == clz || null == data || data.size() <= 0) {
            throw new RuntimeException("参数异常");
        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
        //第一个工作本
        SXSSFSheet sheet = sxssfWorkbook.createSheet();
        sheet.trackAllColumnsForAutoSizing();
        //表头
        Row header = sheet.createRow(0);
        //读取类字段、注解信息
        Field[] fields = clz.getDeclaredFields();
        //写入表头
        int invalidFieldNum = 0;
        for (Field field : fields) {
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (excelColumn == null) {
                continue;
            }
            Cell headerCell = header.createCell(invalidFieldNum++);
            headerCell.setCellStyle(headerCellStyle(sxssfWorkbook));
            headerCell.setCellValue(excelColumn.title());
        }
        //写入数据
        for (int i = 0; i < data.size(); i++) {
            //第0行为表头
            Row row = sheet.createRow(i + 1);
            //循环读取字段
            int cellIndex = 0;
            for (int j = 0; j < fields.length; j++) {
                Field field = fields[j];
                field.setAccessible(true);
                Object cellValue;
                ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                if (null == excelColumn) {
                    continue;
                }
                Object o;
                try {
                    o = field.get(data.get(i));
                    cellValue = o;
                } catch (Exception e) {
                    throw new RuntimeException("系统异常");
                }

                CellUtil.createCell(row, cellIndex++, String.valueOf(cellValue));
            }
        }
        //自适应宽度
        for (int i = 0; i < invalidFieldNum; i++) {
            sheet.autoSizeColumn(i);
        }
        return sxssfWorkbook;
    }

    /**
     * <p>Description:【设置Excel表头样式】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/11 14:07】</p>
     * @param wb
     * @return
     **/
    public static CellStyle headerCellStyle(Workbook wb) {

        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //加粗
        font.setBold(true);
        // 设置背景色
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //让单元格居中
        style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        // 左右居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        style.setFont(font);
        return style;
    }

}

map转实体工具类

package com.andacx.lbs.util;

import com.andacx.tool.StringUtil;
import org.apache.commons.collections.MapUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by Boomer on 2017/5/19.
 * @author Boomer
 */
public class MapToBeanUtils {

    /**
     * 获取类类型的所有Field包括父类中的Field
     *
     * @param clazz 类类型
     * @return 返回类类型的所有Field包括父类中的Field
     */
    public static Field[] getAllFields(Class clazz) {
        Map<String, Field> map = new HashMap<String, Field>();
        for (Field field : clazz.getDeclaredFields()) {

            Class fieldType = field.getType();
            String fieldName = field.getName();
            if(isBaseType(fieldType)){
                try {
                    map.put(fieldName, field);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        while (clazz.getSuperclass() != null) {
            clazz = clazz.getSuperclass();
            if (clazz == Object.class) {
                break;
            }

            for (Field field : clazz.getDeclaredFields()) {
                if (!map.containsKey(field.getName())) {
                    map.put(field.getName(), field);
                }
            }
        }
        return map.values().toArray(new Field[map.size()]);
    }

    /**
     * <p>Description:【判断是否是基本类型】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/8 16:13】</p>
     * @param fieldType
     * @return
     **/
    public static Boolean isBaseType(Class fieldType){

        if (fieldType == String.class) {
            return true;
        } else if (fieldType == double.class || fieldType == Double.class) {
            return true;
        } else if (fieldType == int.class || fieldType == Integer.class) {
            return true;
        } else if (fieldType == Boolean.class || fieldType == boolean.class) {
            return true;
        } else if (fieldType == Date.class) {
            return true;
        }

        return false;
    }

    /**
     * <p>Description:【将首字母转换为大写】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/5 15:54】</p>
     *
     * @param a
     * @return
     **/
    public static String firstCharToUpper(String a) {

        return (new StringBuilder()).insert(0, a.substring(0, 1).toUpperCase()).append(a.substring(1)).toString();
    }

    /**
     * <p>Description:【将map隐射到实体类】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/5 16:27】</p>
     *
     * @param map
     * @param model
     * @return
     **/
    public static <T> T getModel(Map map, Class<T> model) {

        Field[] fields = getAllFields(model);
        String value;
        Method method;
        T obj;
        try {
            obj = model.newInstance();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        for (Field field : fields) {
            int modifiers = field.getModifiers();
            if (Modifier.isStatic(modifiers) || Modifier.isFinal(modifiers)) {
                continue;
            }

            //根据传入的获取str来判断用哪个字符串获取参数
            value = MapUtils.getString(map, field.getName());
            if (StringUtil.isEmpty(value)) {
                if (field.getType() == String.class) {
                    try {
                        method = model.getMethod("get" + firstCharToUpper(field.getName()));
                        Object invokeValue = method.invoke(obj);
                        if (invokeValue != null) {
                            value = invokeValue.toString();
                        }
                    } catch (Exception e) {
                        if (field.getType() == Boolean.class || field.getType() == boolean.class) {
                            try {
                                String fieldName = field.getName();
                                method = model.getMethod("get" + firstCharToUpper(fieldName.startsWith("is") ? fieldName.replace("is", "") : fieldName));
                                Object invokeValue = method.invoke(obj);
                                if (invokeValue != null) {
                                    value = invokeValue.toString();
                                }
                            } catch (Exception e1) {
                                System.out.println(e1.getMessage());
                            }
                        } else {
                            System.out.println(e.getMessage());
                        }
                    }
                }
            }

            if (StringUtil.isNotEmpty(value)) {
                invokeMethod(model, obj, value, field.getName(), field.getType());
            }
        }

        return obj;
    }

    /**
     * <p>Description:【调用方法】</p>
     * <p>Author:【Boomer】</p>
     * <p>Date:【2018/1/5 16:26】</p>
     *
     * @param modelClass
     * @param object
     * @param value
     * @param fieldName
     * @param fieldType
     * @return
     **/
    private static <T> void invokeMethod(Class<T> modelClass, T object, String value, String fieldName, Class fieldType) {

        Method method = null;
        try {
            method = modelClass.getMethod("set" + firstCharToUpper(fieldName), fieldType);
        } catch (NoSuchMethodException e) {
            if (fieldType == Boolean.class || fieldType == boolean.class) {
                try {
                    method = modelClass.getMethod("set" + firstCharToUpper(fieldName.startsWith("is") ? fieldName.replace("is", "") : fieldName), fieldType);
                } catch (NoSuchMethodException e1) {
                }
            } else {
            }
        }
        try {
            if (null != method) {
                if (fieldType == String.class) {
                    method.invoke(object, value);
                } else if (fieldType == double.class || fieldType == Double.class) {
                    try {
                        method.invoke(object, Double.parseDouble(value));
                    } catch (NumberFormatException e) {
                        e.printStackTrace();
                    }
                } else if (fieldType == int.class || fieldType == Integer.class) {
                    try {
                        method.invoke(object, Integer.parseInt(value));
                    } catch (NumberFormatException e) {
                        e.printStackTrace();
                    }
                } else if (fieldType == Boolean.class || fieldType == boolean.class) {
                    try {
                        method.invoke(object, Boolean.parseBoolean(value));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                } else if (fieldType == Date.class) {
                    try {
                        SimpleDateFormat sdf = (value.length() == 10 ? new SimpleDateFormat("yyyy-MM-dd") : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
                        method.invoke(object, sdf.parse(value));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

20180122
这个工具也可以用以下方法实现

public static <T> List<T> mapToBean(List<Map> maps, Class<T> obj) throws IllegalAccessException, InstantiationException {
        List<T> list = new ArrayList<>();
        for (Map<String, Object> map : maps) {
            T module = obj.newInstance();
            BeanWrapper wapper = new BeanWrapperImpl(module);
            for (Map.Entry entry : map.entrySet()) {
                if (wapper.isWritableProperty(entry.getKey().toString())) {
                    wapper.setPropertyValue(entry.getKey().toString(), entry.getValue().toString());
                }
            }
            list.add(module);
        }
        return list;
    }

后语

源码中可能掺杂一些个人工具类源码，各位可自行更改，不影响功能实现。如果问题请留言。

BoomGred

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
5
评论
基于SSM框架的Excel工具类

前言本次提供Excel工具类导入和导出的功能，是本人在借鉴网上的部分代码的基础上搭出一个公用化的工具。如有不妥之处，麻烦指明，本人不胜感激。Excel导出实现思路 - 通过Apache提供POI包读取Excel信息，首先读取指定字段名表头，其次从指定行数开始读取数据，然和对应的字段名映射到Map，最后返回一个List。在获取读取结果后通过调用Map映射到实体类工具，
复制链接

扫一扫