none
How to convert Excel sheet to data table dynamically in simple way.. RRS feed

  • Question

  • I want to convert excel to data table.

    Input template

    Output data table

    Note : eliminate the first 9 rows from the input template for the output. for output data, we need to consider only from row no. 10



    • Edited by Gani tpt Friday, January 31, 2020 8:10 AM dynamically added
    Tuesday, January 28, 2020 1:44 PM

Answers

  • Hi Gani tpt,

    I make a change to my code and use the following code to get the last filled excel row and column.

                Excel.Range last = wsht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                Excel.Range range = wsht.get_Range("A1", last);
    
                int lastUsedRow = last.Row;
                int lastUsedColumn = last.Column;

    Here's the whole code:

            private void button3_Click(object sender, EventArgs e)
            {
                DataTable dataTable = GetExcelDataTable("your file path",11,"A");
            }
            static DataTable GetExcelDataTable(string path, int top, string leftString)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook XLWB = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel._Worksheet wsht = XLWB.ActiveSheet;
    
                Excel.Range last = wsht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                Excel.Range range = wsht.get_Range("A1", last);
    
                int lastUsedRow = last.Row;
                int lastUsedColumn = last.Column;
    
    
                int leftNumber = ExcelColumnNameToNumber(leftString);
                DataTable dt = new DataTable();
              
                for (int i= leftNumber; i< lastUsedColumn + 1; i++)
                {
                    dt.Columns.Add(GetExcelColumnName(i));
                }
                for (int i=0;i<(lastUsedRow - top + 1);i++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int j = 0; j < (lastUsedColumn - leftNumber + 1); j++)
                    {                            
                        string s = Convert.ToString(wsht.Cells[top + i, leftNumber + j].Value);
    
                        dataRow[j] = s;
                        if (j == lastUsedColumn - leftNumber)
                        {
                            dt.Rows.Add(dataRow);
                        }                   
                    }              
                }
                return dt;
            }
            public static int ExcelColumnNameToNumber(string columnName)
            {
                if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
    
                columnName = columnName.ToUpperInvariant();
    
                int sum = 0;
    
                for (int i = 0; i < columnName.Length; i++)
                {
                    sum *= 26;
                    sum += (columnName[i] - 'A' + 1);
                }
    
                return sum;
            }
            public static string GetExcelColumnName(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
    
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
                return columnName;
            }

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Thursday, January 30, 2020 3:34 AM
    Moderator
  • Hi Gani tpt,

    Thank you for posting here.

    According to your description, I make a test on my side and successfully convert excel to DataTable.

    Here's my code:

            private void button3_Click(object sender, EventArgs e)
            {
                DataTable dataTable = GetExcelDataTable("your file path",11,14,"A","H");
            }
            static DataTable GetExcelDataTable(string path, int top, int bottom, string leftString, string rightString)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook XLWB = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel._Worksheet wsht = XLWB.ActiveSheet;
                int leftNumber = ExcelColumnNameToNumber(leftString);
                int rightNumber = ExcelColumnNameToNumber(rightString);
                DataTable dt = new DataTable();
    
                for (int i= leftNumber; i< rightNumber + 1; i++)
                {
                    dt.Columns.Add(GetExcelColumnName(i));
                }
                for (int i=0;i<(bottom - top + 1);i++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int j = 0; j < (rightNumber - leftNumber + 1); j++)
                    {                            
                        string s = Convert.ToString(wsht.Cells[top + i, leftNumber + j].Value);
    
                        dataRow[j] = s;
                        if (j == rightNumber - leftNumber)
                        {
                            dt.Rows.Add(dataRow);
                        }                   
                    }              
                }
                return dt;
            }
            public static int ExcelColumnNameToNumber(string columnName)
            {
                if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
    
                columnName = columnName.ToUpperInvariant();
    
                int sum = 0;
    
                for (int i = 0; i < columnName.Length; i++)
                {
                    sum *= 26;
                    sum += (columnName[i] - 'A' + 1);
                }
    
                return sum;
            }
            public static string GetExcelColumnName(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
    
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
                return columnName;
            }

    My excel:

    Result of the test:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Thursday, January 30, 2020 7:39 AM
    Wednesday, January 29, 2020 5:49 AM
    Moderator
  • As an alternative solution, you can search and install spire.xls from nuget, then use the below code to convert excel to data table.

    using Spire.Xls;
    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace ExcelToDatatable
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Workbook workbook = new Workbook();
                //Load excel file
                workbook.LoadFromFile("input.xlsx");
                //get the first worksheet
                Worksheet sheet = workbook.Worksheets[0];
                //convert to a data table
                DataTable dt = sheet.ExportDataTable(10, 1, sheet.LastRow, sheet.LastColumn, false);
            }
        }
    }

    • Marked as answer by Gani tpt Thursday, February 6, 2020 11:35 AM
    Tuesday, February 4, 2020 9:54 AM

All replies

  • Here is a basic code sample using SpreadSheetLight. Install via NuGet on it's own page with instructions.

    using System;
    using System.Data;
    using SpreadsheetLight;
    
    namespace SpreadSheetLightProject
    {
        public class Operations
        {
            public DataTable Import(string fileName, string sheetName)
            {
                var dt = new DataTable();
                ;
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_id", DataType = typeof(string)});
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_name", DataType = typeof(string)});
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_salary", DataType = typeof(int)});
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_JoinDate", DataType = typeof(DateTime)});
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_RelievingDate", DataType = typeof(DateTime)});
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_supervistor", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "emp_Country", DataType = typeof(string) });
    
                using (var doc = new SLDocument(fileName, sheetName)) 
                {
                    var stats = doc.GetWorksheetStatistics();
    
                    for (int index = 10; index < stats.EndRowIndex +1; index++)
                    {
                        dt.Rows.Add(new object[]
                        {
                            doc.GetCellValueAsString(index, 1),
                            doc.GetCellValueAsString(index, 2),
                            doc.GetCellValueAsInt32(index, 3),
                            doc.GetCellValueAsDateTime(index, 4),
                            doc.GetCellValueAsDateTime(index, 5),
                            doc.GetCellValueAsString(index, 7),
                            doc.GetCellValueAsString(index, 11)
                        });
                    }
                }
    
                return dt;
    
            }
        }
    }
    

    Form with DataGridView

    namespace SpreadSheetLightProject
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var ops = new Operations();
                /*
                 * Pass existing file name and path if not in the app directory
                 * and pass in the sheet name.
                 */
                DataTable table = ops.Import("work1.xlsx", "sheet1");
                dataGridView1.DataSource = table;
            }
        }
    }
    
    Other options is using Excel automation which requires a good deal more code and is prone to not release memory because a coder did not create and destroy objects correctly.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, January 28, 2020 2:43 PM
    Moderator
  • I have been using OfficeDev/Open-XML-SDK: Open XML SDK by Microsoft. It is a little frustrating to understand but at least it is an official Microsoft solution. Install it using NuGet.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, January 28, 2020 7:00 PM
  • @Sam, SpreadSheetLight uses Open-XML-SDK as a base for working with Excel and with that only works with .xlsx format.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, January 28, 2020 7:13 PM
    Moderator
  • Thanks karen.

    But my output header is not like 

    it should consider excel column title like "A","B","C",...."AA"..."BZ",etc...

    almost it is more dynamic template and we can't maintain emp_no,emp_name,etc...

    Output data table

    so column header should be 

    A              B                       .......       ........       ......    BB  ..........  

    ----          -------

    A01010     John

    E2012       Victor

    Wednesday, January 29, 2020 1:10 AM
  • Hi Gani tpt,

    Thank you for posting here.

    According to your description, I make a test on my side and successfully convert excel to DataTable.

    Here's my code:

            private void button3_Click(object sender, EventArgs e)
            {
                DataTable dataTable = GetExcelDataTable("your file path",11,14,"A","H");
            }
            static DataTable GetExcelDataTable(string path, int top, int bottom, string leftString, string rightString)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook XLWB = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel._Worksheet wsht = XLWB.ActiveSheet;
                int leftNumber = ExcelColumnNameToNumber(leftString);
                int rightNumber = ExcelColumnNameToNumber(rightString);
                DataTable dt = new DataTable();
    
                for (int i= leftNumber; i< rightNumber + 1; i++)
                {
                    dt.Columns.Add(GetExcelColumnName(i));
                }
                for (int i=0;i<(bottom - top + 1);i++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int j = 0; j < (rightNumber - leftNumber + 1); j++)
                    {                            
                        string s = Convert.ToString(wsht.Cells[top + i, leftNumber + j].Value);
    
                        dataRow[j] = s;
                        if (j == rightNumber - leftNumber)
                        {
                            dt.Rows.Add(dataRow);
                        }                   
                    }              
                }
                return dt;
            }
            public static int ExcelColumnNameToNumber(string columnName)
            {
                if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
    
                columnName = columnName.ToUpperInvariant();
    
                int sum = 0;
    
                for (int i = 0; i < columnName.Length; i++)
                {
                    sum *= 26;
                    sum += (columnName[i] - 'A' + 1);
                }
    
                return sum;
            }
            public static string GetExcelColumnName(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
    
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
                return columnName;
            }

    My excel:

    Result of the test:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Thursday, January 30, 2020 7:39 AM
    Wednesday, January 29, 2020 5:49 AM
    Moderator
  • Yes. again you rocking.

    Everything is perfect except one condition.

    DataTable dataTable = GetExcelDataTable("your file path",11,14,"A","H");

    you are mentioning 11,14,"A","H" ==> this is ok for sample excel.

    The important thing is, it will vary every template. it means it is dynamic rows and columns.

    start printing the row is : 14 ==> this 100% perfect.

    Start printing column is : "A" ==> this is also 100% perfect.

    Ending columns and rows data always will be changing.

    so we need to consider till end of the columns and rows data available. In that case, we can use any template in the above same code..

    Is it possible to do this...???

    DataTable dataTable = GetExcelDataTable("your file path",11,till end of the data rows,"A",till end of the data columns);


    • Edited by Gani tpt Wednesday, January 29, 2020 1:38 PM
    Wednesday, January 29, 2020 6:43 AM
  • Hi Gani tpt,

    I make a change to my code and use the following code to get the last filled excel row and column.

                Excel.Range last = wsht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                Excel.Range range = wsht.get_Range("A1", last);
    
                int lastUsedRow = last.Row;
                int lastUsedColumn = last.Column;

    Here's the whole code:

            private void button3_Click(object sender, EventArgs e)
            {
                DataTable dataTable = GetExcelDataTable("your file path",11,"A");
            }
            static DataTable GetExcelDataTable(string path, int top, string leftString)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook XLWB = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel._Worksheet wsht = XLWB.ActiveSheet;
    
                Excel.Range last = wsht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                Excel.Range range = wsht.get_Range("A1", last);
    
                int lastUsedRow = last.Row;
                int lastUsedColumn = last.Column;
    
    
                int leftNumber = ExcelColumnNameToNumber(leftString);
                DataTable dt = new DataTable();
              
                for (int i= leftNumber; i< lastUsedColumn + 1; i++)
                {
                    dt.Columns.Add(GetExcelColumnName(i));
                }
                for (int i=0;i<(lastUsedRow - top + 1);i++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int j = 0; j < (lastUsedColumn - leftNumber + 1); j++)
                    {                            
                        string s = Convert.ToString(wsht.Cells[top + i, leftNumber + j].Value);
    
                        dataRow[j] = s;
                        if (j == lastUsedColumn - leftNumber)
                        {
                            dt.Rows.Add(dataRow);
                        }                   
                    }              
                }
                return dt;
            }
            public static int ExcelColumnNameToNumber(string columnName)
            {
                if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
    
                columnName = columnName.ToUpperInvariant();
    
                int sum = 0;
    
                for (int i = 0; i < columnName.Length; i++)
                {
                    sum *= 26;
                    sum += (columnName[i] - 'A' + 1);
                }
    
                return sum;
            }
            public static string GetExcelColumnName(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
    
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
                return columnName;
            }

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Thursday, January 30, 2020 3:34 AM
    Moderator
  • I think it will be working.  i tested i am getting blank rows. we forgot to mention the sheet name in the parameter.

    because I have many sheets in the template which i want to mention the sheet name. that's why i'am not getting any values.

    Shall we pass the sheet name in the parameter...?

    if this is through then my problem solved...


    • Edited by Gani tpt Thursday, January 30, 2020 4:44 AM mention sheet name in the parameter
    Thursday, January 30, 2020 4:42 AM
  • Hi Gani tpt,

    Use 'workbook.Sheets[sheetName]' to get a worksheet by name.

    Here's the code:

            static DataTable GetExcelDataTable(string path, string sheetName, int top, string leftString)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook XLWB = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                //Microsoft.Office.Interop.Excel._Worksheet wsht = XLWB.ActiveSheet;
                Microsoft.Office.Interop.Excel._Worksheet wsht = XLWB.Sheets[sheetName]; 
                //...
             }

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, January 30, 2020 6:19 AM
    Moderator
  • many many thanks a lot. It's really awesome.

    You full filled my requirement. Thanks for your brilliant logic.

    Thursday, January 30, 2020 7:38 AM
  • Hi Xingyu,

    Sorry to disturb you.

    This Question has been closed. I am really happy. I saw in the forums, number of users viewing this post too high. 

    One question i have.


    Excel to Datatable - Sample

    I have tested the attached excel file in the above link.

    Hardly it will take minimum 2 minutes to convert the data table.

    Is there any chance to minimize the time to convert this....?

    Because i have many tables to convert in single application..

    ??????

    Friday, January 31, 2020 12:25 PM
  • Excel automation is always slower then Open XML for Excel as Open XML for Excel bypasses the conventional methods to access objects.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 31, 2020 12:50 PM
    Moderator
  • Is there any way to follow the same logic....?
    Friday, January 31, 2020 12:53 PM
  • Hi Xingyu,

    Can you pls. provide some other alternative way..?

    Sunday, February 2, 2020 1:48 AM
  • As an alternative solution, you can search and install spire.xls from nuget, then use the below code to convert excel to data table.

    using Spire.Xls;
    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace ExcelToDatatable
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Workbook workbook = new Workbook();
                //Load excel file
                workbook.LoadFromFile("input.xlsx");
                //get the first worksheet
                Worksheet sheet = workbook.Worksheets[0];
                //convert to a data table
                DataTable dt = sheet.ExportDataTable(10, 1, sheet.LastRow, sheet.LastColumn, false);
            }
        }
    }

    • Marked as answer by Gani tpt Thursday, February 6, 2020 11:35 AM
    Tuesday, February 4, 2020 9:54 AM
  • Thanks. i will try and let you know...
    Thursday, February 6, 2020 11:34 AM