Imports C1.Silverlight.Excel
Imports C1.Silverlight.FlexGrid
Imports C1.Silverlight

Imports System.Net
Imports System.Windows
Imports System.Windows.Controls
Imports System.Windows.Documents
Imports System.Windows.Ink
Imports System.Windows.Input
Imports System.Windows.Media
Imports System.Windows.Media.Animation
Imports System.Windows.Shapes

Namespace ExcelExport

    ''' <summary>
    ''' Class that provides methods for transferring data between an XLSheet and a C1FlexGrid.
    ''' </summary>
    Friend NotInheritable Class ExcelFilter
        Private Shared _lastBook As C1XLBook
        Private Shared _cellStyles As New Dictionary(Of XLStyle, ExcelCellStyle)()
        Private Shared _excelStyles As New Dictionary(Of ExcelCellStyle, XLStyle)()

        '---------------------------------------------------------------------------------
#Region "** object model"

        ''' <summary>
        ''' Saves the content of a C1FlexGrid into an XLSheet.
        ''' </summary>
        Public Shared Sub Save(ByVal flex As C1FlexGrid, ByVal sheet As XLSheet)
            ' clear style cache if this is a new book
            If sheet.Book IsNot _lastBook Then
                _cellStyles.Clear()
                _excelStyles.Clear()
                _lastBook = sheet.Book
            End If

            ' save global parameters
            sheet.DefaultRowHeight = PixelsToTwips(flex.Rows.DefaultSize)
            sheet.DefaultColumnWidth = PixelsToTwips(flex.Columns.DefaultSize)
            sheet.Locked = flex.IsReadOnly
            sheet.ShowGridLines = flex.GridLinesVisibility <> GridLinesVisibility.None
            sheet.ShowHeaders = flex.HeadersVisibility <> HeadersVisibility.None
            sheet.OutlinesBelow = flex.GroupRowPosition = GroupRowPosition.BelowData

            ' save columns
            sheet.Columns.Clear()
            For Each col As Column In flex.Columns
                Dim c = sheet.Columns.Add()
                If Not col.Width.IsAuto Then
                    c.Width = PixelsToTwips(col.ActualWidth)
                End If
                c.Visible = col.Visible
                If TypeOf col.CellStyle Is ExcelCellStyle Then
                    c.Style = GetXLStyle(flex, sheet, CType(col.CellStyle, ExcelCellStyle))
                End If
            Next col

            sheet.Rows.Clear()

            'save column headers
            Dim headerStyle As XLStyle
            headerStyle = New XLStyle(sheet.Book)
            headerStyle.Font = New XLFont("Arial", 10, True, False)

            For Each row As Row In flex.ColumnHeaders.Rows
                Dim r = sheet.Rows.Add()
                If row.Height > -1 Then
                    r.Height = PixelsToTwips(row.Height)
                End If
                If TypeOf row.CellStyle Is ExcelCellStyle Then
                    r.Style = GetXLStyle(flex, sheet, CType(row.CellStyle, ExcelCellStyle))
                End If
                If TypeOf row Is ExcelRow Then
                    r.OutlineLevel = (CType(row, ExcelRow)).Level
                End If
                For c As Integer = 0 To flex.ColumnHeaders.Columns.Count - 1
                    ' save cell value
                    Dim cell = sheet(row.Index, c)
                    Dim colHeader As String = If(flex.ColumnHeaders(row.Index, c) IsNot Nothing, flex.ColumnHeaders(row.Index, c).ToString(), flex.Columns(c).ColumnName)
                    cell.Value = colHeader

                    ' make column headers bold
                    cell.Style = headerStyle

                Next c
                r.Visible = row.Visible
            Next row



            ' save rows
            For Each row As Row In flex.Rows
                Dim r = sheet.Rows.Add()
                If row.Height > -1 Then
                    r.Height = PixelsToTwips(row.Height)
                End If
                If TypeOf row.CellStyle Is ExcelCellStyle Then
                    r.Style = GetXLStyle(flex, sheet, CType(row.CellStyle, ExcelCellStyle))
                End If
                If TypeOf row Is ExcelRow Then
                    r.OutlineLevel = (CType(row, ExcelRow)).Level
                End If
                r.Visible = row.Visible
            Next row

            ' save cells
            For r As Integer = flex.ColumnHeaders.Rows.Count To flex.Rows.Count - 1
                For c As Integer = 0 To flex.Columns.Count - 1
                    ' save cell value
                    Dim cell = sheet(r, c)
                    Dim obj = flex(r, c)
                    cell.Value = If(TypeOf obj Is FrameworkElement, 0, obj)

                    ' save cell formula and style
                    Dim row = TryCast(flex.Rows(r), ExcelRow)
                    If row IsNot Nothing Then
                        ' save cell formula
                        Dim col = flex.Columns(c)

                        ' save cell style
                        Dim cs = TryCast(row.GetCellStyle(col), ExcelCellStyle)
                        If cs IsNot Nothing Then
                            cell.Style = GetXLStyle(flex, sheet, cs)
                        End If
                    End If
                Next c
            Next r

            ' save selection
            Dim sel = flex.Selection
            If sel.IsValid Then
                Dim xlSel = New XLCellRange(sheet, sel.Row, sel.Row2, sel.Column, sel.Column2)
                sheet.SelectedCells.Clear()
                sheet.SelectedCells.Add(xlSel)
            End If
        End Sub

#End Region

        '---------------------------------------------------------------------------------
#Region "** implementation"

        Private Shared Function TwipsToPixels(ByVal twips As Double) As Double
            Return CInt(Fix(twips / 1440.0 * 96.0 * 1.2 + 0.5))
        End Function
        Private Shared Function PixelsToTwips(ByVal pixels As Double) As Integer
            Return CInt(Fix(pixels * 1440.0 / 96.0 / 1.2 + 0.5))
        End Function
        Private Shared Function PointsToPixels(ByVal points As Double) As Double
            Return points / 72.0 * 96.0 * 1.2
        End Function
        Private Shared Function PixelsToPoints(ByVal pixels As Double) As Double
            Return pixels * 72.0 / 96.0 / 1.2
        End Function

        ' convert excel styles into grid styles
        Private Shared Function GetCellStyle(ByVal x As XLStyle) As ExcelCellStyle
            ' look it up in the cache
            Dim s As ExcelCellStyle
            If _cellStyles.TryGetValue(x, s) Then
                Return s
            End If

            ' not found, create style now
            s = New ExcelCellStyle()

            ' alignment
            Select Case x.AlignHorz
                Case XLAlignHorzEnum.Left
                    s.HorizontalAlignment = HorizontalAlignment.Left
                Case XLAlignHorzEnum.Center
                    s.HorizontalAlignment = HorizontalAlignment.Center
                Case XLAlignHorzEnum.Right
                    s.HorizontalAlignment = HorizontalAlignment.Right
            End Select
            Select Case x.AlignVert
                Case XLAlignVertEnum.Top
                    s.VerticalAlignment = VerticalAlignment.Top
                Case XLAlignVertEnum.Center
                    s.VerticalAlignment = VerticalAlignment.Center
                Case XLAlignVertEnum.Bottom
                    s.VerticalAlignment = VerticalAlignment.Bottom
            End Select
            s.TextWrapping = x.WordWrap

            ' colors
            If x.BackPattern = XLPatternEnum.Solid AndAlso IsColorValid(x.BackColor) Then
                s.Background = New SolidColorBrush(x.BackColor)
            End If
            If IsColorValid(x.ForeColor) Then
                s.Foreground = New SolidColorBrush(x.ForeColor)
            End If

            ' font
            Dim font = x.Font
            If font IsNot Nothing Then
                s.FontFamily = New FontFamily(font.FontName)
                s.FontSize = PointsToPixels(font.FontSize)
                If font.Bold Then
                    s.FontWeight = FontWeights.Bold
                End If
                If font.Italic Then
                    s.FontStyle = FontStyles.Italic
                End If
                If font.Underline <> XLUnderlineStyle.None Then
                    s.TextDecorations = TextDecorations.Underline
                End If
            End If

            ' format
            If Not String.IsNullOrEmpty(x.Format) Then
                s.Format = XLStyle.FormatXLToDotNet(x.Format)
            End If

            ' borders
            s.CellBorderThickness = New Thickness(GetBorderThickness(x.BorderLeft), GetBorderThickness(x.BorderTop), GetBorderThickness(x.BorderRight), GetBorderThickness(x.BorderBottom))
            s.CellBorderBrushLeft = GetBorderBrush(x.BorderColorLeft)
            s.CellBorderBrushTop = GetBorderBrush(x.BorderColorTop)
            s.CellBorderBrushRight = GetBorderBrush(x.BorderColorRight)
            s.CellBorderBrushBottom = GetBorderBrush(x.BorderColorBottom)

            ' save in cache and return
            _cellStyles(x) = s
            Return s
        End Function

        ' convert grid styles into excel styles
        Private Shared Function GetXLStyle(ByVal flex As C1FlexGrid, ByVal sheet As XLSheet, ByVal s As ExcelCellStyle) As XLStyle
            ' look it up in the cache
            Dim x As XLStyle
            If _excelStyles.TryGetValue(s, x) Then
                Return x
            End If

            ' not found, create style now
            x = New XLStyle(sheet.Book)

            ' alignment
            If s.HorizontalAlignment.HasValue Then
                Select Case s.HorizontalAlignment.Value
                    Case HorizontalAlignment.Left
                        x.AlignHorz = XLAlignHorzEnum.Left
                    Case HorizontalAlignment.Center
                        x.AlignHorz = XLAlignHorzEnum.Center
                    Case HorizontalAlignment.Right
                        x.AlignHorz = XLAlignHorzEnum.Right
                End Select
            End If
            If s.VerticalAlignment.HasValue Then
                Select Case s.VerticalAlignment.Value
                    Case VerticalAlignment.Top
                        x.AlignVert = XLAlignVertEnum.Top
                    Case VerticalAlignment.Center
                        x.AlignVert = XLAlignVertEnum.Center
                    Case VerticalAlignment.Bottom
                        x.AlignVert = XLAlignVertEnum.Bottom
                End Select
            End If
            If s.TextWrapping.HasValue Then
                x.WordWrap = s.TextWrapping.Value
            End If

            ' colors
            If TypeOf s.Background Is SolidColorBrush Then
                x.BackColor = (CType(s.Background, SolidColorBrush)).Color
                x.BackPattern = XLPatternEnum.Solid
            End If
            If TypeOf s.Foreground Is SolidColorBrush Then
                x.ForeColor = (CType(s.Foreground, SolidColorBrush)).Color
            End If

            ' font
            Dim fontName = flex.FontFamily.Source
            Dim fontSize = flex.FontSize
            Dim bold = False
            Dim italic = False
            Dim underline As Boolean = False
            Dim hasFont As Boolean = False
            If s.FontFamily IsNot Nothing Then
                fontName = s.FontFamily.Source
                hasFont = True
            End If
            If s.FontSize.HasValue Then
                fontSize = s.FontSize.Value
                hasFont = True
            End If
            If s.FontWeight.HasValue Then
                bold = s.FontWeight.Value = FontWeights.Bold OrElse s.FontWeight.Value = FontWeights.ExtraBold OrElse s.FontWeight.Value = FontWeights.SemiBold
                hasFont = True
            End If
            If s.FontStyle.HasValue Then
                italic = s.FontStyle.Value = FontStyles.Italic
                hasFont = True
            End If
            If s.TextDecorations IsNot Nothing Then
                underline = True
                hasFont = True
            End If
            If hasFont Then
                fontSize = PixelsToPoints(fontSize)
                If underline Then
                    Dim color = Colors.Black
                    If TypeOf flex.Foreground Is SolidColorBrush Then
                        color = (CType(flex.Foreground, SolidColorBrush)).Color
                    End If
                    If TypeOf s.Foreground Is SolidColorBrush Then
                        color = (CType(s.Foreground, SolidColorBrush)).Color
                    End If
                    x.Font = New XLFont(fontName, CSng(fontSize), bold, italic, False, XLFontScript.None, XLUnderlineStyle.Single, color)
                Else
                    x.Font = New XLFont(fontName, CSng(fontSize), bold, italic)
                End If
            End If

            ' format
            If Not String.IsNullOrEmpty(s.Format) Then
                x.Format = XLStyle.FormatDotNetToXL(s.Format)
            End If

            ' borders
            If s.CellBorderThickness.Left > 0 AndAlso TypeOf s.CellBorderBrushLeft Is SolidColorBrush Then
                x.BorderLeft = GetBorderLineStyle(s.CellBorderThickness.Left)
                x.BorderColorLeft = (CType(s.CellBorderBrushLeft, SolidColorBrush)).Color
            End If
            If s.CellBorderThickness.Top > 0 AndAlso TypeOf s.CellBorderBrushTop Is SolidColorBrush Then
                x.BorderTop = GetBorderLineStyle(s.CellBorderThickness.Top)
                x.BorderColorTop = (CType(s.CellBorderBrushTop, SolidColorBrush)).Color
            End If
            If s.CellBorderThickness.Right > 0 AndAlso TypeOf s.CellBorderBrushRight Is SolidColorBrush Then
                x.BorderRight = GetBorderLineStyle(s.CellBorderThickness.Right)
                x.BorderColorRight = (CType(s.CellBorderBrushRight, SolidColorBrush)).Color
            End If
            If s.CellBorderThickness.Bottom > 0 AndAlso TypeOf s.CellBorderBrushBottom Is SolidColorBrush Then
                x.BorderBottom = GetBorderLineStyle(s.CellBorderThickness.Bottom)
                x.BorderColorBottom = (CType(s.CellBorderBrushBottom, SolidColorBrush)).Color
            End If

            ' save in cache and return
            _excelStyles(s) = x
            Return x
        End Function
        Private Shared Function GetBorderThickness(ByVal ls As XLLineStyleEnum) As Double
            Select Case ls
                Case XLLineStyleEnum.None
                    Return 0
                Case XLLineStyleEnum.Hair
                    Return 0.5
                Case XLLineStyleEnum.Thin, XLLineStyleEnum.ThinDashDotDotted, XLLineStyleEnum.ThinDashDotted, XLLineStyleEnum.Dashed, XLLineStyleEnum.Dotted
                    Return 1
                Case XLLineStyleEnum.Medium, XLLineStyleEnum.MediumDashDotDotted, XLLineStyleEnum.MediumDashDotted, XLLineStyleEnum.MediumDashed, XLLineStyleEnum.SlantedMediumDashDotted
                    Return 2
                Case XLLineStyleEnum.Double, XLLineStyleEnum.Thick
                    Return 3
            End Select
            Return 0
        End Function
        Private Shared Function GetBorderLineStyle(ByVal t As Double) As XLLineStyleEnum
            If t = 0 Then
                Return XLLineStyleEnum.None
            End If
            If t < 1 Then
                Return XLLineStyleEnum.Hair
            End If
            If t < 2 Then
                Return XLLineStyleEnum.Thin
            End If
            If t < 3 Then
                Return XLLineStyleEnum.Medium
            End If
            Return XLLineStyleEnum.Thick
        End Function
        Private Shared Function GetBorderBrush(ByVal color As Color) As Brush
            Return If(IsColorValid(color), New SolidColorBrush(color), Nothing)
        End Function
        Private Shared Function IsColorValid(ByVal color As Color) As Boolean
            Return color.A > 0 ' == 0xff;
        End Function

#End Region
    End Class
End Namespace
