如何从EPPLUS中的Excel工作表中删除列 [英] How to remove a column from excel sheet in epplus

问题描述

我正在使用csharp将数据插入excel工作表的7列中。该程序的界面将允许用户选择7个复选框。如果他们选择全部7，则spreadsee中的所有7列都将具有数据，如果选择一个复选框，则只有一列将具有数据。我有一个for循环，它将检查是否有数据，如果没有数据，我想在epplus中删除该列。这是该主题的先前讨论
如何在Web应用程序中使用EPPlus删除XLSX文件列
很安静，所以我只想检查是否有办法。
或者，是否可以将epplus excel工作表转换为Microsoft interop excel工作表并执行一些操作。

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.

当前，我的代码如下：

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)

推荐答案

您是否正在使用EPPlus 4？他们实现的新Cell存储模型增加了进行列插入和删除的功能。因此，您现在可以执行以下操作：

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

这篇关于如何从EPPLUS中的Excel工作表中删除列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！