java解析excel文件的方法

时间:2022-09-23 14:37:03

建立工程前需要导入POI包。POI相关jar包下载地址:http://poi.apache.org/download.html

1.解析.xlsx后缀名的的EXCEL文件:

java" id="highlighter_381215">
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
package com.shuai.hello; 
  
import java.io.FileInputStream; 
import java.io.IOException; 
import java.io.InputStream; 
  
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.xssf.usermodel.XSSFCell; 
import org.apache.poi.xssf.usermodel.XSSFRow; 
import org.apache.poi.xssf.usermodel.XSSFSheet; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
  
public class ReadExcel { 
  public static void main(String[] args) throws IOException { 
      
    //File file = new File("C:/Users.xlsx"); 
    InputStream stream = new FileInputStream("C:/Users.xlsx"); 
  
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(stream); 
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); 
  
    int rowstart = xssfSheet.getFirstRowNum(); 
    int rowEnd = xssfSheet.getLastRowNum(); 
    for(int i=rowstart;i<=rowEnd;i++) 
    
      XSSFRow row = xssfSheet.getRow(i); 
      if(null == row) continue
      int cellStart = row.getFirstCellNum(); 
      int cellEnd = row.getLastCellNum(); 
  
      for(int k=cellStart;k<=cellEnd;k++) 
      
        XSSFCell cell = row.getCell(k); 
        if(null==cell) continue
  
  
        switch (cell.getCellType()) 
        
          case HSSFCell.CELL_TYPE_NUMERIC: // 数字 
            System.out.print(cell.getNumericCellValue() 
                + "\t"); 
            break
          case HSSFCell.CELL_TYPE_STRING: // 字符串 
            System.out.print(cell.getStringCellValue() 
                + "\t"); 
            break
          case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean 
            System.out.println(cell.getBooleanCellValue() 
                + "\t"); 
            break
          case HSSFCell.CELL_TYPE_FORMULA: // 公式 
            System.out.print(cell.getCellFormula() + "\t"); 
            break
          case HSSFCell.CELL_TYPE_BLANK: // 空值 
            System.out.println(" "); 
            break
          case HSSFCell.CELL_TYPE_ERROR: // 故障 
            System.out.println(" "); 
            break
          default
            System.out.print("未知类型  "); 
            break
        
  
      
      System.out.print("\n"); 
    
  
 
/*String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
InputStream stream = new FileInputStream(filePath);
Workbook wb = null;
if (fileType.equals("xls")) {
 wb = new HSSFWorkbook(stream);
} else if (fileType.equals("xlsx")) {
 wb = new XSSFWorkbook(stream);
} else {
 System.out.println("您输入的excel格式不正确");
}*/

2.解析后缀为.xls的EXCEL文件:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package com.shuai.hello; 
  
import java.io.File; 
import java.io.FileInputStream; 
import java.io.IOException; 
  
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFRow; 
import org.apache.poi.hssf.usermodel.HSSFSheet; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.poifs.filesystem.POIFSFileSystem; 
  
public class ReadXls { 
  public static void main(String[] args) throws IOException, IOException { 
    File file = new File("C:/Users/dengta/Desktop/ok1.xls"); 
    POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file)); 
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); 
    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); 
  
    int rowstart = hssfSheet.getFirstRowNum(); 
    int rowEnd = hssfSheet.getLastRowNum(); 
    for(int i=rowstart;i<=rowEnd;i++) 
    
      HSSFRow row = hssfSheet.getRow(i); 
      if(null == row) continue
      int cellStart = row.getFirstCellNum(); 
      int cellEnd = row.getLastCellNum(); 
  
      for(int k=cellStart;k<=cellEnd;k++) 
      
        HSSFCell cell = row.getCell(k); 
        if(null==cell) continue
        //System.out.print("" + k + " "); 
        //System.out.print("type:"+cell.getCellType()); 
  
        switch (cell.getCellType()) 
        
          case HSSFCell.CELL_TYPE_NUMERIC: // 数字 
                  System.out.print(cell.getNumericCellValue() 
                + "  "); 
            break
          case HSSFCell.CELL_TYPE_STRING: // 字符串 
            System.out.print(cell.getStringCellValue() 
                + "  "); 
            break
          case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean 
            System.out.println(cell.getBooleanCellValue() 
                + "  "); 
            break
          case HSSFCell.CELL_TYPE_FORMULA: // 公式 
            System.out.print(cell.getCellFormula() + "  "); 
            break
          case HSSFCell.CELL_TYPE_BLANK: // 空值 
            System.out.println(" "); 
            break
          case HSSFCell.CELL_TYPE_ERROR: // 故障 
            System.out.println(" "); 
            break
          default
            System.out.print("未知类型  "); 
            break
        
  
      
      System.out.print("\n"); 
    
  
}

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://blog.csdn.net/wanghjbuf/article/details/54809134?locationNum=4&fps=1