java用poi读写excel实例

最近做项目的时候需要对excel及properties文件中的数据进行读写，数据很多，手工很费时间，所以用程序解决轻松很多！

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeMap;

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

/**
 * 使用poi读取Excel文件到Properties文件
 * @author lzm 
 * 
 */
public class CollectionAndFileOperator {

	/**
	 * main
	 * @param args
	 */
	public static void main(String[] args) {

//		Map<String, String> map = readProToMap("messages_th.properties");
//		writeMapToExcel(map, "test2.xls");
//		addCellToExcelFromMap(map,"test2.xls");
		
		List<String> list=readExcelToList("test2.xls");
		writeListToExcel(list,"list.xls");

	}
	
	/**
	 * 在excel中添加一列
	 * @param map
	 * @param fileName
	 */
	public static void addCellToExcelFromMap(Map<String,String> map,String fileName){
		
		Set<String> set=map.keySet();
		Iterator<String> it=set.iterator();
		
		File file=new File(fileName);
		FileInputStream fis=null;
		HSSFWorkbook hwb=null;
		FileOutputStream fos=null;
		try {
			fis=new FileInputStream(file);
			hwb=new HSSFWorkbook(fis);
			HSSFSheet sheet=hwb.getSheetAt(0);
			int count=1;
			while(it.hasNext()){
				String key=it.next();
				String value=map.get(key);
				HSSFRow row=sheet.getRow(count++);
				HSSFCell cell2=row.createCell((short) 2);
				cell2.setCellValue(new HSSFRichTextString(value));
			}
			fos=new FileOutputStream(file);
			hwb.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("addCellToExcelFromMap完毕！！！");
	}
	
	/**
	 * 将excel中的数据读取到list
	 * @param fileName
	 * @return
	 */
	public static List<String> readExcelToList(String fileName){
		List<String> list=new ArrayList<String>();
		
		File file = new File(fileName);
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			// 读取输入流
			HSSFWorkbook hwb = new HSSFWorkbook(fis);
			// 获得第一个sheet
			HSSFSheet sheet = hwb.getSheetAt(0);
			// 操作每一行
			for (int i = 1; i < sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow(i);
				HSSFCell cell1 = row.getCell((short) 1);

				String value = getStringCellValue(cell1);
				list.add(value);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("readExcelToList完毕！！！");
		
		return list;
		
	}

	/**
	 * 读取properties文件到map
	 * 
	 * @param fileName
	 * @return map
	 */
	public static Map<String, String> readProToMap(String fileName) {
		Map<String, String> map = new TreeMap<String, String>();

		File file = new File(fileName);
		FileInputStream fis = null;

		try {
			fis = new FileInputStream(file);
			Properties pro = new Properties();
			pro.load(fis);
			Set<Object> set = pro.keySet();
			Iterator<Object> it = set.iterator();
			while (it.hasNext()) {
				String key = (String) it.next();
				String value = pro.getProperty(key);
				map.put(key, value);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("readProToMap完毕！！！");
		
		return map;
	}

	/**
	 * 读取excel文件到map
	 * 
	 * @param fileName
	 * @return map
	 */
	public static Map<String, String> readExcelToMap(String fileName) {
		Map<String, String> map = new TreeMap<String, String>();

		File file = new File(fileName);
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			// 读取输入流
			HSSFWorkbook hwb = new HSSFWorkbook(fis);
			// 获得第一个sheet
			HSSFSheet sheet = hwb.getSheetAt(0);
			// 操作每一行
			for (int i = 1; i < sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow(i);
				HSSFCell cell0 = row.getCell((short) 0);
				HSSFCell cell1 = row.getCell((short) 1);

				String key = getStringCellValue(cell0);
				String value = getStringCellValue(cell1);
				map.put(key, value);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("readExcelToMap完毕！！！");
		
		return map;
	}
	
	/**
	 * 将list数据写到excel文件中
	 * @param list
	 * @param fileName
	 */
	public static void writeListToExcel(List<String> list,String fileName){
		File file=new File(fileName);
		FileOutputStream fos=null;
		try {
			fos=new FileOutputStream(file);
			HSSFWorkbook hwb=new HSSFWorkbook();
			HSSFSheet sheet=hwb.createSheet();
			String str="";
			HSSFRow row=null;
			HSSFCell cell=null;
			for(int i=0;i<list.size();i++){
				str=list.get(i);
				row=sheet.createRow(i);
				cell=row.createCell((short)0);
				cell.setCellValue(new HSSFRichTextString(str));
			}
			hwb.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 将map数据写到excel文件中
	 * @param map
	 * @param fileName
	 */
	public static void writeMapToExcel(Map<String, String> map, String fileName) {
		File file = new File(fileName);
		FileOutputStream fos = null;

		try {
			fos = new FileOutputStream(file);
			HSSFWorkbook hwb = new HSSFWorkbook();
			HSSFSheet sheet = hwb.createSheet();

			Set<String> set = map.keySet();
			Iterator<String> it = set.iterator();
			//表头
			HSSFRow row0 = sheet.createRow((short)0 );
			HSSFCell cell00 = row0.createCell((short) 0);
			HSSFCell cell11 = row0.createCell((short) 1);
			cell00.setCellValue(new HSSFRichTextString("Key"));
			cell11.setCellValue(new HSSFRichTextString("Value"));
			//遍历map将数据写到excel
			while (it.hasNext()) {
				String key = it.next();
				String value = map.get(key);
				// 在excel最后一行创建一行
				HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1 );
				// 创建两列
				HSSFCell cell0 = row.createCell((short) 0);
				HSSFCell cell1 = row.createCell((short) 1);
				// 向单元格中添加数据
				cell0.setCellValue(new HSSFRichTextString(key));
				cell1.setCellValue(new HSSFRichTextString(value));
			}
			hwb.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("writeMapToExcel完毕！！！");
	}

	/**
	 * 将map中的数据写到excel中
	 * 
	 * @param map
	 * @param fileName
	 */
	public static void writeMapToPro(Map<String, String> map, String fileName) {
		File file = new File(fileName);

		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(file);
			Set<String> set = map.keySet();
			Iterator<String> it = set.iterator();
			while (it.hasNext()) {
				String key = it.next();
				String value = map.get(key);
				try {
					fos.write((key + "=" + value + "\n").getBytes());
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (fos != null) {
				fos.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("writeMapToPro完毕！！！");
	}

	/**
	 * 获取Excel单元格数据内容为字符串类型的数据
	 * 
	 * @param cell
	 * @return String 单元格数据内容
	 */
	@SuppressWarnings("deprecation")
	private static String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}
}