How to add Multiple excel data into single data table

[sateesh kumar]

hi, am able to add the excel data to data table vise versa.

now am trying to load multiple excel data into single data table.

this data table want bind data grid.

am trying like this,but not working. 

string[] filesToProcess = Directory.GetFiles(sourcePath);

            foreach(string file in filesToProcess)

            {

                if (!Path.GetExtension(file).ToString().ToLower().Equals(".xls"))

                    Console.WriteLine("Invalid File"+Path.GetFileName(file));

                if (Path.GetFileNameWithoutExtension(file).ToString().ToLower().StartsWith("bills under lc", StringComparison.InvariantCultureIgnoreCase)) ;

                wBook = app.Workbooks.Open(file);

                sheet = wBook.Worksheets[0];

                dt = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

            }

am able to load the excel data, its not concate to previous data.

help me out this.

Thanks for the advance,

J.Sateesh Kumar

[Mohanraj Gunasekaran]

Hi Sateesh, 

 

Thanks for using Syncfusion product. 

 

By default, IWorkSheet.ExportDataTable method can used to generate the DataTable based on the provided range. So, if you want to merge multiple excel files data in a DataTable, you can use the existing DataTable.Merge method. Please refer to the below code example and the sample, 

 

Code example 

DataTable copytable; 

DataTable dt; 

foreach(string file in filesToProcess) 

{ 

    if (!Path.GetExtension(file).ToString().ToLower().Equals(".xlsx")) 

        Console.WriteLine("Invalid File"+Path.GetFileName(file)); 

    if (Path.GetFileNameWithoutExtension(file).ToString().ToLower().StartsWith("data", StringComparison.InvariantCultureIgnoreCase)) ; 

    workbook = application.Workbooks.Open(file); 

    worksheet = workbook.Worksheets[0]; 

    copytable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); 

    dt.Merge(copytable); 

} 

 

 

Sample link: GridControl 

 

Regards, 

Mohanraj G