How to use Apache POI in custom script to create .xlsx files?

Hi Work Fusion,
I have a doubt can we use Apache POI 's class imports in custom Script to create .xlsx and other formats supported by POI? Because we cannot create these formats in create file component.
But this option can be done using bot task code and I have already done it and I want to create .xlxs using custom script.

Yes, you can import it.

You can see an example in this topic

Hi @ashapkina, I already know about this method but by using this we cannot go back to add functionality in Record Perspective we have to completely stick to Code Perspective . so i want to import external class in Custom Script Action component . Can you give me any suggestions.
Thank You . You are a very big help for me .The suggestion you gave me previously helped me to understand workfusion even more.

@mjana It depends on what classes you want to import in Custom Script.
These classes should be supported http://poi.apache.org/apidocs/4.0/

1 Like

Hi @ashapkina, is there standard procedure for importing POI Jar files in Workfusion RPA or just copy them to RPAExpress\Studio\Plugins folder??

Best–

Hi @ManojPuri Work Fusion has inbuilt support for Apache POI and we can create MS office files with out any imports . It works for both in Custom Script actions and as well as in code. Hope this will resolve your doubt.

1 Like

Thanks @mjana, I tried the below simplest method to write to excel sheet and it did not work. Please let me know what wrong am I doing.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.openqa.selenium.support.ui.ExpectedCondition;
import org.openqa.selenium.support.ui.WebDriverWait;


@CustomScriptAction(


)

def customScript()

      public String[] dataToWrite = {'Cheese', 'Pepperoni', 'Black Olives'}
		File file = new File("C:\\Users\\mpuri\\DNA.xls")
		FileInputStream inputStream = new FileInputStream(file);
		Workbook RJ_data = null;
		RJ_data = new HSSFWorkbook(inputStream);
		Sheet sheet = RJ_data.getSheet("DNA");
		int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();
		Row row = sheet.getRow(0);
		Row newRow = sheet.createRow(rowCount + 1);

		for (int j = 0; j < row.getLastCellNum(); j++) {
			Cell cell = newRow.createCell(j);
			cell.setCellValue(dataToWrite[j]);
		}

		inputStream.close();
		FileOutputStream outputStream = new FileOutputStream(file);
		RJ_data.write(outputStream);
		outputStream.close();
 {
   
 }

Hi Ashapkin, would you let me know what is wrong with below script.??

@CustomScriptAction(


)

def customScript(){

def fpath = 'C:\\Users\\mpuri\\DNA.xls'
List values = ['vali', 'tali', 'fali']
openExcel(fpath);  
setRange(fpath, 'A1', values)

 }

Error executing CustomScriptAction
com.workfusion.studio.rpa.recorder.playback.PlaybackException: Error executing TemplateAction[templateName=CustomScriptAction.ftl,id=1,name=Optional[CustomScriptAction],parent=-1,nextSibling=2,arguments=ActionArguments[delay=[0],timeoutMs=[10000],xsi:type=[recorder:CustomScriptAction, recorder:CustomScriptAction],pollingInterval=[300],active=[true],script=[
@CustomScriptAction(

)

def customScript(){

def fpath = ‘C:\Users\mpuri\DNA.xls’
List values = [‘vali’, ‘tali’, ‘fali’]
openExcel(fpath);
setRange(fpath, ‘A1’, values)

}],awaitTimeout=[5000]]]
at com.workfusion.studio.rpa.recorder.playback.flow.StandardControlFlow.execute(StandardControlFlow.java:54)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:28)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:15)
at com.workfusion.studio.rpa.recorder.playback.player.ActionPlayer.next(ActionPlayer.java:81)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.playNextAction(PlaybackLogic.java:154)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.run(PlaybackLogic.java:112)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.openqa.selenium.WebDriverException: Failed to execute script. java.lang.String cannot be cast to java.util.Collection
Build info: version: ‘9.3.1.0’, revision: ‘ef4b445edf’, time: ‘2019-05-29T11:53:16.301Z’
System info: host: ‘ITD06545-PC’, ip: ‘172.19.16.58’, os.name: ‘Windows 7’, os.arch: ‘amd64’, os.version: ‘6.1’, java.version: ‘1.8.0_121’
Driver info: driver.version: UniversalDriver
Command duration or timeout: 0 milliseconds
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.openqa.selenium.remote.ErrorHandler.createThrowable(ErrorHandler.java:216)
at org.openqa.selenium.remote.ErrorHandler.throwIfResponseFailed(ErrorHandler.java:168)
at org.openqa.selenium.remote.http.JsonHttpResponseCodec.reconstructValue(JsonHttpResponseCodec.java:41)
at org.openqa.selenium.remote.http.AbstractHttpResponseCodec.decode(AbstractHttpResponseCodec.java:82)
at org.openqa.selenium.remote.http.AbstractHttpResponseCodec.decode(AbstractHttpResponseCodec.java:45)
at org.openqa.selenium.remote.HttpCommandExecutor.execute(HttpCommandExecutor.java:164)
at org.openqa.selenium.remote.RemoteWebDriver.execute(RemoteWebDriver.java:741)
at org.openqa.selenium.remote.RemoteWebDriver.executeScript(RemoteWebDriver.java:677)
at com.workfusion.rpa.helpers.Excel.setRange(Excel.java:778)
at com.workfusion.rpa.helpers.Excel$setRange$0.callStatic(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallStatic(CallSiteArray.java:55)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callStatic(AbstractCallSite.java:197)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callStatic(AbstractCallSite.java:225)
at CustomScript_customScript.customScript(Script2.groovy:20)
at CustomScript_customScript$customScript.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:120)
at Script2.run(Script2.groovy)
at com.workfusion.studio.rpa.recorder.playback.shell.GroovyShellWrapper.executeScript(GroovyShellWrapper.java:48)
at com.workfusion.studio.rpa.recorder.playback.player.PlaybackContext.executeScript(PlaybackContext.java:108)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.executeBehavior(TemplateAction.java:33)
at com.workfusion.studio.rpa.recorder.playback.flow.StandardControlFlow.execute(StandardControlFlow.java:46)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:28)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:15)
at com.workfusion.studio.rpa.recorder.playback.player.ActionPlayer.next(ActionPlayer.java:81)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.playNextAction(PlaybackLogic.java:154)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.run(PlaybackLogic.java:112)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.openqa.selenium.WebDriverException: Failed to execute script. java.lang.String cannot be cast to java.util.Collection
Build info: version: ‘9.3.1.0’, revision: ‘ef4b445edf’, time: ‘2019-05-29T11:53:16.301Z’
System info: host: ‘ITD06545-PC’, ip: ‘172.19.16.58’, os.name: ‘Windows 7’, os.arch: ‘amd64’, os.version: ‘6.1’, java.version: ‘1.8.0_121’
Driver info: driver.version: UniversalDriver
Build info: version: ‘9.3.1.0’, revision: ‘ef4b445edf’, time: ‘2019-05-29T11:53:16.301Z’
System info: host: ‘ITD06545-PC’, ip: ‘172.19.16.58’, os.name: ‘Windows 7’, os.arch: ‘amd64’, os.version: ‘6.1’, java.version: ‘1.8.0_121’
Driver info: driver.version: UniversalDriver
at com.workfusion.autoit.driver.AutoItDriver.executeScript(AutoItDriver.java:192)
at com.workfusion.universal.driver.UniversalDriver.executeScript(UniversalDriver.java:151)
at org.openqa.selenium.remote.server.handler.ExecuteScript.call(ExecuteScript.java:54)
at org.openqa.selenium.remote.server.handler.WebDriverHandler.handle(WebDriverHandler.java:41)
at org.openqa.selenium.remote.server.rest.ResultConfig.handle(ResultConfig.java:134)
at org.openqa.selenium.remote.server.JsonHttpCommandHandler.handleRequest(JsonHttpCommandHandler.java:206)
at org.openqa.selenium.remote.server.InMemorySession.execute(InMemorySession.java:98)
at org.openqa.selenium.remote.server.WebDriverServlet.lambda$handle$0(WebDriverServlet.java:231)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

1 Like

Hi @ManojPuri

Sorry for delay.

For the SetRange actions, you should have a List<List> variable, so you need tp use double brackets:
List values = [['vali', 'tali', 'fali']]

@CustomScriptAction(
)

def customScript(){

def fpath = 'C:\\Users\\mpuri\\DNA.xls'
List values = [['vali', 'tali', 'fali']]
openExcel(fpath);  
setRange(fpath, 'E1', values)
saveExcel(fpath)

 } 

There is an error in the Javadocs. We’ll correct it.

Thanks, How can I use database plugin in custom action script (Workfusion RPA Express) and later in Control Tower??

Best–

Check the topics below.

Documentation of the database plugin, as well as all other plugins you can use in WorkFusion, is available in the Studio:
Help - Help Contents - Bot Task Plugins - Standard Web-Harvest processors


Thanks ashapkina, I have the plugin ready. It connect and get the data. How can i use data back in Workfusion RPA Studio, is there a way to get it in custom aciton?

Best–

Manoj