Need to Iterate nested List in Excel

Hi Team,

ImportCustomObjects method doesn’t iterate a nested list of object/String into Excel Worksheet.

public int importCustomObjects(java.util.Collection list, int firstRow, int firstColumn, ImportTableOptions options)

Please share a solution to iterate outer list of objects along with inner list of objects/String.

Thanks & Regards,
Shyamala

@Shyamu,

I am not sure on how do you define/create your main List (having some nested List in it). Could you please provide complete sample code (runnable) on creating main array list having some nested list in it in code and importing into MS Excel sheet via Aspose.Cells API? How do you link these lists? Even I could not find any option in MS Excel to import main List that automatically inserts and links to inner list manually. The only way I found is rather a manual way, import your both Lists (outer and inner) separately into the worksheet.

Hi Amjad,

I have attached a Excel Test Package to test a code along with Expected Excel Document.

Please check and share a solution

ExcelTestPackage.zip (10.1 KB)

Actual Requirement as i need to iterate two dimensional nested list of object into excel

Thanks & Regards,
Shyamala

@Shyamu,
We have analyzed your expected output and observed that you want to expand data in two dimensions i.e. row wise and column wise that is not supported. You may try importing data with JSON utility that iterates nested list and imports data into workbook.

public static void main(String[] args) throws Exception {

	
String dataresult = "[\r\n" + 
	"  {\r\n" + 
	"    \"id\": \"1\",\r\n" + 
	"    \"name\": \"Name 1\",\r\n" + 
	"    \"code\": \"CODE2\",\r\n" + 
	"    \"itemunit\": \"Days\",\r\n" + 
	"    \"description\": \"Descriped\",\r\n" + 
	"    \"itemcategory\": \"Custom Itemcategory\",\r\n" + 
	"    \"type\": \"OL Type\",\r\n" + 
	"    \"firstitemvalue\": \"100\",\r\n" + 
	"\"firstitemdate\": \"2021-06-09T17:36:37Z\",\r\n" + 
	"\"enditemvalue\": \"100\",\r\n" + 
	"\"enditemdate\": \"2021-06-09T17:36:37Z\",\r\n" + 
	" \"intermediateitem\": [{\r\n" + 
	"      \r\n" + 
	"       \"value\": \"20\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}, {\r\n" + 
	"      \r\n" + 
	"       \"value\": \"30\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}, {\r\n" + 
	"      \r\n" + 
	"       \"value\": \"40\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}],\r\n"+
	"    \"comments\": \"Commented\"\r\n" + 
	"  },\r\n" + 
	" {\r\n" + 
	"    \"id\": \"2\",\r\n" + 
	"    \"name\": \"Name 2\",\r\n" + 
	"    \"code\": \"COD1\",\r\n" + 
	"    \"itemunit\": \"Days\",\r\n" + 
	"    \"description\": \"Descriped\",\r\n" + 
	"    \"itemcategory\": \"Custom Itemcategory\",\r\n" + 
	"    \"type\": \"Custom Type\",\r\n" + 
	"    \"firstitemvalue\": \"100\",\r\n" + 
	"\"firstitemdate\": \"2021-06-09T17:36:37Z\",\r\n" + 
	"\"enditemvalue\": \"100\",\r\n" + 
	"\"enditemdate\": \"2021-06-09T17:36:37Z\",\r\n" + 
	"    \"intermediateitem\": [{\r\n" + 
	"      \r\n" + 
	"       \"value\": \"20\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}, {\r\n" + 
	"      \r\n" + 
	"       \"value\": \"30\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}, {\r\n" + 
	"      \r\n" + 
	"       \"value\": \"40\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}, {\r\n" + 
	"      \r\n" + 
	"       \"value\": \"50\",\r\n" + 
	"\"date\":\"2021-06-09T17:36:37Z\"\r\n" + 
	"}],\r\n" + 
	"    \"comments\": \"Commented\"\r\n" + 
	"  }\r\n" + 
	"]";

		ImportJsonString(dataresult);
		// Print the message
		System.out.println("WorkSheet executed successfully.");
}

static void ImportJsonString(String dataresult) throws Exception
{
	//Create workbook object
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Specify import table options
ImportTableOptions opts = new ImportTableOptions();


// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.createStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.getFont().setColor(Color.getBlueViolet());
style.getFont().setBold(true);

// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.setTitleStyle(style);
options.setArrayAsTable(true);

// Import JSON Data
JsonUtility.importData(dataresult, ws.getCells(), 0, 0, options);

// Save Excel file
wb.save("ImportingFromJson.xlsx");
	
}

Thanks for your update !!

But, ImportJSONArray couldn’t help me to achieve my requirement.

Could you please share a solution for how to iterate two dimensional - nested list of object into excel worksheet?

Regards,
Shyamala B

@Shyamu,
We are afraid that no direct option is available to iterate through two-dimensional array where data may vary both in rows and columns. If any such option is available in MS Excel, please share the details with us and we will assist you to achieve the same using Aspose.Cells. Otherwise you need to device your own logic to achieve your desired output.