none
How to add top header column on top of the data table...? RRS feed

  • Question

  • I am using Data table and it contains "n" number of columns.

    For some logic, i want to add in top of the header column in data table.

    below is my data table.

    the above datatable, i just converted fom excel to datatable.

    But, i want to set one more column like "A", "B", "C",etc. This should display in top of the data table.

    below if the final output.

    At any case, i want column name should be like excel column like "A","B","C","D"..."ZZ" (dynamically add this based on columns available in datatable)..?

    How to do this...?

    Friday, October 16, 2020 10:20 AM

Answers

  • This is an alternate as getting letter above perfect would be somewhat of a pain to code.

    using System;
    using System.Linq;
    
    namespace DeleteMeLater
    {
        public static class Extensions
        {
    
            public static string ExcelColumnNameFromNumber(this int pIndex)
            {
                var chars = Enumerable.Range(0, 26).Select((i) => 
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
        }
    }

    Form code reads a DataTable to a DataGridView

    dataGridView1.DataSource = DataOperations.ReadCustomers();
    
    for (int index = 0; index < dataGridView1.Columns.Count; index++)
    {
        var colIndex = index + 1;
        dataGridView1.Columns[index].HeaderText = $"{colIndex.ExcelColumnNameFromNumber()}\n{dataGridView1.Columns[index].HeaderText}";
    }

    There are many things to consider otherwise like when a column header is expanded for instance but if that is never done we can put another DataGridView above the first and style as you see fit. That also has issues too, more coding then I would want to do.

    dataGridView1.DataSource = DataOperations.ReadCustomers();
    
    dataGridViewHeader.ColumnCount = dataGridView1.Columns.Count;
    dataGridViewHeader.ColumnHeadersDefaultCellStyle.BackColor = System.Drawing.SystemColors.Control;
    dataGridViewHeader.EnableHeadersVisualStyles = false;
    
    for (int index = 0; index < dataGridView1.Columns.Count; index++)
    {
        var colIndex = index + 1;
        dataGridViewHeader.Columns[index].HeaderText = colIndex.ExcelColumnNameFromNumber();
    }


    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, October 16, 2020 12:07 PM
    Moderator
  • Hi Gani,

    I replied to your question in another thread, please see if it meets your requirements.

    C# - Error while adding Data Header column in data table

    Best Regards,

    Timon


    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 Monday, October 19, 2020 6:52 AM
    Monday, October 19, 2020 5:42 AM

All replies

  • This is an alternate as getting letter above perfect would be somewhat of a pain to code.

    using System;
    using System.Linq;
    
    namespace DeleteMeLater
    {
        public static class Extensions
        {
    
            public static string ExcelColumnNameFromNumber(this int pIndex)
            {
                var chars = Enumerable.Range(0, 26).Select((i) => 
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
        }
    }

    Form code reads a DataTable to a DataGridView

    dataGridView1.DataSource = DataOperations.ReadCustomers();
    
    for (int index = 0; index < dataGridView1.Columns.Count; index++)
    {
        var colIndex = index + 1;
        dataGridView1.Columns[index].HeaderText = $"{colIndex.ExcelColumnNameFromNumber()}\n{dataGridView1.Columns[index].HeaderText}";
    }

    There are many things to consider otherwise like when a column header is expanded for instance but if that is never done we can put another DataGridView above the first and style as you see fit. That also has issues too, more coding then I would want to do.

    dataGridView1.DataSource = DataOperations.ReadCustomers();
    
    dataGridViewHeader.ColumnCount = dataGridView1.Columns.Count;
    dataGridViewHeader.ColumnHeadersDefaultCellStyle.BackColor = System.Drawing.SystemColors.Control;
    dataGridViewHeader.EnableHeadersVisualStyles = false;
    
    for (int index = 0; index < dataGridView1.Columns.Count; index++)
    {
        var colIndex = index + 1;
        dataGridViewHeader.Columns[index].HeaderText = colIndex.ExcelColumnNameFromNumber();
    }


    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, October 16, 2020 12:07 PM
    Moderator
  • Thanks karen.

    can you populate the columns(A,B,C,.....ZZ) in just plain data table instead of datagridview ?

    or can you pls. tell us, how to convert excel data to datatable with column name as Excel column name like (A, B, C,.....).

    below is the excel data.

    below code is converting excel to datatable.

    But, i want to add excel column name like (A, B, C, ......) as a Data table column name.

    convert excel to data table (without column name like (A, B, C, ....)

    string XLData = "C:\proj\ExcelData.xlsx"; //refer attached XL file
    string sheetName = "sheet1";
    string dtSource = "dttable";
    public static DataTable XLtoDatatable(string XLData, string sheetName, string dtSource)
            {
    
                DataTable dt2 = new DataTable();
                DataTable dtResult = null;
                int totalSheet = 0; //No of sheets on excel file  
                using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLData + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
                {
                    objConn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        //sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    sheetName = string.Concat(sheetName, "$");
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, dtSource);
    
                    dtResult = ds.Tables[dtSource];
    
    
                    //for removing empty rows
                    dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
                                field is System.DBNull || string.Compare((field as string).Trim(),
                                string.Empty) == 0)).CopyToDataTable();
    
                    dt2 = dtResult;
                    StripEmptyRows(dt2);
                    objConn.Close();
                    return dt2; //Returning Data table  
                }
    
            }
    
     public static DataTable StripEmptyRows(DataTable dt)
            {
                List<int> rowIndexesToBeDeleted = new List<int>();
                int indexCount = 0;
                foreach (var row in dt.Rows)
                {
                    var r = (DataRow)row;
                    int emptyCount = 0;
                    int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;
    
                    if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);
    
                    indexCount++;
                }
    
                int count = 0;
                foreach (var i in rowIndexesToBeDeleted)
                {
                    dt.Rows.RemoveAt(i - count);
                    count++;
                }
    
                return dt;
            }

    tell us how to add A,B,C,...... as a column name in top of the header in a data table using the above code.

    Note: pls. find attach below is the sample data in excel.

    Sample Excel Data - Attached



    • Edited by Gani tpt Saturday, October 17, 2020 3:49 PM ExcelData.xlsx attached
    Friday, October 16, 2020 3:59 PM
  • Can anyone help us this thread to proceed faster...?

    I have many places to update like this concept..waiting for better solution..

    Saturday, October 17, 2020 1:31 AM
  • any update...
    Saturday, October 17, 2020 11:44 AM
  • Thanks karen.

    can you populate the columns(A,B,C,.....ZZ) in just plain data table instead of datagridview ?

    or can you pls. tell us, how to convert excel data to datatable with column name as Excel column name like (A, B, C,.....).


    Using the extension method ExcelColumnNameFromNumber will do it as shown already. Even if you were to add columns later knowing the Ordinal value of a DataColumn will work.

    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

    Saturday, October 17, 2020 10:17 PM
    Moderator
  • I am adding your logic in my code. but, i am getting error. refer my complete code as mentioned below.

    Also, it will overwrite the existing column. but, it should not like that. 

    The final output will be like below in data table format.

    complete source code

            private void button1_Click_1(object sender, EventArgs e)
            {
    
                string sheetName = "sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx"; // Attached sample excel file
                DataTable dttable = new DataTable();
                dttable = XLtoDT(path, Shtname, tempdt);
    
            }
    
            public static DataTable XLtoDT(string XLpath, string sheetName, string dtSource)
            {
    
                DataTable dt2 = new DataTable();
                DataTable dtResult = null;
                int totalSheet = 0; //No of sheets on excel file  
                using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLpath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
                {
                    objConn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        //sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    sheetName = string.Concat(sheetName, "$");
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, dtSource);
    
                    dtResult = ds.Tables[dtSource];
    
    
                    //for removing empty rows
                    dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
                                field is System.DBNull || string.Compare((field as string).Trim(),
                                string.Empty) == 0)).CopyToDataTable();
    
                    dt2 = dtResult;
                    StripEmptyRows(dt2);
    
    
                    for (int index = 0; index < dt2.Columns.Count; index++)
                    {
                        var colIndex = index + 1;
                        dt2.Columns[index].ColumnName = ExcelColumnNameFromNumber(colIndex).
    						    ToString().Trim(); // Error : Additional information: A column named 'F' already belongs to this DataTable.
                    }
    
                    objConn.Close();
                    return dt2; //Returning Data table  
                }
    
            }
    
            public static string ExcelColumnNameFromNumber(int pIndex)
            {
                var chars = Enumerable.Range(0,26).Select((i) =>
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
    
    
    
            public static DataTable StripEmptyRows(DataTable dt)
            {
                List<int> rowIndexesToBeDeleted = new List<int>();
                int indexCount = 0;
                foreach (var row in dt.Rows)
                {
                    var r = (DataRow)row;
                    int emptyCount = 0;
                    int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;
    
                    if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);
    
                    indexCount++;
                }
    
                int count = 0;
                foreach (var i in rowIndexesToBeDeleted)
                {
                    dt.Rows.RemoveAt(i - count);
                    count++;
                }
    
                return dt;
            }


    • Edited by Gani tpt Sunday, October 18, 2020 2:26 AM complete source code added
    Sunday, October 18, 2020 2:23 AM
  • thanks thats good
    Sunday, October 18, 2020 2:31 PM
  • problem solved
    Sunday, October 18, 2020 2:31 PM
  • problem not solved yet. still error coming and my requirement will not met the above code.

    final output would below.

    If above output will come in data table, then my problem solved.

    otherwise this will not be a complete solution.

    Sunday, October 18, 2020 3:52 PM
  • can anyone help to provide the working correct source code with above mentioned output(data table). ?

    otherwise it is waste of time to sending and receiving mails.

    i am working this logic past 3 to 4 days. but, i am unable to form the correct logic.

    If this will work, i have to apply many places in my many project(s).

    pls. guide us..

    Monday, October 19, 2020 12:06 AM
  • can anyone help to provide the working correct source code with above mentioned output(data table). ?

    otherwise it is waste of time to sending and receiving mails.

    i am working this logic past 3 to 4 days. but, i am unable to form the correct logic.

    If this will work, i have to apply many places in my many project(s).

    pls. guide us..

    With no disrespect, you have everything needed and unsure why it's taking days to plug in what I gave you. I don't have time to provide a complete robust solution as I have other things to do away from the computer especially on weekends.

    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

    Monday, October 19, 2020 12:15 AM
    Moderator
  • ???????
    Monday, October 19, 2020 2:40 AM
  • Hi Gani,

    I replied to your question in another thread, please see if it meets your requirements.

    C# - Error while adding Data Header column in data table

    Best Regards,

    Timon


    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 Monday, October 19, 2020 6:52 AM
    Monday, October 19, 2020 5:42 AM