Java 实现Excel表的创建与读取

封装ExcelExportUtil.java工具类

public class ExcelExportUtil {
	// 创建文件头信息
	private static Workbook createHeader(String fileName, String[] headers) {
		Workbook wb = new XSSFWorkbook();
		Sheet sheet = wb.createSheet(fileName);
		Row row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(headers[i]);
		}
		return wb;
	}
	
	// 写入文件内容
	public static void exportExecl(String path, String fileName, String[] headers, 
			Class clazz, String[] fieldName, List list) {
		Workbook wb = createHeader(fileName, headers);
		Sheet sheet = wb.getSheetAt(0);
		try {
			// 写入行
			for (int i = 0; i < list.size(); i++) {
				Row row = sheet.createRow(i + 1);
				// 写入列
				for (int j = 0; j < headers.length; j++) {
					Field field = clazz.getDeclaredField(fieldName[j]);
					field.setAccessible(true);
					Cell cell = row.createCell(j);
					cell.setCellValue(field.get(list.get(i)).toString());
					field.setAccessible(false);
				}
			}
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		
		writeFile(wb, path, fileName);
	}
	
	// 导出execl
	private static void writeFile(Workbook wb, String path, String fileName) {
		FileOutputStream os = null;
		try {
			os = new FileOutputStream(new File(path, fileName + ".xlsx"));
			wb.write(os);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if(os != null)
					os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
}

 创建测试

public class test{
	
	public static void main(String[] args) {
		List<Student> list = new ArrayList<>();
		list.add(new Student("张三", "1120班", 99));
		list.add(new Student("李四", "1120班", 95));
		list.add(new Student("王五", "1120班", 100));
		
		String[] headers = new String[]{"姓名", "班级", "分数"};
		String[] fields = new String[]{"name", "classes", "score"};
		
		ExcelExportUtil.exportExecl("E:/", "学生成绩表", headers, Student.class, fields, list);
	}
	
}

读取测试

public class ReadTest {
	
	public static void main(String[] args) throws Exception {
		InputStream is = new FileInputStream("学生成绩表.xlsx");
		Workbook wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);
		// 获取行
		int rouCount = sheet.getPhysicalNumberOfRows();
		
		for (int i = 0; i < rouCount; i++) {
			Row row = sheet.getRow(i);
			int colCount = row.getLastCellNum();
			
			for (int j = 0; j < colCount; j++) {
				// 获取列
				Cell cell = row.getCell(j);
		/**
		 *	CellType		类型		值
		 *	CELL_TYPE_NUMERIC	数值型	         0
		 *	CELL_TYPE_STRING	字符串型	         1
		 *	CELL_TYPE_FORMULA	公式型	        2
		 *	CELL_TYPE_BLANK		空值		3
		 *	CELL_TYPE_BOOLEAN	布尔型	        4
		 *	CELL_TYPE_ERROR		错误		5
		 */
				switch (cell.getCellType()) {
					case Cell.CELL_TYPE_BOOLEAN:
						System.out.print(cell.getBooleanCellValue() + "  ");
						break;
					case Cell.CELL_TYPE_NUMERIC:
						System.out.print(cell.getNumericCellValue() + "  ");
						break;
					case Cell.CELL_TYPE_STRING:
						System.out.print(cell.getStringCellValue() + "  ");
						break;
				}
			}
			System.out.println();
		}
	}
	
}