Read Excel to DataTabe (Custom)

[SyncfusionUser1020]

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]

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.    Documentation Link:  https://help.syncfusion.com/file-formats/xlsio/overview

 

Please let us know if you have any concerns. 

 

Regards, 

Abirami. 

[SyncfusionUser1020]

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

[Abirami Varadharajan]

Hi Patrick, 

 

You are most welcome. 

 

Please let us know if you need any further assistance. 

 

Regards, 

Abirami.