Java读取Excel_01

读取excel操作

package com.operationExcel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
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.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javafx.scene.transform.Shear;

public class ReadExcelParm {
private SimpleDateFormat fmt=new SimpleDateFormat(“yyyy-mm-dd”);
private XSSFWorkbook xssfWorkbook;
private HSSFWorkbook hssfWorkbook;
//工作的sheet页
private Sheet sheet;
public int rows=0;
public int linerow=0;

	//构造函数打开excel
	public ReadExcelParm (String path) throws IOException {
		//截取文件后缀名，选择使用打开文件的方法
		String type=path.substring(path.lastIndexOf("."));
		FileInputStream in=null;
		try {
			in=new FileInputStream(new File(path));
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return;
		}
		//判断是xls格式还是xlsx格式
		if(type.equals(".xlsx")) {
			xssfWorkbook=new XSSFWorkbook(in);
			sheet=xssfWorkbook.getSheetAt(0);
			//获取最大行数
			rows=sheet.getPhysicalNumberOfRows();
			linerow=0;
			
		}
		if (type.equals(".xls")) {
			try {
				hssfWorkbook = new HSSFWorkbook(in);
				sheet = hssfWorkbook.getSheetAt(0);
				rows = sheet.getPhysicalNumberOfRows();
				linerow = 0;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		try {
			in.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		if(sheet==null) {
			System.err.println("打开失败");
		}
	}



    public void setDataFormat(String dataformate) {
    	fmt=new SimpleDateFormat(dataformate);
    }

    public void usesheet(String sheetname) {
    	if(sheet !=null) {
    		if (hssfWorkbook !=null) {
    			sheet=hssfWorkbook.getSheet(sheetname);
    		}
    		else{
    			sheet=xssfWorkbook.getSheet(sheetname);
    			
    		}
    	rows=sheet.getPhysicalNumberOfRows();
    	linerow=0;
    	}
    	else {
    		System.out.println("未打开文件");
    	}
	}
    
    //获取所有sheet页
    public int gettotalsheet() {
    	int sheets=0;
    	if(hssfWorkbook !=null) {
    		sheets=hssfWorkbook.getNumberOfSheets();
    		
    	}
    	else {
    		sheets=xssfWorkbook.getNumberOfSheets();
    		
    	}
    	return sheets;
    }
    
    //获取当前sheet的名字
    public String  sheetname(int sheetindex) {
    	String sheetname="";
    	if(hssfWorkbook !=null) {
    		sheetname=hssfWorkbook.getSheetName(sheetindex);
    	}
    	else {
    		sheetname=xssfWorkbook.getSheetName(sheetindex);
    	}
    	return sheetname;
	}
    
    
    //根据sheet序号指定使用的sheet
    public void usesheetByIndex(int sheetindex) {
    	if(sheet!=null) {
    		if(xssfWorkbook!=null) {
    			sheet=xssfWorkbook.getSheetAt(sheetindex);
    		}
    		else {
    			sheet=hssfWorkbook.getSheetAt(sheetindex);
    		}
    		rows=sheet.getPhysicalNumberOfRows();
    		linerow=0;
    	}
    	else {
			System.err.println("未打开excel文件");
		}
    }

    //关闭文件
    public void closeexcel() {
    	try {
    		if(xssfWorkbook!=null) {
    			xssfWorkbook.close();
    		}
    		else {
    			hssfWorkbook.close();
    		}
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		
	}
    
    //读取linenow行
    public List<String>readnextline(){
    	List<String>line=new ArrayList<String>();
    	org.apache.poi.ss.usermodel.Row row=sheet.getRow(linerow);
    	int cellCount=row.getPhysicalNumberOfCells();
    	for(int c=0;c<cellCount;c++) {
    		line.add(getCellValue(row.getCell(c)));
    	}
    	linerow++;
    	return line;
    }

    //读取参数中指定的行
    public List<String>readline(int rowNo){
    	List<String>line=new ArrayList<String>();
    	Row row=sheet.getRow(rowNo);
    	int cellCount=row.getPhysicalNumberOfCells();
    	for (int c=0;c<cellCount;c++) {
    		line.add(getCellValue(row.getCell(c)));
    	}
    	return line;
    }

    
	// 读取指定列
	public List<String> readColumn(int colNo) {
		List<String> column = new ArrayList<String>();
		for (int i = 0; i < rows; i++) {
			Row row = sheet.getRow(i);
			column.add(getCellValue(row.getCell(colNo)));
		}
		return column;
	}

	// 读取指定单元格
	public String readCell(int rowNo, int column) {
		String content;
		Row row = sheet.getRow(rowNo);
		content = getCellValue(row.getCell(column));
		return content;
	}
	
	//以二维数组形式读取excel文件内容
	public Object[][] readAsMatrix(){
		int cellcount=sheet.getRow(0).getPhysicalNumberOfCells();
		Object[][] matrix=new Object[rows-1][cellcount];
		for(int rowNo =1;rowNo<rows;rowNo++) {
			for(int colNo=0;colNo<cellcount;colNo++)
			{
				matrix[rowNo-1][colNo]=readCell(rowNo, colNo);
			}
		}
		return matrix;
	}

    
    
    
 // 针对单元格内容不同格式进行读取
	@SuppressWarnings("deprecation")
	private String getCellValue(Cell cell) {
		// 当单元格内容为空时，返回空字符串，这是由于poi读取某些xls格式的excel表时，针对某些空白格会报空指针异常
		String cellValue = "";
		if (cell == null)
			return cellValue;
		try {
			int cellType = cell.getCellType();
			// 将所有格式转为字符串
			switch (cellType) {
			case Cell.CELL_TYPE_STRING: // 文本
				cellValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_NUMERIC: // 数字、日期
				if (DateUtil.isCellDateFormatted(cell)) {
					cellValue = fmt.format(cell.getDateCellValue()); // 日期型
				} else {
					Double d = cell.getNumericCellValue();
					DecimalFormat df = new DecimalFormat("#.##");
					cellValue = df.format(d);
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN: // 布尔型
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_BLANK: // 空白
				cellValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_ERROR: // 错误
				cellValue = "错误";
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				cellValue = "错误";
				break;
			default:
				cellValue = "错误";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cellValue;
	}

}

读取excel操作2

package com.operationExcel;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;

/**

• Poi写Excel
• @author jianggujin

*/
public class ReadEtest
{
private Workbook workbook;
private Sheet sheet;
private int sheets=0;
private int rows=0;
public ReadEtest(String path) throws EncryptedDocumentException, InvalidFormatException, IOException {
File xlsFile = new File(path);
// 获得工作簿
workbook = WorkbookFactory.create(xlsFile);

}
public int sheettotal() {

  sheets=workbook.getNumberOfSheets();
  return sheets;
// System.out.println(sheets);

}
public void usesheetbyindex(int sheetindex) {
sheet=workbook.getSheetAt(sheetindex);
// rows=sheet.getPhysicalNumberOfRows();
}

public int getrow() {

  int sheets=workbook.getNumberOfSheets();
 for (int i=0;i<sheets;i++) {
	 sheet=workbook.getSheetAt(i);
	 rows=sheet.getLastRowNum()+1;
	// System.out.println(rows);
 }return rows;

}

public Listreadline(int rowNo){
Listline=new ArrayList();
Row row=sheet.getRow(rowNo);
int cellCount=row.getPhysicalNumberOfCells();
for (int c=0;c<cellCount;c++) {
line.add(row.getCell©.getStringCellValue());
}
return line;
}

}