none
Remove CellFormula using OpenXML RRS feed

  • Question

  • I want to remove cell formula using OpenXML. Please find below  my code.

    If cell1.CellFormula IsNot Nothing Then

    cell1.CellFormula.Remove()

    End If

    I get a format corrupt error. Please help to resolve this issue.


    Monday, July 1, 2013 3:42 PM

Answers

  • No issue. Thanks for reply!

    I got the answer to my question. If formula is shared formula then do not remove the start cell from Formula Chain.

    Please find below code lines. Check cell is start cell or not for given defined name range. If it is start cell do not call below function else call below function.


    Mangesh Gabhane


    Protected Sub RemoveCellFormula(ByVal workbookPart1 As WorkbookPart, ByVal sheetName As String, ByVal cellReference1 As String)

    Dim sheet As Sheet = workbookPart1.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = sheetName).FirstOrDefault()

    If sheet IsNot Nothing Then

    Dim worksheet1 As Worksheet = DirectCast(workbookPart1.GetPartById(sheet.Id), WorksheetPart).Worksheet

    Dim cell1 As Cell = InsertCellInWorksheet(worksheet1, cellReference1)

    Dim calculationChainPart1 As CalculationChainPart = workbookPart1.CalculationChainPart

    Dim calculationChain1 As CalculationChain = calculationChainPart1.CalculationChain

    Dim calculationCell1 As CalculationCell = calculationChain1.Elements(Of CalculationCell).Where(Function(c) c.CellReference = cellReference1).FirstOrDefault()

    If cell1.CellFormula IsNot Nothing Then

    calculationCell1.Remove()

    cell1.CellFormula.Remove()

    End If

    End If

    End Sub

    Tuesday, July 16, 2013 2:35 PM

All replies

  • Hi Mangesh,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 3, 2013 11:31 AM
    Moderator
  • No issue. Thanks for reply!

    I got the answer to my question. If formula is shared formula then do not remove the start cell from Formula Chain.

    Please find below code lines. Check cell is start cell or not for given defined name range. If it is start cell do not call below function else call below function.


    Mangesh Gabhane


    Protected Sub RemoveCellFormula(ByVal workbookPart1 As WorkbookPart, ByVal sheetName As String, ByVal cellReference1 As String)

    Dim sheet As Sheet = workbookPart1.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = sheetName).FirstOrDefault()

    If sheet IsNot Nothing Then

    Dim worksheet1 As Worksheet = DirectCast(workbookPart1.GetPartById(sheet.Id), WorksheetPart).Worksheet

    Dim cell1 As Cell = InsertCellInWorksheet(worksheet1, cellReference1)

    Dim calculationChainPart1 As CalculationChainPart = workbookPart1.CalculationChainPart

    Dim calculationChain1 As CalculationChain = calculationChainPart1.CalculationChain

    Dim calculationCell1 As CalculationCell = calculationChain1.Elements(Of CalculationCell).Where(Function(c) c.CellReference = cellReference1).FirstOrDefault()

    If cell1.CellFormula IsNot Nothing Then

    calculationCell1.Remove()

    cell1.CellFormula.Remove()

    End If

    End If

    End Sub

    Tuesday, July 16, 2013 2:35 PM
  • The Answer chosen talks about not being able to remove the formula for the starting cell of a shared formula and I need to know how to do that.
    Wednesday, January 27, 2016 3:35 AM