里克资源网

里克资源网

「教程19」国产编程语言Cbrother Excel 操作

yoelike 0 10 次浏览 未分类

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);
}
原文链接：，转发请注明来源！
里克资源网 › 未分类 › 「教程19」国产编程语言Cbrother Excel 操作