Preventing empty columns when exporting to excel

Thank you very much Prasad. You are fine. In the end, The problem was solved thanks to you. I'm ashamed to say it, but the same problem exists when transferring to word. Can we solve it too?

Prasad, I am so sorry. thatI bothered you. In my research, I found that the reason why the code did not work properly was due to the code block below. But I don't know how to change it

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;

Apologies for the confusion earlier. It seems the issue might be related to the method CheckIfColumnHasData, which might not be accurately determining whether a column is entirely empty. Let's refine the approach to exclude empty columns when exporting to Excel. Try this alternative method:

public void Export_DataGridView_To_Excel(DataGridView DGV, string filename)
{
    // Previous code...

    int excelColumn = 2;
    List columnsToInclude = new List();

    for (int dgvColumn = 0; dgvColumn < DGV.Columns.Count; dgvColumn++)
    {
        bool columnHasData = false;

        if (DGV.Columns[dgvColumn].Visible == true)
        {
            for (int i = 0; i < DGV.Rows.Count; i++)
            {
                if (DGV.Rows[i].Cells[dgvColumn].Value != null && !string.IsNullOrWhiteSpace(DGV.Rows[i].Cells[dgvColumn].Value.ToString()))
                {
                    columnHasData = true;
                    break;
                }
            }

            if (columnHasData)
            {
                oSheet.Cells[1, excelColumn] = DGV.Columns[dgvColumn].HeaderText;
                columnsToInclude.Add(dgvColumn);
                excelColumn++;
            }
        }
    }

    for (int i = 0; i < DGV.Rows.Count; i++)
    {
        excelColumn = 2;
        foreach (int colIndex in columnsToInclude)
        {
            oSheet.Cells[i + 2, excelColumn] = DGV.Rows[i].Cells[colIndex].Value;
            excelColumn++;
        }
    }

    // Remaining formatting and saving code...
}

This adjusted code directly checks each cell in the DataGridView to determine if it has non-empty data. If any cell in a column has non-empty data, the entire column will be included in the exported Excel file.

If after making these adjustments, the empty columns are still present in the Excel output, the issue might lie in other parts of the code or the way the DataGridView is populated. Ensure that the DataGridView does not contain empty columns before the export process begins and verify the visibility toggling logic as well.

I am sorry, Prasad but the result is the same. There is no change.

Sorry to hear that. Let us try the below approach.

It seems the code is checking for empty cells within each column, but it doesn't remove the empty columns entirely. To remove the empty columns, you need to dynamically adjust the width of the columns or skip writing the empty columns to the Excel file altogether. Here's an adjustment in your existing code to skip writing empty columns:

public void Export_DataGridView_To_Excel(DataGridView DGV, string filename)
{
    // Previous code...

    int excelColumn = 2;
    List columnsToInclude = new List();

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

    for (int i = 0; i < DGV.Rows.Count; i++)
    {
        excelColumn = 2;
        foreach (int colIndex in columnsToInclude)
        {
            oSheet.Cells[i + 2, excelColumn] = DGV.Rows[i].Cells[colIndex].Value;
            excelColumn++;
        }
    }

    // Remaining formatting and saving code...
}

In this modified version, columnsToInclude stores the indices of the columns that have non-empty data. During the data export loop, it writes only the columns present in the columnsToInclude list to the Excel file, effectively skipping the empty columns.

Ensure that the formatting code after the data export section remains intact and customize it as per your requirements.

This approach will exclude the empty columns from being written to the Excel file while still maintaining the data and formatting for the non-empty columns.

Prasad, thanks for your intrest. I modified my codes as in the following. But I am sorry to inform that the result is the same.

 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 && CheckIfColumnHasData(DGV.Columns[dgvColumn]))
         {
             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 && CheckIfColumnHasData(DGV.Columns[j]))
             {
                 oSheet.Cells[i + 2, excelColumn] = DGV.Rows[i].Cells[j].Value;
                 excelColumn++;
             }
         }
     }

     
     //Add some formatting
     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();

     //Add a header row
     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 bool CheckIfColumnHasData(DataGridViewColumn column)
 {
     foreach (DataGridViewRow row in DGV.Rows)
     {
         if (row.Cells[column.Index].Value != null && !string.IsNullOrEmpty(row.Cells[column.Index].Value.ToString()))
         {
             return true; // Column has non-empty data
         }
     }
     return false; // Column is empty
 }

To exclude empty columns when exporting a DataGridView to Excel, you can modify the logic within the Export_DataGridView_To_Excel method. In this context, you're currently checking for column visibility, but you can further extend this to exclude empty columns based on the data in each column.

Here's an example of how you might modify the method:

public void Export_DataGridView_To_Excel(DataGridView DGV, string filename)
{
    // ... (existing code remains the same)

    int excelColumn = 2;
    for (int dgvColumn = 0; dgvColumn < DGV.Columns.Count; dgvColumn++)
    {
        if (DGV.Columns[dgvColumn].Visible == true && CheckIfColumnHasData(DGV.Columns[dgvColumn]))
        {
            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 && CheckIfColumnHasData(DGV.Columns[j]))
            {
                oSheet.Cells[i + 2, excelColumn] = DGV.Rows[i].Cells[j].Value;
                excelColumn++;
            }
        }
    }

    // ... (rest of the existing code)
}

private bool CheckIfColumnHasData(DataGridViewColumn column)
{
    foreach (DataGridViewRow row in DGV.Rows)
    {
        if (row.Cells[column.Index].Value != null && !string.IsNullOrEmpty(row.Cells[column.Index].Value.ToString()))
        {
            return true; // Column has non-empty data
        }
    }
    return false; // Column is empty
}

This adjustment adds a CheckIfColumnHasData method that checks if a column in the DataGridView has any non-empty data. The Export_DataGridView_To_Excel method is then updated to use this check along with the column visibility condition to exclude empty columns while exporting to Excel.

This change ensures that only columns with non-empty data will be exported to the Excel file. Adjust the method according to your exact requirements and test it thoroughly to ensure it fits your needs.