Excel

阅读(5654) 标签: excel, xlsimport, xlsexport, xlscell,

集算器可以将Excel作为数据源,实现excel文件的读取和写入。这里通过几个简单的例子来看一下具体的做法。

Excel 文件数据的使用

首先,看一下文件读取。读取的目标是data.xlsxdata.xlsexcel97-2000格式)。两个文件的内容一样,sheet0的名称是employeesheet1的名称是orders

Employee的内容是:

Orders内容是:

 

读取excel的集算器脚本如下:

 

A

1

=file("D:/files/work/excel/data.xlsx")

2

=file("D:/files/work/excel/data.xls")

3

=A1.xlsimport@tx()

4

=A2.xlsimport@t(ORDERID,SELLERID,AMOUNT,ORDERDATE;"orders",3:-3)

A1:定义一个文件对象,路径和文件名是:D:/files/work/excel/data.xlsx

A2:定义一个文件对象,路径和文件名是:D:/files/work/excel/data.xls

A3:用xlsimport函数读取A1的文件对象内容。@tx是函数选项,t是指第一行是标题,如果不带t选项的话,集算器会用“_1,_2…”来命名各列。x是指文件时xlsx格式,如果不带x选项的话,集算器会根据文件后缀来判断,判断不出的话,就按照xls读取。因为xlsimport函数没有其他输入参数,所以读取的结果是第一个sheet页的全部数据,如下:

A4:用xlsimport函数读取A2的文件内容。@t选项是指第一行是标题。输入参数ORDERID,SELLERID,AMOUNT,ORDERDATE是指仅仅导入指定的这些列,未指定的不导入。"orders"参数是指导入名称为orderssheet页。3:-3参数是指从第3行开始,导入到倒数第3行。也可以导入第3行到第100行的数据,写为:3:100。需要注意的是,当设定了导入的起始行时,使用了@t选项,要求标题行在指定的起始行。如例子中需要标题行在第3行,否则无法获得正确的表结构。

导入的结果是:

再来看一下写入excel文件。假设我们要把上述A3A4的结果导入到result1.xlsxresult2.xls文件中。A3的结果导入到名字叫r-empsheetA4的结果导入到名字叫r-orderssheet

写入excel的集算器脚本如下:

读取excel的集算器脚本如下:

 

A

1

=file("D:/files/work/excel/data.xlsx")

2

=file("D:/files/work/excel/data.xls")

3

=A1.xlsimport@tx()

4

=A2.xlsimport@t(ORDERID,SELLERID,AMOUNT,ORDERDATE;"orders",3:-3)

5

=file("D:/files/work/excel/result1.xlsx")

6

=file("D:/files/work/excel/result2.xls")

7

>A5.xlsexport@tx(A3,EID:EMPLOYEEID,NAME;"r-emp")

8

>A6.xlsexport(A4;"r-orders")

A5A6:按照要求定义两个文件对象。

A7:使用xlsexport函数,将A3的内容导入到文件A5中。xlsexport@tx选项是指导出的格式是xlsx格式,并且第一行导出的是字段标题。xlsexport函数的输入参数A3,是要导出的序表。参数EID:EMPLOYEEID,NAME是指定要导出的字段,其中EID:EMPLOYEEID是指EID字段导出的时候重命名为EMPLOYEEID。参数"r-emp"指定导出的sheet名称。最终导出的excel文件内容如下:

A8:使用xlsexport函数,将A4的内容导入到文件A6中。xlsexport的没有使用@tx选项,因此导出的格式是根据文件后缀判断出的xls格式,并且第一行不导出字段标题。xlsexport函数的输入参数A4,是要导出的序表。因为输入参数没有指定要导出的字段,所以导出全部字段。参数"r-orders"指定导出的sheet名称。最终导出的excel文件内容如下:

 

除了使用序表以外,集算器中还可以将游标中的数据导出到excel文件中,如:

 

A

1

=file("D:/files/work/excel/data.xlsx")

2

=demo.cursor("select * from EMPLOYEE")

3

=A5.xlsexport@txs(A2;"employee2")

由于使用游标数据时,需要处理的数据量经常会很大,因此在xlsexport函数中添加了@s选项,采用流式处理。由于导出excel文件之前会读入文件,因此导出大数据时,需要注意原始的xlsx文件不能太大。由于excel本身的限制,导出xlsx文件时,当导出一个sheet的数据已满1000,000条时,会自动停止。

 

除了导入与导出外,集算器中也可以使用xo.xlscell(a:b,s,t)函数修改名为ssheet中,单元格c的值为tt中的文本可以用制表符Tab隔开表示多列,或者用回车符隔开表示多行。如:

 

A

1

=file("D:/files/work/excel/data.xls")

2

=A1.xlsopen()

3

=A2 .xlscell("A1","employee";"COL1\tCOL2\nV1\tV2")

A3中,修改data.xls文件,在employee页中,从A1格开始修改数据,字串t包含了制表符和回车符,修改后,A1,B1,A2,B2中的数据都被修改,结果如下:

集算器桌面版与 Excel 插件的安装

本节内容涉及功能,只有集算器桌面版才可使用,其它版本产品的用户可以跳过。

在下面的官方网站可以下载集算器桌面版的安装包:

http://www.raqsoft.com/esproc-download

拿到安装包后执行安装程序,按提示可以逐步完成安装,集算器桌面版仅提供Windows系统版本。

如果需要使用集算器的Excel插件,要求Windows以及Excel均为64位版本。在此基础上,在安装目录中以管理员身份运行esprocDesk.exe打开桌面版集算器:

在菜单栏中选择Tool -> Install excel plug-ins,即可执行安装Excel插件的操作,如果安装正常,则执行后结果如下:

插件加载成功后,在Excel中会增加spl() 函数,用来执行集算器函数计算。

在 Excel 中使用集算器的简单介绍

本节内容涉及功能,只有集算器桌面版才可使用,其它版本产品的用户可以跳过。

函数spl的最基本形式是spl(exp),用集算器计算表达式,并返回结果,例如:

ExcelA1格中输入=spl("=5.(rand(10))"),其中集算器表达式为用等号作为开头的字符串"=5.(rand(10))",此时按下回车Enter,确认后结果如下:

可以看到,A1中被填入了一个10以内的随机整数。实际上A1中表达式的计算结果应该是一个序列,如果需要返回全部结果,则需要在确认表达式时同时按下Ctrl+Enter。重新选择A1格,同时按下Ctrl+Enter后,结果如下:

此时A1中的表达式重新计算,并将结果序列填入了Excel表格中。

在函数spl中可以添加参数,此时形式是spl(exp, arg1,…),和使用eval函数时类似,参数在表达式中用?依次表示,或者用?n表示第n个参数。例如:

D2中用spl函数计算两次测试的平均分,表达式也可以写为=spl("(?1+?2)/2",A2,B2)。执行后结果如下:

如果此时拖拽D2的左下角,将其拖至D4,则D2中的表达式会应用到选中的所有区域,并按位置调整其中的参数:

Excel中使用集算器插件时,E函数经常被用来生成序表,例如:

当把spl函数中的参数设为Excel中的一片格子时,会将其中的数据转换为二维序列传递给集算器执行计算,这对E函数是非常方便的,如在D1中填入公式=spl("=E(?)",A1:B4),同时按下Ctrl+Enter后,结果如下:

可以看到,spl函数计算后的结果数据填入时,会覆盖Excel表格中原有的数据,操作时需要注意。在spl函数中,可以对E函数得到的序表执行更多的计算,得到所需的结果。如将D2中的表达式修改为=spl("=E(?).derive((#1+#2)/2:Avg)",A1:B4),同时按下Ctrl+Enter后,结果如下:

另外还有一点需要注意的,Excel中日期时间数据和Java下是有区别的,如:

执行后结果如下:

可以发现,集算器中得到的日期数据与Excel中不同,这是由于Excel中表示日期时间数据的方式是独有的,为此可以在spl语句中添加E(x)函数,这个函数在xExcel的日期数据时,可以将其转换为通用的日期时间数据,如下:

执行后结果如下:

注意,此时B1B2中的结果实际上都是集算器中所显示的字符串数据,和Excel的日期数据并不相同。如果需要得到Excel的日期数据,可以再次使用E(x)函数,在x是通用的日期数据时,可以将其转换为Excel的日期时间数据,如下:

执行后结果如下:

可以发现,B3中显示了一个数值,这个实际上就是Excel中日期数据的实际数值,在把B3格子的格式设置为日期后,就能正常显示结果了:

 

关于用集算器执行Excel数据处理的更多内容,可以阅读论坛中的相应内容帮你早下班-esProc桌面版与Excel数据处理