Export to Excel. Broken Excel files

[alexey77777]

ReoGrid 2.0 (from reogrid.net). Windows Forms. Standard IDE: Visual Studio 2012. Visual Basic OS: WIndows 10

Brief: After Export to Excel do not work standard option "Format Cell" and problem with backgrounds

Full: Actions:

1. Creating a empty rgf file

2. Resizing (12 cols, approx. 100 rows), formatting and adding a background for multiple ranges of cells

3. Adding data (text only)

4. Saving with standard method Save

5. Exporting to Excel

6. Opening with Excel 2010

7. Do not work option "Format Cell" (Excel's window does not appear) and problems with backgrounds Area with data have white background - OK Areas outside the data range have an alternate color, which was previously specified only for the data area.

Code (Styling, Exporting - fragments)

Set styles

intMaxCols = ws.ColumnCount - 1
intmaxRows = ws.RowCount - 1

Dim StartStyle As New WorksheetRangeStyle
StartStyle.Flag = PlainStyleFlag.FontName Or PlainStyleFlag.FontSize Or PlainStyleFlag.AlignAll Or PlainStyleFlag.Padding Or PlainStyleFlag.TextWrap
StartStyle.FontName = xxx.strFontName
StartStyle.FontSize = CSng(Val(xxx.intCells)) 'CSng(Val(xxx.intCells))
StartStyle.HAlign = ReoGridHorAlign.Center
StartStyle.VAlign = ReoGridVerAlign.Middle
StartStyle.TextWrapMode = TextWrapMode.WordBreak

Dim DayStyleFont As New WorksheetRangeStyle
With DayStyleFont
    .Flag = PlainStyleFlag.FontName Or PlainStyleFlag.FontSize Or PlainStyleFlag.AlignAll Or PlainStyleFlag.RotateAngle Or PlainStyleFlag.TextWrap
    .FontName = xxx.strFontName
    .FontSize = CSng(Val(xxx.int10Days))
    .HAlign = ReoGridHorAlign.Center
    .VAlign = ReoGridVerAlign.Middle
    .RotateAngle = 90
    .TextWrapMode = TextWrapMode.WordBreak
End With

Dim MainStyle As New WorksheetRangeStyle
MainStyle.Flag = PlainStyleFlag.BackColor
MainStyle.BackColor = Color.Empty

Dim AlterStyle As New WorksheetRangeStyle
AlterStyle.Flag = PlainStyleFlag.BackColor
AlterStyle.BackColor = Color.FromArgb(255, 173, 216, 230)

Dim stlBold As New RangeBorderStyle
Dim stlSolid As New RangeBorderStyle
Dim stlDashed As New RangeBorderStyle

'Line type
stlBold.Style = BorderLineStyle.BoldSolid 'Полужирная сплошная
stlBold.Color = Color.Black
stlDashed.Style = BorderLineStyle.Dashed 'Штриховая
stlDashed.Color = Color.Black
stlSolid.Style = BorderLineStyle.Solid 'Сплошная
stlSolid.Color = Color.Black

'Start style
.SetRangeStyles(0, 0, intmaxRows, intMaxCols, StartStyle)

Dim intK1 As Integer = 1
For intI As Integer = intHeaderRowNum + 1 To intmaxRows - 8 Step 7
    .SetRangeStyles(intI, 0, 1, 1, DayStyleFont)

    If (intK1 Mod 2 = 0) Then
        ws.SetRangeStyles(intI, 0, 7, ws.ColumnCount, AlterStyle)
    Else
        ws.SetRangeStyles(intI, 0, 7, ws.ColumnCount, MainStyle)
    End If

    intK1 += 1
Next

.Cells("A1").Style.FontSize = 16

'Ranges
ws.SetRangeBorders(intHeaderRowNum + 1, 0, intmaxRows - (intHeaderRowNum + 2), ws.ColumnCount, BorderPositions.InsideAll, stlSolid)
ws.SetRangeBorders(intHeaderRowNum + 1, 0, intmaxRows - (intHeaderRowNum + 2), ws.ColumnCount, BorderPositions.Outside, stlSolid)

For intI As Integer = intHeaderRowNum To intmaxRows - 2 Step 7
    ws.SetRangeBorders(intI, 0, 1, ws.ColumnCount, BorderPositions.Bottom, stlBold)
Next
End With

For intX As Integer = 0 To intmaxRows
    ws.AutoFitRowHeight(intX)
Next

For intX2 As Integer = 0 To intMaxCols
    ws.AutoFitColumnWidth(intX2)
Next

Export to Excel

Dim frmXXX As New Form
Dim rGridX As New ReoGridControl

With rGridX
'Init
.CurrentWorksheet.SelectionStyle = WorksheetSelectionStyle.Default
.CurrentWorksheet.SelectionMode = WorksheetSelectionMode.Range
.CurrentWorksheet.SelectionForwardDirection = unvell.ReoGrid.SelectionForwardDirection.Down
.CurrentWorksheet.SetSettings(WorksheetSettings.View_ShowGridLine, True)
.CurrentWorksheet.SetSettings(WorksheetSettings.View_ShowPageBreaks, True)
.CurrentWorksheet.SetSettings(WorksheetSettings.View_ShowRowHeader, True)
.CurrentWorksheet.SetSettings(WorksheetSettings.View_ShowColumnHeader, True)
.CurrentWorksheet.SetSettings(WorksheetSettings.View_AllowShowRowOutlines, True)
.CurrentWorksheet.SetSettings(WorksheetSettings.View_AllowShowColumnOutlines, True)
.CurrentWorksheet.SelectionMode = WorksheetSelectionMode.Cell

.SetSettings(WorkbookSettings.View_ShowSheetTabControl, False)
.SetSettings(WorkbookSettings.View_ShowScrolls, True)
.SetSettings(WorkbookSettings.View_ShowHorScroll, True)
.SetSettings(WorkbookSettings.View_ShowVerScroll, True)

.Load(strFileNameR, unvell.ReoGrid.IO.FileFormat.ReoGridFormat, Encoding.UTF8)
frmXXX.Controls.Add(rGridX)

.CurrentWorksheet.ShowRows(0, intHeaderRowNum)
.CurrentWorksheet.Unfreeze()

Dim strX As String = String.Empty

'Autocorrection (fragment)
For intI As Integer = 0 To .CurrentWorksheet.RowCount - 1
    For intJ As Integer = 0 To .CurrentWorksheet.ColumnCount - 1
        strX = .CurrentWorksheet.Cells(intI, intJ).DisplayText

'Other code for strings

           'Change background for data areas
        .CurrentWorksheet.Cells(intI, intJ).Style.BackColor = Color.White
    Next intJ
Next intI

'Saving
Dim strFileName As String = "xxx.xlsx"
.Save(strFileName, unvell.ReoGrid.IO.FileFormat.Excel2007)
frmXXX.Close()
End With

[jingwood]

Confirmed. This bug happens when set styles to entire rows.