利用Java提取excel内容

1、准备POI.jar

Apache POI官方下载网址：http://poi.apache.org/download.html

下载完后解压，解压后文件夹如图

在IDEA中导入包

2、使用POI包进行excel提取

1、创建文件流和Workbook

方式一：

		//If using HSSFWorkbook or XSSFWorkbook directly,
	  // you should generally go through POIFSFileSystem or OPCPackage,
	// to have full control of the lifecycle 

// HSSFWorkbook, File
POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
....
fs.close();
// HSSFWorkbook, InputStream, needs more memory
POIFSFileSystem fs = new POIFSFileSystem(myInputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
// XSSFWorkbook, File
OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();
// XSSFWorkbook, InputStream, needs more memory
OPCPackage pkg = OPCPackage.open(myInputStream);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();

方式二：

// Use a file
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

// Use an InputStream, needs more memory
Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

方法一需要获得相应读取权限，要注意结束时关闭字节流；(.xls和.xlsx要用不同的方法)
方式二可直接用文件方式获取对应excel文件(不用区分.xls和.xlsx)

2、选择工作表

Sheet sheet = wb.getSheetAt(0);	//0代表excel中第一张表，1为第二张

3、选择行

Row row = sheet.getRow(0);

4、选择单元格

Cell cell = row.getCell(0);

5、获取单元格内容

先判断单元格内容类型，再进行获取

switch(cell.getCellType()){
	case STRING:
   		 System.out.print(cell.getStringCellValue()+"\t");
         break; 
    case NUMERIC:
         System.out.print(cell.getNumericCellValue()+"\t");
         break;
    default:
     	 System.out.print("\t");
 }

3、例子

import org.apache.poi.ss.usermodel.*;
import java.io.File;

public class test_baseinfo {
    public static void main(String[] args) {
        // 文档路径
        String path = "D:\\Person_files\\MyCode\\javacode\\Test_excel\\workbook.xlsx";
        readExcel(path);
    }
    public static void readExcel(String path){
        try{
            //连接工作表
            Workbook book = WorkbookFactory.create(new File(path));
            Sheet sheet = book.getSheetAt(0);
            //遍历表
            for(Row row : sheet){
                for(Cell cell : row){               
                     /判断单元格类型
                    switch(cell.getCellType()){
                        case STRING:
                            System.out.print(cell.getStringCellValue()+"\t");
                            break;
                        case NUMERIC:
                            System.out.print(cell.getNumericCellValue()+"\t");
                            break;
                        default:
                            System.out.print("\t");
                    }
                }
                System.out.println();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }
}