Getting the Column Length from DataTable when exporting DataTable from excel data using ExportDataTable()

Aspose.Cells Product Family
1

Hi,
I am using aspose.Cell to retrieve the data from excel and create a SQL Table in SQL server.
I used ExportDataTable() to create datatable , and i am using Datatable.column[i].MaxLength to get the length of each column as i need to create the table based on column length ex. varchar(Columnlength), but it returns -1, while you can get the length of the column from datatable by using Datatable.column[i].MaxLength, please let me know if anyone knows the solution, Thanks.
this is my code:

Workbook workbook = new Workbook(excelFile);
Worksheet wkSheetXL = workbook.Worksheets[“test”];
DataTable dttest = wkSheetXL.Cells.ExportDataTable(0, 0, wkSheetXL.Cells.Rows.Count, wkSheetXL.Cells.MaxDataColumn + 1, true);
foreach (DataColumn item in dttest.Columns)
{
int length = item.MaxLength;
string name = item.ColumnName;
}

2

@omar.sidiqy,

It does not seems to be an issue with Aspose.Cells APIs when exporting to datatable. Please note, when you do not specify the maximum length, the value is -1 (default). Moreover, the property is ignored for non-text columns. See the API Reference page for your reference.

By the way, there seems to me an issue with the line of code:

DataTable dttest = wkSheetXL.Cells.ExportDataTable(0, 0, wkSheetXL.Cells.Rows.Count, wkSheetXL.Cells.MaxDataColumn + 1, true);

it should be:

DataTable dttest = wkSheetXL.Cells.ExportDataTable(0, 0, wkSheetXL.Cells.MaxDataRow + 1, wkSheetXL.Cells.MaxDataColumn + 1, true);
3

@Amjad_Sahi
thanks for your reply, is there any way to get the length of columns from datatable or get the length of the value with the most max length from datatable for each column?
exactly I need to know the maximum length of values in columns and create SQL table based on them.
note: when you export a datatable from database you are able to get length, look into this code

string query = “Select * from [dbo].[test]”;
DataTable dttest = ExecuteSelectQuery( query);
foreach (DataColumn item in dttest.Columns)
{
int length = item.MaxLength;
string name = item.ColumnName;
}

4

@omar.sidiqy
DataColumn.MaxLength property is not set because it is not required.
Please check it by the following codes:
private static int[] GetMaxColumnLenth(DataTable dt)
{
int[] ret = new int[dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < ret.Length; j++)
{
string str = dt.Rows[i][j] as string;
if (!string.IsNullOrEmpty(str))
{
if (ret[j] < str.Length)
{
ret[j] = str.Length;

                    }
                }
            }
        }
        return ret;
    }
1 Like