cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X

How to give the file path as input parameter to a service in creating extensions.

Sruthi
14-Alexandrite

How to give the file path as input parameter to a service in creating extensions.

Hi,

I am trying to create an extension(Thing template with one service) which is used to read the data in an Excel file and convert it to Json. My problem is I don't know how to get file path from the user as input parameter to the service. I have attached the code below. If I give the whole file path in the java code itself, extension is working fine. But,If I try to get file path from the user my result after executing the service is blank. Anybody help me with this.

1 ACCEPTED SOLUTION

Accepted Solutions
Sruthi
14-Alexandrite
(To:PaiChung)

Solved the issue by correcting the file path. I gave the full file path i.e. starting  from "C:\\.....". Thanks for your reply

View solution in original post

5 REPLIES 5
PaiChung
22-Sapphire I
(To:Sruthi)

You probably want to log it out, but most likely the input specified as a parameter isn't 'arriving' in the same form.

You could try to give the input with " quotes around it from your user input. Maybe even use things like %20 etc. for spaces.

Sruthi
14-Alexandrite
(To:PaiChung)

Sorry, I don't understand why I need to Logout. I am executing the service in Composer only.

The code I have written in java is

 

package newExcelToJson;

import com.thingworx.logging.LogUtilities;
import com.thingworx.metadata.annotations.ThingworxBaseTemplateDefinition;
import com.thingworx.metadata.annotations.ThingworxServiceDefinition;
import com.thingworx.metadata.annotations.ThingworxServiceParameter;
import com.thingworx.metadata.annotations.ThingworxServiceResult;
import com.thingworx.things.Thing;

import net.sf.json.JSONObject;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;

@ThingworxBaseTemplateDefinition(name = "GenericThing")
public class ReadExcelDataWithDynamicColumn extends Thing {

private static Logger _logger = LogUtilities.getInstance()
.getApplicationLogger(ReadExcelDataWithDynamicColumn.class);

public ReadExcelDataWithDynamicColumn() {
// TODO Auto-generated constructor stub
}

@ThingworxServiceDefinition(name = "readExcelAsJson", description = "", category = "", isAllowOverride = false, aspects = {
"isAsync:false" })
@ThingworxServiceResult(name = "result", description = "", baseType = "STRING", aspects = {})
public String readExcelAsJson(
@ThingworxServiceParameter(name = "path", description = "", baseType = "STRING") String path) {
_logger.trace("Entering Service: readExcelAsJson");

 

String jsonString = "hi";
try{

/* First need to open the file. */
FileInputStream fInputStream = new FileInputStream("/Thingworx/Things/ExcelRepository/"+path);

/* Create the workbook object to access excel file. */
Workbook excelWorkBook = new XSSFWorkbook(fInputStream)
/* Because this example use .xls excel file format, so it should use HSSFWorkbook class. For .xlsx format excel file use XSSFWorkbook class.*/;
//Workbook excelWorkBook = new HSSFWorkbook(fInputStream);

// Get all excel sheet count.
int totalSheetNumber = excelWorkBook.getNumberOfSheets();


// Get current sheet.
Sheet sheet = excelWorkBook.getSheetAt(0);

// Get sheet name.
String sheetName = sheet.getSheetName();

if(sheetName != null && sheetName.length() > 0)
{
// Get current sheet data in a list table.
List<List<String>> sheetDataTable = getSheetDataList(sheet);

// Generate JSON format of above sheet data and write to a JSON file.
jsonString = getJSONStringFromList(sheetDataTable);

}

excelWorkBook.close();

}catch(Exception ex){
ex.printStackTrace();
}

_logger.trace("Exiting Service: readExcelAsJson");

return jsonString;

}

/* Return sheet data in a two dimensional list.
* Each element in the outer list is represent a row,
* each element in the inner list represent a column.
* The first row is the column name row.*/
private static List<List<String>> getSheetDataList(Sheet sheet)
{
List<List<String>> ret = new ArrayList<List<String>>();

// Get the first and last sheet row number.
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();

if(lastRowNum > 0)
{
// Loop in sheet rows.
for(int i=firstRowNum; i<lastRowNum + 1; i++)
{
// Get current row object.
Row row = sheet.getRow(i);

// Get first and last cell number.
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();

// Create a String list to save column data in a row.
List<String> rowDataList = new ArrayList<String>();

// Loop in the row cells.
for(int j = firstCellNum; j < lastCellNum; j++)
{
// Get current cell.
Cell cell = row.getCell(j);

// Get cell type.
int cellType = cell.getCellType();

if(cellType == CellType.NUMERIC.getCode())
{
double numberValue = cell.getNumericCellValue();

// BigDecimal is used to avoid double value is counted use Scientific counting method.
// For example the original double variable value is 12345678, but jdk translated the value to 1.2345678E7.
String stringCellValue = BigDecimal.valueOf(numberValue).toPlainString();

rowDataList.add(stringCellValue);

}else if(cellType == CellType.STRING.getCode())
{
String cellValue = cell.getStringCellValue();
rowDataList.add(cellValue);
}else if(cellType == CellType.BOOLEAN.getCode())
{
boolean numberValue = cell.getBooleanCellValue();

String stringCellValue = String.valueOf(numberValue);

rowDataList.add(stringCellValue);

}else if(cellType == CellType.BLANK.getCode())
{
rowDataList.add("");
}
}

// Add current row data list in the return list.
ret.add(rowDataList);
}
}
return ret;
}

/* Return a JSON string from the string list. */
private static String getJSONStringFromList(List<List<String>> dataTable)
{
String ret = "";

if(dataTable != null)
{
int rowCount = dataTable.size();

if(rowCount > 1)
{
// Create a JSONObject to store table data.
JSONObject tableJsonObject = new JSONObject();

// The first row is the header row, store each column name.
List<String> headerRow = dataTable.get(0);

int columnCount = headerRow.size();

// Loop in the row data list.
for(int i=1; i<rowCount; i++)
{
// Get current row data.
List<String> dataRow = dataTable.get(i);

// Create a JSONObject object to store row data.
JSONObject rowJsonObject = new JSONObject();

for(int j=0;j<columnCount;j++)
{
String columnName = headerRow.get(j);
String columnValue = dataRow.get(j);

rowJsonObject.put(columnName, columnValue);
}

tableJsonObject.put("Row " + i, rowJsonObject);
}

// Return string format data of JSONObject object.
ret = tableJsonObject.toString();

}
}
return ret;
}


}

 

 

After Building and importing this extension in Thingworx am getting the result as "hi". So I feel it is not able to go inside the file path.

FileInputStream fInputStream = new FileInputStream("/Thingworx/Things/ExcelRepository/"+path);

Can you tell me whether the format of the path above is correct or not?

PaiChung
22-Sapphire I
(To:Sruthi)

'log out' I mean use some logger statements to see what it believes the input parameter is that is received.

 

the path you list is correct to get to the file as a browser link

 

not sure if you can use any of the OOTB Repository services to load your file (LoadJSON, LoadBinary, LoadText, LoadXML, and LoadImage)

Sruthi
14-Alexandrite
(To:PaiChung)

Solved the issue by correcting the file path. I gave the full file path i.e. starting  from "C:\\.....". Thanks for your reply

PaiChung
22-Sapphire I
(To:Sruthi)

Glad you solved it, hard coding the drive is probably not the ideal solution.

There are a few extensions out there that you could 'deconstruct' to see how they do the relative reference.

Parsley / csv parser come to mind

Top Tags