devbox
数据科学灵魂
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

aardio - 【库】libxl库，一个dll操作excel

作者：数据科学灵魂 | 2024-01-31 15:13:32

赞

踩

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.1.0.2，但部分功能仍存在问题，见本文最后所述，好在这些功能平时很少用。

4、例程代码效果如下：

一、自定义数字格式

2162a082b53342a792c098c16eda5845.png

  1. import godking.libxl;
  2.  
  3. var book = godking.libxl.new("d:\custom.xls","各种数字格式")
  4. var sheet = book.sheet();
  5. var format = {};
  6. var numformat = {
  7.     "0.0";
  8.     "0.00";
  9.     "0.000";
  10.     "0.0000 元";
  11.     "#,###.00 $";
  12.     "#,###.00 $[Black][<1000];#,###.00 $[Red][>=1000]";
  13.     "合计 ##,###.00 元"
  14. }
  15. for(i=1;#numformat;1){
  16. 	format[i] = book.addFormat();
  17. 	format[i].numFormat = book.addNumFormat(numformat[i])
  18. 	// 以上两句可以合并为一句实现，如下：
  19. 	// format[i] = book.addFormat({ numFormat = book.addNumFormat(numformat[i]) });
  20. }
  21. sheet.setValue( 1, 1, 25.718, format[1] )
  22. sheet.setValue( 2, 1, 25.718, format[2] )
  23. sheet.setValue( 3, 1, 25.718, format[3] )
  24. sheet.setValue( 4, 1, 25.718, format[4] )
  25. sheet.setValue( 5, 1, 1800.5, format[5] )
  26. sheet.setValue( 6, 1, 500   , format[6] )
  27. sheet.setValue( 7, 1, 1600  , format[6] )
  28.  
  29. sheet.setValue( 8, 1, "=SUM(A1:A7)", format[7] )
  30. sheet.setCol( 1,1,20 )
  31.  
  32. book.save()
  33. book.release()

 二、写入各种类型的数据

967af24a00e74f6aa72a860586f4d5a8.png

  1. import godking.libxl;
  2.  
  3. //新建文件
  4. var book = godking.libxl.new("d:\example.xls");
  5. var sheet = book.sheet();
  6.  
  7. //按照指定的单元格格式写入数据
  8. sheet.setCellStr(1,	1,	"我是字符串1");
  9. sheet.setCellNum(2,	1,	3.14);
  10. sheet.setCellDate(3,1,	"2022-11-17");
  11. sheet.setCellBool(4,1,	true);
  12. sheet.setCellBlank(5,1);
  13.  
  14. //自动识别单元格格式写入数据，支持文本、数值、日期、逻辑、公式、空
  15. sheet.setValue(1,2,	"我是字符串2");
  16. sheet.setValue(2,2,	3.1415926);
  17. sheet.setValue(3,2,	..time("2022-12-18"));
  18. sheet.setValue(4,2,	false);
  19. sheet.setValue(5,2);
  20.  
  21. //批量获取、设置单元格内容(批量复制内容，不带格式)
  22. var t = sheet.getValues(1,1,5,2);  // 复制(1,1)到(5,2)范围内的所有内容
  23. sheet.setValues(1,3,t); // 粘贴到 (1,3) 单元格，向右、下一直填充到(5,4)单元格
  24.  
  25. //测试清空单元格内容
  26. sheet.setValue(1,3); //清空(1,3)单元格内容
  27.  
  28. //设置列宽
  29. sheet.setCol(1,4,12); //设置第1列到第4列的列宽为12
  30.  
  31. //批量设置单元格内容
  32. sheet.setTable(1,5,{
  33.  		colCount = 2; // 避免第一行的null值影响获取列数
  34. 		{"姓名",null}
  35. 		{"年龄",18}
  36. 		{null,`=(F2+1)&"虚岁"`}
  37. 		{..time("2022-10-1"),"国庆节"}
  38. 		{"婚否",false}
  39. 	});
  40.  
  41. //保存文件
  42. book.save()
  43. book.release()

 三、设置单元格字体、格式

c2cd330ea40e48769ad09bfd039027fe.png

  1. import godking.libxl;
  2.  
  3. var book = godking.libxl.new("d:\format.xls","格式演示")
  4. var sheet = book.sheet();
  5.  
  6. var font = book.addFont()
  7. font.config = {
  8. 	name = "宋体",
  9. 	size = 18,
  10. 	italic = false,
  11. 	strikeOut = false,
  12. 	color = 51,
  13. 	bold = false,
  14. 	script = false,
  15. 	underline = false,
  16. }
  17.  
  18. var format = book.addFormat()
  19. format.config = {
  20.     alignH = 2;
  21.     border = 12;
  22.     borderColor = 2;
  23.     font = font;
  24. 	fillPattern=1,
  25. 	patternForegroundColor=30,
  26. 	patternBackgroundColor=30,
  27. }
  28.  
  29. sheet.setValue( 2, 1, "格式演示Format", format )
  30. sheet.setCol( 1, 1, 25 ) 
  31.  
  32. book.save( )
  33. book.release()

 四、填写收据

641b6db5d75e4626b0003e94f3e897ad.png

  1. import godking.libxl;
  2.  
  3. var book = godking.libxl.new("d:\invoice.xls","发票例程" );
  4. var sheet = book.sheet()
  5.  
  6. var boldFont = book.addFont({bold = true});
  7.  
  8. var titleFont = book.addFont({name = "黑体",size = 16});
  9.  
  10. var titleFormat = book.addFormat({font = titleFont,alignH = 2});
  11.  
  12. var headerFormat = book.addFormat({ alignH = 2/*_ALIGNH_CENTER*/,
  13. 									border = 1/*_BORDERSTYLE_THIN*/,
  14. 									font = boldFont;
  15. 									fillPattern = 1 /*_FILLPATTERN_SOLID*/,
  16. 									patternForegroundColor = 47 /*COLOR_TAN*/
  17. 									});
  18.  
  19. var descriptionFormat = book.addFormat({borderLeft = 1 /*BORDERSTYLE_THIN*/});
  20.  
  21. var amountFormat = book.addFormat({	numFormat = 5,
  22. 									borderLeft = 1,
  23. 									borderRight = 1
  24. 									});
  25.  
  26. var totalLabelFormat = book.addFormat({	borderTop = 1,
  27. 										alignH = 3,
  28. 										font = boldFont
  29. 										});
  30. 										
  31. var totalFormat = book.addFormat({	numFormat = 5,
  32. 									border = 1,
  33. 									font = boldFont,
  34. 									fillPattern = 1,
  35. 									patternForegroundColor = 13});
  36.  
  37. var signatureFormat = book.addFormat({	alignH = 2,
  38. 										borderTop = 10});
  39.  
  40. sheet.setMerge(2,1,2,2)
  41. sheet.setValue(2, 1, "收款收据", titleFormat)
  42.  
  43. sheet.setValue(4, 1, "姓名: 张三")
  44. sheet.setValue(5, 1, "地址: 中国山东")
  45. sheet.setValue(6, 1, "开票时间："++..tostring(..time()))
  46.  
  47. sheet.setValue(7, 1, "品名", headerFormat)
  48. sheet.setValue(7, 2, "数量", headerFormat)
  49.  
  50. sheet.setValue( 8, 1, "铅笔", descriptionFormat);
  51. sheet.setValue(8, 2, 85, amountFormat);
  52. sheet.setValue( 9, 1, "衬衫", descriptionFormat);
  53. sheet.setValue(9, 2, 150, amountFormat);
  54. sheet.setValue( 10, 1, "茶杯", descriptionFormat);
  55. sheet.setValue(10, 2, 45, amountFormat);
  56.  
  57. sheet.setValue( 11, 1, "合计:", totalLabelFormat);
  58. sheet.setValue(11, 2, "=SUM(B8:B10)", totalLabelFormat);
  59.  
  60. sheet.setValue(14, 2, "签名", signatureFormat);
  61.  
  62. sheet.setCol( 1, 1, 40, null, 0);
  63. sheet.setCol(2, 2, 15, , 0);
  64.  
  65. book.save()
  66. book.release()

五、与sql查询数据无缝衔接：

64bc919823b940848bfbb30317d8218b.png

  1. import console; 
  2. import sqlServer
  3. var s = sqlServer( 
  4. 	["Data Source"] = "192.168.1.18,1433";
  5. 	["Database"] = "fang";
  6. 	["User ID"] = "fangs";
  7. 	["Password"] = "fangs1234"; 
  8. )
  9. var t = s.getTable("select * from fangs")
  10.  
  11. import godking.libxl
  12. var book = godking.libxl.new(".xls","Sheet1")
  13. var sheet = book.sheet()
  14. sheet.setTable(1,1,t.fields); //填充表头
  15. // 或 sheet.setRowValue(1,1,t.fields);  //填充表头
  16. sheet.setTable(2,1,t); //填充数据
  17. book.save("d:\t.xls")
  18.  
  19. console.pause(true);

六、筛选、排序

7f8681fc0ce44f8db6fbfbe854586cef.png

  1. //筛选器例程
  2. import godking.libxl;
  3. // 注意：xls格式不支持筛选功能
  4. var book = godking.libxl.new("d:\filter.xlsx","筛选器例程")
  5. var sheet = book.sheet();
  6.  
  7.     sheet.setValue(2, 1,"国家");
  8.     sheet.setValue(2, 2,"交通死亡率");
  9.     sheet.setValue(2, 3,"吸烟死亡率");
  10.     sheet.setValue(2, 4,"自杀死亡率");
  11.  
  12.     sheet.setValue(3, 1,"USA");   	  sheet.setValue(4, 1,"Greenland"); 
  13.     sheet.setValue(3, 2, 64);         sheet.setValue(4, 2, 94);
  14.     sheet.setValue(3, 3, 69);         sheet.setValue(4, 3, 55);
  15.     sheet.setValue(3, 4, 49);         sheet.setValue(4, 4, 64);
  16.  
  17.     sheet.setValue(5, 1,"Germany");   sheet.setValue(6, 1,"Switzerland");
  18.     sheet.setValue(5, 2, 88);         sheet.setValue(6, 2, 93); 
  19.     sheet.setValue(5, 3, 46);         sheet.setValue(6, 3, 54);
  20.     sheet.setValue(5, 4, 55);         sheet.setValue(6, 4, 50);
  21.  
  22.     sheet.setValue(7, 1,"Spain");     sheet.setValue(8, 1,"Gobon"); 
  23.     sheet.setValue(7, 2, 86);         sheet.setValue(8, 2, 75); 
  24.     sheet.setValue(7, 3, 47);         sheet.setValue(8, 3, 52);
  25.     sheet.setValue(7, 4, 69);         sheet.setValue(8, 4, 71);
  26.  
  27.     sheet.setValue(9, 1,"Greece");    sheet.setValue(10, 1,"Japan");
  28.     sheet.setValue(9, 2, 67);         sheet.setValue(10, 2, 91);
  29.     sheet.setValue(9, 3, 23);         sheet.setValue(10, 3, 57);
  30.     sheet.setValue(9, 4, 87);         sheet.setValue(10, 4, 36);
  31.  
  32. var filter = sheet.filter()
  33.  
  34. //添加筛选条件1：第1列为"G"开头
  35. var col1 = filter.filterColumn(1)
  36. col1.addFilter("G*")
  37.  
  38. //添加筛选条件2：第2列数值>50且<90
  39. var col2 = filter.filterColumn(2)
  40. col2.setCustomFilter(">",50,"<",90,"and")
  41.  
  42. //添加筛选条件3：第3列数值>20且<50
  43. var col3 = filter.filterColumn(3)
  44. col3.setCustomFilter(">",20,"<",50,"and")
  45.  
  46. //添加排序方式
  47. filter.setSort(2,true) // 按第2列逆序排序
  48.  
  49. // 应用筛选和排序设置
  50. filter.apply()
  51.  
  52. book.save()
  53. book.release()
  54.  
  55. import win ; win.delay(500) ;
  56. import process
  57. process.execute("d:\filter.xlsx")

七、条件格式

示例1：

  1. import godking.libxl
  2. var book = godking.libxl(".xlsx","Sheet1")
  3. var sheet =book.sheet()
  4.  
  5. // 填充内容
  6. for(i=1;10;1){
  7. 	for(n=1;10;1){
  8. 		sheet.setCellStr(i,n,tostring(i*100+n));
  9. 	}
  10. }
  11.  
  12. // 不包含5的变为红色
  13. var cFormat = book.addConditionalFormat()
  14. cFormat.font.bold=true
  15. cFormat.font.color = 10/*_LIBXL_COLOR_RED*/
  16.  
  17. var cf = sheet.addConditionalFormatting()
  18. cf.addRange(1,10,1,10);
  19. cf.addRule(9/*_LIBXL_CFORMAT_NOTCONTAINSTEXT*/ , cFormat, "5" ) 
  20.  
  21. // 以2开头的变为绿色
  22. var cFormat = book.addConditionalFormat()
  23. cFormat.font.bold=true
  24. cFormat.font.color = 17/*_LIBXL_COLOR_GREEN*/
  25.  
  26. var cf = sheet.addConditionalFormatting()
  27. cf.addRange(1,10,1,10);
  28. cf.addRule(0/*_LIBXL_CFORMAT_BEGINWITH*/ , cFormat, "2" ) 
  29.  
  30. // 保存并查看
  31. book.save("d:\ConditionalFormatting.xlsx")
  32. import process
  33. process("d:\ConditionalFormatting.xlsx")

示例二： 

  1. // 大于等于301，且小于等于601的，变为红色
  2. var cFormat = book.addConditionalFormat()
  3. cFormat.font.color = 10/*_LIBXL_COLOR_RED*/
  4.  
  5. var cf = sheet.addConditionalFormatting()
  6. cf.addNumRule(6/*_LIBXL_CFOPERATOR_BETWEEN*/ , cFormat, 301, 601, false)
  7. cf.addRange(1,4,1,4); //创建多区域
  8. cf.addRange(5,10,5,10); //创建多区域

示例三：

  1. var cf = sheet.addConditionalFormatting()
  2. cf.addRange(2,8,2,2);
  3. cf.add2ColorRule(12/*_LIBXL_COLOR_BLUE*/,17/*_LIBXL_COLOR_GREEN*/,  0/*_LIBXL_CFVO_MIN*/, 0, 1/*_LIBXL_CFVO_MAX*/, 0, false)

八、富文本

  1. import godking.libxl
  2. var b = godking.libxl(".xlsx","Sheet1")
  3. var s = b.sheet()
  4. var r = b.addRichString()
  5. r.addText("哈哈", r.addFont({
  6. 	/*添加新字体，以便与addText()方法一起使用。返回：字体对象。*/
  7. 	initFont = null, /*参考字体*/
  8. 	name = "宋体",	/*字体名称*/
  9. 	size = 10,		/*字体大小*/
  10. 	italic = null,	/*倾斜 true,false*/
  11. 	strikeOut = null,	/*删除线 true,false*/
  12. 	color = 0xFF0000,	/*颜色 _LIBXL_COLOR_*/
  13. 	bold = null,	/*加粗 true,false*/
  14. 	script = null,	/*上下标 _LIBXL_SCRIPT_*/
  15. 	underline = null,	/*下划线 _LIBXL_UNDERLINE_*/
  16. }))
  17. r.addText("呵呵", r.addFont({
  18. 	/*添加新字体，以便与addText()方法一起使用。返回：字体对象。*/
  19. 	initFont = null, /*参考字体*/
  20. 	name = "黑体",	/*字体名称*/
  21. 	size = 17,		/*字体大小*/
  22. 	italic = null,	/*倾斜 true,false*/
  23. 	strikeOut = null,	/*删除线 true,false*/
  24. 	color = 0x0000FF,	/*颜色 _LIBXL_COLOR_*/
  25. 	bold = true,	/*加粗 true,false*/
  26. 	script = 0/*_LIBXL_SCRIPT_SUPER*/ ,	/*上下标 _LIBXL_SCRIPT_*/
  27. 	underline = null,	/*下划线 _LIBXL_UNDERLINE_*/
  28. }))
  29. s.setCellRichStr(1,1,r)
  30. b.save("C:\Users\Administrator\Desktop\a.xlsx")

 

其他注意事项：

1、调用sheet.addConditionalFormatting()添加条件格式后，保存的文件，再用excel打开，可能会提示文件有问题：

出现这种问题的原因，可能是条件格式设置不正确，特别要注意创建条件格式的参数类型，是【文本型】还是【数值型】。 

2、使用 xlAutoFilterAddSort() 添加按列排序，官方文档说明本函数支持多列排序，但测试不通过，只能支持一列。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/blog/article/detail/50930
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号