Delete columns from spreadsheet document in c#

Question

     SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open( 
                                                        newFilePath, true );
            var sheet = spreadsheetDocument.WorkbookPart.Workbook
                            .Sheets.Elements<Sheet>()
                            .FirstOrDefault();

            var sheetReferenceId = sheet.Id;

            WorksheetPart worksheetPart = ( WorksheetPart ) 
           spreadsheetDocument.WorkbookPart.GetPartById( sheetReferenceId );
      IEnumerable<Row> rows = 
      worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
                    foreach ( Row row in rows )
            {
                int index = ( int ) row.RowIndex.Value + 1;

                IEnumerable<Cell> cells = row.Elements<Cell>();

                IEnumerable<Cell> cellToDelete = cells.Where( c => string.Compare( c.CellReference.Value, "DD" + index, true ) == 0 );

                if ( cellToDelete.Count() > 0 )
                {
                    cellToDelete.First().Remove();
                }
            }

This code only remove the text from column.But i want to delete the entire column "DD". I am using Open XML SDK for it.

usually in excel it is not a cell you delete but something like entirecolumn.delete — Luuklag
yes i tried but it delete the text from cell but not delete the entire column. — user3515219

Ravikumar Anbarasu Ravikumar Anbarasu · Accepted Answer · 2022-05-17T13:14:18

We can achieve by using Microsoft.Office.Interop.Excel;

    public string removerowsfromexcel(string errorexceldocasbase64text, list<int> rownums)
    {
        string data = string.empty;
        memorystream ms = new memorystream(0);
        byte[] a = convert.frombase64string(errorexceldocasbase64text);//encoding.ascii.getbytes(base64);
        ms.write(a, 0, a.length);

        string processfilename = "\\temp" + datetime.now.ticks + ".xlsx";
        var tempfiledirectory = environment.currentdirectory + "\\tempfiles";
        string processfilepath = tempfiledirectory + processfilename;

        if (directory.exists(tempfiledirectory))
        {
            if (file.exists(processfilepath))
            {
                file.delete(processfilepath);
                file.writeallbytes(processfilepath, a);
            }
            else
            {
                file.writeallbytes(processfilepath, a);
            }
        }
        else
        {
            directory.createdirectory(tempfiledirectory);

            if (directory.exists(tempfiledirectory))
            {
                if (file.exists(processfilepath))
                {
                    file.delete(processfilepath);
                    file.writeallbytes(processfilepath, a);
                }
                else
                {
                    file.writeallbytes(processfilepath, a);
                }
            }
        }

        _application docexcel = new microsoft.office.interop.excel.application { visible = false };
        dynamic workbooksexcel = docexcel.workbooks.open(processfilepath);
        var worksheetexcel = (_worksheet)workbooksexcel.activesheet;

        foreach (var rowidx in rownums)
        {
            if (rowidx != 1)
            {
                ((range)worksheetexcel.rows[rowidx, missing.value]).delete(xldeleteshiftdirection.xlshiftup);
            }
        }

        workbooksexcel.save();
        workbooksexcel.close(false);
        byte[] bytes = file.readallbytes(processfilepath);
        string base64 = convert.tobase64string(bytes);
        docexcel.application.quit();
        file.delete(processfilepath);
        return base64;
    }

Delete columns from spreadsheet document in c#

1 Answers