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(); |