none
C# - How to convert Excel sheet to data table dynamically RRS feed

  • Question

  • Hi,

    I have the excel and i want to convert same structure format as datatable.

    for example,

    below is my screenshot and output.

    The datatable should dynamically generate based on my excel column input.

    for example.

    if i have 5 excel columns then datatable only A to E,

    if i have 3 excel columns then datatable only A to C, like that.

    How to do this.??

    Tuesday, December 19, 2017 10:18 AM

Answers

  • Using SpreadSheetLight

    From NuGet

    First method I used to reply to your other question, second if for this question.

    using System;
    using System.Data;
    using SpreadsheetLight;
    
    namespace SpreadSheetLightProject
    {
        public class Operations
        {
            public DataTable Import1(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;
    
            }
            public DataTable Import2(string fileName, string sheetName)
            {
                var dt = new DataTable();
                ;
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col1", DataType = typeof(int) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col2", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col3", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col4", DataType = typeof(string) });
    
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col5", DataType = typeof(string) });
    
                using (var doc = new SLDocument(fileName, sheetName))
                {
                    var stats = doc.GetWorksheetStatistics();
    
                    for (int index = 2; index < stats.EndRowIndex + 1; index++)
                    {
                        dt.Rows.Add(new object[]
                        {
                            doc.GetCellValueAsInt32(index, 1),
                            doc.GetCellValueAsString(index, 2),
                            doc.GetCellValueAsString(index, 3),
                            doc.GetCellValueAsString(index, 4),
                            doc.GetCellValueAsString(index, 5),
    
                        });
                    }
                }
    
                return dt;
    
            }
        }
    }
    


    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

    • Marked as answer by Gani tpt Thursday, January 30, 2020 9:12 AM
    Tuesday, January 28, 2020 3:41 PM
    Moderator

All replies

  • There is an in depth article here to check out. 

    Important points (as per the article, all use OleDb data provider)

    • In the connection string specify HDR=No to exclude the headers. When doing so the DataTable column names will be F1, F2, F3,F4,F5 but you can alias them e.g. SELECT F1 As A, F2 As C etc.
    • You may need to add IMEX (mentioned in the article) for data to come thru properly, there are several values which are explained in the article. 
    • If you want only want the first three columns then do SELECT F1 As A, F2 As B, F3 As C.
    • Note the connection string will be different between .xls and .xlsx

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, December 19, 2017 11:08 AM
    Moderator
  • Thanks for your reply.

    i am new to VBA code (to get range,select cell,etc).

    can you pls. provide some sample code to mention above logic...?

    pls. find attached excel file.

    https://1drv.ms/x/s!AiSRcgO5FUmNbQl1vcDSEbrfyPs

    Excel File

    Tuesday, December 19, 2017 11:27 AM
  • If you are using VBA then this is the wrong forum, please let me know if you are not using C# but using VBA and if that is the case I will move your question to the proper forum. This forum is only for working with C# and of course accessing Excel using C#.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, December 19, 2017 11:50 AM
    Moderator
  • Sorry. we are using c# and this question for Excel using C#.


    Tuesday, December 19, 2017 12:10 PM
  • Here I'm reading an excel file in the same folder as the executable.

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication3
    {
        public partial class Form2 : Form
        {
            public Form2()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var dt = new DataTable();
    
                var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File1.xlsx");
                var query = "SELECT F1 As A, F2 As B, F3 As C FROM [Sheet1$]";
                using (OleDbConnection cn = new OleDbConnection { ConnectionString = ConnectionString(fileName, "No") })
                {
                    using (OleDbCommand cmd = new OleDbCommand { CommandText = query, Connection = cn })
                    {
                        cn.Open();
    
                        OleDbDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                    }
    
                }
                if (dt.Rows.Count >1)
                {
                    // remove header
                    dt.Rows[0].Delete();
                }
                dt.AcceptChanges();
                Console.WriteLine();
            }
            public string ConnectionString(string FileName, string Header)
            {
                OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
                if (Path.GetExtension(FileName).ToUpper() == ".XLS")
                {
                    Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX=1;HDR={0};", Header));
                }
                else
                {
                    Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX=1;HDR={0};", Header));
                }
    
                Builder.DataSource = FileName;
    
                return Builder.ConnectionString;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Gani tpt Wednesday, December 20, 2017 5:28 AM
    • Unmarked as answer by Gani tpt Tuesday, January 28, 2020 12:56 PM
    Tuesday, December 19, 2017 12:40 PM
    Moderator
  • Thanks..

    Question : if it is dynamic column, it means 5,10,11,etc,.. what will happen..?

    As of now we have hard coded three columns.. Am i correct...?

    Wednesday, December 20, 2017 5:30 AM
  • You can have a check on the following sample, it illustrates how to import data from data table to excel and export data from excel to data table using an external 3rd party library(free spire.xls in this sample), the code is very simple.

    https://code.msdn.microsoft.com/Operate-excel-document-in-881b1ce8

    I already tried the following code and it works fine.

    //Load the Excel file
    Workbook workbook = new Workbook();       
    workbook.LoadFromFile(@"Input.xlsx");    
          
    //Get the first worksheet
    Worksheet sheet = workbook.Worksheets[0];
    //Export data to data table
    DataTable dt = sheet.ExportDataTable();



    • Edited by LeonDav Wednesday, December 20, 2017 6:06 AM
    • Marked as answer by Gani tpt Thursday, December 21, 2017 12:37 PM
    • Unmarked as answer by Gani tpt Tuesday, January 28, 2020 12:54 PM
    Wednesday, December 20, 2017 5:59 AM
  • Is the 3rd party library is free or license version...?
    Wednesday, December 20, 2017 6:51 AM
  • Is the 3rd party library is free or license version...?
    It's a free version.
    Thursday, December 21, 2017 2:37 AM
  • Try this FREE. ZetExcel.com its the best. 
    Sunday, February 17, 2019 4:16 PM
  • Hi Kevin,

    I am new to zetexcel or spire dll. Also main thing is, "n" number of columns of data will be available in the excel.

    that is the reason, we want dynamically convert all the excel data with header to C# data table.

    can you pls. provide some sample C# code to convert excel to data table in c# application.



    • Edited by Gani tpt Tuesday, January 28, 2020 12:56 PM "n" number of column of data will be available in the excel
    Tuesday, January 28, 2020 12:53 PM
  • Using SpreadSheetLight

    From NuGet

    First method I used to reply to your other question, second if for this question.

    using System;
    using System.Data;
    using SpreadsheetLight;
    
    namespace SpreadSheetLightProject
    {
        public class Operations
        {
            public DataTable Import1(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;
    
            }
            public DataTable Import2(string fileName, string sheetName)
            {
                var dt = new DataTable();
                ;
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col1", DataType = typeof(int) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col2", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col3", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col4", DataType = typeof(string) });
    
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Col5", DataType = typeof(string) });
    
                using (var doc = new SLDocument(fileName, sheetName))
                {
                    var stats = doc.GetWorksheetStatistics();
    
                    for (int index = 2; index < stats.EndRowIndex + 1; index++)
                    {
                        dt.Rows.Add(new object[]
                        {
                            doc.GetCellValueAsInt32(index, 1),
                            doc.GetCellValueAsString(index, 2),
                            doc.GetCellValueAsString(index, 3),
                            doc.GetCellValueAsString(index, 4),
                            doc.GetCellValueAsString(index, 5),
    
                        });
                    }
                }
    
                return dt;
    
            }
        }
    }
    


    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

    • Marked as answer by Gani tpt Thursday, January 30, 2020 9:12 AM
    Tuesday, January 28, 2020 3:41 PM
    Moderator
  • Yes. you are done very good job.

    my template is always dynamic and it contains more than 150 to 250 columns values many times.

    so we cannot maintain the header always. that is the reason, we just want to refer the excel "A","B","C"..."BZ",,,"CZ" etc columns.

    finally we are internally maintaining one table for what is the header of the reference column. 

    for example,

    if excel column is "A", then it is emp_no (maintaining  header in another table). so logic is simple.

    ??????? 

    Wednesday, January 29, 2020 1:16 AM
  • Hello,

    I've done a great deal with reading Excel and always have business regulated to pre-defined templates, without that all you have is chaos. No consistency all you are doing is running into a mess.


    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

    Wednesday, January 29, 2020 1:43 AM
    Moderator
  • I understood. But, each and every day i am getting different kind of dynamic template. 

    many thing i can manage. but, some i am trying and unable to process quickly.

    this question also little bit tricky and not able to manage.

    Getting the result of excel header "A","B","C",etc is quite difficult for the dynamic template.

    more result i am getting, i am happy and share with my colleagues and my company's.

    ???

    Wednesday, January 29, 2020 4:33 AM