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?
