Export dynamically added columns(datagridview) to excel

See more:

C#

WinForms

DataGridView

Hello everyone,

I am using the ClosedXML library in c# for my WinForms datagridview, and in my code, I added a bunch of columns dynamically. My issue is that those columns aren't exported afterwards to my excel(my guess is that it's not in the binding source of my datagridview). How can I make it included?

What I have tried:

Up until now, I've just made a code to export all cells to excel, and it's working for all the cells that are already in my database, but not the added ones.


Here's the part of the code that generates the datagridview :

        dataGridView2.Columns.OfType<DataGridViewColumn>().ToList().ForEach(col => col.Visible = false);
foreach (DataGridViewRow row in dataGridView2.Rows)
            {
         row.Cells["toleranceMinDataGridViewTextBoxColumn"].Value = "";
         row.Cells["toleranceNomDataGridViewTextBoxColumn"].Value = "";
         row.Cells["toleranceMaxDataGridViewTextBoxColumn"].Value = "";

         row.Cells["toleranceMinDataGridViewTextBoxColumn"].ReadOnly =false;
         row.Cells["toleranceNomDataGridViewTextBoxColumn"].ReadOnly =false;
         row.Cells["toleranceMaxDataGridViewTextBoxColumn"].ReadOnly =false;

            }

void creation()
            {
                dataGridView2.Columns.OfType<DataGridViewColumn>().ToList().ForEach(col => col.Visible = false);
                dataGridView2.Columns["lotDataGridViewTextBoxColumn"].Visible = true;
                dataGridView2.Columns["ItemDataGridViewTextBoxColumn"].Visible = true;
                dataGridView2.Columns["lotDataGridViewTextBoxColumn"].Visible = true;
                dataGridView2.Columns["ItemDataGridViewTextBoxColumn"].Visible = true;
                dataGridView2.Columns["toleranceMinDataGridViewTextBoxColumn"].Visible = true;
                dataGridView2.Columns["toleranceNomDataGridViewTextBoxColumn"].Visible = true;
                dataGridView2.Columns["toleranceMaxDataGridViewTextBoxColumn"].Visible = true;


                dataGridView2.Columns[$"toleranceMinDataGridViewTextBoxColumn"].DefaultCellStyle.BackColor = System.Drawing.Color.Wheat;
                dataGridView2.Columns[$"toleranceNomDataGridViewTextBoxColumn"].DefaultCellStyle.BackColor = System.Drawing.Color.Wheat;
                dataGridView2.Columns[$"toleranceMaxDataGridViewTextBoxColumn"].DefaultCellStyle.BackColor = System.Drawing.Color.Wheat;
            }

And here's the part that generates the dynamically added columns :

List<int> valeurs = new List<int>();
                var result = from DataGridViewRow row in dataGridView2.Rows where row.Visible select row;
                foreach (var row in result)
                {
                    string a = row.Cells[$"donnees{comboBox1.Text + comboBox2.Text}DataGridViewTextBoxColumn"].Value?.ToString();
                    if (!(a is ""))
                    {
                        valeurs.Add(Convert.ToInt32(a));
                    }

                }
                b = valeurs.Max();

                for (int i = 1; i <= b; i++)
                {
                    dataGridView2.Columns.Add($"Test{i}", $"Donnee{i}");

                }


                foreach (var row in result)
                {
                    for (int i = 1; i <= b; i++)
                    {
                        row.Cells[$"Test{i}"] = new DataGridViewTextBoxCellEx();
                    }
                }
                int k = 0;

                foreach (var row in result)
                {
                    if (b == 5)
                    {
                        if (valeurs[k] < b)
                        {
                            var cell1 = (DataGridViewTextBoxCellEx)row.Cells["Test1"];
                            cell1.ColumnSpan = 2;
                            cell1.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

                            var cell2 = (DataGridViewTextBoxCellEx)row.Cells["Test3"];
                            cell2.ColumnSpan = 2;
                            cell2.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

                            k++;

                        }
                    }
                }




            }

Here's the part that exports to excel :

string filename = "C:/Users/guizaoui/Desktop/partage/fichier.xlsx";
            var wb = new ClosedXML.Excel.XLWorkbook(filename);
            var ws = wb.Worksheets.Worksheet("Feuil1");


            void listeverif()
            {
                ws.Cell("T8").Value = textBox1.Text;
                ws.Cell("T10").Value = textBox2.Text;
            }


            listeverif();


            void entete()
            {
                ws.Cell("G4").Value = comboBox1.Text + comboBox2.Text;
                ws.Cell("B4").Value = textBox3.Text;
                ws.Cell("B5").Value = textBox4.Text;
                ws.Cell("B6").Value = textBox5.Text;
                ws.Cell("B7").Value = textBox6.Text;
                ws.Cell("B8").Value = textBox7.Text;
                ws.Cell("B9").Value = textBox8.Text;
                ws.Cell("B10").Value = textBox9.Text;



            }

            entete();


            void itemsettolerances()
            {



                
                for (int i = 0; i < dataGridView2.Rows.Count; i++)
                {

                    for (int j = 0; j < dataGridView2.Columns.Count; j++)
                    {
                        if ((dataGridView2.Rows[i].Visible == true) && (dataGridView2.Columns[j].Visible == true))
                        {
                            ws.Cell(i + 14, j + 1).Value = dataGridView2.Rows[i].Cells[j].Value?.ToString();
                            if ((j+1 == 4) || (j+1 == 5) || (j+1 == 6))
                             {
                                 ws.Cell(i + 14, j + 1).Style.Fill.SetBackgroundColor(XLColor.Wheat);

                             }


                            XLAlignmentHorizontalValues align;

                            switch (dataGridView2.Rows[i].Cells[j].Style.Alignment)
                            {
                                case DataGridViewContentAlignment.BottomRight:
                                    align = XLAlignmentHorizontalValues.Right;
                                    break;
                                case DataGridViewContentAlignment.MiddleRight:
                                    align = XLAlignmentHorizontalValues.Right;
                                    break;
                                case DataGridViewContentAlignment.TopRight:
                                    align = XLAlignmentHorizontalValues.Right;
                                    break;

                                case DataGridViewContentAlignment.BottomCenter:
                                    align = XLAlignmentHorizontalValues.Center;
                                    break;
                                case DataGridViewContentAlignment.MiddleCenter:
                                    align = XLAlignmentHorizontalValues.Center;
                                    break;
                                case DataGridViewContentAlignment.TopCenter:
                                    align = XLAlignmentHorizontalValues.Center;
                                    break;

                                default:
                                    align = XLAlignmentHorizontalValues.Left;
                                    break;
                            }
                        }




                    }
                }
            }
            itemsettolerances();
            int lastcell = 14;
            void deleteblankrows()
            {

                int lastrow = ws.LastRowUsed().RowNumber();
                var rows = ws.Rows(14, lastrow);
                foreach (IXLRow row in rows)
                {
                    if (row.IsEmpty())
                    {
                        row.Delete();
                    }
                    else
                    {
                        lastcell++;
                    }
                }

            }
            deleteblankrows();

            ws.Rows().AdjustToContents();

            void mergetolerances()
            {
                for(int i=14;i<=lastcell-1;i++)
                {
                    if ((ws.Cell(i,4).Value == ws.Cell(i,5).Value)&&(ws.Cell(i, 4).Value == ws.Cell(i, 6).Value))
                    {
                        var range = ws.Range($"D{i}:F{i}");
                        range.Merge();
                    }
                }
            }
            mergetolerances();



            void lots()
            {
                int lastrow = ws.LastRowUsed().RowNumber();

                //lot1
                int deb1 = 14;
                int fin1 = 0;

                //lot2
                int deb2 = 0;
                int fin2 = 0;

                //lot3
                int deb3 = 0;
                int fin3 = lastcell-1;


                for(int i = 14; i <= lastrow;i++)
                {
                    int m = 0;
                    int l = 0;
                    if((int.TryParse(ws.Cell(i,2).Value.ToString(), out m))){
                        ws.Cell(i, 35).Value = m;
                        l = Convert.ToInt32(ws.Cell(i, 2).Value.ToString());
                        if ((l == 2) && (deb2 == 0))
                        {
                            deb2 = i;
                            fin1 = i - 1;

                        }
                        else if ((l == 3) && (deb3 == 0))
                        {
                            fin2 = i - 1;
                            deb3 = i;
                        }
                        

                    }



                }
                var range1 = ws.Range($"B{deb1}:B{fin1}");
                var range2 = ws.Range($"B{deb2}:B{fin2}");
                var range3 = ws.Range($"B{deb3}:B{fin3}");

               // range1.Merge();
               // range2.Merge();
                 //range3.Merge();
            }
            lots();

            void borders()
            {
                int lastrow = ws.LastRowUsed().RowNumber();
                int lastcolumn = ws.LastColumnUsed().ColumnNumber();
                for (int i = 14; i <= lastrow; i++)
                {
                    for (int j = 2; j <= 6; j++)
                    {
                        ws.Cell(i, j).Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
                    }
                }
            }
            borders();
            








            wb.SaveAs($"C:/Users/guizaoui/Downloads/Controle{comboBox1.Text + comboBox2.Text}.xlsx");