1 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
2 import org.apache.poi.ss.format.CellFormat;
3 import org.apache.poi.ss.usermodel.Cell;
4 import org.apache.poi.ss.usermodel.Row;
5 import org.apache.poi.ss.usermodel.Sheet;
6 import org.apache.poi.ss.usermodel.Workbook;
7 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
8 public List<Bean> getDataFormExcel() {
9 // TODO Auto-generated method stub
10 File f = null;
11 InputStream is = null;
12 List<Bean> allDataBeans = new ArrayList<Bean>();
13 try {
14 f = new File(this.getClass().getResource("/").getPath());
15 String path = f.getAbsolutePath();
16 String newPath = path.replace('\\', '/');
17 if (newPath.contains("apache")) {
18 newPath = newPath.substring(0, newPath.indexOf("/apache"));
19 }
20 String dirName = newPath + "/batchne/"; // 获取自己创建的文件夹路径
21 f = new File(dirName);
22 if (!f.exists()) {
23 f.mkdirs();
24 }
25 String fileNames[] = f.list(); // 获取文件夹下的文件
26 if(fileNames.length == 0){
27 System.out.println("目录下无文件");
28 return allDataBeans;
29 }
30 String resultFileName = "";
31 System.out.println("文件 is " + fileNames[0]);
32 resultFileName = fileNames[0];
33 f=new File(dirName+resultFileName);
34 //获取文件类型是xls还是xlsx
35 String fileType = resultFileName.substring(resultFileName.lastIndexOf(".") + 1);
36 System.out.println(fileType);
37 is = new FileInputStream(f);
38 Workbook xssfWorkbook = null;
39 //兼容xls和xlsx两种格式
40 if (fileType.equals("xls")) {
41 xssfWorkbook = new HSSFWorkbook(is);
42 } else if (fileType.equals("xlsx")) {
43 xssfWorkbook = new XSSFWorkbook(is);
44 }else {
45 System.out.println("表格格式错误");
46 }
47 //获取第一个单元
48 Sheet sheet = xssfWorkbook.getSheetAt(0);
49 // 得到所有的行数
50 int rows = sheet.getLastRowNum();
51 // 越过第一行 它是列名称
52 for (int j = 1; j <=rows; j++) {
53 Bean Bean = Bean();
54 // 得到每一行的单元格的数据
55 Row row = sheet.getRow(j);
56 Bean.setType(String.valueOf(row.getCell(0)));
57 // 存储每一条数据
58 allDataBeans.add(Bean);
59 }
60
61 } catch (Exception e) {
62 // TODO Auto-generated catch block
63 e.printStackTrace();
64 }
65 return allDataBeans;
66 }