Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to remove a column from excel sheet in epplus

Tags:

c#

epplus

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)
like image 715
sanmis Avatar asked Feb 06 '15 05:02

sanmis


People also ask

How do I hide columns in EPPlus?

Apparently according to the documentation, you can use AutoFitColumns(Double MinimumWidth, Double MaximumWidth) : Set the column width from the content of the range. Note: Cells containing formulas are ignored if no calculation is made. Wrapped and merged cells are also ignored.

How do you delete a column in a worksheet?

Delete cells, rows, or columns Select the cells, rows, or columns that you want to delete. Right-click, and then select the appropriate delete option, for example, Delete Cells & Shift Up, Delete Cells & Shift Left, Delete Rows, or Delete Columns.

How do you delete a column in Excel using C#?

Delete Columns from Excel Worksheets using C# Create an instance of the Workbook class with the input file path. Create an instance of the Worksheet class. Access the worksheet from Worksheets collection by its index. Delete a column by calling the DeleteColumn() method and pass the column index to delete.


1 Answers

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);
    }
}
like image 140
Ernie S Avatar answered Oct 12 '22 10:10

Ernie S