none
How to remove a Column? How about a Row? RRS feed

  • Question

  • How do we programmatically remove a Column in the same fashion as Excel would?

    While we are in the topic: How to remove a Row?

    TIA

    Saturday, December 28, 2013 10:48 PM

Answers

  • Hi,

    According to your description, you want to remove a column and a row with Open XML SDK.

    Here is a sample for your reference.

    string result = RemoveAColumn(@"C:\Users\test1\Documents\Book1.xlsx", "Sheet1", "D");
    
    result = RemoveARow(@"C:\Users\test1\Documents\Book1.xlsx", "Sheet1", 3);
    
    public static string RemoveAColumn(string docName, string sheetName, string colName)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
    
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return sheetName + "doesn't exist";
            }
    
            string relationshipId = sheets.First().Id.Value;
    
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    
            // Get the Total Rows
            IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
    
            if (rows.Count() == 0)
            {
                return "Rows doesn't exist";
            }
    
            // Loop through the rows and adjust Cell Index
            foreach (Row row in rows)
            {
                int index = (int)row.RowIndex.Value;
    
                IEnumerable<Cell> cells = row.Elements<Cell>();
    
                IEnumerable<Cell> cellToDelete = cells.Where(c => string.Compare(c.CellReference.Value, colName + index, true) == 0);
    
                if (cellToDelete.Count() > 0)
                {
                    cellToDelete.First().Remove();
                }
            }
            worksheetPart.Worksheet.Save();
            return "Removed Column";
        }
    }
    
    public static string RemoveARow(string docName, string sheetName, uint rowIndex)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
    
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return sheetName + "doesn't exist";
            }
    
            string relationshipId = sheets.First().Id.Value;
    
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    
            // Get the Total Rows
            IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
    
            if (rows.Count() == 0)
            {
                return "Rows doesn't exist";
    
            }
    
            // Loop through the rows and adjust Cell Index
            foreach (Row row in rows)
            {
                int index = (int)row.RowIndex.Value;
    
                if (rowIndex == index)
                {
                    row.Remove();
                }
            }
            worksheetPart.Worksheet.Save();
            return "Removed Row";
        }
    }

    About deleting a cell in a special row and column, you could refer to How to: Delete text from a cell in a spreadsheet document (Open XML SDK)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 30, 2013 8:17 AM
    Moderator
  • Hi,

    We could remove a column or a row with Remove method after finding the related cells or row as the code above.

    However, it's a complex task to delete a column or a row instead of clearing the content.

    You could take a look at the structure of a spreadsheet by Open XML productivity tool as below.

    We could see that there is RowIndex property to define the information of row and CellReference property to define the information of every cell containing row and column index. But no property is used to specify column index.

    To delete a column “D”, we should clear the cells where the CellReference property contains “D” in every used row and then move all the right cells to left. To move the cells, we should decrease the CellReference property of right cells in every used row orderly.

    To delete a row “3”, we should clear the whole row where the RowIndex property equal 3 firstly as the code above. Then we should not only minus the RowIndex property of the behind rows but only decrease the CellReference property of cells in these rows.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 31, 2013 10:51 AM
    Moderator

All replies

  • Hi,

    According to your description, you want to remove a column and a row with Open XML SDK.

    Here is a sample for your reference.

    string result = RemoveAColumn(@"C:\Users\test1\Documents\Book1.xlsx", "Sheet1", "D");
    
    result = RemoveARow(@"C:\Users\test1\Documents\Book1.xlsx", "Sheet1", 3);
    
    public static string RemoveAColumn(string docName, string sheetName, string colName)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
    
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return sheetName + "doesn't exist";
            }
    
            string relationshipId = sheets.First().Id.Value;
    
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    
            // Get the Total Rows
            IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
    
            if (rows.Count() == 0)
            {
                return "Rows doesn't exist";
            }
    
            // Loop through the rows and adjust Cell Index
            foreach (Row row in rows)
            {
                int index = (int)row.RowIndex.Value;
    
                IEnumerable<Cell> cells = row.Elements<Cell>();
    
                IEnumerable<Cell> cellToDelete = cells.Where(c => string.Compare(c.CellReference.Value, colName + index, true) == 0);
    
                if (cellToDelete.Count() > 0)
                {
                    cellToDelete.First().Remove();
                }
            }
            worksheetPart.Worksheet.Save();
            return "Removed Column";
        }
    }
    
    public static string RemoveARow(string docName, string sheetName, uint rowIndex)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
    
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return sheetName + "doesn't exist";
            }
    
            string relationshipId = sheets.First().Id.Value;
    
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    
            // Get the Total Rows
            IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
    
            if (rows.Count() == 0)
            {
                return "Rows doesn't exist";
    
            }
    
            // Loop through the rows and adjust Cell Index
            foreach (Row row in rows)
            {
                int index = (int)row.RowIndex.Value;
    
                if (rowIndex == index)
                {
                    row.Remove();
                }
            }
            worksheetPart.Worksheet.Save();
            return "Removed Row";
        }
    }

    About deleting a cell in a special row and column, you could refer to How to: Delete text from a cell in a spreadsheet document (Open XML SDK)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 30, 2013 8:17 AM
    Moderator
  • Hi Luna:

    Thanks for your kind reply, but I am afraid the code provided is incomplete. :-(

    From my post: "remove a Column in the same fashion as Excel would"

    In the above image, columns "E" and "F" should move to the left. The code posted simply clears the content of column "D", while columns "E" and "F" remain untouched, in the same place.

    Thanks again!


    Monday, December 30, 2013 11:10 AM
  • Hi,

    We could remove a column or a row with Remove method after finding the related cells or row as the code above.

    However, it's a complex task to delete a column or a row instead of clearing the content.

    You could take a look at the structure of a spreadsheet by Open XML productivity tool as below.

    We could see that there is RowIndex property to define the information of row and CellReference property to define the information of every cell containing row and column index. But no property is used to specify column index.

    To delete a column “D”, we should clear the cells where the CellReference property contains “D” in every used row and then move all the right cells to left. To move the cells, we should decrease the CellReference property of right cells in every used row orderly.

    To delete a row “3”, we should clear the whole row where the RowIndex property equal 3 firstly as the code above. Then we should not only minus the RowIndex property of the behind rows but only decrease the CellReference property of cells in these rows.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 31, 2013 10:51 AM
    Moderator
  • Hey Luna and thanks for your replies.

    I know it's been a while but I'm facing a similar problem where I need to delete entire columns off a spreadsheet.

    I tried your solution but it only clears the contents of those cells, the column is not deleted.

    Thank you


    Shimmy


    Wednesday, November 27, 2019 9:50 AM