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.

Read Excel to DataTabe (Custom)

Thread ID:

Created:

Updated:

Platform:

Replies:

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

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 05:57 AM

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 01:32 PM

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

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

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.

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.

;