Import Data into Worksheet
How to Import Data into Worksheet
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 Workbook class that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Cells collection provides useful methods to import data from different data sources. This article explains how these methods can be used.
How to Import data into Excel with ICellsDataTable interface
Implement ICellsDataTable to wrap your various data sources, then use Cells.ImportData() to import data to Excel worksheet.
Sample Code
The implementation of CustomerDataSource, Customer, and CustomerList classes is given below
How to Import Data into Excel 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.
How to Import Data into Excel from ArrayList
To import data from an ArrayList to worksheets, call the Cells collection’s ImportArrayList method. The ImportArray method takes the following parameters:
- Array list, represents the ArrayList object you’re importing.
- Row number, represents the row number of the first cell that the data will be imported to.
- Column number, represents 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.
How to Import Data into Excel from Custom Objects
To import data from a collection of objects to a worksheet, use ImportCustomObjects. Provide a list of columns/properties to the method to display your desired list of objects.
How to Import Data into Excel from Custom Objects and Check 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 ofImportTableOptions.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.
How to Import Data into Excel from DataTable
To import data from a DataTable, call the Cells collection’s ImportDataTable method. There are many overloaded versions of the ImportDataTable method but a typical overload takes the following parameters:
- Data table, the DataTable object that you’re importing the content from.
- Is field name shown, specifies whether the names of the DataTable columns should be imported to the worksheet as a first row or not.
- Start cell, represents the name of the start cell (for example “A1”) from where to import the contents of the DataTable.
How to Import Data into Excel from dynamic object as data source
Aspose.Cells provides features to work with dynamic objects as datasource. It helps in using datasource where properties are added dynamically to the objects. Once the properties are added to the object, Aspose.Cells considers the first entry as the template and handles the rest accordingly. It means if some dynamic property is added to a first item only and not to other objects, Aspose.Cells considers that all items in the collection should be the same.
In this example, a template model is used which initially contains two variables only. This List is converted to List of dynamic objects. Then some additional field is added into it and finally loaded into the workbook. The workbook picks only those values which are in the template XLSX file. This template workbook uses Smart Markers which also contain parameters. Parameters allow you to modify how the information is laid out. Details about the Smart Markers can be obtained from the following article:
How to Import DataColumn into Excel
A DataTable or DataView object is composed of one or more columns. Developers can also import data from any Column/Columns of the DataTable or DataView by calling the ImportData method of the Cells collection. The ImportData method accepts a parameter of type ImportTableOptions. The ImportTableOptions class provides a ColumnIndexes property that accepts an array of columns indexes.
The sample code given below demonstrates the use of ImportTableOptions.ColumnIndexes to import selective columns.
How to Import DataView into Excel
To import data from a DataView, call the Cells collection’s ImportData method. There are many overloaded versions of the ImportData method but the one for DataView takes the following parameters:
- DataView: The DataView object that you’re about to import content from.
- First Row: the row number of the first cell that the data will be imported to.
- First Column: the column number of the first cell that the data will be imported to.
- ImportTableOptions: The import options.
How to Import DataGrid into Excel
It is possible to import data from a DataGrid by calling the ImportDataGrid method of the Cells collection. There are many overloaded versions of the ImportDataGrid method but a typical overload takes the following parameters:
- Data grid, the DataGrid 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.
- Insert Rows, a Boolean property that indicates whether extra rows should be added to the worksheet to fit data or not.
How to Import GridView into Excel
To import data from a GridView control, call the ImportGridView method of the Cells collection.
Aspose.Cells allows us to respect HTML formatted values while importing data to the spreadsheet. When HTML parsing is enabled while importing data, Aspose.Cells converts the HTML into corresponding cell formatting.
How to Import HTML formatted data into Excel
Aspose.Cells provides a Cells class that provides very useful methods for importing data from external data sources. This article shows how to parse HTML formatted text while importing data and convert the HTML into formatted cell values.
How to Import Data into Excel 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.