Debashish Mahonta

Debashish Mahonta

  • 1.6k
  • 165
  • 3.9k

Define custom header of datagridview when load data from excel file

Jun 28 2023 8:48 AM

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;
            }
        }
    }
}

 


Answers (6)