package cn.com.tengen.excel;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * excel写操作
 * @author lucky
 *
 */
public class ExcelWrite {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    
    public static void main(String[] args) throws IOException {
    	ExcelWrite ew = new ExcelWrite();
    	//excel数据, key是sheet的名字
    	Map<String, List<List<String>>> dataSheets = new HashMap<>();
    	//excel中一个sheet的数据
    	List<List<String>> dataSheet = new ArrayList<>();
    	//excel的一行数据
    	List<String> row = new ArrayList<>();
    	//excel中一个单元的数据
    	row.add("aaaa");
    	row.add("aaaa");
    	row.add("aaaa");
    	dataSheet.add(row);
    	row = new ArrayList<>();
    	row.add("bbbbb");
    	row.add("bbbbb");
    	row.add("bbbbb");
    	dataSheet.add(row);
    	row = new ArrayList<>();
    	row.add("ccccc");       
    	row.add("ccccc");       
    	row.add("ccccc"); 
    	dataSheet.add(row);
    	dataSheets.put("aaa", dataSheet);
    	dataSheets.put("aab", dataSheet);
    	dataSheets.put("aac", dataSheet);
    	ew.writeExcel(dataSheets,"qqqq.xlsx");
    }

    /**
     * 创建一个excel <br>
     * @param dataSheets
     * @param file
     * @throws IOException
     */
    public void writeExcel(Map<String, List<List<String>>> dataSheets,String path) throws IOException {
    	
    	Workbook workBook = null;
    	OutputStream out = null;
    	File file = new File(path);
    	try {
    	workBook = getWriteWorkbok(file);
    	Set<Entry<String, List<List<String>>>> entrySet = dataSheets.entrySet();
    	for (Entry<String, List<List<String>>> dataSheet : entrySet) {
    		//创建sheet
    		Sheet sheet = workBook.createSheet(dataSheet.getKey());
    		List<List<String>> list = dataSheet.getValue();
    		if(list != null) {
    			for (int i = 0; i < list.size(); i++) {
    				//创建行
    				Row row = sheet.createRow(i);
    				List<String> dataRow = list.get(i);
    				if(dataRow != null) {
    					for (int j = 0; j < dataRow.size(); j++) {
    						//创建单元格
    						Cell cell = row.createCell(j);
    						cell.setCellValue(dataRow.get(j));
    					}
    				}
    			}
    		}
    		
		}
    	out =  new FileOutputStream(file);
        workBook.write(out);
    	}finally {
    		if(workBook != null) {
    			workBook.close();
    		}
    		if(out != null) {
    			out.close();
    		}
    	}
    }
    
    
    /**
     * 判断Excel的版本,获取Workbook
     * @param file
     * @return
     * @throws IOException
     */
    private  Workbook getWriteWorkbok(File file) throws IOException{
    	
        Workbook wb = null;
        if(file.getName().endsWith(EXCEL_XLS)){     
            wb = new HSSFWorkbook();
        }else if(file.getName().endsWith(EXCEL_XLSX)){    
            wb = new XSSFWorkbook();
        }
        return wb;
    }

}

package cn.com.tengen.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRead {
	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";



	/**
	 * 读取Excel
	 * @param path
	 * @return
	 * @throws IOException
	 */
	public Map<String, List<List<String>>> readExcel(String path) throws IOException {

		Workbook workbook = getWriteWorkbok(path);
		//excel数据, key是sheet的名字
    	Map<String, List<List<String>>> dataSheets = new HashMap<>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			Sheet sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}
			//excel中一个sheet的数据
	    	List<List<String>> dataSheet = new ArrayList<>();
	    	dataSheets.put(sheet.getSheetName(), dataSheet);
			
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (row != null) {
					//excel的一行数据
			    	List<String> dataRow = new ArrayList<>();
			    	dataSheet.add(dataRow);
					Iterator<Cell> it = row.cellIterator();
					while(it.hasNext()) {
						dataRow.add(getValue(it.next()));
					}
				}
			}
		}
		return dataSheets;
	}

	/**
	 * 获取单元格的值
	 * @param row
	 * @return
	 */
	private String getValue(Cell row) {
		if (row.getCellType() == CellType.BOOLEAN) {
			return String.valueOf(row.getBooleanCellValue());
		} else if (row.getCellType() == CellType.NUMERIC) {
			return String.valueOf(row.getNumericCellValue());
		} else {
			return String.valueOf(row.getStringCellValue());
		}
	}

	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param file
	 * @return
	 * @throws IOException
	 */
	private Workbook getWriteWorkbok(String path) throws IOException {
		if (path == null) {
			throw new IOException("找不到文件，文件路径可能是null");
		}
		InputStream is = new FileInputStream(path);

		if (path.endsWith(EXCEL_XLS)) {
			return new HSSFWorkbook(is);
		}
		if (path.endsWith(EXCEL_XLSX)) {
			return new XSSFWorkbook(is);
		}
		
		is.close();
		throw new IOException(path + " 不是Excel文件");
	}
	
	
	public static void main(String[] args) throws IOException {
		ExcelRead er = new ExcelRead();
		Map<String, List<List<String>>> readExcel = er.readExcel("qqqq.xlsx");
		Set<Entry<String, List<List<String>>>> entrySet = readExcel.entrySet();
		for (Entry<String, List<List<String>>> sheetsMap : entrySet) {
			System.out.println("sheet: "+sheetsMap.getKey());
			for(List<String> sheet:sheetsMap.getValue()) {
				for (String value : sheet) {
					System.out.print(value);
					System.out.print("\t\t");
				}
				System.out.println();
			}
			System.out.println("---------------------------------------------------------");
		}
	}
}