POI（3）----------Excle表、存储数据与取数据

有个一个需求：将emp表中的数据存入Excle表中

                /*
* POI库与数据库的交互方式；
* 加入有一张表emp
* ID     Name   Deg      salary   dept
* 1201   sun    Manager  10000   IT
* 1202   tim    Reader   4500    Testing
* 1203   Tom    Hr       8000    HR
* 1204   yang   Op       30000   
  * 要求将数据存入Excle表中，并读出来

*/

1、Emp 类

class Emp{
	private Integer id;
	private String name;
	private String deg;
	private Double salary;
	private String dept;
	
	public Emp(){}
	
	public Emp(Integer id, String name, String deg, Double salary, String dept) {
		super();
		this.id = id;
		this.name = name;
		this.deg = deg;
		this.salary = salary;
		this.dept = dept;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDeg() {
		return deg;
	}
	public void setDeg(String deg) {
		this.deg = deg;
	}
	public Double getSalary() {
		return salary;
	}
	public void setSalary(Double salary) {
		this.salary = salary;
	}
	public String getDept() {
		return dept;
	}
	public void setDept(String dept) {
		this.dept = dept;
	}
	@Override
	public String toString() {
		return "Emp [id=" + id + ", name=" + name + ", deg=" + deg
				+ ", salary=" + salary + ", dept=" + dept + "]";
	}
}

2、存数据

List<Emp> list = new ArrayList<Emp>();
		Emp emp1 = new Emp(1201,"sun","Manager",10000.0,"IT");
		Emp emp2 = new Emp(1202,"tim","Reader",4500.0,"testing");
		Emp emp3 = new Emp(1203,"tom","HR",8000.0,"HR");
		Emp emp4 = new Emp(1204,"yang","OP",30000.0,"");
		
		list.add(emp1);
		list.add(emp2);
		list.add(emp3);
		list.add(emp4);
		HSSFRow row = null;
		HSSFCell cell = null;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("emp");
		
		
		//合并标题
		CellRangeAddress region = new CellRangeAddress(0,0,1,5);
		sheet.addMergedRegion(region);
		row = sheet.createRow(0);
		cell = row.createCell(1);
		cell.setCellValue("emp表");
		
		//设置列宽
		sheet.setColumnWidth(0, 3788);
		//设置单元格颜色
		String color = "cbfdee";
		//转为RGB码
		int r = Integer.parseInt(color.substring(0,2),16);
		int g = Integer.parseInt(color.substring(2, 4),16);
		int b = Integer.parseInt(color.substring(4, 6),16);
		
		//自定义cell颜色
		HSSFPalette palette = wb.getCustomPalette();
		palette.setColorAtIndex((short)9, (byte)r, (byte)g, (byte)b);
		//设置样式
		HSSFCellStyle style = wb.createCellStyle();
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor((short)9);
		//居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		
		//设置字体样式
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.PALE_BLUE.index);
		font.setBoldweight((short)100);
		font.setFontHeight((short)300);
		font.setFontName("黑体");
		
		style.setFont(font);
		row = sheet.createRow(1);
		for(int i=1;i<=5;i++){
			cell = row.createCell(i);
			switch(i){
			case 1:cell.setCellValue("ID");break;
			case 2:cell.setCellValue("Name");break;
			case 3:cell.setCellValue("Deg");break;
			case 4:cell.setCellValue("Salary");break;
			case 5:cell.setCellValue("Dept");break;
			}
		
			cell.setCellStyle(style);
		}
		
		
		
		System.out.println(list.size());
		for(int i=2;i<list.size()+2;i++){
			row = sheet.createRow(i);
			for(int j=1;j<=5;j++){
				cell = row.createCell(j);
				switch(j){
				case 1:cell.setCellValue(list.get(i-2).getId());break;
				case 2:cell.setCellValue(list.get(i-2).getName());break;
				case 3:cell.setCellValue(list.get(i-2).getDeg());break;
				case 4:cell.setCellValue(list.get(i-2).getSalary());break;
				case 5:cell.setCellValue(list.get(i-2).getDept());break;
				}
			}
		}
		//将样式加入cell表中
		cell.setCellStyle(style);
		
		FileOutputStream os = new FileOutputStream("emp.xls");
		wb.write(os);		
		
	}

3、取数据

public static void main(String[] args) throws Exception{
		//读数据
		List<Emp> list = new ArrayList<Emp>();
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("emp.xls"));
		HSSFSheet sheet = workbook.getSheetAt(0);
		HSSFRow row = null;
		Emp emp ;
		//遍历每一行
		for(int i=2;i<=sheet.getLastRowNum();i++){
			row = sheet.getRow(i);
			emp = new Emp();
			//遍历本行的每一个表格
			for(int j=1;j<row.getLastCellNum();j++){
				if(getValue(row.getCell(j)) != null){
					switch(j){
					case 1:
						String id = getValue(row.getCell(1));
						int a = id.lastIndexOf(".");
						String b = id.substring(0,a);
						emp.setId(Integer.parseInt(b));
					break;
					case 2:
						emp.setName(getValue(row.getCell(2)));
						break;
					case 3:emp.setDeg(getValue(row.getCell(3)));break;
					case 4:
						emp.setSalary(Double.parseDouble(getValue(row.getCell(4))));
						break;
					case 5:
						String s = getValue(row.getCell(5));
						emp.setDept(s);
					break;
					}
					
				}
			}
			list.add(emp);
		}
		System.out.println("------------------------------");
		//输出集合
		for (Emp e: list) {
			System.out.println(e.getId()+"  "+e.getName()+"  "+e.getDeg()+"  "+e.getSalary()+"  "+e.getDept());
		}	
	}
	//转字符串操作
	private static  String getValue(HSSFCell hssfCell){
		 if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN){    
		      return String.valueOf( hssfCell.getBooleanCellValue());    
		    }else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){    
		      return String.valueOf( hssfCell.getNumericCellValue());    
		    }else if (hssfCell.getCellType() ==hssfCell.CELL_TYPE_STRING){  
		        return String.valueOf(hssfCell.getStringCellValue());  
		    }else{    
		      return String.valueOf( hssfCell.getStringCellValue());    
		    }    
	}