Import from excel file

Hi Laurence,

I am testing your product, so far it works great. Just have a few questions

I want to import data from excel to our sql server database communicating by asp.net(c#). Which method I need to call? cell.ExportDataTable?

How to tell this method that the first row is the column name or the first column is the column name?

for example:

case 1:

name age
a 34
b 55

case 2:

name a b
age 34 55

How to use this method to import these two excel file including the column name?

Thanks

Sincerely,

Tao

Yes. You can use Cells.ExportDataTable method to export data from Excel files and populate them to database. Please check `http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.ExportDataTable.html`.

Please set exportColumnName parameter to true to include the column name.

For case 1, do you want to export data horizontally? A column data in Excel file represents a row in datatable, doesn't it? Aspose.Excel doesn't support such a feature now. I will add a new overloaded ExportDataTable method.

For case 2, Aspose.Excel supports it.

For example,

A B C

1 a b

2 34 55

You can use the following sample code:

DataTable dt = cells.ExportDataTable(0, 0, 2, 2, true);

Hi Laurence,

I think.aspose.excel support case 1 not case 2. A row data in Excel file represent a row in datatable. is that right? Store data in excel normally in two ways--vertical and horizontal. I need to identify which direction the headers are going in order to determine if the data is a row or a column. so i can import it accordingly. Please let me know when you can have the overloaded ExportDataTable method ready.

Thanks

Tao

Hi Laurence,

another question. How to calculate the maxrows or maxcolumns if I only know the startHeadercell is A1 and endHeadercell is D1

Is there a way to convert column Letter to a number?

Thanks

Tao

Hi Tao,

Sorry. I misundertood your case. Yes, currently Aspose.Excel supports case 1. I had thought "a" and "b" is header name.

To convert column name to index, please try Cells.ColumnNameToIndex method.

Thanks Laurence, I solved the problem. Can you tell me when you can have the overloaded ExportDataTable method ready? I will place an order pretty soon.

Thanks

Tao

It's available now. Please try this attached fix.

public DataTable ExportDataTable(int firstRow, byte firstColumn, int maxRows, int maxColumns, bool exportColumnName, bool isVertical)

DataTable dt = excel.Worksheets[0].Cells.ExportDataTable(0, 0, 3, 3, false, false);