Merging Cells corrupts workbook

Sep 23, 2010 at 12:59 PM

I have created a spreadsheet using EPPlus and it works great.  I tried to add code to set a range of cells to merge as true but the gets spreadsheet corrupt.  If commecnt out the code that does the merge the spreadsheet is fine.  Meger subroutine code is below (I have tried using ExceRange and it also did not work).  I am am using version 2.7.0.1.

    Sub MergeCells(ByVal sStartCellAddress As String, ByVal sEndCellAddress As String, ByVal iRowOffset As Integer, ByVal iColOffset As Integer)

        Dim sNewStartAddress, sNewEndAddress As String

        sNewStartAddress = CalcCellAddress(sStartCellAddress, iRow, iCol)
        sNewEndAddress = CalcCellAddress(sEndCellAddress, iRow, iCol)
        Dim sRange As String = sNewStartAddress & ":" & sNewEndAddress
        xlWorksheet.Cells(sRange).Merge = True
        xlWorksheet.Cells(sRange).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
        xlWorksheet.Cells(sRange).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top
        xlWorksheet.Cells(sRange).Style.WrapText = True
    End Sub

Thanks,

Glenn

Coordinator
Sep 23, 2010 at 1:54 PM

Hi,

That's supposed to work.

Some questions...

What is the value of sRange when you set the Merge propery?

What is the content of this range?

iRow and iCol are not declared in the method? Do you intend to use iRowOffset and iColOffset?

Jan

Sep 23, 2010 at 2:10 PM

The value of the range is "D12:I12".  As far as the value is concerned I have tried both setting it to "" and a string value.

I see your point about the iRow and iCol are not defined and should be iRowOffset and iColOffset.  The fuction does return the correct values.  I am olny testing this routine ones with offset values of 0.

 I have also hard coded the the range and have tried using the range (12, 4, 12, 9)

Here is the corrected code and it still fails.

    Sub MergeCells(ByVal sStartCellAddress As String, ByVal sEndCellAddress As String, ByVal iRowOffset As Integer, ByVal iColOffset As Integer)

        Dim sNewStartAddress, sNewEndAddress As String

        sNewStartAddress = CalcCellAddress(sStartCellAddress, iRowOffset, iColOffset)
        sNewEndAddress = CalcCellAddress(sEndCellAddress, iRowOffset, iColOffset)
        Dim sRange As String = sNewStartAddress & ":" & sNewEndAddress
        xlWorksheet.Cells(sRange).Merge = True
        xlWorksheet.Cells(sRange).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
        xlWorksheet.Cells(sRange).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top
        xlWorksheet.Cells(sRange).Style.WrapText = True
    End Sub

The error when I open the workbook is:

"Excel found unreadable content in 'spreadsheetname.xlsx'.  Do you want to recover the contents of this workbook?  If you trust the source of this workbook, click Yes."

Glenn