excel读写

最近做个小工具，需要对excel进行读写，以前写过poi读取excel，但是一点印象也没有，这次记录一下，方便以后查阅。

对excel2003读写的api在包org.apache.poi.hssf.usermodel内

HSSFWorkbook：表示一个excel文档

HSSFSheet：表示一个excel页签

HSSFRow：表示一个数据行

HSSFCell：表示一个单元格。

对excel2007的读写是通过另外一套api,在包org.apache.poi.xssf.usermodel内

与之对应的是XSSFWorkbook

XSSFSheet

XSSFRow

XSSFCell四个类。

/**
	 * 读取数据map格式
	 * @param head
	 * @param file
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, String>> getDate(List<String> head, String file) throws Exception{
		List<Map<String, String>> data = new ArrayList<Map<String,String>>();
		String fileType = file.substring(file.lastIndexOf(".")+1).toUpperCase();
		Workbook workBook = null;
		if(EXL2003.equals(fileType)){
			workBook = new HSSFWorkbook(new FileInputStream(file));
		}else if(EXL2007.equals(fileType)){
			workBook = new XSSFWorkbook(new FileInputStream(file));
		}else{
			throw new Exception("文件不被支持");
		}
		Sheet sheet = workBook.getSheetAt(0);
		for(int i = 0, rows = sheet.getLastRowNum()+1; i < rows; i++){
			Row row = sheet.getRow(i);
			Map<String, String> rowInfo = new LinkedHashMap<String, String>();
			for(int j = 0, cols = head.size(); j < cols; j++){
				Cell cell = row.getCell(j);
				rowInfo.put(head.get(j), getCellDate(cell));
			}
			data.add(rowInfo);
		}
		return data;
	}
	
	
	@SuppressWarnings("resource")
	public static void writeDate(List<List<String>> date, String file) throws Exception{
		FileOutputStream fos = new FileOutputStream(file);
		String fileType = file.substring(file.lastIndexOf(".")+1).toUpperCase();
		Workbook workBook = null;
		if(EXL2003.equals(fileType)){
			workBook = new HSSFWorkbook();
		}else if(EXL2007.equals(fileType)){
			workBook = new XSSFWorkbook();
		}else{
			throw new Exception("文件不被支持");
		}
		Sheet sheet = workBook.createSheet("sheet1");
		for(int i = 0, rows = date.size(); i < rows; i++){
			Row row = sheet.createRow(i);
			List<String> rowInfo = date.get(i);
			for(int j = 0, cols = rowInfo.size(); j < cols; j++){
				Cell cell = row.createCell(j);
				cell.setCellValue(rowInfo.get(j));
			}
		}
		workBook.write(fos);
		fos.flush();
		fos.close();
	}
	
	
	public static String getCellDate(Cell cell){
		String value = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_BOOLEAN:{
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		}
		case Cell.CELL_TYPE_ERROR:
			break;
		case Cell.CELL_TYPE_NUMERIC:{
			value = String.valueOf(cell.getNumericCellValue());
			break;
		}
		case Cell.CELL_TYPE_FORMULA:{
			if(HSSFDateUtil.isCellDateFormatted(cell)){
				 Date date = cell.getDateCellValue();
                 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                 value = sdf.format(date);
			}else{
				value = cell.getNumericCellValue()+"";
			}
			break;
		}
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
		default:
			value = "";
		}
		return value;
	}