经常用到excel操作，也有几个现成的库能实现我需要的功能，但用起来总是感觉不顺手。
于是便在aaz.libxl库的基础上，按照我的使用习惯进行了修改。
经过多次升级完善，常用的功能基本都封装了。
以后再也不用为操作excel发愁啦。
下载地址：http://chengxu.online/show.asp?softid=194
下载其中的文件：libxl.rar ，并解压缩到：\lib\godking\ 目录。
1、大名鼎鼎的libxl，封装为aardio库，便于使用。
2、为了节约劳动力，以aaz.libxl库为基础进行了重构，在此对作者表示感谢。
3、例程代码效果如下：
一、自定义数字格式
import godking.libxl;
var book = godking.libxl.new("d:\custom.xls","各种数字格式")
var sheet = book.sheet();
var format = {};
var numformat = {
"0.0";
"0.00";
"0.000";
"0.0000 元";
"#,###.00 $";
"#,###.00 $[Black][<1000];#,###.00 $[Red][>=1000]";
"合计 ##,###.00 元"
}
for(i=1;#numformat;1){
format[i] = book.addFormat();
format[i].numFormat = book.addNumFormat(numformat[i])
// 以上两句可以合并为一句实现，如下：
// format[i] = book.addFormat({ numFormat = book.addNumFormat(numformat[i]) });
}
sheet.setValue( 1, 1, 25.718, format[1] )
sheet.setValue( 2, 1, 25.718, format[2] )
sheet.setValue( 3, 1, 25.718, format[3] )
sheet.setValue( 4, 1, 25.718, format[4] )
sheet.setValue( 5, 1, 1800.5, format[5] )
sheet.setValue( 6, 1, 500 , format[6] )
sheet.setValue( 7, 1, 1600 , format[6] )
sheet.setValue( 8, 1, "=SUM(A1:A7)", format[7] )
sheet.setCol( 1,1,20 )
book.save()
book.release()二、写入各种类型的数据
import godking.libxl;
//新建文件
var book = godking.libxl.new("d:\example.xls");
var sheet = book.sheet();
//按照指定的单元格格式写入数据
sheet.setCellStr(1, 1, "我是字符串1");
sheet.setCellNum(2, 1, 3.14);
sheet.setCellDate(3,1, "2022-11-17");
sheet.setCellBool(4,1, true);
sheet.setCellBlank(5,1);
//自动识别单元格格式写入数据，支持文本、数值、日期、逻辑、公式、空
sheet.setValue(1,2, "我是字符串2");
sheet.setValue(2,2, 3.1415926);
sheet.setValue(3,2, ..time("2022-12-18"));
sheet.setValue(4,2, false);
sheet.setValue(5,2);
//批量获取、设置单元格内容(批量复制内容，不带格式)
var t = sheet.getValues(1,1,5,2); // 复制(1,1)到(5,2)范围内的所有内容
sheet.setValues(1,3,t); // 粘贴到 (1,3) 单元格，向右、下一直填充到(5,4)单元格
//测试清空单元格内容
sheet.setValue(1,3); //清空(1,3)单元格内容
//设置列宽
sheet.setCol(1,4,12); //设置第1列到第4列的列宽为12
//批量设置单元格内容
sheet.setTable(1,5,{
colCount = 2; // 避免第一行的null值影响获取列数
{"姓名",null}
{"年龄",18}
{null,`=(F2+1)&"虚岁"`}
{..time("2022-10-1"),"国庆节"}
{"婚否",false}
});
//保存文件
book.save()
book.release()
三、设置单元格字体、格式
import godking.libxl;
var book = godking.libxl.new("d:\format.xls","格式演示")
var sheet = book.sheet();
var font = book.addFont()
font.config = {
name = "宋体",
size = 18,
italic = false,
strikeOut = false,
color = 51,
bold = false,
script = false,
underline = false,
}
var format = book.addFormat()
format.config = {
alignH = 2;
border = 12;
borderColor = 2;
font = font;
fillPattern=1,
patternForegroundColor=30,
patternBackgroundColor=30,
}
sheet.setValue( 2, 1, "格式演示Format", format )
sheet.setCol( 1, 1, 25 )
book.save( )
book.release()四、填写收据
import godking.libxl;
var book = godking.libxl.new("d:\invoice.xls","发票例程" );
var sheet = book.sheet()
var boldFont = book.addFont({bold = true});
var titleFont = book.addFont({name = "黑体",size = 16});
var titleFormat = book.addFormat({font = titleFont,alignH = 2});
var headerFormat = book.addFormat({ alignH = 2/*_ALIGNH_CENTER*/,
border = 1/*_BORDERSTYLE_THIN*/,
font = boldFont;
fillPattern = 1 /*_FILLPATTERN_SOLID*/,
patternForegroundColor = 47 /*COLOR_TAN*/
});
var descriptionFormat = book.addFormat({borderLeft = 1 /*BORDERSTYLE_THIN*/});
var amountFormat = book.addFormat({ numFormat = 5,
borderLeft = 1,
borderRight = 1
});
var totalLabelFormat = book.addFormat({ borderTop = 1,
alignH = 3,
font = boldFont
});
var totalFormat = book.addFormat({ numFormat = 5,
border = 1,
font = boldFont,
fillPattern = 1,
patternForegroundColor = 13});
var signatureFormat = book.addFormat({ alignH = 2,
borderTop = 10});
sheet.setMerge(2,1,2,2)
sheet.setValue(2, 1, "收款收据", titleFormat)
sheet.setValue(4, 1, "姓名: 张三")
sheet.setValue(5, 1, "地址: 中国山东")
sheet.setValue(6, 1, "开票时间："++..tostring(..time()))
sheet.setValue(7, 1, "品名", headerFormat)
sheet.setValue(7, 2, "数量", headerFormat)
sheet.setValue( 8, 1, "铅笔", descriptionFormat);
sheet.setValue(8, 2, 85, amountFormat);
sheet.setValue( 9, 1, "衬衫", descriptionFormat);
sheet.setValue(9, 2, 150, amountFormat);
sheet.setValue( 10, 1, "茶杯", descriptionFormat);
sheet.setValue(10, 2, 45, amountFormat);
sheet.setValue( 11, 1, "合计:", totalLabelFormat);
sheet.setValue(11, 2, "=SUM(B8:B10)", totalLabelFormat);
sheet.setValue(14, 2, "签名", signatureFormat);
sheet.setCol( 1, 1, 40, null, 0);
sheet.setCol(2, 2, 15, , 0);
book.save()
book.release()五、与sql查询数据无缝衔接：
import console;
import sqlServer
var s = sqlServer(
["Data Source"] = "192.168.1.18,1433";
["Database"] = "fang";
["User ID"] = "fangs";
["Password"] = "fangs1234";
)
var t = s.getTable("select * from fangs")
import godking.libxl
var book = godking.libxl.new(".xls","Sheet1")
var sheet = book.sheet()
sheet.setTable(1,1,t.fields); //填充表头
// 或 sheet.setRowValue(1,1,t.fields); //填充表头
sheet.setTable(2,1,t); //填充数据
book.save("d:\t.xls")
console.pause(true);
六、筛选、排序
//筛选器例程
import godking.libxl;
// 注意：xls格式不支持筛选功能
var book = godking.libxl.new("d:\filter.xlsx","筛选器例程")
var sheet = book.sheet();
sheet.setValue(1, 1,"国家");
sheet.setValue(1, 2,"交通死亡率");
sheet.setValue(1, 3,"吸烟死亡率");
sheet.setValue(1, 4,"自杀死亡率");
sheet.setValue(3, 1,"USA"); sheet.setValue(4, 1,"USA");
sheet.setValue(3, 2, 64); sheet.setValue(4, 2, 94);
sheet.setValue(3, 3, 69); sheet.setValue(4, 3, 55);
sheet.setValue(3, 4, 49); sheet.setValue(4, 4, 64);
sheet.setValue(5, 1,"USA"); sheet.setValue(6, 1,"USA");
sheet.setValue(5, 2, 88); sheet.setValue(6, 2, 93);
sheet.setValue(5, 3, 46); sheet.setValue(6, 3, 54);
sheet.setValue(5, 4, 55); sheet.setValue(6, 4, 50);
sheet.setValue(7, 1,"USA"); sheet.setValue(8, 1,"Gobon");
sheet.setValue(7, 2, 86); sheet.setValue(8, 2, 75);
sheet.setValue(7, 3, 47); sheet.setValue(8, 3, 52);
sheet.setValue(7, 4, 69); sheet.setValue(8, 4, 71);
sheet.setValue(9, 1,"Greece"); sheet.setValue(2, 1,"Japan");
sheet.setValue(9, 2, 67); sheet.setValue(2, 2, 91);
sheet.setValue(9, 3, 23); sheet.setValue(2, 3, 57);
sheet.setValue(9, 4, 87); sheet.setValue(2, 4, 36);
var filter = sheet.filter()
//添加排序方式
filter.setSort(1,false) // 按第1列顺序排序
filter.addSort(2,true) // 按第2列逆序排序
// 应用筛选和排序设置
filter.apply();
book.save()
book.release()
import win ; win.delay(500) ;
import process;
process("d:\filter.xlsx");注意：多列排序在4.2版本中有问题（4.3版本已解决），测试代码如下：
//筛选器例程
import godking.libxl;
// 注意：xls格式不支持筛选功能
var book = godking.libxl.new("d:\filter.xlsx","筛选器例程1")
var sheet = book.sheet();
sheet.setValue(1, 1,"国家");
sheet.setValue(1, 2,"交通死亡率");
sheet.setValue(1, 3,"吸烟死亡率");
sheet.setValue(1, 4,"自杀死亡率");
sheet.setValue(3, 1,"USA"); sheet.setValue(4, 1,"USA");
sheet.setValue(3, 2, 64); sheet.setValue(4, 2, 94);
sheet.setValue(3, 3, 69); sheet.setValue(4, 3, 55);
sheet.setValue(3, 4, 49); sheet.setValue(4, 4, 64);
sheet.setValue(5, 1,"USA"); sheet.setValue(6, 1,"USA");
sheet.setValue(5, 2, 88); sheet.setValue(6, 2, 93);
sheet.setValue(5, 3, 46); sheet.setValue(6, 3, 54);
sheet.setValue(5, 4, 55); sheet.setValue(6, 4, 50);
sheet.setValue(7, 1,"USA"); sheet.setValue(8, 1,"Gobon");
sheet.setValue(7, 2, 86); sheet.setValue(8, 2, 75);
sheet.setValue(7, 3, 47); sheet.setValue(8, 3, 52);
sheet.setValue(7, 4, 69); sheet.setValue(8, 4, 71);
sheet.setValue(9, 1,"Greece"); sheet.setValue(2, 1,"Japan");
sheet.setValue(9, 2, 67); sheet.setValue(2, 2, 91);
sheet.setValue(9, 3, 23); sheet.setValue(2, 3, 57);
sheet.setValue(9, 4, 87); sheet.setValue(2, 4, 36);
//添加排序方式
var filter = sheet.filter()
filter.setSort(1,false) // 按第1列顺序排序
filter.addSort(2,true) // 按第2列逆序排序
filter.apply();
book.save()
book.release()
import win ; win.delay(500) ;
import process;
process("d:\filter.xlsx");七、条件格式
示例1：
import godking.libxl
var book = godking.libxl(".xlsx","Sheet1")
var sheet =book.sheet()
// 填充内容
for(i=1;10;1){
for(n=1;10;1){
sheet.setCellStr(i,n,tostring(i*100+n));
}
}
// 不包含5的变为红色
var cFormat = book.addConditionalFormat()
cFormat.font.bold=true
cFormat.font.color = 10/*_LIBXL_COLOR_RED*/
var cf = sheet.addConditionalFormatting()
cf.addRange(1,10,1,10);
cf.addRule(9/*_LIBXL_CFORMAT_NOTCONTAINSTEXT*/ , cFormat, "5" )
// 以2开头的变为绿色
var cFormat = book.addConditionalFormat()
cFormat.font.bold=true
cFormat.font.color = 17/*_LIBXL_COLOR_GREEN*/
var cf = sheet.addConditionalFormatting()
cf.addRange(1,10,1,10);
cf.addRule(0/*_LIBXL_CFORMAT_BEGINWITH*/ , cFormat, "2" )
// 保存并查看
book.save("d:\ConditionalFormatting.xlsx")
import process
process("d:\ConditionalFormatting.xlsx")
示例二：
// 大于等于301，且小于等于601的，变为红色
var cFormat = book.addConditionalFormat()
cFormat.font.color = 10/*_LIBXL_COLOR_RED*/
var cf = sheet.addConditionalFormatting()
cf.addNumRule(6/*_LIBXL_CFOPERATOR_BETWEEN*/ , cFormat, 301, 601, false)
cf.addRange(1,4,1,4); //创建多区域
cf.addRange(5,10,5,10); //创建多区域示例三：
var cf = sheet.addConditionalFormatting()
cf.addRange(2,8,2,2);
cf.add2ColorRule(12/*_LIBXL_COLOR_BLUE*/,17/*_LIBXL_COLOR_GREEN*/, 0/*_LIBXL_CFVO_MIN*/, 0, 1/*_LIBXL_CFVO_MAX*/, 0, false)八、富文本
import godking.libxl
var b = godking.libxl(".xlsx","Sheet1")
var s = b.sheet()
var r = b.addRichString()
r.addText("哈哈", r.addFont({
/*添加新字体，以便与addText()方法一起使用。返回：字体对象。*/
initFont = null, /*参考字体*/
name = "宋体", /*字体名称*/
size = 10, /*字体大小*/
italic = null, /*倾斜 true,false*/
strikeOut = null, /*删除线 true,false*/
color = 0xFF0000, /*颜色 _LIBXL_COLOR_*/
bold = null, /*加粗 true,false*/
script = null, /*上下标 _LIBXL_SCRIPT_*/
underline = null, /*下划线 _LIBXL_UNDERLINE_*/
}))
r.addText("呵呵", r.addFont({
/*添加新字体，以便与addText()方法一起使用。返回：字体对象。*/
initFont = null, /*参考字体*/
name = "黑体", /*字体名称*/
size = 17, /*字体大小*/
italic = null, /*倾斜 true,false*/
strikeOut = null, /*删除线 true,false*/
color = 0x0000FF, /*颜色 _LIBXL_COLOR_*/
bold = true, /*加粗 true,false*/
script = 0/*_LIBXL_SCRIPT_SUPER*/ , /*上下标 _LIBXL_SCRIPT_*/
underline = null, /*下划线 _LIBXL_UNDERLINE_*/
}))
s.setCellRichStr(1,1,r)
b.save("C:\Users\Administrator\Desktop\a.xlsx")其他注意事项：
1、调用sheet.addConditionalFormatting()添加条件格式后，保存的文件，再用excel打开，可能会提示文件有问题：
出现这种问题的原因，可能是条件格式设置不正确，特别要注意创建条件格式的参数类型，是【文本型】还是【数值型】。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
