Excelfile to DataGridview with ClosedXML

Question

In my project I have to import an Excelfile. The type is .xlsx. The file has a table in it with 4 columns. I have to add extra columns to this excel file. My problem is that with my solution the DataGridview has the headertext twice. And every time I read the file again I get a new row with the headertext. I think the problem is in the foreach (IXLCell cell in row.Cells()) but I do not find out there the bug is.

try
{

    OpenFileDialog OpenDataList = new OpenFileDialog();// Erzeugen einen OpenFileDialog-Objektes
    OpenDataList.Title = "Datenpunktliste öffnen"; // Initialisiert den Namen des Fensters
    OpenDataList.InitialDirectory = @"C:\Users\AdministratorOSMO\Desktop\Testordner"; // Legt den Default Dateipfad fest
    OpenDataList.Filter = "Excel Files|*.xls;*.xlsx"; // Filter für die zur Auswahl stehenden Dateitypen
    OpenDataList.FilterIndex = 2;// Legt den Standardfilter beim Öffnen fest

    if (OpenDataList.ShowDialog() == DialogResult.OK)//Abfrage ob der ShowDialog mit OK bestätigt wurde
    {
        string PathName = OpenDataList.FileName;// die Variable pathName wird mit dem Inhalt von Filename initialisiert
        using (XLWorkbook workBook = new XLWorkbook(PathName))//Arbeitmappe wird erstellt
        {

            IXLWorksheet workSheet = workBook.Worksheet(1);//Tabelle 1 der Excel-Datei wird in workSheet geschrieben
            DataTable dt = new DataTable();// neuer DataTable dt wird erzeugt
            bool firstRow = true;// Boolsche Variable für Erste Reihe ?
            foreach (IXLRow row in workSheet.Rows())
            {
                while (firstRow)

                {
                    foreach (IXLCell cell in row.Cells())
                    {
                    dt.Columns.Add(cell.Value.ToString());
                    }

                    dt.Columns.Add(new DataColumn("OK", typeof(bool)));
                    dt.Columns.Add(new DataColumn("Datum", typeof(string)));
                    firstRow = false;
                }

                dt.Rows.Add();

                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count-1][i] = cell.Value.ToString();
                    i++;
                }

                Dgv_Data_List.DataSource = dt;
                Dgv_Data_List.ReadOnly = true;

            }

        }                                       
    }
}
catch (Exception ex)
{
    MessageBox.Show("Error: " + ex.Message);
}

Raidri · Accepted Answer · 2020-01-09T15:33:15.917

After you set your column headers from the first row you also handle the same row as a normal data row. You can stop that by using continue to go to the next row. (Also the last two lines below can go outside the foreach loop.)

foreach (IXLRow row in workSheet.Rows())
{
    if (firstRow)       
    {
        foreach (IXLCell cell in row.Cells())
        {
            dt.Columns.Add(cell.Value.ToString());
        }

        dt.Columns.Add(new DataColumn("OK", typeof(bool)));
        dt.Columns.Add(new DataColumn("Datum", typeof(string)));
        firstRow = false;
        continue;  // go to next row
    }

    dt.Rows.Add();

    int i = 0;
    foreach (IXLCell cell in row.Cells())
    {
        dt.Rows[dt.Rows.Count-1][i] = cell.Value.ToString();
        i++;
    }
}

Dgv_Data_List.DataSource = dt;
Dgv_Data_List.ReadOnly = true;

Updated my answer. Change the `while` to `if`, you don't need the `while` loop (the `continue` went to the `while` loop instead of the `foreach` loop, that was the problem with my first version.) BTW: You should'nt post a reply to my answer as a seperate answer to the question. — Raidri, Jan 09 '20 at 15:36

Excelfile to DataGridview with ClosedXML

1 Answers1