How to remove a column from excel sheet in epplus

Question

I'm using csharp to insert data into excel sheet into 7 columns. The interface of this program will allow users to select 7 checkboxes. If they select all 7, all the 7 columns in spreadhseet will have data, if they select one checkbox then only one column will have data. I have got a for loop which will check if data is there, if no data exists, I want to remove that column in epplus. Here's a previous discussion on this topic How can I delete a Column of XLSX file with EPPlus in web app It's quiet old so I just wanna check if there's a way to do this. Or, is there a way to cast epplus excel sheet to microsoft interop excel sheet and perform some operations.

Currently, I've code like this:

for(int j=1; j <= 9; j++) //looping through columns
{
int flag = 0;
for(int i = 3; i <= 10; i++) // looping through rows
{
    if(worksheet.cells[i, j].Text != "")
    {
        flag ++;
    }
}
if (flag == 0)
{
     worksheet.column[j].hidden = true; // hiding the columns- want to  remove it
}
}

Can we do something like:

Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp = worksheet; (where worksheet is epplus worksheet)

Ernie S Ernie S · Accepted Answer · 2015-02-06T14:02:11

Are you using EPPlus 4? The ability to do column inserts and deletion was added with the new Cell store model they implemented. So you can now do something like this:

[TestMethod]
public void DeleteColumn_Test()
{
    //http://stackoverflow.com/questions/28359165/how-to-remove-a-column-from-excel-sheet-in-epplus

    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.Add(new DataColumn("Col1"));
    datatable.Columns.Add(new DataColumn("Col2"));
    datatable.Columns.Add(new DataColumn("Col3"));

    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row["Col1"] = "Col1 Row" + i;
        row["Col2"] = "Col2 Row" + i;
        row["Col3"] = "Col3 Row" + i;
        datatable.Rows.Add(row);
    }

    using (var pack = new ExcelPackage(existingFile))
    {

        var ws = pack.Workbook.Worksheets.Add("Content");
        ws.Cells.LoadFromDataTable(datatable, true);
        ws.DeleteColumn(2);

        pack.SaveAs(existingFile);
    }
}

How to remove a column from excel sheet in epplus

1 Answers