Excel导入导出

POI创建Excel

1. 引入相关jar包
   (1) commons-io-2.2.jar
   (2) poi-3.11-20141221.jar

2. 新建类PoiExpExcel.java

package com.bong.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.commons.io.FileUtils;
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 PoiExpExcel {
	/**
	 * POI生成Excel文件
	 * @author bongxin
	 * @param args
	 */
	public static void main(String[] args) {
		
		String [] title = {"id","name","sex"};
		
		//创建Excel工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		//创建一个工作表sheet
		HSSFSheet sheet = workbook.createSheet();
		//创建第一行
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		//插入第一行数据：id,name,sex
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
		}
		//追加数据
		for (int i = 1; i < 10; i++) {
			HSSFRow nextRow = sheet.createRow(i);
			HSSFCell cell2 = nextRow.createCell(0);
			cell2.setCellValue("00"+i);
			cell2 = nextRow.createCell(1);
			cell2.setCellValue("bong"+i);
			cell2 = nextRow.createCell(2);
			cell2.setCellValue("男");
		}
		//创建一个文件
		File file = new File("D:/Workspaces/eclipse/poi_test.xls");
		try {
			//将Excel内容存盘
			file.createNewFile();
			FileOutputStream stream = FileUtils.openOutputStream(file);
			workbook.write(stream);
			stream.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

POI解析Excel

1. 新建类PoiExpExcel.java

package com.bong.excel;

import java.io.File;
import java.io.IOException;

import org.apache.commons.io.FileUtils;
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 PoiReadExcel {
	/**
	 * POI解析Excel文件
	 * @author bongxin
	 * @param args
	 */
	public static void main(String[] args) {
		// 需要解析的Excel文件
		File file = new File("D:/Workspaces/eclipse/poi_test.xls");
		try {
			//创建Excel,读取文件内容
			HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
			//获取第一个工作表
			//HSSFSheet sheet = workbook.getSheet("Sheet0");
			//读取默认第一个工作表sheet
			HSSFSheet sheet = workbook.getSheetAt(0);
			//读取工作表中数据
			int firstRowNum = 0;
			//获取sheet中最后一行行号
			int lastRowNum = sheet.getLastRowNum();
			for (int i = firstRowNum; i < lastRowNum; i++) {
				HSSFRow row = sheet.getRow(i);
				//获取当前行最后一个单元格列号
				int lastCellNum = row.getLastCellNum();
				for (int j = 0; j < lastCellNum; j++) {
					HSSFCell cell = row.getCell(j);
					String value = cell.getStringCellValue();
					System.out.print(value+" ");
				}
				System.out.println();
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}