POI读取excel_JAVA_编程开发_程序员俱乐部

class="java">
public class ExportStandName {

	/**
	 * 取得标准表中的标准字段放在一个map的key中
	 *
	 * @return standMap
	 */
	private Map<String, String> readStandExcel() {
		Map<String, String> standMap = new HashMap<String, String>();
		String filePath = "path/Stand.xls";
		File file = new File(filePath);
		try {
			FileInputStream fileInputStream = new FileInputStream(file);
			POIFSFileSystem readPoiFileSystem = new POIFSFileSystem(
					fileInputStream);
			HSSFWorkbook workbook = new HSSFWorkbook(readPoiFileSystem);
			HSSFSheet sheet = workbook.getSheetAt(0);
			Iterator<Row> rows = sheet.rowIterator();
			while (rows.hasNext()) {
				Row row = rows.next();
				String name = row.getCell(1).getStringCellValue();
				standMap.put(name, "");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		for (String s : standMap.keySet()) {
			System.out.println(s);
		}
		return standMap;
	}

	/**
	 *
	 *
	 * @param location
	 * @param map
	 * @return map
	 */
	private Map<String, String> readCityExcel(String location,
			Map<String, String> map) {
		Map<String,String>cityMap=new HashMap<String, String>();
		String filePath = "C:/Users/Administrator/Desktop/工作/" + location
				+ ".xls";
		File file = new File(filePath);
		try {
			POIFSFileSystem readPoiFileSystem = new POIFSFileSystem(
					new FileInputStream(file));
			HSSFWorkbook workbook = new HSSFWorkbook(readPoiFileSystem);
			HSSFSheet sheet = workbook.getSheetAt(0);
			Iterator<Row> rows = sheet.rowIterator();
			while (rows.hasNext()) {
				Row row = rows.next();
				String value = row.getCell(0).getStringCellValue();
				System.out.println(">>>>>" + value);
				Cell cell = row.getCell(3);
				if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
					String name = row.getCell(3).getStringCellValue();
					if (name != null && name.length() > 0) {
						String mapValue = cityMap.get(name);
						if (map.containsKey(name)) {
							value += ",";
							mapValue += value;
							cityMap.put(name, mapValue);
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cityMap;
	}

	private void writeExcel(int index,String location, Map<String, String> map) {
		
		String filePath = "C:/Users/Administrator/Desktop/工作/Stand.xls";
		File file = new File(filePath);
		FileOutputStream fileOutputStream = null;
		try {
			POIFSFileSystem readPoiFileSystem = new POIFSFileSystem(new FileInputStream(file));
			HSSFWorkbook readWorkbook = new HSSFWorkbook(readPoiFileSystem);
			HSSFSheet readSheet = readWorkbook.getSheetAt(0);
			Row row1 = readSheet.getRow(0);
			if(row1==null){
				row1=readSheet.createRow(0);
			}
			int rowNum = row1.getLastCellNum();
			HSSFCell pnCell = null;
			if (index >=rowNum) {
				pnCell = (HSSFCell) row1.createCell(index);
			} else {
				pnCell=(HSSFCell)row1.getCell(index);
			}
			pnCell.setCellValue(location);
			for(Row row:readSheet){
				HSSFCell nameCell=(HSSFCell)row.getCell(1);
				if(nameCell!=null){
					String name=nameCell.getStringCellValue();
					HSSFCell valueCell=(HSSFCell)row.getCell(index);
					if(valueCell==null){
						valueCell=(HSSFCell)row.createCell(index);
					}
					String value=map.get(name);
					if(value!=null&&value.length()>0){
						value = value.substring(4, value.length()-1);
					}
					valueCell.setCellValue(value);
				}
			}
			fileOutputStream=new FileOutputStream(file);
			fileOutputStream.flush();
			readWorkbook.write(fileOutputStream);
			fileOutputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	public void doSomething() {
		Map<String, String> map = readStandExcel();
		setStanMap(map);
		Map<String, String> map1=null;
		for (int i = 0; i < locations.length; i++) {
			map1= readCityExcel(locations[i], getStanMap());
			writeExcel(i + 2, locations[i], map1);
		}
	}
	public static void main(String[] args) {
		new ExportStandName().doSomething();
//		String value = "啊21保守党发按时";
//		value = value.substring(0,value.length()-1);
//		System.out.println(value);
	}
}