Excel OI（Java操作Ecxcel）

解决办法：poi （在pom.xml中添加依赖）

<!-- poi to excel -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.10.1</version>
      <exclusions>
      	<exclusion>
      		<groupId>xml-apis</groupId>
      		<artifactId>xml-apis</artifactId>
      	</exclusion>
      	</exclusions>
    </dependency> 
           

XSSFWorkbook 类 解决xlsx HSSFWorkbook 类 解决xls XSSFWorkbook 类/HSSFWorkbook 类 源码：public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<XSSFSheet>

#构造方法 public XSSFWorkbook() public XSSFWorkbook(InputStream is) throws IOException #sheet操作 public XSSFSheet createSheet() #创建一个Sheet public XSSFSheet createSheet(String sheetname)

public XSSFSheet getSheet(String name) public XSSFSheet getSheetAt(int index)

public int getSheetIndex(String name) public int getSheetIndex(Sheet sheet) public String getSheetName(int sheetIx)

public XSSFSheet cloneSheet(int sheetNum)  复制sheet public void removeSheetAt(int index)  删除sheet

private String getUniqueSheetName(String srcName) public XSSFName getName(String name) public XSSFName getNameAt(int nameIndex) public int getNameIndex(String name) public void setSheetName(int sheetIndex, String sheetname)

XSSFSheet 类 源码：public class XSSFSheet extends POIXMLDocumentPart implements Sheet （实现Sheet类抽象方法+自身方法）

protected XSSFSheet()

Sheet 类

源码：public interface Sheet extends Iterable<Row>

Row createRow(int rownum)  void removeRow(Row row); Row getRow(int rownum);

int getFirstRowNum(); int getLastRowNum();

void setColumnHidden(int columnIndex, boolean hidden); boolean isColumnHidden(int columnIndex);

void setColumnWidth(int columnIndex, int width); int getColumnWidth(int columnIndex);

short getDefaultRowHeight();

public CellStyle getColumnStyle(int column); Workbook getWorkbook(); String getSheetName();

Row 类 源码：public interface Row extends Iterable<Cell> Cell createCell(int column); Cell createCell(int column, int type); void removeCell(Cell cell);

void setRowNum(int rowNum); int getRowNum();

Cell getCell(int cellnum); Cell getCell(int cellnum, MissingCellPolicy policy); short getFirstCellNum(); short getLastCellNum();

short getHeight(); void setHeight(short height); CellStyle getRowStyle();

Cell 类

源码：public interface Cell

public final static int CELL_TYPE_NUMERIC = 0; public final static int CELL_TYPE_STRING = 1; public final static int CELL_TYPE_FORMULA = 2; public final static int CELL_TYPE_BLANK = 3; public final static int CELL_TYPE_BOOLEAN = 4; public final static int CELL_TYPE_ERROR = 5;

int getColumnIndex(); int getRowIndex();

void setCellValue(double value); void setCellValue(Date value); void setCellValue(Calender value) void setCellValue(String value); void setCellFormula(String formula) throws FormulaParseException; void setCellValue(boolean value); void setCellErrorValue(byte value); void setCellType(int cellType); void setCellStyle(CellStyle style);

String getCellFormula(); double getNumericCellValue(); Date getDateCellValue(); RichTextString getRichStrringCellValue(); String getStringCellValue(); boolean getBooleanCellValue(); int getCellType(); CellStyle getCellStyle(); byte getErrorCellValue(); 新建一个操作Excel的类

封装属性和方法，方便别人调用

得到一个Workbook类的对象

将单元格中的值存放在一个新的二维数组中。遍历输出单元格中的值

源码：

package com.youceedu.api.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

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;

/**
 * 
 * @ClassName:  ExcelUtil   
 * @Description:TODO   
 * @author: zhangyi 
 * @date:   2018年7月12日 下午6:30:42   
 *     
 */
public class ExcelUtil {
	
	private static String excelPath = null;
    /**
     * 
     * @Title:  ExcelUtil   
     * @Description:   类的对象进行实例化
     * @param:  @param excelPath  
     * @throws
     */
	public ExcelUtil(String excelPath) {
		this.excelPath  = excelPath;
	}

	/**
	 * 
	 * @Title: getwb   
	 * @Description: 得到一个Workbook的对象
	 * @param: @return      
	 * @return: Workbook      
	 * @throws
	 */
	public static Workbook getwb() {
		InputStream input;
		Workbook wb = null;
		try {
			input = new FileInputStream(excelPath);
			
			if(excelPath.endsWith(".xlsx")) {
				try {
					// //向上转型得到一个Workbook对象
					 wb = new XSSFWorkbook(input);        
				} catch (IOException e) {
					e.printStackTrace();
				}
				}else if(excelPath.endsWith(".xls")) {
				try {
					// //向上转型得到一个Workbook对象
					 wb = new HSSFWorkbook(input);  
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}     
		return wb;
	}
	
	/**
	 * @Title: getSheet   
	 * @Description: 得到一个Sheet类型的对象
	 * @param: @param index
	 * @param: @return      
	 * @return: Sheet      
	 * @throws
	 */
	public static Sheet getSheet(int index) {
		Sheet sheet = null;
		if(!getwb().equals(null)) {
			sheet = getwb().getSheetAt(index);
		}
		return sheet;
	}
	
	/**
	 * 
	 * @Title: getCellTypeWithCellValue   
	 * @Description: 根据不同的CellType类型得到不同的value值
	 * @param: @param cell
	 * @param: @return      
	 * @return: Object      
	 * @throws
	 */
	public static Object getCellTypeWithCellValue(Cell cell) {
		Object value = null;
		//根据不同的CellType类型得到不同的value值
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC ) {
			value = cell.getNumericCellValue();
		}else if(cell.getCellType() == Cell.CELL_TYPE_STRING ) {
			value = cell.getStringCellValue();	
		}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA ) {
			value = cell.getCellFormula();
		}else if(cell.getCellType() == Cell.CELL_TYPE_BLANK ) {
			value = "" ;
		}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN ) {
			value = cell.getBooleanCellValue();
		}else if(cell.getCellType() == Cell.CELL_TYPE_ERROR ) {
			value = cell.getErrorCellValue();
		}else {
			value = cell.getDateCellValue();
		}
		return value;
	}
	
	/**
	 * @Title: getCellValue   
	 * @Description: 遍历excel的行和列所有的值
	 * @param: @return      
	 * @return: Object      
	 * @throws
	 */
	public static Object getCellValue(int rowNum,int cellNum) {
		Object value = null;
		Sheet sheet = getSheet(0);
		
		for( rowNum = sheet.getFirstRowNum();rowNum<=sheet.getLastRowNum();rowNum++) {
			//遍历行
			Row row = sheet.getRow(rowNum);
			for( cellNum = row.getFirstCellNum();cellNum<row.getLastCellNum();cellNum++) {
				//遍历列
				Cell cell = row.getCell(cellNum);
				value = getCellTypeWithCellValue(cell);
				System.out.println((rowNum+1)+"行"+(cellNum+1)+"列的值是："+value);
			}
		}
		return value;
	}
	
	/**
	 * @Title: getArrayCellValue   
	 * @Description: 把excel内的数据存储到一个二维数组中
	 * @param: @param index
	 * @param: @return      
	 * @return: Object[][]      
	 * @throws
	 */
	public Object[][] getArrayCellValue(int index){
		//初始化数组
		Object[][] excelArray = null;
		//数组总行数
		int totoalRowIndex = getSheet(index).getLastRowNum();
		//分配内存空间
		excelArray = new Object[totoalRowIndex][10];
		
		for(int arrayRowIndex = 1;arrayRowIndex<=totoalRowIndex;arrayRowIndex++) {
			//指定行
			Row row = getSheet(index).getRow(arrayRowIndex);
			if(row == null)	{	
				continue;
			}
			//遍历指定列
			for(int arrayCellIndex = 0;arrayCellIndex<row.getLastCellNum();arrayCellIndex++) {
				Cell cell = row.getCell(arrayCellIndex);
				if(cell == null) {
					excelArray[arrayRowIndex-1][arrayCellIndex]="";
				}else {
					excelArray[arrayRowIndex-1][arrayCellIndex]=getCellTypeWithCellValue(cell);
				}
				}
			}			
		return excelArray;		
	}
	
	/**
	 * 
	 * @Title: main   
	 * @Description: 主方法
	 * @param: @param args      
	 * @return: void      
	 * @throws
	 */
	public static void main(String[] args) {
		String excelPath = "D:\\app_testcase.xlsx";
		ExcelUtil excelUtil = new ExcelUtil(excelPath);
		//getCellValue(0,0);
		Object[][] excelArray =excelUtil.getArrayCellValue(0);
		System.out.println(excelArray[0][0]);
		
		
//		ExcelUtil excelFile = new ExcelUtil(excelPath);
//		getCellValue();		
//		System.out.println("sheet的名字是："+sheet.getSheetName());  //输出这个sheet的名字
//		System.out.println("sheet共有："+(sheet.getLastRowNum()+1)+"行")							
		//获得一个row
//			Row row = sheet.getRow(0);
//			
//			Cell cell = row.getCell(0);
//			
//			System.out.println("第一行第一列的值是："+cell.getStringCellValue());
	}

}