Read excel and update or create new files in custom object ca clarity

[Wilson Cardenas]

Hello everyone, I want to know if anyone has developed a script that can read fields from Excel and can create direct records in Clarity for an investment. This development is possible

[Nirbhay Kumar]

Hello Wilson,

You can use Apache POI class (org.apache.poi.ss.usermodel.WorkbookFactory) to open the workbook from filestream.. then read through it using various methods.

<!-- If reading from attachment attribute in clarity -->
<core:invokeStatic className="com.niku.dms.util.FileUtil" method="retrieveFileData" var="byteArray">
	<core:arg type="long" value="${fileVersionID}"/> <!-- Pass the file versionId from CLB_DMS_VERSIONS -->
</core:invokeStatic>
<core:new className="java.io.ByteArrayInputStream" var="fileInputStream">
	<core:arg value="${byteArray}"/>
</core:new>

<!--  If reading file from server-->
<core:new className="java.io.FileInputStream" var="fileInputStream">
	<core:arg type="java.lang.String" value="${filePath}"/>
</core:new>

Open workbook like this :

<!-- For byte stream in excel-->
<core:invokeStatic className="org.apache.poi.ss.usermodel.WorkbookFactory" method="create" var="workbook">
	<core:arg type="java.io.InputStream" value="${fileInputStream}"/>
</core:invokeStatic>

<!-- Get the sheet of name, lets call the sheet name as Sheet 1 -->
<core:set value="${workbook.getSheet('Sheet 1')}" var="worksheet"/>


<!-- Find last row of sheet -->
<core:set value="${worksheet.getLastRowNum()}" var="lastRow"/>

<!-- Iterate through sheet data-->
<core:forEach begin="0" end="${lastRow}" var="r">
	<core:set value="${worksheet.getRow(r)}" var="row"/>
	<!-- Iterate through let's say 20 columns -->
	<core:forEach begin="0" end="20" var="c">
		<core:set var="cellValue" value="${row.getCell(c).getStringCellValue().trim()}"/>
	</core:forEach>

</core:forEach>

Once you have the data from excel, use it to update Clarity fields.

[Wilson Cardenas]

Hello Nirbhay Kumar thanks for yur help, you have of course if is possible a example of XOG or GEL complete.