Mehmet Fatih

Mehmet Fatih

  • 858
  • 939
  • 41.2k

Preventing empty columns when exporting to excel

Nov 3 2023 3:57 PM

I am hiding some columns in datagridview. When transferring data from Datagridview to Excel, hidden columns appear as empty columns. How can we prevent empty columns from appearing?

private void ExceleAktar_Load(object sender, EventArgs e)
{
    SinifCek();
    ComboBox1.SelectedIndex = 0;

    DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
    DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

    DGV.Columns["tcno"].Visible = tcnochk.Checked;
    DGV.Columns["dtarihi"].Visible = dogchk.Checked;
    DGV.Columns["atel"].Visible = atelchk.Checked;
    DGV.Columns["btel"].Visible = btelchk.Checked;

    DGV.TopLeftHeaderCell.Value = "S.No";
    DGV.Columns[0].HeaderText = "T.C.No";
    DGV.Columns[1].HeaderText = "Ö.No";
    DGV.Columns[2].HeaderText = "Adi";
    DGV.Columns[3].HeaderText = "Soyadi";
    DGV.Columns[4].HeaderText = "Cinsiyeti";
    DGV.Columns[5].HeaderText = "Sinifi";
    DGV.Columns[7].HeaderText = "Anne Tel";
    DGV.Columns[8].HeaderText = "Baba Tel";
}

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

    int excelColumn = 2;
    for (int dgvColumn = 0; dgvColumn < DGV.Columns.Count; dgvColumn++)
    {
        if (DGV.Columns[dgvColumn].Visible == true)
        {
            oSheet.Cells[1, excelColumn] = DGV.Columns[dgvColumn].HeaderText;
            excelColumn++;
        }
    }

    for (int i = 0; i < DGV.Rows.Count; i++)
    {
        excelColumn = 2;
        for (int j = 0; j < DGV.Columns.Count; j++)
        {
            if (DGV.Columns[j].Visible == true)
            {
                oSheet.Cells[i + 2, excelColumn] = DGV.Rows[i].Cells[j].Value;
                excelColumn++;
            }
        }
    }

    Range rng1 = oSheet.get_Range("A1", 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("A2", 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();

    oSheet.get_Range("A1", Range_Letter + "2").EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);
    oSheet.Cells[1, 3] = "ÖGRENCI LISTESI ";
    Range rng3 = oSheet.get_Range("A1", 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.HorizontalAlignment = XlVAlign.xlVAlignCenter;
    rng3.Interior.Color = System.Drawing.Color.LightSkyBlue;

    oBook.SaveAs(filename);
    MessageBox.Show("Your file is saved");
    oBook.Close();
    oApp.Quit();
}

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);
        DGV.DataSource = table;
        conn.Close();
    }
}

private void tcnochk_CheckedChanged(object sender, EventArgs e)
{
    DGV.Columns["tcno"].Visible = tcnochk.Checked;
}

private void dogchk_CheckedChanged(object sender, EventArgs e)
{
    DGV.Columns["dtarihi"].Visible = dogchk.Checked;
}

private void atelchk_CheckedChanged(object sender, EventArgs e)
{
    DGV.Columns["atel"].Visible = atelchk.Checked;
}

private void btelchk_CheckedChanged(object sender, EventArgs e)
{
    DGV.Columns["btel"].Visible = btelchk.Checked;
}

 


Answers (8)