Java 读取本地 Excel 文件

比眉伴天荒 2022-12-03 12:49 281阅读 0赞

## GAV 坐标 ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.1</version>
    </dependency>

## 测试类 ##

*  Order.class

package cn.me.es;
    
    import lombok.Data;
    
    /** * @author yanyg * @since 2020/9/3 */
    @Data
    public class Order { 
        String wayBillNo;
        String name;
        String phone;
        String province;
        String city;
        String county;
        String address;
    
    }

*  ReadExcelUtils.class

package cn.me.es;
    
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.ParseException;
    import java.util.ArrayList;
    import java.util.List;
    
    
    public class ReadExcelUtils { 
    
        //XSSF -- 提供读写Microsoft Excel OOXML格式档案的功能
        //XSSFWorkbook:是操作Excel2007（以上）的版本，扩展名是.xlsx
        public static List<Order> read2007Xlsx(InputStream in) throws IOException, InvalidFormatException, ParseException { 
    
            List<Order> list = new ArrayList<>();
            XSSFWorkbook xWorkbook = new XSSFWorkbook(in);
            // Read the Sheet
            XSSFSheet xssfSheet = xWorkbook.getSheetAt(0);
    
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { 
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) { 
                    Order order =new Order();
                    // 单元格从 0 开始
                    order.setWayBillNo(xssfRow.getCell(0).getStringCellValue());
                    order.setName(xssfRow.getCell(1).getStringCellValue());
                    order.setPhone(xssfRow.getCell(2).getStringCellValue());
                    order.setProvince(xssfRow.getCell(3).getStringCellValue());
                    order.setCity(xssfRow.getCell(4).getStringCellValue());
                    order.setCounty(xssfRow.getCell(5).getStringCellValue());
                    order.setAddress(xssfRow.getCell(6).getStringCellValue());
                    list.add(order);
    
                }
    
            }
    
            xWorkbook.close();
            return list;
        }
    
        public static void main(String[] args) throws InvalidFormatException, IOException, ParseException { 
            String fullFileName = "D:\\41473.xlsx";
            InputStream is = new FileInputStream(fullFileName);
            List<Order> list = read2007Xlsx(is);
            System.out.println("size==>" + list.size());
    
            // 获取resource目录下的 41473.xlsx 文件:
    // String resource = "41473.xlsx";
    // ClassPathResource classPathResource = new ClassPathResource(resource);
    // List<Order> list = read2007Xlsx(classPathResource.getInputStream());
    // System.out.println("size==>" + list.size());
        }
    
    }