Import from excel file


#1

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


#2

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


#3

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


#4

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


#5

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.


#6

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


#7

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