Add empty row above the header row in datatable

Question

I followed this Adding new row to datatable's top to add the empty row but even if my index at 0 it will still not adding the empty row above the header at my datatable.

I tried this

DataRow blankRow = dt.NewRow(); dt.Rows.InsertAt(blankRow, 0);

This my code

public void filldatagridview(ExcelWorksheet workSheet)
        {
            DataTable dt = new DataTable();

            //Create the data column
            for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
            {
                dt.Columns.Add(col.ToString());
            }

           for (int row = 12; row <= 26; row++)
            {
                DataRow newRow = dt.NewRow(); //Create a row
                int i = 0;
                for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
                {
                    newRow[i++] = workSheet.Cells[row, col].Text;
                }
                dt.Rows.Add(newRow);
            }

            dt.Columns.RemoveAt(0); //remove No 
            dt.Columns.RemoveAt(0); //remove article

            //Get BookCode 
            using (SqlConnection conn = new SqlConnection("Server con.."))
            using (SqlCommand cmd = new SqlCommand(null, conn))
            {
                StringBuilder sb = new StringBuilder("SELECT InvtID AS BOOKCODE FROM InventoryCustomer WHERE Barcode In (");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i != 0) sb.Append(",");

                    string name = "@P" + i;
                    cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]);
                    sb.Append(name);
                }
                sb.Append(")");

                cmd.CommandText = sb.ToString();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);

                dt.DefaultView.Sort = "BOOKCODE";
                dt = dt.DefaultView.ToTable();

                dt.Columns["BOOKCODE"].SetOrdinal(0);

                dataGridView2.DataSource = dt;
            }
}

 private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        }

You can not add a new row above the header in standard control. To do that you need another (custom/third party) control. — Julo, Jan 29 '19 at 04:33
@Ben my program read excel file into datatable. That line of code only add empty row below the header and not above the header as I want it to be — Nebula9, Jan 29 '19 at 05:53
@Nebula9: Example of what? How to create custom control with requested functionality? This is too much to code. Or of third party control. Sorry I do not know none by name. I only know they exist, but I found only paid, or with incompatible license *(this means that I can not use these in in my projects)*. But the main question is: Why do you need a row above header? Do you really need a row above header? Can it not be solved any other way? e.g. moving the control and placing another control (e.g. `TextLabel`) above it. There are many ways how it can be solved, but only you know what you need. — Julo, Jan 29 '19 at 07:28
Because the client requested the header and the rest of data starts at row 7 in excel. Do you have any other solution for me to achieve this? — Nebula9, Jan 29 '19 at 07:35
Using colours/bold font to create something like header a few rows after the actual header won't work? When you disable the top (real) header, this can like like the header is not n the first row. Clients with absurd specifications are pain... — Julo, Jan 29 '19 at 07:48
I know right haha. Yeah i will try that if there is not other way i can do. Thanks! — Nebula9, Jan 29 '19 at 07:57

Gauravsa · Answer 1 · 2019-01-29T06:30:48.023

0

You can try this. As the DataTable is empty, adding a row automatically adds it at 0th index:

DataRow row = dt.NewRow();
dt.Rows.Add(row);

Or you can do this:

DataRow blankRow = dt.NewRow(); 

for (int temp = 0; temp < 10; temp++)  // here 10 is number of columns
{
    blankRow[temp] = "";  // use appropriate data type,
}
dt.Rows.InsertAt(blankRow, 0);

Update:

On DataBound event before adding a datasource:

GridViewRow headerRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);

TableHeaderCell headerCell = new TableHeaderCell();
headerCell.Text = "headercol1”;
headerCell.ColumnSpan = 2;
headerRow.Controls.Add(headerCell);

headerCell = new TableHeaderCell();
headerCell.ColumnSpan = 2;
headerCell.Text = "headercol2”;
headerRow.Controls.Add(headerCell);
dataGridView2.HeaderRow.Parent.Controls.AddAt(0, headerRow);

This assumes you have a gridview with 4 columns. I gave 2 colspace to headercol1 and 2 colspace to headercol2

edited Jan 29 '19 at 06:30

answered Jan 29 '19 at 04:23

Gauravsa

6,330
2
21
30

For the first solution, the row still below the header. And the 2nd solution i got error System.IndexOutOfRangeException: 'Cannot find column 0.' I insert that code after initialize my datatable and before the program read excel file into datatable. – Nebula9 Jan 29 '19 at 05:50
are you displaying datatable as a gridview. do you need row above header in gridview ? – Gauravsa Jan 29 '19 at 05:53
yeah I display the datatable as a gridview `dataGridView2.DataSource = dt;` That code doesnt work for gridview? – Nebula9 Jan 29 '19 at 05:56
Updated the answer. Please see. This adds a separate header to gridview before adding to datatable – Gauravsa Jan 29 '19 at 06:31
Would you please look at my code. Should i use it in datagridview2 or filleddatagridview? As i tried both, the 'GridViewRow' shows an error and suggest me to use 'datagridviewrow' same goes with 'TableHeaderCell'. I googled whats the difference of both but couldn't fine one that i can understand. – Nebula9 Jan 29 '19 at 07:02

Add empty row above the header row in datatable

1 Answers1