java对Excel读写操作

本人菜鸟,写的不好的地方多多包容,写这些东西,为了以后在遇到要写的需求回来看看.

--------------------------------------------------------------------------------------------------------------

主要是两种实现方法JXL,POI ,个人感觉还是poi好用 虽然jxl比较简单,

方法一: JXL实现

1)依赖的jar 包 jxl.jar

2)代码实现Excel创建:

public static void main(String[] args) {
		// 创建表头
		String[] title = { "id", "name", "age" };
		// 创建excel文件
		File file = new File("g:/jxlExcel.xls");
		try {
			file.createNewFile();
			// 创建工作簿
			WritableWorkbook workbook = Workbook.createWorkbook(file);
			// 创建sheet页
			WritableSheet sheet = workbook.createSheet("Sheet1", 0);
			Label label = null;
			// 第一行设置表头
			for (int i = 0; i < title.length; i++) {
				label = new Label(i, 0, title[i]);
				sheet.addCell(label);
			}
			// 追加数据
			for (int i = 1; i < 5; i++) {
				label = new Label(0, i, i + "");
				sheet.addCell(label);
				label = new Label(1, i, "name" + i);
				sheet.addCell(label);
				label = new Label(2, i, (i*10)+" ");
				sheet.addCell(label);

			}

			workbook.write();
			workbook.close();
			System.out.println("JXL创建表格成功!");

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

3)代码实现Excel读取 :

public static void main(String[] args) {

		try {
			// 创建一个Workbook
			Workbook workbook = Workbook.getWorkbook(new File(
					"g:/jxlExcel.xls"));
			// 获取第一个sheet页
			Sheet sheet = workbook.getSheet(0);

			for (int i = 0; i < sheet.getRows(); i++) {
				for (int j = 0; j < sheet.getColumns(); j++) {
					Cell cell = sheet.getCell(j, i);
					System.out.print(cell.getContents() + " ");
				}
				System.out.println();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

方法二: POI实现
1)依赖jar包 

2)poi实现Excel创建

public static void main(String[] args) {


		// 创建表头
		String[] title = { "id", "name", "sex" };


		// 创建Excel工作簿
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		// 创建一个工作表sheet
		HSSFSheet sheet = hssfWorkbook.createSheet();
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		// 创建行
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
		}
		// 追加数据
		for (int i = 1; i < 10; i++) {
			HSSFRow row1 = sheet.createRow(i);
			HSSFCell cell1 = row1.createCell(0);
			cell1.setCellValue("a" + i);
			cell1 = row1.createCell(1);
			cell1.setCellValue("user" + i);
			cell1 = row1.createCell(2);
			cell1.setCellValue("男" + i);
		}
		File file = new File("g:/poi_excel.xls");


		try {
			file.createNewFile();
			FileOutputStream fileOutputStream = FileUtils
					.openOutputStream(file);
			hssfWorkbook.write(fileOutputStream);
			fileOutputStream.close();


		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}

3)poi实现Excel读取

public static void main(String[] args) {
		// 获取需要的文件
		File file = new File("g:/poi_excel.xls");

		try {
			// 创建excel,读取文件内容
			HSSFWorkbook workbook = new HSSFWorkbook(FileUtils
					.openInputStream(file));
			// 读取sheet页 方法1
			HSSFSheet sheet = workbook.getSheet("Sheet0");
			// 读取sheet页方法2
			// HSSFSheet sheet1=workbook.getSheetAt(0);
			// 得到sheet的最大行数
			int maxRow = sheet.getLastRowNum();

			for (int i = 0; i < maxRow; i++) {
				// 获取一行
				HSSFRow row = sheet.getRow(i);
				// 获取当前行的最后一列
				int maxCell = row.getLastCellNum();
				for (int j = 0; j < maxCell; j++) {
					HSSFCell cell = row.getCell(j);
					String CellValue = cell.getStringCellValue();
					System.out.print(CellValue + "  ");
				}
				System.out.println();

			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}