ImportData method not working properly when upgraded to version 8.5.1.0

Aspose.Cells Product Family
1

Hi,


We were using Aspose with version 5.0 previously. Now we have upgraded to Aspose 8.5.1.0.
As the ImportDataColumn method is deprecated, we have replaced it with ImportData method. The
data is not being propely imported into the excel.

As an example,

Aspose 5.0
columnList As String()
counter As Integer

sheet.Cells.ImportDataColumn(table, True, 0, counter, columnList (counter), False)

Aspose 8.5.1.0

Dim tblOptForcol As New ImportTableOptions
tblOptForcol.IsFieldNameShown = True
tblOptForcol.InsertRows = False
tblOptForcol.ColumnIndexes ={counter}

sheet.Cells.ImportData(table, 0, _counter, tblOptForcol)

The data in the datatable(table) is directly mapped into the sheet without matching the column columnlist(counter).

Please let us know the specifications of the ColumnIndexes that has to be given to the ImportTableOptionsin this case.

Also can you please give the link of the new Aspose Documentation, as it is very difficult to post for every issue we face. Please note we do we have a license.

Thanks,
Varsha

2

Hi Varsha,


Thank you for contacting Aspose support.

What I understand from your problem description is that you wish to import specific columns from the source DataTable rather than importing everything. If I am correct in my understanding then you need to use the ImportTableOptions.ColumnIndexes property, that could accept an array of integers. That means, you can specify the column indices in an array and set it as ColumnIndexes. Please check the following piece of code for better understanding.

VB.NET

’ Here we create a DataTable with four columns.
Dim table As New DataTable()
table.Columns.Add(“Dosage”, GetType(Integer))
table.Columns.Add(“Drug”, GetType(String))
table.Columns.Add(“Patient”, GetType(String))
table.Columns.Add(“Date”, GetType(DateTime))

’ Here we add five DataRows.
table.Rows.Add(25, “Indocin”, “David”, DateTime.Now)
table.Rows.Add(50, “Enebrel”, “Sam”, DateTime.Now)
table.Rows.Add(10, “Hydralazine”, “Christoff”, DateTime.Now)
table.Rows.Add(21, “Combivent”, “Janet”, DateTime.Now)
table.Rows.Add(100, “Dilantin”, “Melanie”, DateTime.Now)

Dim book = New Workbook()
Dim importOptions = New ImportTableOptions()
’ Specify the list of columns to be imported in form of Integer Array
importOptions.ColumnIndexes = New Integer() {1, 2}
’ Only 2nd & 3rd columns will be imported i.e Drug & Patient
book.Worksheets(0).Cells.ImportData(table, 0, 0, importOptions)
book.Save(dir + “output.xlsx”)


That said, if I have misunderstood your requirement then please explain with the help of examples. Regarding the documentation, the online programmer's guide as well as API reference guide is always up-to-date according to the latest releases. You can check it from here. If you wish to check the documentation specific to your current version, you can find it in the help folder of the Aspose.Cells for .NET installation directory on your machine.
3

Thanks Babar. You have got my question right.


But in this case you are sure of the requirement as “Drug” and “Patient” and hence you have given the array values as {1,2}. How do I do it if I am not sure of which column, I mean how do we give if my required column list varies to “Patient” and “Dosage” next time.

Thanks,
Varsha
4

Hi Varsha,


Thank you for the confirmation. Regarding your recent concerns, the mentioned APIs require the list of column indices to import the corresponding data onto the worksheet. If you will be getting the data with different column positions then you have to devise some mechanism to identify the required column (name or index). By the way, how were you achieving the same goal before upgrading the application to use latest APIs?
5

Hi Babar,


As an example,

Table: colList1: colList2:
1 PatientID
2 Name Name Name
3 Age Age Age
4 Father’s Name
5 Drug Drug
6 Dosage Dosage
7 Address Address
8 Telephone Telephone

We are not sure whether to use colList1 or colList2. So we cannot initialize the array to fixed values as {2,3,5,6} or {2,3,7,8} as the array values are variable.

Previously we used the following code:
sheet.Cells.ImportDataColumn(table, True, 0, counter, colList(counter), False)
where colList(counter) is a string which gives the column name.

So when I give the counter value to Table Options as
Dim tblOptForcol As New ImportTableOptions
tblOptForcol.ColumnIndexes = {counter}

My excel headers is not matching the data in the column. It appears like this.
PatientName Age Drug Dosage
PatientName1 Age1 Father's Name1 Drug1
PatientName2 Age2 Father's Name2 Drug2

How do we achieve this functionality. Please let me know.

Thanks,
Varsha


6

Hi Varsha,


Thank you for explaining the scenario further.

As discussed earlier, you must have a mapping mechanism to map the columns from DataTable to the Worksheet in order to get the correct results. Moreover, this feature has to be implemented outside the Aspose.Cells for .NET APIs because these APIs do not provide such facility.

Please check the attached spreadsheets and the following piece of code. Please note, the code first extracts the column names from the source DataTable and build a mapping using the Dictionary. Based on the mapping, the Aspose.Cells APIs import only the columns that are present in the source Workbook (book1.xlsx) in same sequence as they occur.

VB.NET

Dim table As New DataTable()
table.Columns.Add(“Id”, GetType(Integer))
table.Columns.Add(“Patient”, GetType(String))
table.Columns.Add(“Father Name”, GetType(String))
table.Columns.Add(“Address”, GetType(String))
table.Columns.Add(“Age”, GetType(Integer))
table.Columns.Add(“Dosage”, GetType(Integer))
table.Columns.Add(“Drug”, GetType(String))
table.Columns.Add(“Date”, GetType(Date))

table.Rows.Add(1, “David”, “Mike”, “123, Paris Road”, 36, 12, “Incidol”, Date.Now)
table.Rows.Add(3, “Harrison”, “Ford”, “12, Parkin Road”, 24, 1, “Rupar”, Date.Now)
table.Rows.Add(4, “Jessica”, “Jones”, “209, Silver line Street”, 22, 4, “Mxzim”, Date.Now)
table.Rows.Add(6, “David”, “Harp”, “2, Kingston Street”, 45, 1, “Disprin”, Date.Now)
table.Rows.Add(7, “John”, “Doe”, “23, Kings Road”, 11, 3, “Febrol”, Date.Now)
table.Rows.Add(9, “Jenny”, “Leon”, “123, South Alberta”, 34, 2, “Novidet”, Date.Now)

Dim dict As Dictionary(Of Integer, String) = New Dictionary(Of Integer, String)()
Dim index As Integer = 0
For Each tableColumn As DataColumn In table.Columns
dict(index) = tableColumn.ColumnName
index += 1
Next tableColumn

Dim book = New Workbook(Dir & “book1.xlsx”)
Dim sheet = book.Worksheets(0)
Dim cells = sheet.Cells
Dim indices(cells.MaxDataColumn) As Integer
Dim i As Integer = 0
Do While i < cells.MaxDataColumn + 1
Dim columnHeaderName As String = cells(0, i).StringValue
Dim keyMatch = dict.Where(Function(p) p.Value = columnHeaderName).Select(Function(p) p.Key)
For Each key As Integer In keyMatch
Dim colunmIndex As Integer = CInt(key)
indices(i) = colunmIndex
Next key
i += 1
Loop
Dim importOptions = New ImportTableOptions()
importOptions.IsFieldNameShown = False
importOptions.ColumnIndexes = indices

book.Worksheets(0).Cells.ImportData(table, 1, 0, importOptions)
book.Save(Dir & “output2.xlsx”)


Please note, the code shared above is for demonstration purposes. You may amend it to suit your application requirements.