excel 导入工具类

淡淡的烟草味﹌ 2022-04-05 06:16 295阅读 0赞

我很抱歉的说，这个工具类同样不是我写的，公司大神的作品，拿过老以作借鉴。

下面是代码

package ;

import com.opencsv.CSVReader;

import org.apache.commons.collections.map.HashedMap;  
import org.apache.commons.lang3.StringUtils;  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
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.XSSFWorkbook;

import java.io.\*;  
import java.text.SimpleDateFormat;  
import java.util.\*;

public class ResolveImportObjFile \{  
    /\*\*  
     \*  
     \* @param file 文件  
     \* @param fileName 文件名  
     \* @return 返回表中的数据  
     \*/  
    public static List<List<String>> getListFromFile(File file, String fileName)\{  
        List<List<String>> mapList = new ArrayList<>();  
        try \{  
            /\*\* 如果是excel格式调用ImportTxtFile方法，直接返回结果 \*/  
            mapList = importExcelFile(file,fileName);  
        \}catch (IOException e) \{  
            e.printStackTrace();  
        \}  
          
        return mapList;  
    \}  
    /\*\*  
     \*  
     \* @param file 文件  
     \* @param fileName 文件名  
     \* @return 返回表中的数据  
     \*/  
    public static List<Map<String,Object>> getMapFromFile(File file, String fileName)\{  
        List<Map<String, Object>> mapList = new ArrayList<>();  
        try \{  
            /\*\* 如果是excel格式调用ImportTxtFile方法，直接返回结果 \*/  
            mapList = importExcelFileReturnMapList(file,fileName);  
        \}catch (IOException e) \{  
            e.printStackTrace();  
        \}  
          
        return mapList;  
    \}  
      
    /\*\*  
     \* 读取Excel的内容，第一维数组存储的是多少行，二维数组存储的每一行是多少列。  
   
     \* 兼容Excel 2003（后缀名：xls）及 2007（后缀名：xlsx）的文件，同时还支持读取csv格式的文件  
     \*  
     \* @param file  
     \*            文件  
     \* @return 这里返回的是List<List<String>> 格式  
     \* @throws Exception  
     \*/  
    private static List<List<String>> importExcelFile(File file,String fileName) throws IOException \{  
        List<List<String>> list = new ArrayList<>();  
        String\[\]\[\] returnArray;  
        /\*\* 如果是CSV格式调用ImportCsvFile方法，直接返回结果 \*/  
        if (isCsv(fileName)) \{  
            returnArray = importCsvFile(file,fileName,0);  
        \}else\{  
            returnArray = importXlsAndXlsxFile(file,fileName,0);  
        \}  
          
        if(null!=returnArray && returnArray.length>0)\{  
            for (int i = 0; i < returnArray.length; i++) \{  
                String\[\] newStr=Arrays.copyOf(returnArray\[i\], returnArray\[i\].length-1);  
                list.add(Arrays.asList(newStr));  
            \}  
        \}  
        return list;  
    \}  
      
    /\*\*  
     \* 读取Excel的内容，第一维数组存储的是多少行，二维数组存储的每一行是多少列。  
   
     \* 兼容Excel 2003（后缀名：xls）及 2007（后缀名：xlsx）的文件，同时还支持读取csv格式的文件  
     \*  
     \* @param file  
     \*            文件  
     \* @return  这里返回的是List<Map<String,Object>> 格式  
     \* @throws Exception  
     \*/  
    private static List<Map<String,Object>> importExcelFileReturnMapList(File file,String fileName) throws IOException \{  
        List<Map<String,Object>> mapList = new ArrayList<>();  
        String\[\]\[\] returnArray;  
        /\*\* 如果是CSV格式调用ImportCsvFile方法，直接返回结果 \*/  
        if (isCsv(fileName)) \{  
            returnArray = importCsvFile(file,fileName,0);  
        \}else\{  
            returnArray = importXlsAndXlsxFile(file,fileName,0);  
        \}  
          
        if(null!=returnArray && returnArray.length>0)\{

//从一开始，0是excel表头，我导入的excel一般是只有一行表头  
            for (int i = 1; i < returnArray.length; i++) \{  
                String\[\] newStr=Arrays.copyOf(returnArray\[i\], returnArray\[i\].length-1);  
                Map<String,Object> map = new HashedMap();  
                for(int j=0;j<returnArray\[i\].length;j++)\{  
                    map.put(returnArray\[0\]\[j\], returnArray\[i\]\[j\]);  
                \}  
                mapList.add(map);  
            \}  
        \}  
        return mapList;  
    \}  
      
    /\*\*  
     \* csv格式的文件，第一维数组存储的是多少行，二维数组存储的每一行是多少列。  
     \*  
     \* @param file  
     \*            文件  
     \* @param ignoreRows  
     \*            读取数据忽略的行数，例：行头不需要读入，忽略的行数为1，那么将ignoreRows设为1即可  
     \* @return  
     \* @throws Exception  
     \*/  
    public static String\[\]\[\] importCsvFile(File file, String fileName, int ignoreRows) throws IOException \{  
   
        /\*\* 验证文件是否合法 \*/  
        if (!isCsv(fileName)) \{  
            throw new RuntimeException("不是csv格式的文件");  
        \}  
        InputStreamReader inputStream = null;  
        CSVReader reader = null;  
        List<String\[\]> result = new ArrayList<String\[\]>();  
        int rowSize = 0;  
        try \{  
            inputStream = new InputStreamReader(new FileInputStream(file), "GBK");  
            reader = new CSVReader(inputStream);  
            String\[\] nextRow = null;  
            int i = 0;  
            while ((nextRow = reader.readNext()) != null) \{  
                ++i;  
                if (i <= ignoreRows) \{  
                    continue;  
                \}  
                if (nextRow == null || nextRow.length <= 0) \{  
                    continue;  
                \}  
                int tempRowSize = nextRow.length;  
                if (tempRowSize > rowSize) \{  
                    rowSize = tempRowSize;  
                \}  
                result.add(nextRow);  
            \}  
   
            reader.close();  
            inputStream.close();  
        \} catch (IOException e) \{  
            throw e;  
        \} finally \{  
            if (inputStream != null) \{  
                try \{  
                    inputStream.close();  
                \} catch (IOException e) \{  
                    inputStream = null;  
                    e.printStackTrace();  
                \}  
            \}  
            if (reader != null) \{  
                try \{  
                    reader.close();  
                \} catch (IOException e) \{  
                    reader = null;  
                    e.printStackTrace();  
                \}  
            \}  
        \}  
   
        String\[\]\[\] returnArray = new String\[result.size()\]\[rowSize\];  
        for (int i = 0; i < returnArray.length; i++) \{  
            returnArray\[i\] = result.get(i);  
        \}  
        return returnArray;  
    \}  
      
    /\*\*  
     \* xls、xlsx格式的文件，第一维数组存储的是多少行，二维数组存储的每一行是多少列。  
     \*  
     \* @param file  
     \*            文件  
     \* @param ignoreRows  
     \*            读取数据忽略的行数，例：行头不需要读入，忽略的行数为1，那么将ignoreRows设为1即可  
     \* @return  
     \* @throws Exception  
     \*/  
    public static String\[\]\[\] importXlsAndXlsxFile(File file, String fileName, int ignoreRows) throws IOException \{  
        List<String\[\]> result = new ArrayList<String\[\]>();  
          
        Workbook workbook = null;  
        InputStream inputStream = null;  
        int rowSize = 0;  
   
        try \{  
            inputStream = new FileInputStream(file);  
            if (isExcel2003(fileName)) \{  
                workbook = new HSSFWorkbook(inputStream);  
            \} else \{  
                workbook = new XSSFWorkbook(inputStream);  
            \}  
   
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) \{  
                Sheet sheet = workbook.getSheetAt(sheetIndex);  
                for (int rowIndex = ignoreRows; rowIndex <= sheet.getLastRowNum(); rowIndex++) \{  
                    Row row = sheet.getRow(rowIndex);  
                    if (row == null||isEmpty(row)) \{  
                        continue;  
                    \}

//这里有个加一，会导致结果最后一列会用空列，我不知道为什么要加一，我尝试着去掉，然后会报错。                                      // 所以，最后处理结果集的时候，我选择直接删去最后一列  
                    int tempRowSize = row.getLastCellNum() + 1;  
                    if (tempRowSize > rowSize) \{  
                        rowSize = tempRowSize;  
                    \}  
                    String\[\] values = new String\[rowSize\];  
                    Arrays.fill(values, "");  
                    boolean hasValue = false;  
                    for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) \{  
                        String value = "";  
                        Cell cell = row.getCell(columnIndex);  
                        if (cell != null) \{  
                            cell.setCellType(1);  
                            switch (cell.getCellType()) \{  
                            case HSSFCell.CELL\_TYPE\_STRING:  
                                value = cell.getStringCellValue();  
                                break;  
                            case HSSFCell.CELL\_TYPE\_NUMERIC:  
                                if (HSSFDateUtil.isCellDateFormatted(cell)) \{  
                                    Date date = cell.getDateCellValue();  
                                    if (date != null) \{  
                                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);  
                                    \} else \{  
                                        value = "";  
                                    \}  
                                \} else \{  
//                                    value = new DecimalFormat("0").format(cell.getNumericCellValue());  
                                    value = cell.getNumericCellValue() + "";  
                                \}  
                                break;  
                            case HSSFCell.CELL\_TYPE\_FORMULA:  
                                // 导入时如果为公式生成的数据则无值  
                                if (!cell.getStringCellValue().equals("")) \{  
                                    value = cell.getStringCellValue();  
                                \} else \{  
                                    value = cell.getNumericCellValue() + "";  
                                \}  
                                break;  
                            case HSSFCell.CELL\_TYPE\_BLANK:  
                                break;  
                            case HSSFCell.CELL\_TYPE\_ERROR:  
                                value = "";  
                                break;  
                            case HSSFCell.CELL\_TYPE\_BOOLEAN:  
                                value = (cell.getBooleanCellValue() == true ? "Y" : "N");  
                                break;  
                            default:  
                                value = "";  
                            \}  
                        \}  
                        if (columnIndex == 0 && value.trim().equals("")) \{  
                            continue;  
                        \}  
                        values\[columnIndex\] = rightTrim(value);  
                        hasValue = true;  
                    \}  
   
                    if (hasValue) \{  
                        result.add(values);  
                    \}  
                \}  
            \}  
   
            inputStream.close();

// opencsv 这个jar包低版本会有close方法，高版本没有  
//            workbook.close();  
        \} catch (IOException e) \{  
            throw e;  
        \} finally \{  
            if (inputStream != null) \{  
                try \{  
                    inputStream.close();  
                \} catch (IOException e) \{  
                    inputStream = null;  
                    e.printStackTrace();  
                \}  
            \}

// opencsv 这个jar包低版本会有close方法，高版本没有  
//            if (workbook != null) \{  
//                try \{  
//                    workbook.close();  
//                \} catch (IOException e) \{  
//                    workbook = null;  
//                    e.printStackTrace();  
//                \}  
//            \}  
        \}  
   
        String\[\]\[\] returnArray = new String\[result.size()\]\[rowSize\];  
        for (int i = 0; i < returnArray.length; i++) \{  
            returnArray\[i\] = result.get(i);  
        \}  
        return returnArray;  
    \}  
      
    private static boolean isEmpty(Row row) \{  
        Iterator<Cell> iterator = row.cellIterator();  
        while(iterator.hasNext())\{  
            Cell cell = iterator.next();  
            cell.setCellType(1);  
            if(!StringUtils.isBlank(cell.getStringCellValue()))\{  
                return false;  
            \}  
        \}  
        return true;  
    \}

/\*\*  
     \* 验证excel文件  
     \*  
     \* @param fileName  
          \*            文件名称  
     \* @return boolean  
     \*/  
    public static boolean validateExcel(String fileName) \{  
        /\*\* 检查文件名是否为空或者是否是Excel格式的文件 \*/  
        return fileName != null && isExcel2003(fileName) || isExcel2007(fileName);  
    \}  
   
   
    /\*\*  
     \* 是否是2003的excel，返回true是2003  
     \*  
     \* @param fileName  
     \*            文件名称  
     \* @return boolean  
     \*/  
    public static boolean isExcel2003(String fileName) \{  
        return fileName.matches("^.+\\\\.(?i)(xls)$");  
    \}  
   
    /\*\*  
     \* 是否是2007的excel，返回true是2007  
     \*  
     \* @param fileName  
     \*            文件名称  
     \* @return boolean  
     \*/  
    public static boolean isExcel2007(String fileName) \{  
        return fileName.matches("^.+\\\\.(?i)(xlsx)$");  
    \}  
      
    /\*\*  
     \* 是否是csv格式的文件，返回true是csv格式  
     \*  
     \* @param fileName  
     \*            文件名称  
     \* @return boolean  
     \*/  
    public static boolean isCsv(String fileName) \{  
        return fileName.matches("^.+\\\\.(?i)(csv)$");  
    \}  
   
    /\*\*  
     \* 去掉字符串右边的空格  
     \*  
     \* @param str  
     \*            要处理的字符串  
     \* @return 处理后的字符串  
     \*/  
    private static String rightTrim(String str) \{  
        if (str == null) \{  
            return "";  
        \}  
        int length = str.length();  
        for (int i = length - 1; i >= 0; i--) \{  
            if (str.charAt(i) != 0x20) \{  
                break;  
            \}  
            length--;  
        \}  
        return str.substring(0, length);  
    \}  
\}

需要引入的jar包

<dependency>  
            <groupId>com.opencsv</groupId>  
            <artifactId>opencsv</artifactId>  
            <version>版本号</version>  
        </dependency>

导入的excel 我自己也写过，由于没有对excel空值的处理，导致数据库存了许多的 null 字符串，StringUtil.isNotBlank（）方法没有判断出来，debug才发现excel的空值转换成了字符串 "null"