We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Read Excel to DataTabe (Custom)

Thread ID:

Created:

Updated:

Platform:

Replies:

130419 May 11,2017 07:40 PM UTC May 18,2017 11:19 AM UTC WPF 3
loading
Tags: XlsIO
SyncfusionUser1020
Asked On May 11, 2017 07:40 PM UTC

Hello

I am wanting to convert an Excel Sheet to a data table using custom code as I want to handle merge cells and other items later differntly. I previously wrote something to read to a datatable with EPPlus and have got the code below to hopefully give an idea of what I want to start with in XLSIO (screenshot and Text below).

I think the biggest issue is trying to find what XLSIO equivalent is for the various classes. Some mappings are simple for example IWorksheet but overs such as Dimensions (ExcelAddressBase) I’m struggling with.

Any advice on the equivalent types, converting the code or better approach using XLSIO would be much appreciated.

Thank You


ExcelCellAddress startInfomration = worksheet.Dimension.Start;
            DataTable worksheetTable = new DataTable();

            // Add DataTable Headers.
            for (int i = 0; i < worksheet.Dimension.Columns; i++)
            {
                worksheetTable.Columns.Add(worksheet.Cells[startInfomration.Row, i + 1].Text);
            }

            // Add DataTable Rows.
            for (int i = startInfomration.Row; i < worksheet.Dimension.Rows; i++)
            {
                int currentRow = i + 1;
                ExcelRange worksheetRow = worksheet.Cells[currentRow, startInfomration.Column, currentRow, worksheet.Dimension.Columns];
                DataRow row = worksheetTable.NewRow();

                foreach (ExcelRangeBase cell in worksheetRow)
                {
                    string value = "";

                    if (cell.Merge)
                    {
                        string mergedCellRange = worksheet.MergedCells[cell.Start.Row, cell.Start.Column];
                        value = worksheet.Cells[mergedCellRange].First().Text.ToString();
                    }
                    else
                    {
                        value = cell.Text;
                    }

                    row[cell.Start.Column - 1] = value;
                }

                worksheetTable.Rows.Add(row);
            }


Abirami Varadharajan [Syncfusion]
Replied On May 12, 2017 09:57 AM UTC

Hi Patrick, 
 
Thank you for contacting Syncfusion support. 
 
Please find the details for your queries below. 
 
Queries 
Details 
I am wanting to convert an Excel Sheet to a data table using custom code as I want to handle merge cells and other items later differntly. I previously wrote something to read to a datatable with EPPlus and have got the code below to hopefully give an idea of what I want to start with in XLSIO (screenshot and Text below). 
 
We have prepared sample as per your requirement and the sample can be downloaded from the following link. 
 
Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Sample-295364010 

Also, we have a ExportDataTable method to export the worksheet data to a DataTable. Kindly refer the below documentation link to know more about export to DataTable.

UG Link: https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-data-table
 
I think the biggest issue is trying to find what XLSIO equivalent is for the various classes. Some mappings are simple for example IWorksheet but overs such as Dimensions (ExcelAddressBase) I’m struggling with. 
Any advice on the equivalent types, converting the code or better approach using XLSIO would be much appreciated.  
 
We have a IWorksheet.UsedRange property to get the total used cells from a worksheet.  
 
Kindly refer our documentation below to know more about XlsIO. 
 
 
Please let us know if you have any concerns. 
 
Regards, 
Abirami. 


SyncfusionUser1020
Replied On May 16, 2017 05:32 PM UTC

Thanks for the sample- know were I went wrong now.

Abirami Varadharajan [Syncfusion]
Replied On May 18, 2017 11:19 AM UTC

Hi Patrick, 
 
You are most welcome. 
 
Please let us know if you need any further assistance. 
 
Regards, 
Abirami. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;