Search code examples
excelvb.netepplus

While opening excel error message comes up when written with epplus


When I try to write something from the method given by EPPlus i.e. It comes up with two error messages

We have found a problem with some content

Excel completed file level validation and repair. some parts of this workbook may have been repaired or discarded.

Excel opens successfully but, with error messages and one more thing excel I'm writing is already written that means it is a template.

 Dim consh As ExcelWorksheet
    'Dim excelStream As New MemoryStream()
    'excelStream.Write(excel, 0, excel.Length)
    Dim exlpck As New ExcelPackage(excel)
    If exlpck.Workbook.Worksheets(cellExcelTabName) Is Nothing Then
        consh = exlpck.Workbook.Worksheets.Add(cellExcelTabName)
    Else
        consh = exlpck.Workbook.Worksheets(cellExcelTabName)
    End If
    Dim start = consh.Dimension.Start
    Dim [end] = consh.Dimension.[End]
    For row As Integer = 4 To [end].Row
        ' Row by row...
        For col As Integer = 18 To 35
            ' ... Cell by cell...
            ' This got me the actual value I needed.
            Dim cellValue As String = consh.Cells(row, col).Text
            Dim cellAddress = consh.Cells(row, col).Address
            Dim i = 0
            For Each mText In textToFind
                If cellValue.Contains(mText) Then
                    consh.Cells(cellAddress).Value = cellValue.Replace(mText, "")[enter image description here][1]
                    consh.Cells(cellAddress).Style.Fill.PatternType = ExcelFillStyle.Solid
                    consh.Cells(cellAddress).Style.Fill.BackgroundColor.SetColor(color(mText.Substring(1, 1) - 1))
                    i = i + 1
                End If
            Next
        Next
    Next
    'Dim exlpck1 As New ExcelPackage(e)
    exlpck.Save()
    Dim s = New MemoryStream(exlpck.GetAsByteArray())
    Return s

Solution

  • I found the fix for my code

    exlpck.Save()
    

    to be Replaced by

    exlpck.SaveAs(ms)
    

    And it worked :)