Exporting DataTable with ColumnNames

Aspose.Cells Product Family
1

I know I can use the functions, ExportDataTable() and ExportDataTableAsString() to get my Excel data into a DataTable object.

But is there a way to export the Excel data to a DataTable, such that the first line of the Excel file becomes DataColumn objects in my DataTable?

I’ve tried exporting the DataTable, and then looping through and renaming each Column of my DataTable according to the first row, and then removing the first row, like this:

DataTable xlTable = wksht.Cells.ExportDataTableAsString(0, 0, wksht.Cells.MaxRow + 1, wksht.Cells.MaxColumn + 1);

foreach (DataColumn col in xlTable.Columns)
col.ColumnName = xlTable.Rows[0][col].ToString();

xlTable.Rows.RemoveAt(0);

And this works (sort of), but it causes the Column names to be case sensitive (I have no idea why), but I need them to be case insensitive, as is the case when you add a column using Columns.Add(“MyColumn”);

Any ideas?
-Steve

2

Dear Steve,

You suggestion is great. I will add this support in future release.

About the case sensative issue, I think it may be a document bug of Microsoft. Actually the column name in datatable columns is case insensitive. You can test it by this:

DataTable dt = new DataTable();
dt.Columns.Add(“first”);
dt.Columns.Add(“FIRST”);

You will will find it works without any problem. But that is not consistent with MS documentation.

So you can try the workaround to convert all the columns names to upper or lower case.

3

Dear Steve,

You suggestion is great. I will add this support in future release.

About the case sensative issue, I think it may be a document bug of Microsoft. Actually the column name in datatable columns is case sensitive. You can test it by this:

DataTable dt = new DataTable();
dt.Columns.Add(“first”);
dt.Columns.Add(“FIRST”);

You will will find it works without any problem. But that is not consistent with MS documentation.

So you can try the workaround to convert all the columns names to upper or lower case.

4

Laurence,

Hi. Thanks for your reply. I actually read the Microsoft documentation, and according to it, ColumnNames are supposed to be case sensitive conditionally. In other words, if there is a Column named “MyColumn” and a column named “myColumn”, then a search for “mycolumn” will not return anything – b/c case sensitivity would be in place.

But if there was only the column “MyColumn”, then a search for “mycolumn” would return that column.

The problem seems to be if you rename a column. For example:

myColumn.ColumnName = “Renamed”;

Once you do that, it appears that the ColumnName becomes case sensitive, and that may actually be a Microsoft bug? Can anyone confirm this?

-Steve


5

Hi Steve,

I am not sure about if this is a MS bug. You can try following workaround.

You can convert all columns names to upper case and put them in a hashtable. Before your add a new column name, also convert it to upper case and check if it is alreadly contained in the hashtable.

6

Hi.

I was wondering if the latest version (2.4) offers the feature I requested earlier – exporting to DataTable using the first line of an excel sheet as the names for the DataColumns?

I noticed that ExportDataTable now takes an object array. Is there were that information would be specified?

Also, can it be done with the function ExportDataTableAsString?

Thanks
-Steve

7

Dear Steve,

This feature will be available in v2.4.1 which will be released before the start of next week.

The object array parameter is used to set default values for the exported data table.

8

hotfix 2.4.1 is released and this feature is available. Please check http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.ExportDataTable2.html 30 and http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.ExportDataTableAsString2.html.

9

Laurence,

I downloaded the lastest release and tried out the ExportDataTableAsString() function to see if I could export a DataTable with column names.

It does in fact work, but I’m still having a problem. I think it may be due to the way the function is creating the table.

Does the function simply export everything to a datatable, then rename each column in the DataTable according to the first line of the Excel file, and then delete row 0 of the DataTable?

This is what I was doing manually, and it was causing me problems because of an apparent bug with renaming DataColumns in .NET.

Any insight to how this function works would be appreciated.
-Steve

10

Yes. I create the datatable and set the column name according to the first row data, then export data from second row to the last.

11

@sstchur,
Aspose.Excel is discontinued now and no more under development. It is replaced with Aspose.Cells which is much advanced in terms of features and performance as compared to its predecessor. We can export data from a worksheet in a variety of ways. Following is an example of exporting data from a worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

string filePath = dataDir + "Book1.xlsx";

// Instantiating a Workbook object
Workbook workbook = new Workbook(filePath);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 11, 2, true);

foreach (DataRow r in dataTable.Rows)
{
    foreach (DataColumn c in dataTable.Columns)
    {
        string value = r.Field<string>(c);
        Console.Write(value + "            ");
    }
    Console.WriteLine();
}

Refer to the following article for more details on exporting data from worksheet:
Export Data from Worksheet

Give a try to this new product by downloading the latest free trial version here:
Aspose.Cells for .NET(Latest version)

You may download a runnable solution here for detailed testing of this new product.