SSM 使用 POI 实现EXCEL的上传与下载

导入包

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.cglib.beans.BeanMap;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

EXCEL工具类

读取EXCEL

public static Workbook readExcel(String filePath) {
    Workbook wb = null;
    if (filePath == null) {
        return null;
    }
    String extString = filePath.substring(filePath.lastIndexOf("."));
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        if (".xls".equals(extString)) {
            return wb = new HSSFWorkbook(is);
        } else if (".xlsx".equals(extString)) {
            return wb = new XSSFWorkbook(is);
        } else {
            return wb = null;
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return wb;
}

EXCEL值转换

public static Object getCellFormatValue(Cell cell) {
    Object cellValue = null;
    if (cell != null) {
        //判断cell类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: {
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = dff.format(theDate);
                } else {
                    cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            }
            case Cell.CELL_TYPE_FORMULA: {
                //判断cell是否为日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    //转换为日期格式yyyy-mm-dd
                    cellValue = cell.getDateCellValue();
                } else {
                    //数字
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            case Cell.CELL_TYPE_STRING: {
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            default:
                cellValue = "";
        }
    } else {
        cellValue = "";
    }
    return cellValue;
}

将map转为javabean对象

public static <T> T mapToBean(Map<String, Object> map, Class<T> T) throws Exception
{
    if (map == null || map.size() == 0)
    {
        return null;
    }
    //获取map中所有的key值，全部更新成大写，添加到keys集合中,与mybatis中驼峰命名匹配
    Object mvalue = null;
    Map<String, Object> newMap = new HashMap<>();
    Iterator<Map.Entry<String, Object>> it = map.entrySet().iterator();
    while(it.hasNext()){
        String key = it.next().getKey();
        mvalue = map.get(key);
        newMap.put(key.toUpperCase(Locale.US), mvalue);
    }
        BeanInfo beanInfo = Introspector.getBeanInfo(T);
    T bean = T.newInstance();
    PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
    for (int i = 0, n = propertyDescriptors.length; i < n; i++)
    {
        PropertyDescriptor descriptor = propertyDescriptors[i];
        String propertyName = descriptor.getName();
        String upperPropertyName = propertyName.toUpperCase();
        if (newMap.keySet().contains(upperPropertyName))
        {
            Object value = newMap.get(upperPropertyName);
            //这个方法不会报参数类型不匹配的错误。
            BeanUtils.copyProperty(bean, propertyName, value);
        }
    }
    return bean;
}

将对象转为map

public static <T> Map<String, Object> beanToMap(T bean) {
    Map<String, Object> map = new HashMap<>();
    if (bean != null) {
        BeanMap beanMap = BeanMap.create(bean);
        for (Object key : beanMap.keySet()) {
            map.put(String.valueOf(key), beanMap.get(key));
        }
    }
    return map;
}

获取EXCEL数据列表

public static List<Map<String, Object>> LoadExcle(String filePath, String[] columns) throws IOException, ParseException {
    System.out.println(">>>>>>>>>>>>>读取Excel");
    Workbook wb = null;
    Sheet sheet = null;
    Row row = null;
    String cellData = null;
    //创建List对象储存map集合
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    wb = readExcel(filePath);
    if (wb != null) {
        //获取第一个sheet
        sheet = wb.getSheetAt(0);
        //获取最大行数
        int rownum = sheet.getPhysicalNumberOfRows();
        System.out.println("最大行数" + rownum);
        //获取第一行
        row = sheet.getRow(1);
        //获取最大列数
        int colnum = row.getPhysicalNumberOfCells();
        System.out.println("最大列数" + colnum);
        //从第二行开始获取数据
        for (int i = 2 ; i < rownum; i++) {
            //用来存放表中数据
            Map<String, Object> map = new HashMap<String, Object>();
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < colnum; j++) {
                    cellData = (String) getCellFormatValue(row.getCell(j));
                    map.put(columns[j], cellData);
                }
            } else {
                break;
            }
            list.add(map);
        }
    }
    return list;
}

将对象列表转换成EXCEL

public static String outExcel(String table_name,String[] head,String[] name,List<Map<String,Object>> list,int start) throws IOException{
    //创建工作薄对象
    XSSFWorkbook workbook=new XSSFWorkbook();
    // 创建工作表对象
    XSSFSheet sheet = workbook.createSheet();
    //表头
    XSSFRow row_name = sheet.createRow(0);//设置第一行，从零开始
    if (name!=null){
        for (int i = 0 ; i < name.length ; i++){
            row_name.createCell(i).setCellValue(name[i]);//第一行第i列
        }
    }
    //表体
    //start为表体起始行
    for (Map map:list) {
        //遍历行数index
        XSSFRow row = sheet.createRow(start);//设置第a+1行
        //创建工作表的列
        for (int j = 0 ; j < head.length ;j++){
            row.createCell(j).setCellValue(String.valueOf(map.get(head[j])));//第a行第j+1列
        }
        start++;
    }
    workbook.setSheetName(0,table_name);//设置sheet的Name
    // 文档输出
    String filePath = "D:/"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xlsx";
    FileOutputStream out = new FileOutputStream(filePath);
    workbook.write(out);
    out.close();
    return filePath;
}