Import and Export Data

Import Data into Worksheet

Data represents the world as it is. To make sense of data, we analyze it and gain an understanding of the world. Data turns into information.

There are many ways of performing analysis: putting data into spreadsheets and manipulating it in different ways is one common method. With Aspose.Cells, it is easy to create spreadsheets that take data from a range of external sources and prepare them for analysis.

This article discusses some data import techniques that developers have access to through Aspose.Cells.

Importing Data Using Aspose.Cells

When you open an Excel file with Aspose.Cells, all data in the file is automatically imported. Aspose.Cells can also import data from other data sources:

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains the collection WorksheetCollection which allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Cells collection provides very useful methods for importing data from other data sources. This article explains how these methods can be used.

Importing from Array

To import data to a spreadsheet from an array, call the importArray method of the Cells collection. There are many overloaded versions of the importArray method but a typical overload takes the following parameters:

  • Array, the array object that you’re importing content from.
  • Row number, the row number of the first cell that the data will be imported to.
  • Column number, the column number of the first cell that the data will be imported to.
  • Is vertical, a Boolean value that specifies whether to import data vertically or horizontally.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromArray.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Creating an array containing names as string values
String[] names = new String[] { "laurence chen", "roman korchagin", "kyle huang" };
// Importing the array of names to 1st row and first column vertically
Cells cells = worksheet.getCells();
cells.importArray(names, 0, 0, false);
// Saving the Excel file
workbook.save(dataDir + "ImportingFromArray_out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");

Importing from Multi-dimensional Arrays

To import data to a spreadsheet from multi-dimensional arrays, call the relevant importArray overload of the Cells collection:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromMultiDimensionalArray.class) + "data/";
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first worksheet (default sheet) in the Workbook
Cells cells = workbook.getWorksheets().get("Sheet1").getCells();
// Define a multi-dimensional array and store some data into it.
String[][] strArray = { { "A", "1A", "2A" }, { "B", "2B", "3B" } };
// Import the multi-dimensional array to the sheet
cells.importArray(strArray, 0, 0);
// Save the Excel file
workbook.save(dataDir + "IFMDA_out.xlsx");

Importing from an ArrayList

To import data from an ArrayList to worksheets, call the ImportArrayList method of the Cells collection. The ImportArrayList method takes the following parameters:

  • ArrayList, the ArrayList object whose contents will be imported.
  • Row Number, the row number of the first cell of the cell range from which contents will be imported.
  • Column Number, the column number of the first cell from which data will be imported.
  • Is Vertical, is a Boolean value that specifies whether to import data vertically or horizontally.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromArrayList.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Instantiating an ArrayList object
ArrayList list = new ArrayList();
// Add few names to the list as string values
list.add("laurence chen");
list.add("roman korchagin");
list.add("kyle huang");
list.add("tommy wang");
// Importing the contents of ArrayList to 1st row and first column
// vertically
worksheet.getCells().importArrayList(list, 0, 0, true);
// Saving the Excel file
workbook.save(dataDir + "IFromArrayList_out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");

Importing from Custom Objects to merged area

To import data from a collection of objects to a worksheet containing merged cells, use ImportTableOptions.CheckMergedCells property. If the Excel template has merged cells, set the value of ImportTableOptions.CheckMergedCells property to true. Pass the ImportTableOptions object along with the list of columns/properties to the method to display your desired list of objects. The following code sample demonstrates the use of ImportTableOptions.CheckMergedCells property to import data from Custom Objects to merged cells. Please see the attached source Excel file and the output Excel file for reference.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromArrayList.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Instantiating an ArrayList object
ArrayList list = new ArrayList();
// Add few names to the list as string values
list.add("laurence chen");
list.add("roman korchagin");
list.add("kyle huang");
list.add("tommy wang");
// Importing the contents of ArrayList to 1st row and first column
// vertically
worksheet.getCells().importArrayList(list, 0, 0, true);
// Saving the Excel file
workbook.save(dataDir + "IFromArrayList_out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");

Importing Data from JSON

Aspose.Cells provides a JsonUtility class for processing JSON. JsonUtility class has an ImportData method for importing JSON data. Aspose.Cells also provides a JsonLayoutOptions class that represents the options of JSON layout. The ImportData method accepts JsonLayoutOptions as a parameter. The JsonLayoutOptions class provides the following properties.

  • ArrayAsTable: Indicates in the array should be processed as a table or not.
  • ConvertNumericOrDate: Gets or sets a value that indicates whether the string in JSON is to be converted to numeric or date.
  • DateFormat: Gets and sets the format of the date value.
  • IgnoreArrayTitle: Indicates whether to ignore the title if the property of the object is an array
  • IgnoreNull: Indicates whether the null value should be ignored or not.
  • IgnoreObjectTitle: Indicates whether to ignore the title if the property of the object is an object.
  • NumberFormat: Gets and sets the format of numeric value.
  • TitleStyle: Gets and sets the style of the title.

The sample code given below demonstrates the use of the JsonUtility and JsonLayoutOptions classes to import JSON data.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromJson.class) + "Data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
// Read File
File file = new File(dataDir + "Test.json");
BufferedReader bufferedReader = new BufferedReader(new FileReader(file));
String jsonInput = "";
String tempString;
while ((tempString = bufferedReader.readLine()) != null) {
jsonInput = jsonInput + tempString;
}
bufferedReader.close();
// 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(jsonInput, worksheet.getCells(), 0, 0, options);
// Save Excel file
workbook.save(dataDir + "ImportingFromJson.out.xlsx");

Export Data from Worksheet

Aspose.Cells not only lets its users import data to worksheets from external data sources but also allow them to export worksheet data to an array.

Exporting Data Using Aspose.Cells - Exporting Data to Array

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection.

Data can easily be exported to an Array object using the Cells class' exportArray method.

Columns Containing Strongly Typed Data

Spreadsheets stores data as a sequence of rows and columns. Use the exportArray method to export the data from a worksheet to an array. exportArray takes the following parameters to export worksheet data as an Array object:

  • Row number, the row number of the first cell the data will be exported from.
  • Column number, the column number of the first cell from where the data will be exported
  • Number of rows, the number of rows to export.
  • Number of columns, the number of columns to export.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ExportingDataFromWorksheets.class) + "Data/";
// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(dataDir + "book1.xls");
// Instantiating a Workbook object
Workbook workbook = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
// Exporting the contents of 7 rows and 2 columns starting from 1st cell
// to Array.
Object dataTable[][] = worksheet.getCells().exportArray(0, 0, 7, 2);
// Printing the number of rows exported
System.out.println("No. Of Rows Exported: " + dataTable.length);
// Closing the file stream to free all resources
fstream.close();

Advance topics

  • Import Data from Microsoft Access Database ResultSet Object to the Worksheet
  • Specify Formula Fields while Importing Data to Worksheet