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;
import org.junit.Test;

public class poi_write {

    @Test
    public void testWrite() {
        final String [] str = {"id", "姓名", "性别"};
        //创建Excel工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建Excel工作表
        HSSFSheet sheet = workbook.createSheet();
        //创建第一行
        HSSFRow row = sheet.createRow(0);
        //定义单元格
        HSSFCell cell = null;

        //添加表头，为第一行填充内容
        for (int i = 0; i < str.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(str[i]);
        }

        //追加数据，创建十行，为每行添加数据，每行三个单元格，下表依次0，1，2
        for (int i = 1; i <= 10; i++) {
            HSSFRow rows = sheet.createRow(i);
            HSSFCell cell_id = rows.createCell(0);
            cell_id.setCellValue(i+"");
            HSSFCell cell_name = rows.createCell(1);
            cell_name.setCellValue("name:"+i);
            HSSFCell cell_sex = rows.createCell(2);
            cell_sex.setCellValue("男");
        }

        //文件保存路径
        File file = new File("E:/tmp/poi_text.xls");
        try{
            if(!file.exists()){
                file.createNewFile();
            }
            FileOutputStream fileOut = new FileOutputStream(file);//创建一个文件输出流对象
            workbook.write(fileOut);
            fileOut.close();//关闭输出流对象
        }catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("Excel文件已生成");
    }

    @Test
    public  void testRead() {
        final File file = new File("E:/tmp/poi_text.xls");
        if(file.exists()){
            try {
                //生成workbook
                HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
               // workbook.getSheet("Sheet0");//获得指定的工作表
               // HSSFSheet sheet = workbook.getSheet("Sheet0");
                //workbook.getSheetAt(0);//默认获得第一个工作表
                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) {
                e.printStackTrace();
            }
        }else{
            System.out.println("读取文件不存在");
        }
    }

}