exceltools.java

人力资源管理系统

package org.HumResManSys.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelTools {
	
	private String sheetName;
	private String savePath;
	private int curRowNumber;
	private HSSFSheet sheet;
	private HSSFWorkbook workbook;
	private int maxCol;
	private final int MAXCOLS=25;
	

	//构造函数
	public ExcelTools(String savePath,boolean write){
		this.savePath=savePath;
		this.curRowNumber=0;
		this.maxCol=0;
		if(write){
			// 创建新的Excel 工作簿
			this.setWorkbook(new HSSFWorkbook());
			// 在Excel工作簿中建一工作表，其名为缺省值
			this.setSheet(workbook.createSheet(sheetName));
		}else{//读取excel表格
			try {
				this.setWorkbook(new HSSFWorkbook(new FileInputStream(this.getSavePath())));
				this.setSheet(this.getWorkbook().getSheetAt(0));
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return ;
			}
		}

	}



	//构造函数
	public ExcelTools(String sheetName,String savePath,boolean write){
		this.sheetName=sheetName;
		this.savePath=savePath;
		this.curRowNumber=0;
		this.maxCol=0;
		if(write){
			// 创建新的Excel 工作簿
			this.setWorkbook(new HSSFWorkbook());
			// 在Excel工作簿中建一工作表，其名为缺省值
			this.setSheet(workbook.createSheet(sheetName));
		}else{//读取excel表格
			try {
				this.setWorkbook(new HSSFWorkbook(new FileInputStream(this.getSavePath())));
				if(sheetName==null){
					this.setSheet(this.getWorkbook().getSheetAt(0));
				}else{
					this.setSheet(this.getWorkbook().getSheet(this.getSheetName()));
				}
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return ;
			}
		}

	}

	//添加一行
	public boolean addRow(String[] cols){
		try {
			HSSFRow row = getSheet().createRow(this.getCurRowNumber());
			if(cols.length > this.getMaxCol()){
				this.setMaxCol(cols.length);
			}
			for(int i=0;i<cols.length;i++){
				if(cols[i].equalsIgnoreCase("")||cols[i]==null){

				}else{
					HSSFCell cell = row.createCell((short)i);
					// 定义单元格为字符串类型
				    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				    // 在单元格中输入一些内容
				    cell.setCellValue(new HSSFRichTextString(cols[i]));
				}
			    
			}
			this.setCurRowNumber(this.getCurRowNumber()+1);
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	//添加一行
	public boolean addRow(int rowNumber,String[] cols){
		try {
			HSSFRow row = getSheet().createRow(rowNumber);
			if(cols.length > this.getMaxCol()){
				this.setMaxCol(cols.length);
			}
			for(int i=0;i<cols.length;i++){
				if(cols[i].equalsIgnoreCase("")||cols[i]==null){

				}else{
					HSSFCell cell = row.createCell((short)i);
					// 定义单元格为字符串类型
				    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				    // 在单元格中输入一些内容
				    cell.setCellValue(new HSSFRichTextString(cols[i]));
				}
			    
			}
			this.setCurRowNumber(this.getCurRowNumber()+1);
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	//更新一行
	public boolean updateRow(int rowNumber,String[] cols){
		
		String []oldData=readFromExcel(rowNumber);
		String []newData=new String[cols.length];
		
		for(int j=0;j<newData.length;j++){
			if(cols[j]==null||cols[j].equalsIgnoreCase("")){
				newData[j]=oldData[j];
			}else{
				newData[j]=cols[j];
			}
		}
		if(deleteRow(rowNumber)){
			return insertRowAfter(rowNumber-1,newData);
		}
		return false;
	}
	
	//更新一行
	public boolean updateRow(HSSFRow row){
		return false;
	}
	
	//插入一行
	public boolean insertRowAfter(int afterRowNumber,String[] cols){
		if((afterRowNumber <= this.getCurRowNumber())&&(afterRowNumber >= 0)){
			getSheet().shiftRows(afterRowNumber+1, getCurRowNumber(), 1);
			return addRow(afterRowNumber+1,cols);
		}else{
			return false;
		}

	}
	
	//插入一行
	public boolean insertRowBefore(int beforeRowNumber,String[] cols){
		if((beforeRowNumber <= this.getCurRowNumber())&&(beforeRowNumber >= 0)){
			getSheet().shiftRows(beforeRowNumber, getCurRowNumber(), 1);
			return addRow(beforeRowNumber,cols);
		}else{
			return false;
		}

	}
	
	//删除一行
	public boolean deleteRow(int rowNumber){
		try {
			if((rowNumber <= this.getCurRowNumber())&&(rowNumber >= 0)){
				getSheet().removeRow(getSheet().getRow(rowNumber));
				getSheet().shiftRows(rowNumber+1, getCurRowNumber(), -1);
				return true;
			}else{
				return false;
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	//另存为excel
	public boolean saveAsExcel(){
		
		try {
			// 新建一输出文件流
			FileOutputStream fOut = new FileOutputStream(getSavePath());
			// 把相应的Excel 工作簿存盘
			this.getWorkbook().write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	//从excel中读取数据
	public String[] readFromExcel(int rowNumber){
		String [] returnValues=new String[MAXCOLS];
		try {
			HSSFRow row = getSheet().getRow(rowNumber);
			Iterator colsIt=row.iterator();
			int j=0;
			while(colsIt.hasNext()){
				returnValues[j++]=(String)colsIt.next().toString();
			}
			return returnValues;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	//从excel中读取数据
	public String[][] readFromExcelByPage(int currentPage,int pageSize,boolean readHead){
		String [][] returnValues=new String[pageSize][MAXCOLS];
		//System.out.println("getMaxCol:"+this.getMaxCol());
		try {
			this.setCurRowNumber(this.getSheet().getLastRowNum()-this.getSheet().getFirstRowNum());
			//curRowNumber是从0 开始计数
			System.out.println("最大行数："+this.getCurRowNumber());
			
			//计算页数
			int maxPage=-1;
			if((getCurRowNumber()+1)%pageSize==0){
				maxPage=(getCurRowNumber()+1)/pageSize;
			}else{
				maxPage=(getCurRowNumber()+1)/pageSize+1;
			}
			System.out.println("maxPage:"+maxPage);
			
			if(currentPage < 0){//如果小于0，读取第一页
				currentPage=0;
			}else if(currentPage > maxPage){//如果大于最大值，读取最后一页
				currentPage = maxPage;
			}
			
		
			//每次查询，查找pageSize行记录
			for(int i=0;i<pageSize;i++){
				//生成当前需要查询的行的行号
				int rowNo=currentPage*pageSize+i;
				if((rowNo==0)&&!readHead){
					continue;
				}
				if(rowNo > getCurRowNumber()){//如果生成的行号超过最大行号，返回
					return returnValues;
				}else{//否则执行查询
					HSSFRow row = getSheet().getRow(rowNo);
					Iterator colsIt=row.iterator();
					int j=0;
					while(colsIt.hasNext()){
						returnValues[i][j++]=(String)colsIt.next().toString();
						//System.out.println("   "+(String)colsIt.next().toString());
					}
				}
				
			}
			
			
			return returnValues;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	
	//从excel中读取数据
	public String[][] readContentFromExcel(){
		String [][] returnValues=new String[MAXCOLS][MAXCOLS];
		this.setCurRowNumber(sheet.getLastRowNum()-sheet.getFirstRowNum());
		System.out.println("当前的行数为："+getCurRowNumber());
		for(int i=1;i<=this.getCurRowNumber();i++){
			HSSFRow row = getSheet().getRow(i);
			Iterator colsIt=row.iterator();
			int j=0;
			while(colsIt.hasNext()){
				returnValues[i-1][j++]=(String)colsIt.next().toString();
				//System.out.println("   "+(String)colsIt.next().toString());
			}
		}
		return returnValues;
	}
	
	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public String getSavePath() {
		return savePath;
	}

	public void setSavePath(String savePath) {
		this.savePath = savePath;
	}

	
	public int getCurRowNumber() {
		return curRowNumber;
	}

	public void setCurRowNumber(int curRowNumber) {
		this.curRowNumber = curRowNumber;
	}
	
	
	public HSSFSheet getSheet() {
		return sheet;
	}

	public void setSheet(HSSFSheet sheet) {
		this.sheet = sheet;
	}
	
	

	public HSSFWorkbook getWorkbook() {
		return workbook;
	}


	public void setWorkbook(HSSFWorkbook workbook) {
		this.workbook = workbook;
	}
	

	public int getMaxCol() {
		return maxCol;
	}

	public void setMaxCol(int maxCol) {
		this.maxCol = maxCol;
	}
	
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
/*		ExcelTools et=new ExcelTools("sheet-0","d:\\学生名单.xls",true);
		String []head={"学号","姓名","性别","出生年月","学院","联系方式"};
		String []wcl={"05301051","王春来","男","1987年01月29日","软件学院","13422225555"};
		String []liuben={"05301041","刘奔","男","1086年01月07日","软件学院","13269477556"};
		String []czq={"05301035","陈紫千","男","1986年*月*日","软件学院","1353255454354"};
		String []tww={"053010**","谭伟文","男","1986年*月*日","软件学院","1353255454354"};
		String []zrt={"05301060","赵玉兰","女","1986年*月*日","软件学院","1353255454354"};
		String []wclnew={"王春来","王春来","男","1987年01月29日","软件学院","11111111111"};
		et.addRow(head);
		et.addRow(wcl);
		et.addRow(liuben);
		et.addRow(czq);
		et.insertRowAfter(1, tww);
		et.insertRowBefore(4, zrt);
		et.deleteRow(3);
		et.updateRow(1,wclnew);
		et.saveAsExcel();*/

		

		ExcelTools et=new ExcelTools("d:\\t.xls",false);
		String [][]ss=et.readFromExcelByPage(0,3,false);
		for(int i=0;i<ss.length;i++){
			for(int j=0;j<ss[0].length;j++){
				if(ss[i][j]!=null){
					System.out.println(ss[i][j]);
				}
				
			}
		}

/*		String []s=et.readFromExcel(3);
		for(int i=0;i<s.length;i++){
			if(s[i]!=null){
				System.out.println(s[i]);
			}
		}*/
	}

}