JAVA POI操作Excel范例

JAVA POI操作Excel实例

 

/**写入**/

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelWriter {

	public static String outputFile = "D:\\test.xls";

	public static void main(String argv[]) {

		try {

			// 创建新的Excel 工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();

			// 在Excel工作簿中建一工作表，其名为缺省值
			// 如要新建一名为"效益指标"的工作表，其语句为：
			// HSSFSheet sheet = workbook.createSheet("效益指标");
			HSSFSheet sheet = workbook.createSheet();
			// 在索引0的位置创建行（最顶端的行）
			HSSFRow row = sheet.createRow((short) 0);

			HSSFCell empCodeCell = row.createCell((short) 0);
			empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			empCodeCell.setCellValue("员工代码");
			
			HSSFCell empNameCell = row.createCell((short) 1);
			empNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			empNameCell.setCellValue("姓名");

			HSSFCell sexCell = row.createCell((short) 2);
			sexCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			sexCell.setCellValue("性别");
			
			HSSFCell birthdayCell = row.createCell((short) 3);
			birthdayCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			birthdayCell.setCellValue("出生日期");

			HSSFCell orgCodeCell = row.createCell((short) 4);
			orgCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			orgCodeCell.setCellValue("机构代码");

			HSSFCell orgNameCell = row.createCell((short) 5);
			orgNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			orgNameCell.setCellValue("机构名称");
			
			HSSFCell contactTelCell = row.createCell((short) 6);
			contactTelCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			contactTelCell.setCellValue("联系电话");

			HSSFCell zjmCell = row.createCell((short) 7);
			zjmCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			zjmCell.setCellValue("助记码");
			for (int i=1; i<=10; i++) {
				row = sheet.createRow((short) i);
				empCodeCell = row.createCell((short) 0);
				empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				empCodeCell.setCellValue("001_" + i);
				
				empNameCell = row.createCell((short) 1);
				empNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				empNameCell.setCellValue("张三_" + i);

				sexCell = row.createCell((short) 2);
				sexCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				sexCell.setCellValue("性别_" + i);
				
				birthdayCell = row.createCell((short) 3);
				birthdayCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				birthdayCell.setCellValue("出生日期_" + i);

				orgCodeCell = row.createCell((short) 4);
				orgCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				orgCodeCell.setCellValue("机构代码_" + i);

				orgNameCell = row.createCell((short) 5);
				orgNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				orgNameCell.setCellValue("机构名称_" + i);
				
				contactTelCell = row.createCell((short) 6);
				contactTelCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				contactTelCell.setCellValue("联系电话_" + i);

				zjmCell = row.createCell((short) 7);
				zjmCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				zjmCell.setCellValue("助记码_" + i);
				
			} 
			// 新建一输出文件流
			FileOutputStream fOut = new FileOutputStream(outputFile);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			System.out.println("文件生成...");

		} catch (Exception e) {
			System.out.println("已运行 xlCreate() : " + e);
		}
	}
}

/**读取**/

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelReader {

	public static String fileToBeRead = "D:\\test.xls";

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
					fileToBeRead));
			HSSFSheet sheet = workbook.getSheetAt(0);
			int i = 0;
			while (true) {
				HSSFRow row = sheet.getRow(i);
				if (row == null) {
					break;
				}
				HSSFCell cell0 = row.getCell((short)0);
				HSSFCell cell1 = row.getCell((short)1);
				HSSFCell cell2 = row.getCell((short)2);
				HSSFCell cell3 = row.getCell((short)3);
				HSSFCell cell4 = row.getCell((short)4);
				HSSFCell cell5 = row.getCell((short)5);
				HSSFCell cell6 = row.getCell((short)6);
				
				System.out.print(cell0.getStringCellValue());
				System.out.print("," + cell1.getStringCellValue());
				System.out.print("," + cell2.getStringCellValue());
				System.out.print("," + cell3.getStringCellValue());
				System.out.print("," + cell4.getStringCellValue());
				System.out.print("," + cell5.getStringCellValue());
				System.out.println("," + cell6.getStringCellValue());
				i++;
			}
		} catch (Exception e) {
			System.out.println("已运行xlRead() : " + e);
		}
	}

}