Open XML: Delete entire excel column using column index

Viewed 4808

I have got column index for an excel column in a spreadsheet and need to delete the entire column using this column index. I am confined to use Open XML SDK 2.0.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace openXMLDemo
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string fileFullPath = @"path to the excel file here";
            string sheetName = "excelsheet name here";

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileFullPath, true))
            {
                Sheet sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
                if (sheet != null)
                {
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);

                    // This is where I am struggling.... 
                    // finding the reference to entire column with the use of column index
                    Column columnToDelete = sheet.GetFirstChild<SheetData>().Elements<Column>()
                }
            }
        }
    }    
}
2 Answers

@Shimmy Weitzhandler. OpenXML does not emulate Excel. The code proposed by @Chawin would indeed remove the cells. If you open the workbook as a zip archive and open the sheet from which cells were removed, you will no longer see those cells. But, if you have cells to the right from the removed cells, they will remain where they were. To move these cells left, you will need to adjust their Cell.CellReference property. Depending upon the contents of the sheet, you might need to also adjust other things, like formulas, formatting, data validation, ignored errors, and so on.

Related