Document Solutions for Excel, Java Edition | Document Solutions
Features / Import Data
In This Topic
    Import Data
    In This Topic

    Sometimes, you may need to import data from an object collection or a data table to a range to create a new spreadsheet or report. DsExcel enables you to import data from various data sources using importData method of IRange interface. With importData method, you can import the following types of data sources:

    DsExcel provides the following importData methods to import data from an object collection or a data table to a range:

    Method Parameter Description
    DataImportResult importData(Object items) items The items to import, which can be:
    • Element Types
      • Primitive types: byte, short, int, long, float, double, and boolean.
      • Nullable primitives: Byte, Short, Integer, Long, Float, Double, Boolean, String, CalcError, Classic date and time types: java.util.Date, java.util.Calendar, JSR310 date and time types: java.time.LocalDate, java.time.LocalTime, java.time.LocalDateTime, java.time.OffsetTime, java.time.OffsetDateTime, java.time.ZonedDateTime, java.time.Instant, java.time.YearMonth, java.time.Year, and java.time.MonthDay.
      • Custom objects: objects that have public get methods (properties in Kotlin) to be mapped to cell values.
      • Map where the key is String: It will be treated as a collection of property names and values.
    • Collection Types
      • Flat (non-jagged, 1D in total) Iterable or array.
      • Jagged 2D array where the element type of the inner collection is primitive or nullable primitive.

    Note: The method accepts table as parameter through ResultSet object.

    DataImportResult importData(Object items, DataImportOptions options) items The items to import, which can be:
    • Element Types
      • Primitive types: byte, short, int, long, float, double, and boolean.
      • Nullable primitives: Byte, Short, Integer, Long, Float, Double, Boolean, String, CalcError, Classic date and time types: java.util.Date, java.util.Calendar, JSR310 date and time types: java.time.LocalDate, java.time.LocalTime, java.time.LocalDateTime, java.time.OffsetTime, java.time.OffsetDateTime, java.time.ZonedDateTime, java.time.Instant, java.time.YearMonth, java.time.Year, and java.time.MonthDay.
      • Custom objects: objects that have public get methods (properties in Kotlin) to be mapped to cell values.
      • Map where the key is String: It will be treated as a collection of property names and values.
    • Collection Types
      • Flat (non-jagged, 1D in total) Iterable or array.
      • Jagged 2D array where the element type of the inner collection is primitive or nullable primitive.
    options The import options.

    options parameter of importData method accepts the following import options that are accessible through DataImportOptions class, which enable you to choose how to import data from object collections or data tables to a range:

    Import Option Description
    InsufficientSpaceHandling This option determines how to handle insufficient space when importing data.
    IncludeColumnHeader This option determines whether to include the column header as the first row when importing data.
    ColumnsSelector This option selects columns to import. The default behavior is to auto-generate all columns.
    ItemTypeProvider This option gets item type from the specified Iterable or array. The type is used to auto-generate columns. The default behavior is to try to get the item type from the array, then try to get the type from the first item.

    Note: DsExcel recommends adhering to the following when importing data to the range:

    • Use generic type where possible.
    • Use the delegate-based columns selector for custom objects in AOT projects.
    • Use a 2D array instead of a jagged array where possible.
    • Cache a LINQ result into a list or array if the result is not very long.
    • Keep the length of jagged arrays the same where possible.
    • Make sure to not use duck typing unless you have no choice.
    • Make sure to not attempt to import an asynchronous data source unless async continuations run on the original thread.
    • Make sure to not use covariance in array types unless you have no choice.

    Import Simple Enumerable Vertically

    Refer to the following example code to import simple enumerable vertically to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
var worksheet = workbook.getWorksheets().get(0);

// Add 1-D array of doubles.
double[] doubleData = {1.0, 2.0, 3.0, 4.0, 5.0};

// Import array vertically.
worksheet.getRange("A1:A5").importData(doubleData);

// Save the workbook.
workbook.save("ImportSimpleEnumerableVertically.xlsx");


    Import Simple Enumerable Horizontally

    Refer to the following example code to import simple enumerable horizontally to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add 1-D array of doubles.
double[] doubleData = {1.0, 2.0, 3.0, 4.0, 5.0};

// Import array horizontally.
worksheet.getRange("A1:E1").importData(doubleData);

// Save the workbook.
workbook.save("ImportSimpleEnumerableHorizontally.xlsx");


    Import 1D Data

    Refer to the following example code to import 1D data to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(Locale.US);
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("Import1DData");

// Set values to the range.
sheet1.getRange("A1").setValue("Tour Budget Table");

// Import 1D array horizontally when the range is not vertical.
String[] titles = {"Employee ID", "Adults", "Children", "Rooms", "Spa", "Hotpot", "Budget"};
sheet1.getRange("A2:G2").importData(titles);

// Generate test data. You can set the seed value of the random number generator to get the same data.
Random rnd = new Random(1234);
final int rowCount = 20;
int[] uids = rnd.ints(rowCount, 10000, 99999).toArray();
int[] adults = rnd.ints(rowCount, 1, 4).toArray();
int[] children = rnd.ints(rowCount, 0, 3).toArray();
List<Integer> rooms = IntStream.range(0, rowCount)
        .mapToObj(i -> (adults[i] + children[i]) / 2)
        .collect(Collectors.toList());
double[] spa = IntStream.range(0, rowCount)
        .mapToDouble(i -> Math.ceil((adults[i] + children[i]) * rnd.nextDouble()))
        .toArray();
int[] hotpot = IntStream.range(0, rowCount)
        .map(i -> adults[i] + children[i])
        .toArray();

final int budgetBase = 750;
List<Integer> budget = IntStream.range(0, rowCount)
        .mapToObj(i -> adults[i] * 200 + children[i] * 100 + rooms.get(i) * 400 + (int) spa[i] * 188 + hotpot[i] * 233 + budgetBase)
        .collect(Collectors.toList());

// Import data by columns.
/* "rooms" is a list. It can also be imported like 1D arrays.
   "budget" is a LINQ result. It can also be imported like 1D arrays. */
List<Object> columns = Arrays.asList(uids, adults, children, rooms, spa, hotpot, budget);
for (int i = 0; i < columns.size(); i++) {
    // 1D array/list will be imported vertically if the range is vertical.
    sheet1.getRange(2, i, 2, 1).importData(columns.get(i));
}

// Set range style.
sheet1.getRange(1, 0, 1, 7).setStyle(workbook.getStyles().get("Heading 3"));
sheet1.getRange(2, 0, rowCount, 7).setStyle(workbook.getStyles().get("20% - Accent1"));
sheet1.getRange(1, 0, 1, 7).getEntireColumn().autoFit();
sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1"));
sheet1.getRange("1:2").autoFit();

// Save the workbook.
workbook.save("Import1DData.xlsx");


    Import 2D Data

    Refer to the following example code to import 2D data to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(Locale.US);
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("Import2DData");
sheet1.getRange("A1").setValue("Tour Budget Table");

// Import titles with 2D array.
String[][] titles = {{"Employee ID", "Adults", "Children", "Rooms", "Spa", "Hotpot", "Budget"}};
sheet1.getRange("A2").importData(titles);

// Generate test data. You can set the seed value of the random number generator to get the same data.
Random rnd = new Random(1234);
final int rowCount = 20;

// Import data from 2D arrays. This code is similar to using Range.Value.
double[][] numbers = new double[rowCount][7];
for (int i = 0; i < rowCount; i++) {
    int employeeId = rnd.nextInt(90000) + 10000;
    int adults = rnd.nextInt(3) + 1;
    int children = rnd.nextInt(3);
    int rooms = (adults + children) / 2;
    double spa = Math.ceil((adults + children) * rnd.nextDouble());
    int hotpot = adults + children;
    double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;

    numbers[i][0] = employeeId;
    numbers[i][1] = adults;
    numbers[i][2] = children;
    numbers[i][3] = rooms;
    numbers[i][4] = spa;
    numbers[i][5] = hotpot;
    numbers[i][6] = budget;
}

DataImportResult result = sheet1.getRange("A3").importData(numbers);
int columnsImported = result.getColumnsImported();
int rowsImported = result.getRowsImported();

// Set range style.
sheet1.getRange(1, 0, 1, columnsImported).setStyle(workbook.getStyles().get("Heading 3"));
sheet1.getRange(2, 0, rowsImported, columnsImported).setStyle(workbook.getStyles().get("20% - Accent1"));
sheet1.getRange(1, 0, 1, columnsImported).getEntireColumn().autoFit();
sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1"));
sheet1.getRange("1:2").autoFit();
sheet1.getRange(2, 6, rowsImported, 1).setNumberFormat("$#,##0.00");
sheet1.getRange(2, 6, rowsImported, 1).getEntireColumn().setColumnWidth(10);

// Save the workbook.
workbook.save("Import2DData.xlsx");


    Import Multidimensional Array

    Refer to the following example code to import multidimensional array to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add multidimensional array.
double[][][] doubleDataMt = {
        {
                {1.0, 2.0, 3.0},
                {4.0, 5.0, 6.0},
                {7.0, 8.0, 9.0}
        },
        {
                {10.0, 11.0, 12.0},
                {13.0, 14.0, 15.0},
                {16.0, 17.0, 18.0}
        },
        {
                {19.0, 20.0, 21.0},
                {22.0, 23.0, 24.0},
                {25.0, 26.0, 27.0}
        }
};

// Import multidimensional array.
int startRow = 0;
for (double[][] item : doubleDataMt) {
    // A worksheet does not support multi-dimensional arrays.
    // But you can import inner arrays one by one using the import result to perform layout.
    DataImportResult imported = worksheet.getRange(startRow, 0).importData(item);
    startRow += imported.getRowsImported();
}

// Save the workbook.
workbook.save("ImportMultidimensionalArray.xlsx");


    Import Jagged Arrays

    Refer to the following example code to import jagged array to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add jagged array.
double[][] doubleDataJagged = {
        {1.0, 2.0, 3.0},
        {4.0, 5.0},
        {7.0, 8.0, 9.0}
};
        
// Inner arrays can have different lengths.
// But it's recommended to keep the length the same and use 2D arrays for better performance.
worksheet.getRange("A1").importData(doubleDataJagged);

// Save the workbook.
workbook.save("ImportJaggedArrays.xlsx");


    Import Entity Data

    Refer to the following example code to import data from entities to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(Locale.US);
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("ImportEntities");
sheet1.getRange("A1").setValue("Tour Budget Table");

// Generate test data. You can set the seed value of the random number generator to get the same data.
Random rnd = new Random(1234);
final int rowCount = 20;

// Create a list of TourBudget entities.
List<TourBudget> tourBudgets = new ArrayList<>();

for (int i = 0; i < rowCount; i++) {
    int employeeId = rnd.nextInt(89999) + 10000;
    int adults = rnd.nextInt(3) + 1;
    int children = rnd.nextInt(3);
    int rooms = (adults + children) / 2;
    double spa = Math.ceil((adults + children) * rnd.nextDouble());
    int hotpot = adults + children;
    double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;

 // Add entity to the list.
    tourBudgets.add(new TourBudget(employeeId, adults, children, rooms, spa, hotpot, budget));
}

// Import list into the worksheet.
DataImportResult result = sheet1.getRange("A2").importData(tourBudgets);

// Set range style.
sheet1.getRange(1, 0, 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("Heading 3"));
sheet1.getRange(2, 0, result.getRowsImported() - 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("20% - Accent1"));
sheet1.getRange(1, 0, result.getRowsImported(), result.getColumnsImported()).getEntireColumn().autoFit();
sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1"));
sheet1.getRange("1:2").autoFit();
        
// Find index of the budget column, because the order of columns is unspecified.
Object[][] titles = (Object[][]) sheet1.getRange("A2:G2").getValue();
int budgetIndex = -1;
for (int i = 0; i < titles[0].length; i++) {
    if (titles[0][i].equals("Budget")) {
        budgetIndex = i;
        break;
    }
}

// Format the budget column as currency.
sheet1.getRange(2, budgetIndex, result.getRowsImported(), 1).setNumberFormat("$#,##0.00");
sheet1.getRange(2, budgetIndex, result.getRowsImported(), 1).getEntireColumn().setColumnWidth(10);

// Save the workbook.
workbook.save("ImportDataFromEntities.xlsx");

    Note: To execute the example code, you need to use TourBudget.java public class file.

    Import Entity Data with Filter

    Refer to the following example code to import data from entities with selected rows and columns to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(new Locale("en", "US"));
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("ImportEntities");
sheet1.getRange("A1").setValue("Tour Budget Table");

// Generate test data. You can set the seed value of the random number generator to get the same data.
Random rnd = new Random(1234);
final int rowCount = 20;

// Create a list of TourBudget entities.
List<TourBudget> tourBudgets = new ArrayList<>();

for (int i = 0; i < rowCount; i++) {
    int employeeId = rnd.nextInt(90000) + 10000;
    int adults = rnd.nextInt(3) + 1;
    int children = rnd.nextInt(3);
    int rooms = (adults + children) / 2;
    double spa = Math.ceil((adults + children) * rnd.nextDouble());
    int hotpot = adults + children;
    double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;

 // Add entity to the list.
    tourBudgets.add(new TourBudget(employeeId, adults, children, rooms, spa, hotpot, budget));
}

// Order by budget from high to low and take top five rows.
List<TourBudget> top10 = tourBudgets.stream()
        .sorted(Comparator.comparingDouble(TourBudget::getBudget).reversed())
        .limit(5)
        .collect(Collectors.toList());

// Import the query result into the worksheet.
// Only show the employee ID, adults, children, and budget with custom column headers.
DataImportOptions options = new DataImportOptions();
options.setColumnsSelector(items -> {
    if (!(items instanceof List<?>)) return null;
    return new AbstractMap.SimpleEntry[]{
            new AbstractMap.SimpleEntry<>("Employee ID", (Function<TourBudget, Integer>) TourBudget::getEmployeeID),
            new AbstractMap.SimpleEntry<>("Adults", (Function<TourBudget, Integer>) TourBudget::getAdults),
            new AbstractMap.SimpleEntry<>("Children", (Function<TourBudget, Integer>) TourBudget::getChildren),
            new AbstractMap.SimpleEntry<>("Budget", (Function<TourBudget, Double>) TourBudget::getBudget)
    };
});
DataImportResult result = sheet1.getRange("A2").importData(top10, options);

// Set range style.
sheet1.getRange(1, 0, 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("Heading 3"));
sheet1.getRange(2, 0, result.getRowsImported() - 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("20% - Accent1"));
sheet1.getRange(1, 0, result.getRowsImported(), result.getColumnsImported()).getEntireColumn().autoFit();
sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1"));
sheet1.getRange("1:2").autoFit();
sheet1.getRange(2, 3, result.getRowsImported(), 3).setNumberFormat("$#,##0.00");
sheet1.getRange(2, 3, result.getRowsImported(), 3).getEntireColumn().setColumnWidth(10);

// Save the workbook.
workbook.save("ImportDataFromEntitiesSelectRowsColumns.xlsx");

    Note: To execute the example code, you need to use TourBudget.java public class file.

    Import Entity Data with Option Builder

    Refer to the following example code to import data from entities with selected rows and columns with a "Builder" design pattern to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(Locale.US);
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("DataImportOptionsBuilder");
sheet1.getRange("A1").setValue("GPU Performance Test Result");
sheet1.getRange("A1:B1").merge();
sheet1.getRange("A1:B1").setStyle(workbook.getStyles().get("Heading 1"));

// Set test data.
FrameRateTestResult[] frameRateResults = new FrameRateTestResult[]{
        new FrameRateTestResult("GeForce RTX 4090", 154.1, "1080p Ultra"),
        new FrameRateTestResult("Radeon RX 7900 XTX", 149.0, "1080p Ultra"),
        new FrameRateTestResult("GeForce RTX 4080 Super", 148.3, "1080p Ultra"),
        new FrameRateTestResult("Radeon RX 7900 XT", 143.9, "1080p Ultra"),
        new FrameRateTestResult("GeForce RTX 4090", 146.1, "1440p Ultra"),
        new FrameRateTestResult("Radeon RX 7900 XTX", 135.3, "1440p Ultra"),
        new FrameRateTestResult("GeForce RTX 4080 Super", 133.0, "1440p Ultra"),
        new FrameRateTestResult("Radeon RX 7900 XT", 125.9, "1440p Ultra"),
        new FrameRateTestResult("GeForce RTX 4090", 114.5, "4K Ultra"),
        new FrameRateTestResult("Radeon RX 7900 XTX", 95.1, "4K Ultra"),
        new FrameRateTestResult("GeForce RTX 4080 Super", 91.9, "4K Ultra"),
        new FrameRateTestResult("Radeon RX 7900 XT", 81.2, "4K Ultra")
};

// Build options with DataImportOptionsBuilder. The code is easier to read and maintain.
// Note that DataImportOptionsBuilder is a custom class in this example code. It is not part of the public API.
final double[] maxFrameRateOfGroup = new double[1];
DataImportOptions importOptions = new DataImportOptionsBuilder()
        .hasColumnsSelector(FrameRateTestResult.class, it -> {
            it.bind("Graphics Card", FrameRateTestResult::getGpuName)
                    .bind("Frame Rate", item -> String.format("%s(%sfps)",
                            NumberFormat.getPercentInstance().format(item.getFrameRate() / maxFrameRateOfGroup[0]),
                            new DecimalFormat("#.0", DecimalFormatSymbols.getInstance(Locale.US)).format(item.getFrameRate()))
                    );
        })
        .build();

// Import grouped data and apply styles.
int curRow = 2;

// Group the frame rate results by workload.
Map<String, List<FrameRateTestResult>> groupedResults = Arrays.stream(frameRateResults)
        .collect(Collectors.groupingBy(FrameRateTestResult::getWorkload));

for (Map.Entry<String, List<FrameRateTestResult>> group : groupedResults.entrySet()) {
    // Set group header.
    sheet1.getRange(curRow, 0).setValue(group.getKey());
    sheet1.getRange(curRow, 0).setStyle(workbook.getStyles().get("Heading 2"));
    curRow++;

    // Sort data by frame rate from high to low and update the max frame rate.
    List<FrameRateTestResult> sortedData = group.getValue().stream()
            .sorted(Comparator.comparingDouble(FrameRateTestResult::getFrameRate).reversed())
            .collect(Collectors.toList());
    maxFrameRateOfGroup[0] = group.getValue().stream().mapToDouble(FrameRateTestResult::getFrameRate).max().orElse(0);

    // Import data with options.
    DataImportResult result = sheet1.getRange(curRow, 0).importData(sortedData, importOptions);

    // Use the import result to apply styles.
    sheet1.getRange(curRow, 0, 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("Accent1"));
    curRow += result.getRowsImported() + 1;
}

// Set range layout.
IRange usedRange = sheet1.getUsedRange();
usedRange.getEntireRow().autoFit();
usedRange.getEntireColumn().autoFit();

// Save the workbook.
workbook.save("ImportDataFromEntitiesSelectRowsColumnsBuilder.xlsx");

    Note: To execute the example code, you need to use DataImportOptionsBuilder.java, FrameRateTestResult.java, ItemTypeProviderBuilder.java, and ObjectColumnSelectorBuilder.java public class files.

    Import Data from Custom Objects

    Refer to the following example code to import data from custom objects of weakly typed collections to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add Cat and Dog data.
Cat cat1 = new Cat("Christopher", 3, true, "Feather Whip");
Cat cat2 = new Cat("Richter", 2, false, "Laser Pointer");
Dog dog1 = new Dog("Julius", 5, true);
Dog dog2 = new Dog("Leon", 4, false);

// Create an array list.
List<Animal> animals = Arrays.asList(cat1, cat2, dog1, dog2);

// Import data from collection.
DataImportOptions options = new DataImportOptions();
options.setItemTypeProvider(items -> {
    if (items instanceof Iterable<?> &&
            ((Iterable<?>) items).iterator().hasNext() &&
            ((Iterable<?>) items).iterator().next() instanceof Animal) {
        return Animal.class;
    } else {
        return null;
    }
});
worksheet.getRange("A1").importData(animals, options);

// Save the workbook.
workbook.save("ImportCustomObjectsfromWeaklyTypedCollection.xlsx");

    Note: To execute the example code, you need to use Animal.java, Cat.java and Dog.java public class files.

    Import Data from Unknown Type of Custom Objects

    Refer to the following example code to import data from an unknown type of custom objects (Duck Typing) to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add Cat and Dog data.
Cat cat1 = new Cat("Christopher", 3, true, "Feather Whip");
Cat cat2 = new Cat("Richter", 2, false, "Laser Pointer");
Dog dog1 = new Dog("Julius", 5, true);
Dog dog2 = new Dog("Leon", 4, false);

// Create an array list.
ArrayList<Object> animals = new ArrayList<>();
animals.add(cat1);
animals.add(cat2);
animals.add(dog1);
animals.add(dog2);

// Import data from collection.
DataImportOptions options = new DataImportOptions();
options.setItemTypeProvider(s -> Object.class);
options.setColumnsSelector(s -> new String[] {"Name", "Age", "IsIndoor", "FavoriteToy", "IsTrained"});
worksheet.getRange("A1").importData(animals, options);

// Save the workbook.
workbook.save("ImportDatafromUnknownTypeofCustomObjectsDuckTyping.xlsx");

    Note: To execute the example code, you need to use Animal.java, Cat.java and Dog.java public class files.

    Import Dynamic Objects

    Refer to the following example code to import data from dynamic objects to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add a Dictionary.
Map<String, Object>[] customers = new Map[]{
        new HashMap<String, Object>() {{
            put("CustomerID", 1);
            put("Name", "John");
            put("CompanyName", "Arasaka");
            put("Contact", "john240891@arasaka.co.jp");
        }},
        new HashMap<String, Object>() {{
            put("CustomerID", 2);
            put("Name", "Mary");
            put("CompanyName", "Militech");
            put("Contact", "mary327670@militech.com");
        }}
};

// Import data from Dictionary.
DataImportOptions options = new DataImportOptions();
options.setColumnsSelector(items -> {
    if (items instanceof Map[]) {
        return new String[]{"CustomerID", "Name", "CompanyName", "Contact"};
    } else {
        return null;
    }
});
worksheet.getRange("A1").importData(customers, options);

// Save the workbook.
workbook.save("ImportDynamicObjects.xlsx");


    Import Table Data

    Refer to the following example code to import data from DataTable to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(Locale.US);
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("ImportDataTable");
sheet1.getRange("A1").setValue("Tour Budget Table");

// Create a DataTable and define its columns.
ResultSetDataTable dataTable = new ResultSetDataTable();
dataTable.getColumns().add("EmployeeID", Integer.class);
dataTable.getColumns().add("Adults", Integer.class);
dataTable.getColumns().add("Children", Integer.class);
dataTable.getColumns().add("Rooms", Integer.class);
dataTable.getColumns().add("Spa", Double.class);
dataTable.getColumns().add("Hotpot", Integer.class);
dataTable.getColumns().add("Budget", Double.class);

// Generate test data. You can set the seed value of the random number generator to get the same data.
Random rnd = new Random(1234);
final int rowCount = 20;

for (int i = 0; i < rowCount; i++) {
    int employeeId = rnd.nextInt(89999) + 10000;
    int adults = rnd.nextInt(3) + 1;
    int children = rnd.nextInt(3);
    int rooms = (int) Math.floor((adults + children) / 2.0);
    double spa = Math.ceil((adults + children) * rnd.nextDouble());
    int hotpot = adults + children;
    double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;

    // Add rows to DataTable.
    dataTable.getRows().add(employeeId, adults, children, rooms, spa, hotpot, budget);
}

// Import the DataTable into the worksheet.
DataImportResult result = sheet1.getRange("A2").importData(dataTable);

// Set range style.
sheet1.getRange(1, 0, 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("Heading 3"));
sheet1.getRange(2, 0, result.getRowsImported() - 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("20% - Accent1"));
sheet1.getRange(1, 0, result.getRowsImported(), result.getColumnsImported()).getEntireColumn().autoFit();
sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1"));
sheet1.getRange("1:2").autoFit();
sheet1.getRange(2, 6, result.getRowsImported(), 1).setNumberFormat("$#,##0.00");
sheet1.getRange(2, 6, result.getRowsImported(), 1).getEntireColumn().setColumnWidth(10);

// Save the workbook.
workbook.save("ImportDataTable.xlsx");


    Import Table Data with Selection

    Refer to the following example code to import data from DataTable with selected rows and columns by DataView to the worksheet:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Set workbook culture to English (US).
workbook.setCulture(Locale.US);
IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
sheet1.setName("ImportDataTable");
sheet1.getRange("A1").setValue("Tour Budget Table");

// Create a DataTable and define its columns.
ResultSetDataTable dataTable = new ResultSetDataTable();
dataTable.getColumns().add("EmployeeID", Integer.class);
dataTable.getColumns().add("Adults", Integer.class);
dataTable.getColumns().add("Children", Integer.class);
dataTable.getColumns().add("Rooms", Integer.class);
dataTable.getColumns().add("Spa", Double.class);
dataTable.getColumns().add("Hotpot", Integer.class);
dataTable.getColumns().add("Budget", Double.class);

// Generate test data. You can set the seed value of the random number generator to get the same data.
Random rnd = new Random(1234);
final int rowCount = 20;

for (int i = 0; i < rowCount; i++) {
    int employeeId = rnd.nextInt(89999) + 10000;
    int adults = rnd.nextInt(3) + 1;
    int children = rnd.nextInt(3);
    int rooms = (int) Math.floor((adults + children) / 2.0);
    double spa = Math.ceil((adults + children) * rnd.nextDouble());
    int hotpot = adults + children;
    double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;

    // Add rows to DataTable.
    dataTable.getRows().add(employeeId, adults, children, rooms, spa, hotpot, budget);
}

// Import the DataTable into the worksheet.
/* Order by budget from high to low and take top five rows.
   Only show the Employee ID, Adults, Children, and Budget. */
DataImportOptions options = new DataImportOptions();
options.setColumnsSelector(it -> {
    if (it instanceof ResultSetDataTable) {
        return new int[]{1, 2, 3, 7};
    }
    return null;
});
DataImportResult result = sheet1.getRange("A2").importData(dataTable, options);

// Set range style.
sheet1.getRange(1, 0, 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("Heading 3"));
sheet1.getRange(2, 0, result.getRowsImported() - 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("20% - Accent1"));
sheet1.getRange(1, 0, result.getRowsImported(), result.getColumnsImported()).getEntireColumn().autoFit();
sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1"));
sheet1.getRange("1:2").autoFit();
sheet1.getRange(2, 6, result.getRowsImported(), 1).setNumberFormat("$#,##0.00");
sheet1.getRange(2, 6, result.getRowsImported(), 1).getEntireColumn().setColumnWidth(10);

// Save the workbook.
workbook.save("ImportDataTableSelectRowsColumns.xlsx");


    Insert Cells if Insufficient Space

    Refer to the following example code to import data and insert a cell if space is insufficient:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add 1-D array of doubles.
double[] doubleData = {1.0, 2.0, 3.0, 4.0, 5.0};

// Add value at A5 to indicate Shift Down.
worksheet.getRange("A5").setValue("Shift down");

// Import array with adding new cells to fit the data.
DataImportOptions options = new DataImportOptions();
options.setInsufficientSpaceHandling(InsufficientSpaceHandling.InsertCells);
worksheet.getRange("A1:A4").importData(doubleData, options);

// Save the workbook.
workbook.save("InsertCellsIfInsufficientSpace.xlsx");


    Limit Fill Range of Data

    Refer to the following example code to import data with a limited fill range:

    Java
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add 1-D array of doubles.
double[] doubleData = {1.0, 2.0, 3.0, 4.0, 5.0};

// Add value at A5 to indicate Existing Data.
worksheet.getRange("A5").setValue("Existing Data");

// Import array with truncated data to fit the data.
DataImportOptions options = new DataImportOptions();
options.setInsufficientSpaceHandling(InsufficientSpaceHandling.Truncate);
worksheet.getRange("A1:A4").importData(doubleData, options);

// Save the workbook.
workbook.save("LimitFillRangeofData.xlsx");

     

    Limitations

    DsExcel Java does not support java.time.Duration and java.time.Period.