poi 解析excel

1、读excel

package com.jlwang.poi;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.jlwang.model.EmpInfo;

public class ReadDemo {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		List<EmpInfo> emps = readExcel();
		
		if(null != emps && emps.size() >0)
		{
			for (EmpInfo empInfo : emps) 
			{
				System.out.println(empInfo.toString());
				
			}
		}
	}
	public static List<EmpInfo> readExcel() 
	{
		List<EmpInfo> emps = new ArrayList<EmpInfo>();
		Workbook workbook = UtilTools.getWorkbook();
		if(workbook == null)
		{
			System.out.println("workbook is null!");
			return null;
		}
		
		Sheet sheet = workbook.getSheetAt(0);

		for (int i = 1; i < sheet.getLastRowNum(); i++) 
		{
			//获取第一行
			
			Row row = sheet.getRow(i);
			EmpInfo empInfo = new EmpInfo();
			
			empInfo.setName(row.getCell(0).getStringCellValue());
			empInfo.setAge((int)row.getCell(1).getNumericCellValue());
			empInfo.setSex(row.getCell(2).getStringCellValue());
			empInfo.setJob(row.getCell(3).getStringCellValue());
			empInfo.setSal(row.getCell(4).getNumericCellValue());
			empInfo.setAddress(row.getCell(5).getStringCellValue());
			
			emps.add(empInfo);
		}
		return emps;
	}



}

2、写excel

package com.jlwang.poi;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class WriteDemo 
{
	public static final String FILE_PATH = "E:\\workspace\\path\\Book2.xlsx";
	
	private List<String[]> empDatas = new ArrayList<String[]>();

	public static void main(String[] args) 
	{
		WriteDemo writeDemo = new WriteDemo();
		
		writeDemo.init();
		
		List<String[]> tempEmpDatas = writeDemo.getEmpDatas();	
		
		if(null == tempEmpDatas)
		{
			System.out.println("init error!");
			return;
		}
		
//		printDatas(tempEmpDatas);
		
		writeExcle(tempEmpDatas);
		

	}
	
	public static void writeExcle(List<String[]> datas)
	{
		
		System.out.println("datas length=== :"+datas.size());
		Workbook workbook = UtilTools.getWorkbook();
		
		if(workbook == null)
		{
			System.out.println("workbook is null!");
			return;
		}
		Sheet sheet1 = workbook.createSheet("sheetaa");
		
		Row rowTitle = sheet1.createRow(0);
		
		//设置第一行
		for (int i = 0; i < 6; i++) 
		{
			rowTitle.createCell(i).setCellValue(i);	
		}
		
		//将list信息放入excel中
		if(null != datas && datas.size() > 0 )
		{
			for (int i = 0; i < 8; i++) 
			{
				Row row = sheet1.createRow(i + 1);
				
				String[] perRowStrings = datas.get(i);
				if(null == perRowStrings && perRowStrings.length <= 0)
				{
					System.out.println("行数据为空！");
					return;
				}
				for (int j = 0; j < 6; j++) 
				{
					row.createCell(j).setCellValue(perRowStrings[j]);
				}
			}
			
		}

		File file = new File(FILE_PATH);

		if(!file.exists())
		{
			System.out.println("file is not exist!");
			return;
		}
		
		FileOutputStream fos = null;
		try 
		{
			fos = new FileOutputStream(file);
			workbook.write(fos);
			
			System.out.println("write finish!");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (IOException e) 
		{
			e.printStackTrace();
		}finally
		{
			if(null != fos)
			{
				try {
					fos.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		}
	}
	
	//初始化datas数据
	
	public void init()
	{
		
		for (int i = 0; i < 8; i++) 
		{
			String[] rowDataStrings = new String[6];
			
			for (int j = 0; j < rowDataStrings.length; j++) 
			{
				rowDataStrings[j] = "colum"+j;
			}
			
			empDatas.add(rowDataStrings);
			
		}
	}
	
	public static void printDatas(List<String[]> datas)
	{
		for (int i = 0; i < datas.size(); i++) 
		{
			for (int j = 0; j < datas.get(i).length; j++) 
			{
				String itemString = datas.get(i)[j];
				
				System.out.print(itemString+"\t");
			}
			
			System.out.println();
			
		}
	}

	public List<String[]> getEmpDatas() {
		return empDatas;
	}

	public void setEmpDatas(List<String[]> empDatas) {
		this.empDatas = empDatas;
	}

}

3、工具类

package com.jlwang.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class UtilTools 
{
	public static final String FILE_PATH = "E:\\workspace\\path\\Book1.xlsx";
	// 获取workbook

	public static Workbook getWorkbook() {
		File file = new File(FILE_PATH);
		if (!file.exists()) {
			System.out.println("file is not exist!");
			return null;
		}

		Workbook workbook = null;
		try {
			if (FILE_PATH.endsWith(".xlsx")) 
			{

				workbook = new XSSFWorkbook(new FileInputStream(file));

			} else if (FILE_PATH.endsWith(".xls")) 
			{
				workbook = new HSSFWorkbook(new FileInputStream(file));
			}else 
			{
				workbook = null;
				
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return workbook;
	}
	
	//写入的workbook
	public static Workbook getEmptyWorkbook()
	{
		@SuppressWarnings("unused")
		Workbook workbook = null;
		
		if (FILE_PATH.endsWith(".xlsx")) 
		{

			workbook = new XSSFWorkbook();

		} else if (FILE_PATH.endsWith(".xls")) 
		{
			workbook = new HSSFWorkbook();
		}else 
		{
			workbook = null;
			
		}
		
		return null;
	}

}