//Here in the demo, we use a mock class to generate instance of java.sql.ResultSet.
//User who use template in product, must get instance of java.sql.ResultSet from the
//related database connection.
java.sql.ResultSet datasource = new GcMockResultSet(this.getResourceStream("score.csv"));
 
//Add data source
workbook.addDataSource("ds", datasource);

        NestedDataTemplate_Student student2 = new NestedDataTemplate_Student();
        student2.name = "Mark";
        student2.address = "101, Halford Avenue, Fremont, CA";
        Family family3 = new Family();
 
        family3.father = new Guardian();
        family3.father.name = "Jonathan Williams";
        family3.father.setOccupation("Product Engineer");
        family3.mother = new Guardian();
        family3.mother.name = "Joanna Williams";
        family3.mother.setOccupation("Surgeon");
 
        student2.family = new ArrayList<Family>();
        student2.family.add(family3);
 
//Add data source
workbook.addDataSource("ds", student2);

public class CustomDataTable {

    public static void main(String[] args) throws Exception {
        // Initialize Workbook.
        Workbook workbook = new Workbook();
        
        // Open template file.
        workbook.open("ComplexMultiDataSource.xlsx");
        
        // Create table data sources from JSON.
        InputStream order_json;
        order_json = new FileInputStream("order.json");
        InputStream customer_json;
        customer_json = new FileInputStream("customer.json");
        InputStream product_json;
        product_json = new FileInputStream("product.json"); 
        JsonTable order = new JsonTable(order_json);
        JsonTable customer = new JsonTable(customer_json);
        JsonTable product = new JsonTable(product_json);
        
        // Add data sources for template.
        workbook.addDataSource("order", order);
        workbook.addDataSource("customer", customer);
        workbook.addDataSource("product", product);
        
         // Process the template.
        workbook.processTemplate();
        
        // Set column width.
        workbook.getWorksheets().get(0).getRange("A:F").setColumnWidth(16);
            
        // Save to an excel file
        workbook.save("CustomDataTable.xlsx");
    }
}

/*
The class is only part of the sample, not part of the product.
So it is not recommended to use this class in a production environment.
Please implement your own class that meets the requirements of the product.
*/
// Create a class to transform a JSON stream into a table and inherit ITableDataSource.
class JsonTable implements ITableDataSource {
    private final JsonArray _jsonArray;
    private final HashMap<Integer, String> _columnsMap;
    private final HashMap<String, Integer> _reversedColumnsMap;

    public JsonTable(InputStream inputStream) {
        String jsonContent;
        try {
            jsonContent = convertToString(inputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        _jsonArray = JsonParser.parseString(jsonContent).getAsJsonArray();
        JsonObject jsonObject = _jsonArray.get(0).getAsJsonObject();
        _columnsMap = new HashMap<>();
        _reversedColumnsMap = new HashMap<>();
        int index = 0;
        for (Map.Entry<String, JsonElement> entry : jsonObject.entrySet()) {
            String next = entry.getKey();
            _columnsMap.put(index, next);
            _reversedColumnsMap.put(next, index);
            index++;
        }
    }

    private String convertToString(InputStream inputStream) throws IOException {
        StringBuilder stringBuilder = new StringBuilder();
        String line;
        try (BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
            while ((line = bufferedReader.readLine()) != null) {
                stringBuilder.append(line);
            }
        }
        return stringBuilder.toString();
    }

    // Get value from data source.
    @Override
    public Object getValue(int row, int column) {
        return _jsonArray.get(row).getAsJsonObject().get(_columnsMap.get(column)).getAsString();
    }

    // Get row count from data source.
    @Override
    public int getRowCount() {
        return _jsonArray.size();
    }

    // Get column count from data source.
    @Override
    public int getColumnCount() {
        return _columnsMap.size();
    }

    // Get column name from data source.
    @Override
    public String getColumnName(int column) {
        if (_columnsMap.containsKey(column)) {
            return _columnsMap.get(column);
        }
        return null;
    }

    // Get column index from data source.
    @Override
    public int getColumnIndex(String columnName) {
        if (_reversedColumnsMap.containsKey(columnName)) {
            return _reversedColumnsMap.get(columnName);
        }

        return -1;
    }
}

//Get data from json file
String jsonText = "";
try {
    InputStream stream = getResourceStream("Template_FamilyInfo.json");

    ByteArrayOutputStream result = new ByteArrayOutputStream();
    byte[] buffer = new byte[1024];
    int length;
    while ((length = stream.read(buffer)) != -1) {
        result.write(buffer, 0, length);
    }

    jsonText = result.toString("UTF-8");
} catch (IOException e) {
    e.printStackTrace();
}

// Create a JsonDataSource
JsonDataSource datasource = new JsonDataSource(jsonText);

//Add data source
workbook.addDataSource("ds", datasource);

String className = "Class 3";
int count = 500;
 
//Add data source
workbook.addDataSource("cName", datasource);
workbook.addDataSource("count", count);
workbook.addDataSource("owner", "Hunter Liu");

int[] numbers = new int[] { 10, 12, 8, 15};
List<String> countries = new List<String>() { "USA", "Japan", "UK", "China" };
 
List<Person> peoples = new List<Person>();
 
Person p1 = new Person();
p1.Name = "Helen";
p1.Age = 12;
peoples.Add(p1);
 
Person p2 = new Person();
p2.Name = "Jack";
p2.Age = 23;
peoples.Add(p2);
 
Person p3 = new Person();
p3.Name = "Fancy";
p3.Age = 25;
peoples.Add(p3);
 
workbook.addDataSource("p", peoples);
workbook.addDataSource("countries", countries);
workbook.addDataSource("numbers", numbers);

// Create a new workbook.
Workbook workbook = new Workbook();
        
// Load template file from resource.
workbook.open("Template_SalesDataGroup_DataTable.xlsx");

// Add data to DataTable.
SalesData datasource = new SalesData();
datasource.sales = new ArrayList<SalesRecord>();

System.out.println("Creating test data.");

String[] areas = new String[]{"North America", "South America"};
String[] cities = new String[]{"Chicago", "New York", "Santiago", "Quito", "Fremont", "Buenos Aires", "Medillin", "Minnesota"};
String[] categories = new String[]{"Consumer Electronics", "Mobile"};
String[] category1NamePrefixes = new String[]{"Bose ", "Canon ", "Haier ", "IFB ", "Mi ", "Sennheiser "};
String[] category2NamePrefixes = new String[]{"iPhone ", "OnePlus ", "Redmi ", "Samsung "};

Random rand = new Random();
        
// You can increase the loop count if the demo is too fast on your computer.
for (int i = 0; i < 50000; i++) {
    SalesRecord item = new SalesRecord();
    item.area = areas[rand.nextInt(areas.length)];
    item.city = cities[rand.nextInt(cities.length)];
    int categoryId = rand.nextInt(categories.length);
    item.category = categories[categoryId];
    String[] names = (categoryId == 0) ? category1NamePrefixes : category2NamePrefixes;
    item.name = names[rand.nextInt(names.length)] + (10 + rand.nextInt(10000 - 10));
    item.revenue = 10000 + rand.nextInt(100000 - 10000);
    datasource.sales.add(item);
}

// Add template global settings.
workbook.getNames().add("TemplateOptions.KeepLineSize", "true");

// Add data source.
workbook.addDataSource("ds", datasource.sales);

// Cancel data source binding when cancel key is pressed or timeout is reached.
try (CancellationTokenSource cancellation = new CancellationTokenSource()) {
    /* Important:To handle the cancel key event of the console, we are using `sun.misc.Signal` in this sample.
       However, the classes of `sun.misc` might be unavailable in your JDK, because they are restricted APIs.
       You need to either use `jdk.internal.misc.*` (Java 9+) or use
       3rd-party components (such as `javax.realtime.POSIXSignalHandler`) to handle
       the console cancel key event if `sum.misc` is unavailable.*/

     final sun.misc.SignalHandler[] oldSignal = new sun.misc.SignalHandler[1];
     sun.misc.SignalHandler cancelHandler = sig -> {
        
         // Exit the process.
         cancellation.cancel();
         // Invoke rest of the handlers of Console.CancelKeyPress.
         if (oldSignal[0] != null) {
             oldSignal[0].handle(sig);
         }
     };

     sun.misc.Signal cancelKeyPressSignal = new sun.misc.Signal("INT");
     oldSignal[0] = sun.misc.Signal.handle(cancelKeyPressSignal, cancelHandler);

  // Cancel when timeout is reached.
    cancellation.cancelAfter(Duration.ofSeconds(10));
    System.out.println("Start ProcessTemplate.");
    try {
        workbook.processTemplate(cancellation.getToken());
        System.out.println("ProcessTemplate finished.");
    } catch (java.util.concurrent.CancellationException ex) {
        System.out.println("ProcessTemplate was canceled.");
    }

     sun.misc.Signal.handle(cancelKeyPressSignal, oldSignal[0]);
} catch (Exception e) {
    throw new RuntimeException(e);
}
    
// Save the Workbook.
workbook.save("CancelTemplateProcessing.xlsx");