java导入excel到数据库

说明：

1.excel第一行为标签，需要添加中文注释用"#"符号作为分隔符，例：name#姓名

2.读取excel完毕后，返回List<Map>格式数据，Map：key=标签，value=标签对应的值

3.代码中通过区分文件拓展名判断excel是xls还是xlsx文件并采用不同的读取方式

jar：

1.poi-3.9.jar

2.poi-ooxml-3.9.jar

3.poi-ooxml-schemas-3.9.jar

4.xmlbeans-2.4.0.jar

 

package com.peration.core.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * excle工具类
 * @author 
 *
 */
public class ExcelHelper {
    
     /** 
     * 合并方法，读取excel文件 (springMVC方式)
     * 根据文件名自动识别读取方式 
     * 若标题行需加中文备注，则以"#"符号隔开,中文注释必须加在"#"后面,如name#姓名
     * 支持97-2013格式的excel文档 
     *  
     * @param fileName 
     *            上传文件名 
     * @param file 
     *            上传的文件 
     * @return 返回列表内容格式： 
     *  每一行数据都是以对应列的表头为key 内容为value 比如 excel表格为： 
     * =============== 
     *  A | B | C | D 
     * ===|===|===|=== 
     *  1 | 2 | 3 | 4 
     * ---|---|---|---  
     *  a | b | c | d 
     * --------------- 
     * 返回值 map： 
     *   map1:   A:1 B:2 C:3 D:4 
     *   map2:   A:a B:b C:d D:d 
     */ 
    @SuppressWarnings("rawtypes")
    public static List<Map> readExcel(String fileName, MultipartFile file) {
        //    返回值列表
        List<Map> valueList = new ArrayList<Map>();
        String filepathtemp = "/temp";//    缓存文件目录
        String tempFileName = System.currentTimeMillis() + "." + getExtensionName(fileName);// 临时文件名
        String extensionName = getExtensionName(fileName);// 文件拓展名
        //    判断缓存目录是否存在，不存在先创建
        File filelist = new File (filepathtemp);
        if (!filelist.exists() && !filelist.isDirectory())
            filelist.mkdirs();
        
        //    创建文件完整路径
        String filePath = filepathtemp + File.separator + tempFileName;
        File tempfile = new File(filePath);
        //    拷贝文件到服务器缓存目录下
        copy(file, filepathtemp, tempFileName);
        
        //    根据文件拓展名区分文件读取方式
        if (extensionName.equalsIgnoreCase("xls")) {
            valueList = readExcel2003(filePath);
        } else if (extensionName.equalsIgnoreCase("xlsx")) {
            valueList = readExcel2007(filePath);
        }
        //    删除缓存文件
        tempfile.delete();
        return valueList;
    }
    
    /** 
     * 合并方法，读取excel文件 (读取文件方式)
     * 根据文件名自动识别读取方式 
     * 若标题行需加中文备注，则以"#"符号隔开,中文注释必须加在"#"后面,如name#姓名
     * 支持97-2013格式的excel文档 
     *  
     * @param fileName 
     *            上传文件名 
     * @param filePath 
     *            文件存放路径
     * @return 返回列表内容格式： 
     *  每一行数据都是以对应列的表头为key 内容为value 比如 excel表格为： 
     * =============== 
     *  A | B | C | D 
     * ===|===|===|=== 
     *  1 | 2 | 3 | 4 
     * ---|---|---|---  
     *  a | b | c | d 
     * --------------- 
     * 返回值 map： 
     *   map1:   A:1 B:2 C:3 D:4 
     *   map2:   A:a B:b C:d D:d 
     */ 
    public static List<Map> readExcel(String fileName, String filePath) {
        //    返回值列表
        List<Map> valueList = new ArrayList<Map>();
        String extensionName = getExtensionName(fileName);// 文件拓展名
        //    根据文件拓展名区分文件读取方式
        if (extensionName.equalsIgnoreCase("xls")) {
            valueList = readExcel2003(filePath);
        } else if (extensionName.equalsIgnoreCase("xlsx")) {
            valueList = readExcel2007(filePath);
        }
        // 读取excel完毕后，删除excel文件
        File file = new File (filePath);
        file.delete();
        return valueList;
    }
    
    /**
     * 读取xls拓展名文件(1997-2003 exlce格式为xls),若标题行需加中文备注，则以"#"符号隔开,中文注释必须加在"#"后面,如name#姓名
     * @param filePath 文件路径
     * @return
     */
    @SuppressWarnings("rawtypes")
    public static List<Map> readExcel2003 (String filePath) {
        List<Map> valueList = new ArrayList<Map>();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);// 创建对Excel工作簿文件的引用
            HSSFSheet sheet = workbook.getSheetAt(0);// 在Excel文档中，第一张工作表的缺省索引是0
            int rows = sheet.getPhysicalNumberOfRows();// 获取到Excel文件中第一张工作表的所有行数
            Map<Integer, String> keys = new HashMap<Integer, String>();
            int cells = 0;
            // 遍历行第一行表头，转变Map里的key
            HSSFRow firstRow = sheet.getRow(0);
            if (firstRow != null) {
                // 获取第一行中所有的列
                cells = firstRow.getPhysicalNumberOfCells();
                // 遍历列
                for (int i = 0; i < cells; i++) {
                    // 获取到列的值
                    HSSFCell cell = firstRow.getCell(i);
                    String cellValue = getCellValue(cell);
                    // 
                    keys.put(i, cellValue.split("#")[0]);
                }
            }
            // 遍历行(从第二行开始)
            for (int i = 1; i < rows; i++) {
                // 获取遍历行的数据
                HSSFRow row = sheet.getRow(i);
                if (row != null) {
                    // 准备当前行所储存的map
                    Map<String, Object> val = new HashMap<String, Object>();
                    boolean isValidRow = false;
                    // 遍历列
                    for (int j = 0; j < cells; j++) {
                        HSSFCell cell = row.getCell(j);
                        String cellValue = getCellValue(cell);
                        val.put(keys.get(j), cellValue);
                        if (!isValidRow && cellValue != null && cellValue.trim().length() > 0)
                            isValidRow = true;
                    }
                    // 当遍历行所有的列数据读取完毕时，放入valueList
                    if (isValidRow)
                        valueList.add(val);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return valueList;
    }
    
    /** 
     * 读取xls拓展名文件(读取2007-2013 exlce格式为xlsx),若标题行需加中文备注，则以"#"符号隔开,中文注释必须加在"#"后面,如name#姓名
     * @param filePath 文件路径 
     * @return 
     * @throws java.io.IOException 
     */  
    @SuppressWarnings("rawtypes")  
    public static List<Map> readExcel2007(String filePath){
        List<Map> valueList = new ArrayList<Map>();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            /*
            XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook对象，strPath 传入文件路径
            XSSFSheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
            // 定义 row、cell
            XSSFRow row;*/
            Workbook xwb = create(fis); // 构造 XSSFWorkbook对象，strPath 传入文件路径
            Sheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
            // 定义 row、cell
            Row row;
            // 循环输出表格中的第一行内容 表头
            Map<Integer, String> keys = new HashMap<Integer, String>();
            row = sheet.getRow(0);
            if (row != null) {
                for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
                    // 通过 row.getCell(j).toString() 获取单元格内容，
                    if (row.getCell(j) != null) {
                        if (!row.getCell(j).toString().isEmpty()) {
                            keys.put(j, row.getCell(j).toString().split("#")[0]);
                        }
                    } else {
                        keys.put(j, "K-R1C" + j + "E");
                    }
                }
            }
            // 循环输出表格中的从第二行开始内容
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    boolean isValidRow = false;
                    Map<String, Object> val = new HashMap<String, Object>();
                    for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
                        /*XSSFCell cell = row.getCell(j);*/
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            String cellValue = null;
                            if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
                                } else {
                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                }
                            } else {
                                cellValue = cell.toString();
                            }
                            if (cellValue != null && cellValue.trim().length() <= 0) {
                                cellValue = null;
                            }
                            val.put(keys.get(j), cellValue);
                            if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
                                isValidRow = true;
                            }
                        }
                    }
                    // 第i行所有的列数据读取完毕，放入valuelist
                    if (isValidRow) {
                        valueList.add(val);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return valueList;
    }
    
    /**
     * 文件操作 获取文件拓展名
     * @param filename
     * @return
     */
    public static String getExtensionName (String filename) {
        if(filename != null && (filename.length() > 0)){
            int dot = filename.indexOf(".");
            if (dot > -1 && (filename.length() -1 > dot))
                return filename.substring(dot + 1);
        }
        return filename;
    }
    
    public static final int BUFFER_SIZE = 2 * 1024;
    
    /**
     * copy文件(I/O方式)
     * @param src 源文件
     * @param dst 目标位置
     */
    public static void copy(File src, File dst) {
        InputStream in = null;
        OutputStream out = null;
        try {
            in = new BufferedInputStream(new FileInputStream(src), BUFFER_SIZE);
            out = new BufferedOutputStream(new FileOutputStream(dst), BUFFER_SIZE);
            byte[] buffer = new byte[BUFFER_SIZE];
            int len = 0;
            while ((len = in.read(buffer)) > 0)
                out.write(buffer, 0, len);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
     * copy文件(MultipartFile方式)
     * @param file
     * @param savePath 保存文件路径(在linux上要保存完整路径)
     * @param newname 新的文件名
     * @throws Exception
     */
    public static void copy(MultipartFile file, String savePath, String newname){
        try {
            File targetFile = new File(savePath, newname);
            if (!targetFile.exists())
                targetFile.mkdirs();
            file.transferTo(targetFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 获取转换后的列内容
     * @param cell
     * @return
     */
    public static String getCellValue(HSSFCell cell) {
        DecimalFormat df = new DecimalFormat("#");
        String cellValue = null;
        if (cell == null)
            return null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期格式
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    break;
                }
                cellValue = df.format(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符类型
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空字符
                cellValue = null;
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // bollean类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 错误类型
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
        
        }
        if (cellValue != null && cellValue.trim().length() <= 0) {
            cellValue = null;
        }
        return cellValue;
    }
    
    /**
     * 获取工作簿，主要针对xlsx拓展名的excel
     * @param inp
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook create(InputStream inp) throws IOException,InvalidFormatException {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            return new HSSFWorkbook(inp);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }
    
}