excelio 使用心得(2)

本文提取出了所有函数,简单分了个类,使用的时候就可以根据分类简单的进行查询,省得在库一两千行里面慢慢找了

下一篇会总结几个个人使用的例子

/* 
新建或读入 
Load(path);读入表格,这里填路径
New(ext:="xlsx");新建表格使用的,默认是xlsx拓展名,自行改成其他拓展名

排序相关的
classIAutoFilterextendsExcelIO.IBase;排序相关的,首先两个设置区域的
getRef(ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast)
setRef(rowFirst,rowLast,colFirst,colLast)
column(colId)
columnSize()
columnByIndex(index)
getSortRange(ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast)
getSort(ByRefcolumnIndex,ByRefdescending)
setSort(columnIndex,descending:=false)

插入图片设置字体,颜色以及一些格式的东西
classIBookextendsExcelIO.IBase
ifIsNumber(it)
activeSheet();同vba
addCustomNumFormat(customNumFormat)
addFont(initFont:=0)
addFormat(initFormat:=0)
addPicture(filename)
addPicture2(data,size)
addPictureAsLink(filename,insert:=false)
addRichString()
addSheet(name,initSheet:=0)
biffVersion()
calcMode()
colorPack(red,green,blue)
colorUnpack(color,ByRefred,ByRefgreen,ByRefblue)
customNumFormat(fmt)
datePack(year,month,day,hour:=0,min:=0,sec:=0,msec:=0)
dateUnpack(value,ByRefyear,ByRefmonth,ByRefday,ByRefhour:=0,ByRefmin:=0,ByRefsec:=0,ByRefmsec:=0)
defaultFont(ByReffontSize)
delSheet(index)
errorMessage()
font(index)
fontSize()
format(index)
formatSize()

获取图片,表格以及一些信息
getPicture(index,ByRefdata,ByRefsize)
getSheet(index)
getSheetName(index)
insertSheet(index,name,initSheet:=0)
isDate1904()
isTemplate()
load(filename,tempFile:="");读入一个表格
loadInfo(filename)
loadPartially(filename,sheetIndex,firstRow,lastRow,tempFile:="")
loadRaw(data,size,sheetIndex:=-1,firstRow:=-1,lastRow:=-1)
loadSheet(filename,sheetIndex,tempFile:="")
loadWithoutEmptyCells(filename)
moveSheet(srcIndex,dstIndex)
pictureSize()
refR1C1()
release()
rgbMode()
save(filename:="",useTempFile:=false)
saveRaw(ByRefdata,ByRefsize)
setActiveSheet(index)
setCalcMode(CalcMode)
setDate1904(date1904:=true)
setDefaultFont(fontName,fontSize)
setKey(name,key)
setLocale(locale)
setRefR1C1(refR1C1:=true)
setRgbMode(rgbMode:=true)
setTemplate(tmpl:=true)
sheetCount()
sheetType(index)
version()
__Delete()


排序相关
classIFilterColumnextendsExcelIO.IBase
index()
filterType()
filterSize()
filter(index)
addFilter(value)
getTop10(ByRefvalue,ByReftop,ByRefpercent)
setTop10(value,top:=true,percent:=false)
getCustomFilter(ByRefop1,ByRefv1,ByRefop2,ByRefv2,ByRefandOp)
setCustomFilter(op1,v1,op2:=0,v2:="",andOp:=false)
clear()

拓展字体相关
classIFontextendsExcelIO.IBase
size()
setSize(size)
italic()
setItalic(italic:=true)
strikeOut()
setStrikeOut(strikeOut:=true)
color()
setColor(Color)
bold()
setBold(bold)
script()
setScript(Script)
underline()
setUnderline(Underline)
name()
setName(name)

设置单元格样式相关,比如bc边框隐藏之类的
classIFormatextendsExcelIO.IBase;
font()
setFont(font)
numFormat()
setNumFormat(numFormat)
alignH()
setAlignH(Align)
alignV()
setAlignV(Align)
wrap()
setWrap(wrap:=true)
rotation()
setRotation(rotation)
indent()
setIndent(indent)
shrinkToFit()
setShrinkToFit(shrinkToFit:=true)
setBorder(Style:=1)
setBorderColor(Color)
borderLeft()
setBorderLeft(Style:=1)
borderRight()
setBorderRight(style:=1)
borderTop()
setBorderTop(style:=1)
borderBottom()
setBorderBottom(style:=1)
borderLeftColor()
setBorderLeftColor(color)
borderRightColor()
setBorderRightColor(color)
borderTopColor()
setBorderTopColor(color)
borderBottomColor()
setBorderBottomColor(color)
borderDiagonal()
setBorderDiagonal(Border)
borderDiagonalStyle()
setBorderDiagonalStyle(style)
borderDiagonalColor()
setBorderDiagonalColor(color)
fillPattern()
setFillPattern(Pattern)
patternForegroundColor()
setPatternForegroundColor(color)
patternBackgroundColor()
setPatternBackgroundColor(color)
locked()
setLocked(locked:=true)
hidden()
setHidden(hidden:=true)

差不多可以理解为插入艺术字象关的,看那个例子里面
classIRichStringextendsExcelIO.IBase
addFont(initFont:=0)
addText(text,font:=0)
getText(index,ByReffont:=0)
textSize()


这里面包含了向单元格写入东西的简单操作
classISheetextendsExcelIO.IBase
cellType(row,col)
isFormula(row,col);判断是不是公式
cellFormat(row,col)
setCellFormat(row,col,format)
readStr(row,col,ByRefformat:=0)
writeStr(row,col,value,format:=0)
readRichStr(row,col,ByRefformat:=0)
writeRichStr(row,col,richString,format:=0)
readNum(row,col,ByRefformat:=0)
writeNum(row,col,value,format:=0)
readBool(row,col,ByRefformat:=0)
writeBool(row,col,value,format:=0)
readBlank(row,col,ByRefformat:=0)
writeBlank(row,col,format)
readFormula(row,col,ByRefformat:="unset")
writeFormula(row,col,expr,format:=0);能够写入函数
writeFormulaNum(row,col,expr,value,format:=0)
writeFormulaStr(row,col,expr,value,format:=0)
writeFormulaBool(row,col,expr,value,format:=0)
readComment(row,col)
writeComment(row,col,value,author:=0,width:=129,height:=75)
removeComment(row,col)
isDate(row,col)
isRichStr(row,col)
readError(row,col)
writeError(row,col,ErrorType,format:=0)
colWidth(col)
rowHeight(row)
colWidthPx(col)
rowHeightPx(row)
setCol(colFirst,colLast,width,format:=0,hidden:=false)
setRow(row,height,format:=0,hidden:=false)
rowHidden(row)
setRowHidden(row,hidden)
colHidden(col);隐藏
setColHidden(col,hidden)
getMerge(row,col,ByRefrowFirst:=0,ByRefrowLast:=0,ByRefcolFirst:=0,ByRefcolLast:=0)
setMerge(rowFirst,rowLast,colFirst,colLast)
delMerge(row,col)
mergeSize()
merge(index,ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast)
delMergeByIndex(index)
pictureSize()
getPicture(index,ByRefrowTop:=0,ByRefcolLeft:=0,ByRefrowBottom:=0,ByRefcolRight:=0,ByRefwidth:=0,ByRefheight:=0,ByRefoffset_x:=0,ByRefoffset_y:=0)
removePictureByIndex(index)
setPicture(row,col,pictureId,scale:=1.0,offset_x:=0,offset_y:=0,pos:=0)
setPicture2(row,col,pictureId,width:=-1,height:=-1,offset_x:=0,offset_y:=0,pos:=0)
removePicture(row,col)
getHorPageBreak(index)
getHorPageBreakSize()
getVerPageBreak(index)
getVerPageBreakSize()
setHorPageBreak(row,pageBreak:=true)
setVerPageBreak(col,pageBreak:=true)
split(row,col)
splitInfo(ByRefrow,ByRefcol)
groupRows(rowFirst,rowLast,collapsed:=true)
groupCols(colFirst,colLast,collapsed:=true)
groupSummaryBelow()
setGroupSummaryBelow(below)
groupSummaryRight()
setGroupSummaryRight(right)
clear(rowFirst:=0,rowLast:=1048575,colFirst:=0,colLast:=16383)
insertCol(colFirst,colLast,updateNamedRanges:=true)
insertRow(rowFirst,rowLast,updateNamedRanges:=true)
removeCol(colFirst,colLast,updateNamedRanges:=true)
removeRow(rowFirst,rowLast,updateNamedRanges:=true)
insertColAndKeepRanges(colFirst,colLast)
insertRowAndKeepRanges(rowFirst,rowLast)
removeColAndKeepRanges(colFirst,colLast)
removeRowAndKeepRanges(rowFirst,rowLast)
copyCell(rowSrc,colSrc,rowDst,colDst)
firstRow()
lastRow()
firstCol()
lastCol()
firstFilledRow()
lastFilledRow()
firstFilledCol()
lastFilledCol()
displayGridlines()
setDisplayGridlines(show:=true)
printGridlines()
setPrintGridlines(print:=true)
zoom()
setZoom(zoom)
printZoom()
setPrintZoom(zoom)
getPrintFit(ByRefwPages,ByRefhPages)
setPrintFit(wPages:=1,hPages:=1)
landscape()
setLandscape(landscape:=true)
paper()
setPaper(Paper:=0)
header()
setHeader(header,margin:=0.5)
headerMargin()
footer()
setFooter(footer,margin:=0.5)
footerMargin()
hCenter()
setHCenter(hCenter:=true)
vCenter()
setVCenter(vCenter:=true)
marginLeft()
setMarginLeft(margin)
marginRight()
setMarginRight(margin)
marginTop()
setMarginTop(margin)
marginBottom()
setMarginBottom(margin)
printRowCol();打印相关
setPrintRowCol(print:=true)
printRepeatRows(ByRefrowFirst,ByRefrowLast)
setPrintRepeatRows(rowFirst,rowLast)
printRepeatCols(ByRefcolFirst,ByRefcolLast)
setPrintRepeatCols(colFirst,colLast)
printArea(ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast)
setPrintArea(rowFirst,rowLast,colFirst,colLast)
clearPrintRepeats()
clearPrintArea()
getNamedRange(name,ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast,scopeId:=-2,ByRefhidden:=0)
setNamedRange(name,rowFirst,rowLast,colFirst,colLast,scopeId:=-2,hidden:=false)
delNamedRange(name,scopeId:=-2)
namedRangeSize()
namedRange(index,ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast,ByRefscopeId:=0,ByRefhidden:=0)
tableSize()
table(index,ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast,ByRefheaderRowCount,ByReftotalsRowCount)
hyperlinkSize()
hyperlink(index,ByRefrowFirst,ByRefrowLast,ByRefcolFirst,ByRefcolLast)
delHyperlink(index)
addHyperlink(hyperlink,rowFirst,rowLast,colFirst,colLast)
autoFilter();筛选
applyFilter()
removeFilter()
name()
setName(name)
protect()
setProtect(protect:=true,password:=0,enhancedProtection:=-1);这个地址函数需要解决
hidden()
setHidden(SheetState:=1)
getTopLeftView(ByRefrow,ByRefcol)
setTopLeftView(row,col)
rightToLeft()
setRightToLeft(rightToLeft:=true)
setAutoFitArea(rowFirst:=0,colFirst:=0,rowLast:=-1,colLast:=-1)
addrToRowCol(addr,ByRefrow,ByRefcol,ByRefrowRelative:=0,ByRefcolRelative:=0)
rowColToAddr(row,col,rowRelative:=true,colRelative:=true)
setTabColor(Color)
setTabRGBColor(red,green,blue)
addIgnoredError(rowFirst,colFirst,rowLast,colLast,IgnoredError)
addDataValidation(type,op,rowFirst,rowLast,colFirst,colLast,value1,value2)
addDataValidationEx(type,op,rowFirst,rowLast,colFirst,colLast,value1,value2,allowBlank:=true,hideDropDown:=false,showInputMessage:=true,showErrorMessage:=true,promptTitle:=0,prompt:=0,errorTitle:=0,error:=0,errorStyle:=0)
addDataValidationDouble(type,op,rowFirst,rowLast,colFirst,colLast,value1,value2)
addDataValidationDoubleEx(type,op,rowFirst,rowLast,colFirst,colLast,value1,value2,allowBlank:=true,hideDropDown:=false,showInputMessage:=true,showErrorMessage:=true,promptTitle:=0,prompt:=0,errorTitle:=0,error:=0,errorStyle:=0)
removeDataValidations()
__Delete()
switchType(value)
switchStrLower(k)


单元格操作
classICell
__New(row,col,parent)
switchthis.parent.cellType(row,col)
switchret.errcode:=this.parent.readError(row,col)
hidden()
copy(rowDst,colDst)
IsNumber(num:="")
Type(str:="")
StrUpper(str:="")
StrLower(str:="")
Integer(Value:="")
Float(str:="")


*/