aardio - libxl库，一个dll操作excel

经常用到excel操作，也有几个现成的库能实现我需要的功能，但用起来总是感觉不顺手。

于是便抽了两天时间，在aaz.libxl库的基础上，按照我的使用习惯进行了修改。

以后再也不用为操作excel发愁啦。

下载地址：http://chengxu.online  →aardio资源下载→libxl.rar

解压缩将文件放于：\lib\godking\libxl

1、大名鼎鼎的libxl，封装为aardio库，便于使用。

2、为了节约劳动力，在aaz.libxl库的基础上进行修改，在此对作者表示感谢。

3、dll版本为4.0.4.0。

4、例程代码效果如下：

一、

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);