Mehmet Fatih

Mehmet Fatih

  • 746
  • 905
  • 30.9k

The problem of exporting hidden lines into word and excel

Oct 31 2023 9:33 PM

I want to export the  columns I pulled into the datagridview with Checkbox to Excel and Word. However, it also exports the hidden columns to Word and Excel that I have not checked with the checkboxes and that do not appear in the datagridview. I want to export only selected columns as shown in the image. What is the solution?

public void Export_DataGridView_To_Excel(DataGridView DGV, string filename)
{
    string[] Alphabit = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
                      "N", "O","P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

    string Range_Letter = Alphabit[DGV.Columns.Count];
    string Range_Row = (DGV.Rows.Count + 1).ToString();

    if (File.Exists(filename))
    {
        File.Delete(filename);
    }

    Excel.Application oApp;
    Excel.Worksheet oSheet;
    Excel.Workbook oBook;

    oApp = new Excel.Application();
    oBook = oApp.Workbooks.Add();
    oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);


    for (int x = 0; x < dataGridView3.Columns.Count; x++)
    {
        // creating Columns :
        oSheet.Cells[1, x + 2] = dataGridView3.Columns[x].HeaderText;
    }


    for (int i = 0; i < dataGridView3.Columns.Count; i++)
    {
        for (int j = 0; j < dataGridView3.Rows.Count; j++)
        {
            // creating rows :
            oSheet.Cells[j + 2, i + 2] = dataGridView3.Rows[j].Cells[i].Value;
        }
    }

    //Add some formatting
    Range rng1 = oSheet.get_Range("B1", Range_Letter + "1");
    rng1.Font.Size = 14;
    rng1.Font.Bold = true;
    rng1.Cells.Borders.LineStyle = XlLineStyle.xlDouble;
    rng1.Cells.Borders.Color = System.Drawing.Color.DeepSkyBlue;
    rng1.Font.Color = System.Drawing.Color.Black;
    rng1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    rng1.Interior.Color = System.Drawing.Color.LightGray;

    Range rng2 = oSheet.get_Range("B2", Range_Letter + Range_Row);
    rng2.WrapText = false;
    rng2.Font.Size = 12;
    rng2.Cells.Borders.LineStyle = XlLineStyle.xlContinuous;
    rng2.Cells.Borders.Color = System.Drawing.Color.DeepSkyBlue;
    rng2.VerticalAlignment = XlVAlign.xlVAlignCenter;
    rng2.Interior.Color = System.Drawing.Color.Azure;
    rng2.EntireColumn.AutoFit();
    rng2.EntireRow.AutoFit();

    //Add a header row
    oSheet.get_Range("B1", Range_Letter + "2").EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);
    oSheet.Cells[1, 3] = "List of : list title ";
    Range rng3 = oSheet.get_Range("B1", Range_Letter + "2");
    rng3.Merge(Missing.Value);
    rng3.Font.Size = 16;
    rng3.Font.Color = System.Drawing.Color.Blue;
    rng3.Font.Bold = true;
    rng3.VerticalAlignment = XlVAlign.xlVAlignCenter;
    rng3.Interior.Color = System.Drawing.Color.LightSkyBlue;


    oBook.SaveAs(filename);
    oBook.Close();
    oApp.Quit();
    // NASSIM LOUCHANI
}


private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = siniflisteleri25.accdb; Jet OLEDB:Database Password = Fatih2541; Mode = ReadWrite"))
    {

        string query = "SELECT tcno,ono,isim,soyisim,cinsiyet,sinifi,dtarihi,atel,btel from ogrencibilgileri25 where sinifi='" + ComboBox1.Text + " '";

        OleDbCommand command = new OleDbCommand(query, conn);
        conn.Open();
        var adapter = new OleDbDataAdapter(command);
        var table = new System.Data.DataTable();
        adapter.Fill(table);
        dataGridView3.Columns["tcno"].Visible = tcnochk.Checked;
        dataGridView3.Columns["dtarihi"].Visible = dogchk.Checked;
        dataGridView3.Columns["dtarihi"].Visible = dogchk.Checked;
        dataGridView3.Columns["atel"].Visible = atelchk.Checked;
        dataGridView3.Columns["btel"].Visible = btelchk.Checked;
        dataGridView3.DataSource = table;
        conn.Close();
    }
}

private void button1_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();

    sfd.Title = "Export To Excel";
    sfd.Filter = "To Excel (Xlsx)|*.xlsx";
    sfd.FileName = "your document name";

    if (sfd.ShowDialog() == DialogResult.OK)
    {
        Export_DataGridView_To_Excel(dataGridView3, sfd.FileName);
    }
}

 


Answers (2)