Aspose Cells - Would it be possible to remove rows which has 0 for one or more value columns in a Workbook with multiple worksheets

Aspose.Cells Product Family
1

Using Aspose Cells for C3.net, our requirement is to remove rows from table which has 0 value for year value columns[“YE 31 March 2021” and “YE 31 March 2022”]. For example in this table we would want to remove programmatically the last but one row which has 0 value for both columns[“Director’s commission”]. This need to be done for the entire Excel workbook with multiple worksheets.

“Other_expenses” “YE 31 March 2021” “YE 31 March 2020”

Legal and professional fees 157,021.63 157,022.13
Corporate social responsibility (refer note 0) 10,620.65 10,620.65
Travelling and conveyance 74,549.26 74,549.26
Rent 2,996.50 2,996.50
Security Service charges 0 10.00
Director’s commission 0 0 <–This row need to removed
Printing and stationery 1,241.70 1,241.70

Checked the knowledge artcile-> Delete Blank Rows and Columns in a Worksheet|Documentation (aspose.com)

In your solution instead of looping through all rows/cells in a worksheet or workbook
could this below questions possible?

  1. If the 0 value rows[all columns have 0 value] are marked with a metadata (Example a token like “<<zero_val>>”) would it be possible to call a function to delete all the rows which has this metadata? This way this will be a one call and would not need to loop through all the rows and cells.
2

@qlnsubhash,

Thanks for sharing details.

I think you may use Find or Search options provided by Aspose.Cells APIs to find the underlying column’s specified value (e.g.,“0”). This way, you will get the cell. You can get the row index from the (found) cell via Cell.Row attribute. Now you may use this row index to remove the whole row via Cells.DeleteRow/DeleteRows, see the document on how to remove rows in the worksheet for your reference.

If in any case, you still could not evaluate or accomplish the task, kindly zip and attach your template file with details on which row(s) you need to remove. We will check and help you to implement the task via Aspose.Cells APIs.

3

Checked the links provided. Could you able to provide a sample code as our requirement is below. Included the input file with rows highlighted in yellow and the expected output file with the rows removed.
Book1-Input-xlsx.zip (11.9 KB)

Book1-Output-xlsx.zip (11.7 KB)

Our requirement is to remove rows from Excel file which has 0 value for year value columns[“For the year ended 31 March 2021” and “For the year ended 31 March 2020”]. There could be empty column(Column B)which we would need to ignore also in this check. For example in this spreadsheet we would want to remove programmatically the rows(yellow highlight) 5 and 10 which has 0 value for both columns. This same logic need to be applied to other worksheets in the file also.

How to achieve the similar requirement is mentioned in this topic for MS Word-> MS Word - Would it be possible to Insert Bookmark programmatically(C#) in a selected row or position - Aspose.Words Product Family - Free Support Forum - aspose.com

4

@qlnsubhash
There is no good method to implement this feature. Please try the following codes:
var wb = new Workbook(dir + “Book1-Input.xlsx”);
Cells cells = wb.Worksheets[0].Cells;

        for(int i = cells.Rows.Count - 1; i >= 1; i--)
        {
            Row rowInfo = cells.Rows.GetRowByIndex(i);
            Cell cell1 = rowInfo.GetCellOrNull(3);
            if(cell1 == null
                || cell1.Type != CellValueType.IsNumeric
                || cell1.DoubleValue != 0)
            {
                continue;
            }

            cell1 = rowInfo.GetCellOrNull(5);
            if (cell1 == null
                || cell1.Type != CellValueType.IsNumeric
                || cell1.DoubleValue != 0)
            {
                continue;
            }
            cells.DeleteRow(rowInfo.Index);

        }
        wb.Save(dir + "dest.xlsx");
    }