Define custom header of datagridview when load data from excel file

Debashish Mahonta

3y1.4kC#

When i load data in datagrid from excel file using closexml it generate first row as a column header of the excel file. But i want to define my custom header of the datagrid when closexml load data from excel file. I dont know how to do this . I use following code to load data from excel file to datagrid.

try
{
    OpenFileDialog dlg_im = new OpenFileDialog();
    dlg_im.Filter = "Excel File|*.xlsx";

    //create openfileDialog Object
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    //open file format define Excel Files(.xls)|*.xls| Excel Files(.xlsx)|*.xlsx| 
    openFileDialog1.Filter = "Excel Files (*.xlsx) |*.xlsx";
    openFileDialog1.FilterIndex = 3;
    //not allow multiline selection at the file selection level
    openFileDialog1.Multiselect = false;
    //define the name of openfileDialog
    openFileDialog1.Title = "Open Excel File";
    //define the initial directory
    openFileDialog1.InitialDirectory = @"Desktop";
    //executing when file open

    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string filePath = openFileDialog1.FileName;

        //Open the Excel file using ClosedXML.
        using (XLWorkbook workBook = new XLWorkbook(filePath))
        {
            //Read the first Sheet from Excel file.
            IXLWorksheet workSheet = workBook.Worksheet(1);

            //Create a new DataTable.
            DataTable dt = new DataTable();

            //Loop through the Worksheet rows.
            bool firstRow = true;
            foreach (IXLRow row in workSheet.Rows())
            {
                //Use the first row to add columns to DataTable.
                if (firstRow)
                {
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Columns.Add(cell.Value.ToString());
                    }
                    firstRow = false;
                }
                else
                {
                    //Add rows to DataTable.
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
                DataGridImp.DataSource = dt;
            }
        }
    }
}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

try this

try
{
    OpenFileDialog dlg_im = new OpenFileDialog();
    dlg_im.Filter = "Excel File|*.xlsx";

    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    openFileDialog1.Filter = "Excel Files (*.xlsx) |*.xlsx";
    openFileDialog1.FilterIndex = 3;
    openFileDialog1.Multiselect = false;
    openFileDialog1.Title = "Open Excel File";
    openFileDialog1.InitialDirectory = @"Desktop";
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string filePath = openFileDialog1.FileName;
        using (XLWorkbook workBook = new XLWorkbook(filePath))
        {
            IXLWorksheet workSheet = workBook.Worksheet(1);
            DataTable dt = new DataTable();

            bool firstRow = true;
            int columnCount = 1;

            foreach (IXLRow row in workSheet.Rows())
            {
                if (firstRow)
                {
                    foreach (IXLCell cell in row.Cells())
                    {
                        string columnName = "Column Header " + columnCount.ToString();
                        dt.Columns.Add(columnName);
                        columnCount++;
                    }
                    firstRow = false;
                }
                else
                {
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }
            
            DataGridImp.DataSource = dt;
        }
    }
}

Debashish Mahonta3y ago
Thanks. Its working. but i could not specify my column header with different name.
0

Debashish Mahonta3y ago

I could not add same header name again and again. i use following but it shows the error. Where is my fault?

                            if (firstRow)
                            {
                                // Use your custom column header names here
                                foreach (IXLCell cell in row.Cells())
                                {
                                    dt.Columns.Add("Column Header 1");
                                    dt.Columns.Add("Column Header 2");
                                    dt.Columns.Add("Column Header 3");
                                    dt.Columns.Add("Column Header 4");
                                    dt.Columns.Add("Column Header 5");
                                    dt.Columns.Add("Column Header 6");
                                    dt.Columns.Add("Column Header 7");
                                    dt.Columns.Add("Column Header 8");
                                    dt.Columns.Add("Column Header 9");
                                    dt.Columns.Add("Column Header 10");
                                    // Add more column headers as needed
                                }
                                firstRow = false;
                            }

Amit Mohanty3y ago
You cannot add same header name again and again. this error shows that the datatable have already a column name with Column Header 1
0
Debashish Mahonta3y ago
@Amit Mohanty Thanks for your reply. But your code not working. It could not load data from excel file. It shows an error. Here is it.

0

Amit Mohanty3y ago

Check this:

try
{
    OpenFileDialog dlg_im = new OpenFileDialog();
    dlg_im.Filter = "Excel File|*.xlsx";
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    openFileDialog1.Filter = "Excel Files (*.xlsx) |*.xlsx";
    openFileDialog1.FilterIndex = 3;
    openFileDialog1.Multiselect = false;
    openFileDialog1.Title = "Open Excel File";
    openFileDialog1.InitialDirectory = @"Desktop";
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string filePath = openFileDialog1.FileName;
        using (XLWorkbook workBook = new XLWorkbook(filePath))
        {
            IXLWorksheet workSheet = workBook.Worksheet(1);
            DataTable dt = new DataTable();
            bool firstRow = true;
            foreach (IXLRow row in workSheet.Rows())
            {
                if (firstRow)
                {
                    // Use your custom column header names here
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Columns.Add("Column Header 1");
                        dt.Columns.Add("Column Header 2");
                        // Add more column headers as needed
                    }
                    firstRow = false;
                }
                else
                {
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }
            DataGridImp.DataSource = dt;
        }
    }
}
catch
{

}