Java POI Excel读取（已验证）

拼搏现实的明天。 2023-02-28 01:54 103阅读 0赞

poi的  
pom文件：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>

Excel表格时间问题：  
1.产品经理发我的文档  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70]  
2.在时间问题后面新建一列：=text(点击需要改变的值，“需要的格式”) 再双击加号，就可以全部生成

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 1]  
3.Ctrl+c 复制内容，右击，选择粘贴数值  
再 数据–>反列–>  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 2]  
重要的是选择：文本  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 3]  
结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 4]

Java的工具类：ExportReadUtills\`

package com.tonul.core.util;
    
    
    import com.tonul.core.entity.ExportRead;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.springframework.test.util.ReflectionTestUtils;
    
    import java.beans.PropertyDescriptor;
    import java.io.*;
    import java.lang.reflect.Method;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * 读取上篇中的xls文件的内容，并打印出来
     *
     * @author Administrator
     */
    public class ExportReadUtills {
        
    
        /**
              * 
              * @author 
              * May 30, 2016 5:39:01 PM
              * @param beginRow
              * @param beanpros
              * @param classPathName
              * @param results
              * @param sheet
              * @return
              */
        public String getDatasByrc(int beginRow,Map<Integer,String> beanpros,String classPathName,List<Object> results,Sheet sheet){
        
            try {
        
                SimpleDateFormat df = new SimpleDateFormat("YYYY-MM-DD");
    			//获取到实体类
                ClassLoader classLoader = ReflectionTestUtils.class.getClassLoader();
                Class clazz = classLoader.loadClass("com.tonul.core.entity.ExportRead");
                Set<Integer> set = beanpros.keySet() ;
                Row row = sheet.getRow(beginRow);
                while(row!=null) {
        
                    Object obj = clazz.newInstance() ;
                    for(Integer key:set){
        
                        String value = "" ;
                        if(row!=null){
        
                            Cell cell = row.getCell(key) ;
                            if(cell!=null){
        
                                int type = cell.getCellType() ;
                                if(type == Cell.CELL_TYPE_STRING){
        
                                    value = cell.getStringCellValue() ;
                                }else if(type==Cell.CELL_TYPE_NUMERIC||type==Cell.CELL_TYPE_FORMULA){
        
                                    // value = String.valueOf(df.format(cell.getDateCellValue()));
    //                                value = String.valueOf(cell.getDateCellValue());
                                   value = String.valueOf(cell.getNumericCellValue());
                                }else if(type==Cell.CELL_TYPE_BOOLEAN){
        
                                    value = String.valueOf(cell.getBooleanCellValue()) ;
                                }
                            }
                        }
                        PropertyDescriptor pd = new PropertyDescriptor(beanpros.get(key),clazz);
                        Method wM = pd.getWriteMethod();
                        wM.invoke(obj, value);
                    }
                    results.add(obj);
                    row = sheet.getRow(++beginRow);
                }
            } catch (Exception e) {
        
                e.printStackTrace();
                return "用于接受结果的bean不存在";
            }
            return null;
        }
    
        public static void main(String[] args) {
        
            ExportReadUtills mer = new ExportReadUtills() ;
            try {
        
                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(new File("D:\\共享农业\\童优乐\\文档\\0712星期日\\1231.xls")));
                Sheet sheet = wb.getSheetAt(0);
                List<Object> results = new ArrayList<Object>() ;
                Map<Integer,String> beanpros = new HashMap<Integer, String>() ;
                beanpros.put(0, "payTime");
                beanpros.put(1, "orderNo");
                beanpros.put(2, "sellingType");
                beanpros.put(3, "orderTime");
                beanpros.put(4, "storesName");
                beanpros.put(5, "name");
                beanpros.put(6, "phone");
                beanpros.put(7, "ordeMoney");
                beanpros.put(8, "vouchersName");
                beanpros.put(9, "vouchersType");
                beanpros.put(10, "orderNumber");
                beanpros.put(11, "orderStates");
                String s = mer.getDatasByrc(0, beanpros, "ExportRead", results, sheet);
                int i=0;
                if(s==null){
        
                    for(Object obj:results){
        
                        ExportRead bean = (ExportRead) obj ;
                        System.out.println(bean);
                        //1、写逻辑代码
                        i++;
                    }
                }
                System.out.println(i);
            } catch (FileNotFoundException e) {
        
                e.printStackTrace();
            } catch (IOException e) {
        
                e.printStackTrace();
            }
        }
    
    }

实体类打印出来了，完成

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70]: /images/20230209/31eb9156ecc4498296353872c4894136.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 1]: /images/20230209/2c33371dd7bd483cab66c3b9da472f61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 2]: /images/20230209/7ff80404b2af46b29b462ee27a1f7b87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 3]: /images/20230209/da8bcc2aafda4c34810d1f1c38de0414.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk3NDQ2Ng_size_16_color_FFFFFF_t_70 4]: /images/20230209/206ae3d97fa04008be2ce7bd55546f27.png