java解析导入excel表格转为实体类javabean

本是古典何须时尚 2024-04-17 19:01 43阅读 0赞

自制一个小工具，excel的表头为属性名，下面为对应的属性值，只需要填需要set的属性，不一定是每一个属性都写上。然后导入，后台java将其转换成java对象list的工具；代码复制即可用。调用handelExcel方法传入，excle名称，mutifile文件，和实体类Class，即可返回对象实体类的list。

excel：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMTI5ODc1_size_16_color_FFFFFF_t_70][]

实体类：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMTI5ODc1_size_16_color_FFFFFF_t_70 1][]

util工具类：

package com.srf.Utils;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.beans.PropertyDescriptor;
    import java.io.*;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Date;
    import java.util.List;
    
    public class ExcelUitl {
    
        /**
         * 导入Excel转为实体类
         * @param fileName
         * @param file
         * @param t
         * @return
         * @throws Exception
         */
        public  static <T> List handelExcel(String fileName,MultipartFile file,Class<T> t)throws Exception{
    
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                throw new Exception();
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            Sheet sheet = wb.getSheetAt(0);
    
            //获取表头列
            Row row = sheet.getRow(0);
            //表头字段放入titleList中
            List<String> titleList = new ArrayList<String>();
            for (int i = 0; i < row.getLastCellNum(); i++){
                titleList.add(row.getCell(i).getStringCellValue());
            }
            //获得该类的所有属性
            Field[] fields = t.getDeclaredFields();
    
            List<T> list = new ArrayList<T>();
    
            for (int r = 1; r <= sheet.getLastRowNum(); r++){
                T tIns = t.newInstance();
                boolean nullrow=false;
    
                for(Field field:fields){
    
                    PropertyDescriptor pd = new PropertyDescriptor(field.getName(),t);
                    if (!"id".equals(field.getName())) {
                        int num = titleList.indexOf(field.getName());
                        if(num != -1) {
                            //获得set方法
                            Method method = pd.getWriteMethod();
                            System.out.println(num);
                            System.out.println(r);
                            Row nowrow = sheet.getRow(r);
                            if (nowrow !=null){
                                Cell cell = nowrow.getCell(num);
                                if (cell != null) {
                                    cell.setCellType(CellType.STRING);
                                    method.invoke(tIns, cell.getStringCellValue());
                                } else {
                                    method.invoke(tIns, "");
                                }
                            } else {
                                nullrow=true;
                                break;
                            }
    
                        }
                    }
                }
                if (nullrow == true){
                    break;
                }
                list.add(tIns);
            }
    
            return list;
        }
    
        /**
         * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
         *
         * @param file
         *            读取数据的源Excel
         * @param ignoreRows
         *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
         * @return 读出的Excel中数据的内容
         * @throws FileNotFoundException
         * @throws IOException
         */
        public static String[][] getData(File file, int ignoreRows)
                throws FileNotFoundException, IOException {
            List<String[]> result = new ArrayList<String[]>();
            int rowSize = 0;
            BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                    file));
            // 打开HSSFWorkbook
            POIFSFileSystem fs = new POIFSFileSystem(in);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
                HSSFSheet st = wb.getSheetAt(sheetIndex);
                // 第一行为标题，不取
                for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                    HSSFRow row = st.getRow(rowIndex);
                    if (row == null) {
                        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 = row.getCell(columnIndex);
                        if (cell != null) {
                            // 注意：一定要设成这个，否则可能会出现乱码
                            // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                            switch (cell.getCellType()) {
                                case STRING:
                                    value = cell.getStringCellValue();
                                    break;
                                case 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());
                                    }
                                    break;
                                case FORMULA:
                                    // 导入时如果为公式生成的数据则无值
                                    if (!cell.getStringCellValue().equals("")) {
                                        value = cell.getStringCellValue();
                                    } else {
                                        value = cell.getNumericCellValue() + "";
                                    }
                                    break;
                                case BLANK:
                                    break;
                                case ERROR:
                                    value = "";
                                    break;
                                case BOOLEAN:
                                    value = (cell.getBooleanCellValue() == true ? "Y"
                                            : "N");
                                    break;
                                default:
                                    value = "";
                            }
                        }
                        if (columnIndex == 0 && value.trim().equals("")) {
                            break;
                        }
                        values[columnIndex] = rightTrim(value);
                        hasValue = true;
                    }
    
                    if (hasValue) {
                        result.add(values);
                    }
                }
            }
            in.close();
            String[][] returnArray = new String[result.size()][rowSize];
            for (int i = 0; i < returnArray.length; i++) {
                returnArray[i] = (String[]) result.get(i);
            }
            return returnArray;
        }
    
        /**
         * 去掉字符串右边的空格
         *
         * @param str
         *            要处理的字符串
         * @return 处理后的字符串
         */
        public 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);
        }
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMTI5ODc1_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/356a5db7396a42e9b920a70a763f84a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMTI5ODc1_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ea16e19716534b86a7e445c8647bc660.png