Exporting CSV with multiple sheets

[BK_10290693]

Hello,

I want to export data into csv file as similar to data export widget, but it should take more than 1 infotable input and should give output as the csv file
with more than 1 sheets containing the data from those infotables.

I there any similar custom widget available to such requirement. Please suggest.

Thanks!

[Rocko]

CSV does not have sheets, only Excel does. See https://datatracker.ietf.org/doc/html/rfc4180#section-2 for comparison.

You could create multiple files in a file repository and concatenate them in a service.

[BK_10290693]

Is there any possible way to achieve this by exporting as an excel file rather exporting to csv. That will also work for me.

Also, on this point of yours, "You could create multiple files in a file repository and concatenate them in a service."- Are you suggesting the service to be a thingworx service.

Please suggest.

Thanks.

[Rocko]

To write complex formats like Excel you have have to create an extension (or use an existing one like this) which uses HSSF to create the Excel files.

For CSV, you could create a TWX service which just iterates the infotable(s) and writes the values as CSV into a file in a File Repository, The service would then return the link to the file.

[PEHOWE]

@BK_10290693 , The ThingWorx out of the box, does not contain the ability to export an Excel file. If you are willing to create an extension there is a library (Apache POI API). This library can be included as part of the extension. It would support export/import of Excel and CSV data. The library can be located at Apache POI . 
You would need to write the code to interface between ThingWorx and the calls to the Apache POI library.  
If you have general questions I would be happy to share what little I know.

Good Luck

Peter

[ShirishMorkhade]

@BK_10290693, were you able to move forward with this requirement?

Recently I came across same requirement where the requirement is write one info table per sheet (and I have such 10+ different info tables which needs to exports to excel - one info table/sheet) and we don't find a way to do so for now.

I would appreciate if you share your finding so it can be useful to other community users.

Regards,

Shirish

[BK_10290693]

@ShirishMorkhade I didn't find any solution for this. Addition to this, one thing I found is we cannot create multiple sheets in CSV file. Even if you try to create a csv file manually with multiple sheets and then save and close it. If you re-open it, you will find only the 1st sheets and the other sheets will be lost. 

So, if you try to do it with a custom widget as well, there is not point that it should work!

And with excel, I couldn't make it out.

[Rocko · Accepted solution]

There is no such thing as a Sheet in a CSV. Sheets are an Excel concept. If you manage to put sheets into a CSV, it's not CSV anymore.

If you know Java, it should be fairly easy to take the extension I mentioned here and enhance it to support sheets in Excel exports.