package com.util.datadriver;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.util.ArrayList;

import java.util.List;

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;

/**

 * 通过POI对Excel中的数据进行基本的操作

 *

 * 需要导入额jar包： poi-3.11-20141221.jar、 poi-ooxml-3.11-20141221.jar、

 *         poi-ooxml-schemas-3.11-20141221.jar、 xmlbeans-2.6.0.jar

 *

 */

public class ExcelController {

    Workbook workbook = null;

    Sheet sheet = null;

    public void setExcelFile(String filePath, String fileName, String sheetName) {

        try {

            FileInputStream fis = new FileInputStream(new File(filePath));

            String type = fileName.substring(fileName.indexOf("."));

            if (type.equals(".xlsx")) {

                workbook = new XSSFWorkbook(fis);

            } else if (type.equals(".xls")) {

                workbook = new HSSFWorkbook(fis);

            }

            sheet = workbook.getSheet(sheetName);

            fis.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    //获取单元格的值

    public String getCellData(int row, int column){

        try {

            //获得Cell单元格对象

            Cell cell = sheet.getRow(row).getCell(column);

            //设置cell返回值的类型

            cell.setCellType(Cell.CELL_TYPE_STRING);

            //获取到cell单元格中的值

            String cellData = cell.getStringCellValue();

            return cellData;

        } catch (Exception e) {

            throw(e);

        }

    }

    //设置单元格的值

    @SuppressWarnings("static-access")

    public void setCellData(String value, int rowCount, int columnCount, String filePath){

        try {

            Row row = sheet.getRow(rowCount);

            Cell cell = row.getCell(columnCount,row.RETURN_BLANK_AS_NULL);

            if(cell == null){

                row.createCell(columnCount).setCellValue(value);

            }else {

                cell.setCellValue(value);

            }

            FileOutputStream fos = new FileOutputStream(new File(filePath));

            workbook.write(fos);

            fos.flush();

            fos.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    //获取Excel的行数

    public int getColCount(Sheet sheet1){

        int firstColCount = sheet1.getFirstRowNum();

        int lastColCount = sheet1.getLastRowNum();

        int sumColCount = lastColCount - firstColCount + 1;

        return sumColCount;

    }

    //获取Excel中每行的数据，并用数组返回每行所有数据,方便与TestNG做数据驱动

    public Object[][] getExcelData(String filePath, String fileName,

            String sheetName) throws Exception {

        int sumRowCount = getColCount(sheet);;

        List<Object[]> list = new ArrayList<Object[]>();

        // 获取每行的行对象,第一行为信息栏，不计入，所以从1开始

        for (int i = 1; i < sumRowCount; i++) {

            Row row = sheet.getRow(i);

            // 获得一行中最后单元格的count

            int lastCellCount = row.getLastCellNum();

            // 定义一个数组来存放cell中值，根据cell的长度来定义数组的长度

            String[] fileds = new String[lastCellCount];

            for (int j = 0; j < lastCellCount; j++) {

                String cellValue = row.getCell(j).getStringCellValue();

                fileds[j] = cellValue;

            }

            list.add(fileds);

        }

        // 定义一个object[][] 的二维数组，存放list中的值

        Object[][] results = new Object[list.size()][];

        // 设置二维数组每行的值，

        for (int a = 0; a < list.size(); a++) {

            results[a] = list.get(a);

        }

        return results;

    }

}