使用POI写入和读取Excel文件中的数据

一,POI中提供的常用方法

Workbook类提供的方法   
	createSheet("员工信息表") 创建某张表
    getSheetAt(0)	根据工作簿索引获取工作表
    getSheetName(0) 获取第一张表的表名
    getSheet("表名")	获取某个表的工作表
    getNumberOfSheets()   获得工作薄中工作表的个数
	使用for循环获取每张表
    
Sheet接口提供的方法   
    createRow(0)  创建第一列
	getSheetName()   获取Sheet的名称
    getLastRowNum()	   获取最后一行的索引(最后一行行号-1)
    getRow(0)   获取Sheet表中第1行(索引为0)Row对象
       
Row提供的方法
    getLastCellNum() 获取某一行的最后列号,例如E列是第5列
    getCell(0) 获取某一行的第一个单元格

cell提供的方法
	getStringCellValue() 获取单元格数据
	setCellValue() 设置单元格数据
	setCellType() 设置单元格数据类型

二,Excel数据的写入和读取

public class ExcelUtils2 {
    //向Excel总写入数据
    public static void main(String[] args) throws IOException {
        List<Person> list=new ArrayList<>();
        Person person1=new Person(1,"赵一","男",12,new Date());
        Person person2=new Person(2,"杨二","女",22,new Date());
        Person person3=new Person(3,"张三","男",88,new Date());
        Person person4=new Person(4,"李四","女",42,new Date());
        Person person5=new Person(5,"王五","男",28,new Date());
        list.add(person1);
        list.add(person2);
        list.add(person3);
        list.add(person4);
        list.add(person5);

        //创建一个工作簿
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //创建一个工作表
        HSSFSheet sheet = hssfWorkbook.createSheet("员工信息表");
        //创建行,第一行表头
        String[] title={"编号","姓名","性别","年龄","生日"};
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }

        //把list中数据放进去
        for (int i = 0; i < list.size(); i++) {
            HSSFRow row1 = sheet.createRow(i + 1);
            Person person = list.get(i);

            HSSFCell cell1 = row1.createCell(0);
            cell1.setCellValue(person.getId());
            HSSFCell cell2 = row1.createCell(1);
            cell2.setCellValue(person.getName());
            HSSFCell cell3 = row1.createCell(2);
            cell3.setCellValue(person.getSex());
            HSSFCell cell4 = row1.createCell(3);
            cell4.setCellValue(person.getAge());
            HSSFCell cell5 = row1.createCell(4);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年dd月MM日");
            String stringDate = sdf.format(person.getBirthday());
            cell5.setCellValue(stringDate);
        }

        //把数据输出到硬盘中
        File file = new File("G:\\project\\POIexample2.xls");
        OutputStream outputStream=new FileOutputStream(file);
        hssfWorkbook.write(outputStream);
        outputStream.close();

    }

	//将Excel中的数据提取出来
    public static void findBook() throws IOException {
        //获取工作簿
        Workbook workbook = new XSSFWorkbook("G:\\project\\POIexample.xlsx");
        //获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取表中的最后一行的索引
        int lastRowNum = sheet.getLastRowNum();
        //获取单元格列数
        Row row1 = sheet.getRow(0);
        int lastCellNum = row1.getLastCellNum();
        XSSFCell cell = null;

        //遍历表中行
        for (int i = 0; i <= lastRowNum; i++) {
            Row sheetRow = sheet.getRow(i);
            //遍历表中行的单元格
            for (Cell cell1 : sheetRow) {
                //设置单元格的类型
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                String value = cell1.getStringCellValue();
                System.out.println(value);
            }
        }
        //关闭对象
        workbook.close();
    }
    
    //从Excel中提取数据
    public static void readExcel() throws Exception{
	InputStream is = new FileInputStream(new File(fileName));
	Workbook hssfWorkbook = null;
	if (fileName.endsWith("xlsx")){
		hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
	}else if (fileName.endsWith("xls")){
		hssfWorkbook = new HSSFWorkbook(is);//Excel 2003
	}
	// HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	// XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
	User student = null;
	List<User> list = new ArrayList<User>();
	// 循环工作表Sheet
	for (int numSheet = 0; numSheet <hssfWorkbook.getNumberOfSheets(); numSheet++) {
		//HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			continue;
		}
		// 循环行Row
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			//HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			Row hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				student = new User();
				//HSSFCell name = hssfRow.getCell(0);
				//HSSFCell pwd = hssfRow.getCell(1);
				Cell name = hssfRow.getCell(0);
				Cell pwd = hssfRow.getCell(1);
				//这里是自己的逻辑
				student.setUserName(name.toString());
				student.setPassword(pwd.toString());
				list.add(student);
			}
		}
	}
}