To convert excel tables into JSON object

[UshaThavasiappan]

Hello Team,

I'm using spire.office-7.10.1 for Java.
I have a business case like I need to convert all data from excel file into separate tables. And I will convert these tables into JSON object (I will handle this part).

Please guide me for this conversion.

[Abel.He]

Hello,

Thanks for your inquiry.
For your requirement, please refer to the following code. If you have any issue, just feel free to contact us.

Code: Select all

//Load the excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("data/minden.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
//Export the data to datatable
DataTable dataTable = sheet.exportDataTable(); 

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello Abel,
DataTable object will convert all the data in a particular sheet will be converted into a table object.
My requirement will be like, if a sheet has multiple tables, those tables should be converted into a different table object. Will that be possible?

[Abel.He]

Hello,

Thanks for your feedback.
Sorry for the late reply as weekend. For your requirement, you can refer to the following code sinppet. The first parameter specifies the range of table in excel file, the second parameter determines whether to export the first row of the table as column name, and the third parameter determines whether to evaluate the formula value.
If you have any issue, just feel free to contact us.

Code: Select all

//Specifies the scope of the table
CellRange table1 = sheet.getRange().get("B1:C7");
//Export the data to datatable
DataTable dataTable = sheet.exportDataTable(table1,false,false);   

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello,

I tried your opinion, it is working for manual cell range.
In my business, I do not look for a file manually and the files from a directory will be converted into pdf formatted files.
In this case, how could I detect the ranges automatically / programmatically without specifying ranges manually.

Will that be possible to pass from where the table starts and ends in a sheet?
Also a sheet can have multiple tables and those tables also should be detected.

[Abel.He]

Hello,

Thanks for your feedback.
For your requirement, you can refer to the following code and I attached the test excel file for your reference. If this code can’t meet your requirement, please offer your excel file with data that needs to be exported to help us work out a solution for you. Thanks for your assistance in advance.

Code: Select all

//Load the Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("data/test (2).xlsx");
Worksheet sheet1 = workbook.getWorksheets().get(0);
List<DataTable> tables = new ArrayList<>();

//Travel every Table and export the data from table
for (int i = 0; i < sheet1.getListObjects().size(); i++)
{
    CellRange tableRange = (CellRange)(sheet1.getListObjects().get(i).getLocation());
    DataTable dataTable = sheet1.exportDataTable(tableRange, false, false);
    tables.add(dataTable);
}

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello Abel,

Please check my attached files. None of the files detected table object.

Note: Your attachment is missing.

DataTableObjects.zip

(394.37 KiB) Downloaded 158 times

[Abel.He]

Hello,

Thanks for your feedback.
The excel files you provided doesn’t contain any standard table, I again attach the excel file including standard table, you can test these file with the code I provided.

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hi,
Yes, I agree.
Is it possible to get the data with tabular formatted values in a separate object?

I really appreciate your response on this.

[Abel.He]

Hello,

Thanks for your feedback.
Sorry that there is no way to get directly the data with tabular formatted values in a separate object.
If you have any other issue, just feel free to contact us.

Sincerely
Abel
E-iceblue support team