Microsoft.Office.Interop.Excel has different methods for .NET Core and .NET framework?

Question

I am attempting to upgrade dependencies like Microsoft.Office.Interop.Excel and migrate my application from .NET Framework to .NET Core.

However, it seems that this upgrade involves extensive code changes.

Please help!

Methods like these seems unavailable in dotnet Core (Microsoft.Office.Interop.Excel)

deleteRange = worksheet.Range[worksheet.Cells[1, deleteColumnNumber], worksheet.Cells[endRow, deleteColumnNumber]];
deleteRange.Delete(); // seems unavailable

Answer 1

Now we are all using Microsoft 365 while Microsoft.Office.Interop.Excel is mentioned This an assembly you can use for Excel 2013/2016/2019 COM interop, generated and signed by Microsoft. This is entirely unsupported and there is no license since it is a repackaging of Office assemblies.. So that I'm afraid we need to use some other SDK for managing the local excel file.

I find OpenXML SDK which provides tools for working with Office Word, Excel, and PowerPoint documents.

I followed the document to write a sample which can create an xlsx file and delete content. Here's my code and test result.

public void CreateExcelFile(string filepath)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(filepath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = document.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());
        Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());

        Sheet sheet = new Sheet()
        {
            Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "Sheet1"
        };
        sheets.Append(sheet);

        // Get the SheetData to work with.
        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

        // prepare init content
        for (uint rowIndex = 1; rowIndex <= 5; rowIndex++)
        {
            Row row = new Row() { RowIndex = rowIndex };
            sheetData.Append(row);
            for (char col = 'A'; col <= 'C'; col++)
            {
                Cell cell = new Cell()
                {
                    CellReference = $"{col}{rowIndex}", 
                    DataType = CellValues.String,
                    CellValue = new CellValue($"Data {col}{rowIndex}")
                };
                row.Append(cell);
            }
        }
        
        workbookPart.Workbook.Save();
    }
}

public void DeleteRange(string filePath)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
    {
        WorksheetPart worksheetPart = document.WorkbookPart.WorksheetParts.First();//get the first sheet
        Worksheet worksheet = worksheetPart.Worksheet;
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();

        for (uint rowIndex = 1; rowIndex <= 2; rowIndex++)
        {
            Row row = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
            if (row != null)
            {
                // delete content range from A1 to B2
                for (char col = 'A'; col <= 'B'; col++)
                {
                    string cellReference = $"{col}{rowIndex}";
                    Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == cellReference);
                    if (cell != null)
                    {
                        cell.Remove();
                    }
                }
                if (!row.Elements<Cell>().Any())
                {
                    row.Remove();
                }
            }
        }
        worksheet.Save();
    }
}