حذف النص من خلية

OpenXML Excel

 string FilePath = @"..\..\..\..\Sample Files\";

string FileName = FilePath + "Delete text from a cell.xlsx";

string sheetName = "Sheet1";

string colName = "B";

uint rowIndex = 2;

DeleteTextFromCell(FileName, sheetName, colName, rowIndex);

// Given a document, a worksheet name, a column name, and a one-based row index,

// deletes the text from the cell at the specified column and row on the specified worksheet.

public static void DeleteTextFromCell(string docName, string sheetName, string colName, 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;

        }

        string relationshipId = sheets.First().Id.Value;

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

        // Get the cell at the specified column and row.

        Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);

        if (cell == null)

        {

            // The specified cell does not exist.

            return;

        }

        cell.Remove();

        worksheetPart.Worksheet.Save();

    }

}

// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.

private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)

{

    IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);

    if (rows.Count() == 0)

    {

        // A cell does not exist at the specified row.

        return null;

    }

    IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);

    if (cells.Count() == 0)

    {

        // A cell does not exist at the specified column, in the specified row.

        return null;

    }

    return cells.First();

}

// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 

// reference the specified SharedStringItem and removes the item.

private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)

{

    bool remove = true;

    foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())

    {

        Worksheet worksheet = part.Worksheet;

        foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())

        {

            // Verify if other cells in the document reference the item.

            if (cell.DataType != null &&

                cell.DataType.Value == CellValues.SharedString &&

                cell.CellValue.Text == shareStringId.ToString())

            {

                // Other cells in the document still reference the item. Do not remove the item.

                remove = false;

                break;

            }

        }

        if (!remove)

        {

            break;

        }

    }

    // Other cells in the document do not reference the item. Remove the item.

    if (remove)

    {

        SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;

        if (shareStringTablePart == null)

        {

            return;

        }

        SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);

        if (item != null)

        {

            item.Remove();

            // Refresh all the shared string references.

            foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())

            {

                Worksheet worksheet = part.Worksheet;

                foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())

                {

                    if (cell.DataType != null &&

                        cell.DataType.Value == CellValues.SharedString)

                    {

                        int itemIndex = int.Parse(cell.CellValue.Text);

                        if (itemIndex > shareStringId)

                        {

                            cell.CellValue.Text = (itemIndex - 1).ToString();

                        }

                    }

                }

                worksheet.Save();

            }

            document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();

        }

    }

}

Aspose.Cells

 string FilePath = @"..\..\..\..\Sample Files\";

string FileName = FilePath + "Delete text from a cell.xlsx";

string sheetName = "Sheet1";

string colName = "B";

uint rowIndex = 2;

DeleteTextFromCell(FileName, sheetName, colName, rowIndex);

private static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)

    {

        //Instantiating a Workbook object

        Workbook workbook = new Workbook(docName);

        //Obtaining the reference of the worksheet by passing its Name

        Worksheet worksheet = workbook.Worksheets[sheetName];

        //Removing value from cell

        worksheet.Cells[colName + rowIndex].PutValue("");

        //Saving the Excel file

        workbook.Save(docName);

    }

تحميل رمز عينة

• GitHub
• Sourceforge
• Bitbucket