/*
 *$Id: excel.aau 124 2010-08-06 04:16:13Z crane.he $ 
*/


namespace labs.excel;
import com;

//新建Excel文件
function createBook(visible = 1) {
	var oExcel = com.CreateObject("Excel.Application")
	if( !oExcel ) { error("Object is not create") }
	
	//处理是否可见 
	oExcel.Visible = visible
	
	oExcel.Visible = visible
	oExcel.WorkBooks.Add()
	oExcel.ActiveWorkbook.Sheets(1).Select()
	return oExcel
}

//打开已有的Excel文件
function openBook(filePath,visible=1,readOnly=0){
	//查看文件是否存在
	if( ..io.exist(filePath) == false)
   	error("File is not exists")
   	
	var oExcel = com.CreateObject("Excel.Application")
	if( ! oExcel ) { error("Object is not create") }
	
 	
	//处理是否可见ReadOnly  
	visible = visible?1:0
	
	//处理是否只读  
	readOnly = readOnly?1:0
	oExcel.Visible = visible
	
	//打开指定文件
	oExcel.WorkBooks.Open(filePath,null,readOnly)
	oExcel.ActiveWorkbook.Sheets(1).Select()
	return oExcel
}

//写入Cells数据
function writeCell(oExcel,value,row,column){
	//验证参数
	if( !oExcel ){ error("oExcel is not a object!") }
	if( tonumber(row) == null or row < 1 ) { error("row is not a valid number!") }
	if( tonumber(column) == null or column < 1){ error("column is not a valid number!") }
	//对指定Cell位置赋值
	oExcel.Activesheet.Cells(row, column).Value2 = value
	return 1
}

//读取Cells数据
function readCell(oExcel,row,column){
	//验证参数
	if( !oExcel ){ error("oExcel is not a object!") }
	if( tonumber(row) == null or row < 1 ) { error("row is not a valid number!") }
	if( tonumber(column) == null or column < 1){ error("column is not a valid number!") }
	//返回指定Cell位置值
	return oExcel.Activesheet.Cells(row, column).Value2
}

//保存Excel文件
function saveBook(oExcel, alerts=0){
	//验证参数
	if( !oExcel ){ error("oExcel is not a object!") }
	//处理是否提示   
	alerts = alerts?1:0
	oExcel.Application.DisplayAlerts = alerts
	oExcel.Application.ScreenUpdating = alerts
	//进行保存
	oExcel.ActiveWorkBook.Save()
	if(not alerts){
	oExcel.Application.DisplayAlerts = 1
	oExcel.Application.ScreenUpdating = 1
	}
	return 1
}

//另存Excel文件
function saveAsBook(oExcel,filePath,ftype = "xls" ,alerts=0,overWrite=1){
	//验证参数
	if( !oExcel ){ error("oExcel is not a object!") }
	
	//处理保存文件类型
	select(ftype) {
		case "xls" { ftype = -4143 }
		case "csv" { ftype = 6 }
		case "txt" { ftype = -4158 }
		case "template" { ftype = 17 }
		case "html" { ftype = 44 }
		else { error("ftype is not a valid type") } 
	}
 	
	//处理是否提示  
	alerts = alerts?1:0
	oExcel.Application.DisplayAlerts = alerts
	oExcel.Application.ScreenUpdating = alerts
	
	
	//查看文件是否存在
	if( not filePath ){ error("filePath is not valid !") } 
	if( (!overWrite) and ..io.exist(filePath) )error("Can't overwrite the file!")
 	
 	
	//使用ActiveWorkBook时，在已经打开文件时，无法另存，所以使用WorkBookS(1)进行处理
	oExcel.WorkBookS(1).SaveAs(filePath,ftype)
	//继续处理Alerts参数，以便继续使用
	if not alerts {
	oExcel.Application.DisplayAlerts = 1
	oExcel.Application.ScreenUpdating = 1
	}
	return 1
}

//关闭Excel文件
function closeBook(oExcel,save=true,alerts=0){
	//验证参数
	if( not oExcel ){ error("oExcel is not a object!") }
	//处理是否保存
	  
	
	//处理是否提示 
	if tonumber(alerts) == null { error("Alerts is not a number") }
	alerts = alerts?1:0
	
	if (save) { oExcel.ActiveWorkBook.save() }
	oExcel.Application.DisplayAlerts = alerts
	oExcel.Application.ScreenUpdating = alerts
	oExcel.Application.Quit()
	return 1
}

//列出所有Sheet
function getSheets(oExcel){
	//验证参数
	if( not oExcel ){ error("oExcel is not a object!") }
	var temp = oExcel.ActiveWorkbook.Sheets.Count
	var tab = {}
	tab[0] = temp
	for( i = 1;temp){
	tab[i] = oExcel.ActiveWorkbook.Sheets(i).Name
	}
	//返回一个table，其中tab[0]为个数
	return tab
}


//激活指定的sheet
function activeSheet(oExcel, vSheet = 1){
	var tab = {}
	var found = 0
	//验证参数
	if(not oExcel ){ error("oExcel is not a object!") } 
	
	//如果提供参数为数字
	if( tonumber(vSheet) ) {
		if( oExcel.ActiveWorkbook.Sheets.Count < tonumber(vSheet) ) { error("The sheet value is to biger!") }
	}
	else{//如果提供参数为字符
		tab = getSheets(oExcel)
		for( i = 1 ; tab[0] ) begin
		if( tab[i] == vSheet ) { found = 1 }
		end
		if( found != 1 ) { error("Can't find the sheet") }
	}
	oExcel.ActiveWorkbook.Sheets(vSheet).Select ();
	return 1
}


/*
import labs.excel 
var b=assert(labs.excel.createBook(1))
vartab=assert(labs.excel.getSheets(b))
for i,v in tab {
io.print(i,v)
}

assert(labs.excel.activeSheet(b,"Sheet2"))
 
assert(labs.excel.writeCell(b,"haha",1,1))
assert(labs.excel.saveAsBook(b,"c:\a","txt" ))
io.print( labs.excel.readCell(b,1,1))
assert(labs.excel.closeBook(b))
*/
 