package com.javaPOI;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

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.ss.usermodel.WorkbookFactory;

public class POITest {

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		POITest.createExcel();
		//POITest.readExcel();
		POITest.writeToExcel();
		Map<String, String> student = new HashMap<String, String>();
		student.put("名字", "小明");
		student.put("性别", "男");
		student.put("住址", "地球");
		student.put("成绩", "良好");
		
		String filePath = "/Users/macbookpro/Desktop/POITest/workbook.xls";
		
		POITest.writeToExcel(filePath, student, "new sheet");
	}

	public static void createExcel()
	{
		//创建工作表
		Workbook wbook = new HSSFWorkbook();
		//创建sheet页
	    Sheet sh = wbook.createSheet("new sheet");
	    //row 行   cell方格
	    Row row1 = sh.createRow(0);
	    //创建cell
	    Cell cell = row1.createCell(0);
	    //设置方格显示
	    cell.setCellValue("姓名");
	    
	    row1.createCell(1).setCellValue("性别");
	    row1.createCell(2).setCellValue("住址");
	    
	    //创建文件
	    FileOutputStream fos = null;
	    try {
			fos = new FileOutputStream("/Users/macbookpro/Desktop/POITest/workbook.xls");	
     		wbook.write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	public static void readExcel()
	{
		InputStream ips = null;
		try {
			//获取文件输入流
			ips = new FileInputStream("/Users/macbookpro/Desktop/POITest/workbook.xls");
			//根据输入流创建workbook对象
			Workbook wbook = WorkbookFactory.create(ips);
			//得到第一个sheet页
			Sheet sh1 = wbook.getSheetAt(0);
			//遍历行
	        for (Row row : sh1) {
				for (Cell cell : row) {
					System.out.print(cell.toString()+"   ");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				ips.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	public static void writeToExcel()
	{
		OutputStream ops = null;
		InputStream ips = null;
		try {
			ips = new FileInputStream("/Users/macbookpro/Desktop/POITest/workbook.xls");
			//根据输出流创建workbook对象
			Workbook wbook = WorkbookFactory.create(ips);
			//获取sheet页
			Sheet sh = wbook.getSheetAt(0);
			for (Row row : sh) {
				for (Cell cell : row) {
					String str = cell.toString();
					//把姓名改成名字
					if (str.equals("姓名")) {
						cell.setCellValue("名字");
					}
				}
				//新增一个字段
				row.createCell(3).setCellValue("成绩");
			}
			ops = new FileOutputStream("/Users/macbookpro/Desktop/POITest/workbook.xls");
			wbook.write(ops);
			
		} catch (Exception e) {

			e.printStackTrace();
		} finally{
			try{
			   ops.close();
			   ips.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
	public static void writeToExcel(String filePath,Map<String, String>student,String sheetName)
	{
		OutputStream ops = null;
		InputStream ips = null;
        
	    try {
			ips = new FileInputStream(new File(filePath));
			Workbook wbook = WorkbookFactory.create(ips);
			Sheet sh = wbook.getSheet(sheetName);
			
			//获得sheet行数  加1
			int shRowCount = sh.getLastRowNum()+1;
			System.out.println(shRowCount);
			//获得表头列数
			int shColumn = sh.getRow(0).getLastCellNum();
			System.out.println(shColumn);
			Row titleRow = sh.getRow(0);
			//新增一行
			Row newRow = sh.createRow(shRowCount);
			
			for (int i = 0; i < shColumn; i++) {
				//获取列名
				String columnStr = titleRow.getCell(i).toString();
				System.out.println(columnStr);
				newRow.createCell(i).setCellValue(student.get(columnStr));
			}
			
			ops = new FileOutputStream(new File(filePath));
			wbook.write(ops);
		} catch (Exception e) {
            e.printStackTrace();
		} finally{
			try{
				ops.close();
				ips.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
}