教程19国产编程语言CbrotherExcel操作
CBrother提供了一个Excel扩展来操作excle文件,目前支持*.xlsx和*.csv两种格式。 Excel 类
Excel类表示一个Excel文档 import lib/excel var excel = new Excel();
函数
描述
用法
load(path)
加载excel文档,path为绝对路径,返回true加载成功
excel.load("e:/test.xlsx")
excel.load("e:/test.csv")
save(path)
保存excel文档,path为绝对路径,返回true保存成功
excel.save("e:/test.xlsx")
excel.save("e:/test.csv")
getWorksheetCount()
获取文档内表格数量
var cnt = excel.getWorksheetCount()
getWorksheet(name)
根据表格名字获取表格对象,没找到返回null
var sheet = excel.getWorksheet("sheet1")
getWorksheet(index)
根据序号获取表格对象,没找到返回null,序号从0开始
var sheet = excel.getWorksheet(0)
addWorksheet(name)
添加一张表,name为表名,返回添加的表格对象,添加失败返回null
var sheet = excel.addWorksheet("sheet2")
removeWorksheet(name)
根据表格名字删除表格,返回true为删除成功。
excel.removeWorksheet("sheet2")
removeWorksheet(index)
根据序号删除表格,返回true为删除成功,序号从0开始
excel.removeWorksheet(0) ExcelSheet 类
ExcelSheet类表示excel内的一张表格。该对象不能主动创建,只能从Excel类里面获取。
函数
描述
用法
getTotalRows()
获取该表格使用到的最大行数
var rows = excelsheet.getTotalRows()
getTotalCols()
获取该表格使用到的最大列数
var clos = excelsheet.getTotalCols()
getSheetName()
获取本张表格名称
var name = excelsheet.getSheetName()
setSheetName(name)
修改本张表格名称,返回true为修改成功
excelsheet.setSheetName("sheet2")
getCell(name)
根据格子名称获取格子对象,对应格子没有内容返回null
var cell = excelsheet.getCell("A5")
getCell(row,cloName)
根据行数和列名称获取格子对象,row为整数表示行号,从0开始,
cloName为字符串列名称,对应格子没有内容返回null
var cell = excelsheet.getCell(4,"A")
getCell(row,clo)
根据行数和列数获取格子对象,row为整数表示行号,从0开始,
clo为整数列号,从0开始,对应格子没有内容返回null
var cell = excelsheet.getCell(4,0)
addCell(name)
根据格子名称增加格子对象,返回增加的格子对象,出错返回null
var cell = excelsheet.addCell("A5")
addCell(row,cloName)
根据行数和列名称增加格子对象,row为整数表示行号,从0开始,
cloName为字符串列名称,返回增加的格子对象,出错返回null
var cell = excelsheet.addCell(4,"A")
addCell(row,clo)
根据行数和列数增加格子对象,row为整数表示行号,从0开始,
clo为整数列号,从0开始,返回增加的格子对象,出错返回null
var cell = excelsheet.addCell(4,0)
getIndex()
获取本表格序号
var index = excelsheet.getIndex()
setIndex(index)
修改本表格序号,从0开始,修改成功返回true
excelsheet.setIndex(1)
mergeCells(reference_string)
合并单元格
excelsheet.mergeCells("A1:B5")
mergeCells(leftTopCell,rightBottomCell)
合并单元格
excelsheet.mergeCells("A1","B5")
mergeCells(leftRow,leftClo,rightRow,rightClo)
合并单元格
excelsheet.mergeCells(0,0,4,1)
setRowHeight(row,height)
设置行高
excelsheet.setRowHeight(0,60)
setColWidth(col,height)
设置列宽
excelsheet.setColWidth(0,60) ExcelCell 类
ExcelCell类表示表格里的格子。该对象不能主动创建,只能从ExcelSheet类里面获取。
函数
描述
用法
getInt()
获取值,转为整数。
var value = excelCell.getInt()
getDouble()
获取值,转为浮点数。
var value = excelCell.getDouble()
getString()
获取值,转为字符串。
var value = excelCell.getString()
set(value)
设置格子的内容,value可以为字符串、整数、浮点数
excelCell.set(0)
excelCell.set("111")
setFontColor(rgb)
设置格子字体颜色
excelCell.setFontColor("ff0000")
setFontName(name)
设置格子字体
excelCell.setFontName("宋体")
setFontBold(bold)
设置格子字体加粗
excelCell.setFontBold(true)
setFontSize(size)
设置格子字号
excelCell.setFontSize(20)
setBackgroundColor(rgb)
设置格子颜色
excelCell.setBackgroundColor("ffff00")
setTopBorderColor(rgb)
设置格子顶部边框颜色
excelCell.setTopBorderColor("000000")
setTopBorderStyle(style)
设置格子顶部边框样式,样式定义在lib/excel.cb里
excelCell.setTopBorderStyle(BORDER_STYLE_DASHED)
setBottomBorderColor(rgb)
设置格子底部边框颜色
excelCell.setBottomBorderColor("000000")
setBottomBorderStyle(style)
设置格子底部边框样式,样式定义在lib/excel.cb里
excelCell.setBottomBorderStyle(BORDER_STYLE_THIN)
setLeftBorderColor(rgb)
设置格子左侧边框颜色
excelCell.setLeftBorderColor("000000")
setLeftBorderStyle(style)
设置格子左侧边框样式,样式定义在lib/excel.cb里
excelCell.setLeftBorderStyle(BORDER_STYLE_SLANT_DASHDOT)
setRightBorderColor(rgb)
设置格子右侧边框颜色
excelCell.setRightBorderColor("000000")
setRightBorderStyle(style)
设置格子右侧边框样式,样式定义在lib/excel.cb里
excelCell.setRightBorderStyle(BORDER_STYLE_DOTTED)
setDiagonalBorderColor(rgb)
设置格子斜线颜色
excelCell.setDiagonalBorderColor("000000")
setDiagonalBorderStyle(style)
设置格子斜线样式,样式定义在lib/excel.cb里
excelCell.setDiagonalBorderStyle(BORDER_STYLE_DOUBLE)
setDiagonalBorderShow(up,down)
设置格子斜线具体显示那条线
excelCell.setDiagonalBorderShow(true,true) 创建一个Excel例子:import lib/excel function main(parm) { var excel = new Excel(); var excelsheet = excel.addWorksheet("222"); var cell = excelsheet.addCell("A10"); cell.set("aaa"); cell = excelsheet.addCell(5,"B"); cell.set(100); cell = excelsheet.addCell(6,7); cell.set(200.0); excel.addWorksheet("333"); excel.save(GetRoot() + "222.xlsx"); excel.save(GetRoot() + "222.csv"); }
运行后会在脚本工作路径旁边生成222.xlsx和222.csv
打开222.xlsx查看如下图,两张表都在,因为xlsx支持多张表
打开222.csv查看如下图,只有一张表,因为csv不支持多张表
读取Excel例子:import CBExcel.code function main(parm) { var excel = new Excel(); var path = GetRoot() + "222.xlsx"; if(!excel.load(path)) { print "load err:" + path; return; } var sheetCnt = excel.getWorksheetCount(); print "sheetCnt:" + sheetCnt; var excelsheet = excel.getWorksheet(0);//or excel.getWorksheet("222"); var totalRows = excelsheet.getTotalRows(); var totalClos = excelsheet.getTotalCols(); print "totalRows:" + totalRows; print "totalClos:" + totalClos; var cell = excelsheet.getCell("A10");//or getCell(9,0); or getCell(9,"A"); print cell.getString(); cell = excelsheet.getCell(5,"b"); print cell.getInt(); print cell.getString(); cell = excelsheet.getCell(6,7); print cell.getDouble(); print cell.getString(); }
运行结果如下: sheetCnt:2 totalRows:10 totalClos:7 aaa 100 100 200.000000 200.000000
同样的方法也可以读取*.csv 用Excel样式画一个机器人:
先来看一下效果
再来看一下代码 import lib/excel function main(parm) { var excel = new Excel(); var path = GetRoot() + "excelrobot.xlsx"; var excelsheet = excel.addWorksheet("robot"); excelsheet.setColWidth("D",15); excelsheet.setColWidth("F",3); excelsheet.setColWidth("H",15); excelsheet.setRowHeight(5,81); excelsheet.setRowHeight(6,29); //head var cell = excelsheet.addCell("E6"); cell.set("."); cell.setFontBold(true); cell.setFontSize(36); cell.setAlignmentHorizontal(ALIGNMENT_HORIZONTAL_RIGHT); cell.setAlignmentVertical(ALIGNMENT_VERTICAL_TOP); cell.setBackgroundColor("00B050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell.setLeftBorderStyle(BORDER_STYLE_THIN); cell = excelsheet.addCell("F6"); cell.set("_"); cell.setFontBold(true); cell.setFontSize(72); cell.setAlignmentHorizontal(ALIGNMENT_HORIZONTAL_CENTER); cell.setAlignmentVertical(ALIGNMENT_VERTICAL_BOTTOM); cell.setBackgroundColor("00B050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell = excelsheet.addCell("G6"); cell.set("."); cell.setFontBold(true); cell.setFontSize(36); cell.setAlignmentVertical(ALIGNMENT_VERTICAL_TOP); cell.setBackgroundColor("00B050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell.setRightBorderStyle(BORDER_STYLE_THIN); //arm var cell = excelsheet.addCell("B7"); cell.setBackgroundColor("92D050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell.setLeftBorderStyle(BORDER_STYLE_THIN); cell = excelsheet.addCell("C7"); cell.setBackgroundColor("92D050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell.setBottomBorderStyle(BORDER_STYLE_THIN); excelsheet.mergeCells("B8:B16"); cell = excelsheet.addCell("B8"); cell.setBackgroundColor("92D050"); cell.setLeftBorderColor("000000"); cell.setLeftBorderStyle(BORDER_STYLE_THIN); cell.setRightBorderStyle(BORDER_STYLE_THIN); cell = excelsheet.addCell("I7"); cell.setBackgroundColor("92D050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell.setBottomBorderStyle(BORDER_STYLE_THIN); cell = excelsheet.addCell("J7"); cell.setBackgroundColor("92D050"); cell.setTopBorderColor("000000"); cell.setTopBorderStyle(BORDER_STYLE_THIN); cell.setRightBorderStyle(BORDER_STYLE_THIN); excelsheet.mergeCells("J8:J16"); cell = excelsheet.addCell("J8"); cell.setBackgroundColor("92D050"); for (var i = 8; i <= 16 ; i++) { cell = excelsheet.addCell("B" + i); cell.setLeftBorderColor("000000"); cell.setLeftBorderStyle(BORDER_STYLE_THIN); cell.setRightBorderStyle(BORDER_STYLE_THIN); cell.setBottomBorderStyle(BORDER_STYLE_THIN); cell = excelsheet.addCell("J" + i); cell.setLeftBorderColor("000000"); cell.setLeftBorderStyle(BORDER_STYLE_THIN); cell.setRightBorderStyle(BORDER_STYLE_THIN); cell.setBottomBorderStyle(BORDER_STYLE_THIN); } //body excelsheet.mergeCells("D7:H21"); cell = excelsheet.addCell("D7"); cell.set("CBrother"); cell.setFontSize(72); cell.setFontName("宋体"); cell.setFontColor("31869B"); cell.setBackgroundColor("00B0F0"); cell.setAlignmentHorizontal(ALIGNMENT_HORIZONTAL_CENTER); cell.setAlignmentVertical(ALIGNMENT_VERTICAL_CENTER); var arr = ["D","E","F","G","H"]; for (var i = 7; i <= 21 ; i++) { for (var j = 0; j < arr.size() ; j++) { cell = excelsheet.addCell(arr[j] + i); cell.setLeftBorderColor("000000"); cell.setLeftBorderStyle(BORDER_STYLE_DOUBLE); cell.setRightBorderStyle(BORDER_STYLE_DOUBLE); cell.setTopBorderStyle(BORDER_STYLE_DOUBLE); cell.setBottomBorderStyle(BORDER_STYLE_DOUBLE); } } //leg excelsheet.mergeCells("E22:E31"); cell = excelsheet.addCell("E22"); cell.setBackgroundColor("FFFF00"); cell.setDiagonalBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setDiagonalBorderShow(true,false); excelsheet.mergeCells("G22:G31"); cell = excelsheet.addCell("G22"); cell.setBackgroundColor("FFFF00"); cell.setDiagonalBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setDiagonalBorderShow(false,true); for (var i = 22; i <= 31 ; i++) { cell = excelsheet.addCell("E" + i); cell.setLeftBorderColor("000000"); cell.setLeftBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setRightBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setTopBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setBottomBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell = excelsheet.addCell("G" + i); cell.setLeftBorderColor("000000"); cell.setLeftBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setRightBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setTopBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); cell.setBottomBorderStyle(BORDER_STYLE_MEDIUM_DASHDOT); } excel.save(path); }