[Advanced Excel] Excel Delete column

Forge Component
(45)
Published on 18 Jul (2 days ago) by Hanno
45 votes
Published on 18 Jul (2 days ago) by Hanno

Is there a way to delete a column from an excel file?

Can you give an example?

You can import your excel to record list, remove the record you want to remove and then export the excel.

Thanks and Best Regards,

Nuno Pereira

Hi Erik,

It is not needed to delete columns. If you define a sheet using the Cell actions, you can skip the columns you do not need.

If you are using Workbook_AddOsDbSheet however, it is not possible to skip columns. All columns of a dataset are taken in that case.


Regards, René Cornet

When you import an excel to record list, but the excel contians a extra column in the middel that is not in you record definition Excel to record list will not work properly. If I could remove that column, and then use Excel to record list that would be very nice. I would like somethink like this: 

Solution

Hi Erik, the extension does not currently implement a DeleteColumn action, but you should be able to easily add one.

The library that is used by the extension (EPPlus) already has that feature, so all you need to do is open the extension through integration studio and add a new action like DeleteColumn(object workSheet, int column). 

Then implement that action in .NET which is as easy as calling the DeleteColumn method on the Worksheet class (WorkSheet Class on Github).


Let me know if you have issues doing this.

Regards

Solution

Afonso Aguas wrote:

Hi Erik, the extension does not currently implement a DeleteColumn action, but you should be able to easily add one.

The library that is used by the extension (EPPlus) already has that feature, so all you need to do is open the extension through integration studio and add a new action like DeleteColumn(object workSheet, int column). 

Then implement that action in .NET which is as easy as calling the DeleteColumn method on the Worksheet class (WorkSheet Class on Github).


Let me know if you have issues doing this.

Regards


Thanks for you tip, the extention was indeed easily extended. And it does the trick. Hopefuly the delete column action will also be in a new forge version of Advanced Excel :) 

Note: Unfortunately, DeleteColumn fails when there is a cell with a comment in it. 

I'm sure that after extending so quickly, you can easily implement some mechanism to clear a column when you want to delete it.

Question. Is it possible to add the Column_Delete and the DeleteComment function to the forge component? 


C# Column_Delete:

            ExcelWorksheet ws;

            ws = (ExcelWorksheet)ssWorksheet;

            ws.DeleteColumn(ssColumn);

C# DeleteComment   

            ExcelWorksheet ws;

            ws = (ExcelWorksheet)ssWorksheet;

            ws.Comments.Remove(ws.Cells[ssRow, ssColumn].Comment);

Hi Erik

Sure, I will add it.

Hanno

Erik Brzozowski wrote:

Question. Is it possible to add the Column_Delete and the DeleteComment function to the forge component? 


C# Column_Delete:

            ExcelWorksheet ws;

            ws = (ExcelWorksheet)ssWorksheet;

            ws.DeleteColumn(ssColumn);

C# DeleteComment   

            ExcelWorksheet ws;

            ws = (ExcelWorksheet)ssWorksheet;

            ws.Comments.Remove(ws.Cells[ssRow, ssColumn].Comment);

Erik, you should be able to find the requested actions in the latest version of the component.