Free Support Forum - aspose.com

Issue in deleting Column from worksheet

Aspose.Cells Product Family
#1

HI

I have an excel worksheet where I need to delete a column which is having all the row values as 0.

I don't want to loop through the rows and delete. Is there a way where in one shot I can delete a column having all the values as 0.

I am attaching an excel which has 3 columns. I want Col2 to be deleted.

#2

Hi,


Thanks for the file.

Well, there cannot be any single command that can do it because it is your custom logic that cannot be fulfilled even in MS Excel. You have to write a few lines for it. I think you may simply use Find and Search options provided by Aspose.Cells, find your desired values in a column, get the column index. Now you may remove it accordingly. See a sample code that will support your needs, you may update or refine it more if you want.

Sample code:

Workbook workbook = new Workbook(@“e:\test2\Input1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];

Aspose.Cells.Cell foundCell = null;
Aspose.Cells.Cell prevCell=null;
int cnt = 0;
int col=0;

FindOptions findOptions = new FindOptions();
findOptions.CaseSensitive = false;
findOptions.LookInType = LookInType.Values;
findOptions.SeachOrderByRows = false;

do
{
foundCell = worksheet.Cells.Find(“0”, prevCell, findOptions);
// foundCell = worksheet.Cells.FindStringContains("&", prevCell, false, area);
if (foundCell == null)
break;
MessageBox.Show(foundCell.Name);
cnt++;
prevCell = foundCell;

//If you find you got the value in three alternative cells in the same column, then exit the loop.
if (cnt >= 3)
{
col = foundCell.Column;
break;

}

} while (foundCell != null);


worksheet.Cells.DeleteColumn(col, true);
workbook.Save(“e:\test2\outdeletencolumns_test2.xlsx”);

Hope, this helps.

Thank you.