School Report - Aspose.Cells Demos

Imports Aspose.Cells.Charts
Imports Aspose.Cells.Drawing


Public Class SchoolReport
    Inherits System.Web.UI.Page
    Protected WithEvents ListBox1 As System.Web.UI.WebControls.ListBox
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not IsPostBack Then
            CreateList()
        Else
            If Me.Request.Params.Count > 0 Then
                Dim param As String = Me.Request.Params(0)
                Dim workbook As Workbook = New Workbook()
                CreateStaticReport(workbook)
                CreateDynamicReport(workbook)
                workbook.Save("ReportCard.xls", FileFormatType.Excel97To2003, SaveType.OpenInExcel, Me.Response)
                ' End response to avoid unneeded HTML after XLS
                Response.End()
            End If

        End If
    End Sub

    ' Creates student list.
    Private Sub CreateList()
        'Creates student list from data in an Workbook file. 
        'In a real world application, all kind of data sources can be used.
        Dim path As String = MapPath(".")
        '   path = path.Substring(0, path.LastIndexOf("\"))
        Dim dataFile As String = path + "\Designer\SchoolData.xls"

        Dim workbook As Workbook = New Workbook()
        workbook.Open(dataFile)

        Dim cells As Cells = workbook.Worksheets(0).Cells
        Dim nameList(,) As Object = cells.ExportArray(1, 0, cells.MaxDataRow, 2)

        Dim i As Integer
        For i = 0 To nameList.Length / 2 - 1
            Me.ListBox1.Items.Add(nameList(i, 0).ToString() + " " + nameList(i, 1).ToString())
        Next
        Me.ListBox1.SelectedIndex = 0
    End Sub

    Private Sub CreateStaticReport(ByVal workbook As Workbook)
        'Sets default font
        Dim style As Style = workbook.DefaultStyle
        style.Font.Name = "Tahoma"
        workbook.DefaultStyle = style

        Dim sheet As Worksheet = workbook.Worksheets(0)
        sheet.Name = "Report Card"
        sheet.IsGridlinesVisible = False

        AddImageAndChart(workbook)

        Dim index As Integer = workbook.Worksheets.Add()
        sheet = workbook.Worksheets(index)
        sheet.Name = "Grade Table"
        sheet.IsGridlinesVisible = False


        SetRowColumn(workbook)
        CreateOutline(workbook)
        CreateCellsFormatting(workbook)
        CreateStaticData(workbook)



    End Sub

    Private Sub SetRowColumn(ByVal workbook As Workbook)
        Dim cells As Cells = workbook.Worksheets(0).Cells
        Dim i As Integer
        For i = 0 To 21
            cells.SetRowHeight(i, 13.5)
        Next

        cells.SetRowHeight(22, 6)
        cells.SetRowHeight(23, 13.5)

        For i = 24 To 28
            cells.SetRowHeight(i, 22.5)
        Next

        cells.SetRowHeight(29, 13.5)
        cells.SetRowHeight(30, 6)

        For i = 31 To 33
            cells.SetRowHeight(i, 13.5)
        Next


        cells.SetColumnWidth(0, 1.86)
        cells.SetColumnWidth(1, 1.86)
        cells.SetColumnWidth(2, 19)
        cells.SetColumnWidth(3, 15.14)

        Dim column As Byte
        For column = 4 To 9
            cells.SetColumnWidth(column, 5)
        Next

        cells.SetColumnWidth(11, 15.43)
        cells.SetColumnWidth(12, 2)

        cells = workbook.Worksheets(2).Cells
        cells.SetRowHeight(1, 15.75)
        cells.SetColumnWidth(0, 2)
        cells.SetColumnWidth(1, 11.86)
        For i = 2 To 14
            cells.SetColumnWidth(i, 5.14)
        Next
    End Sub

    Private Sub CreateOutline(ByVal workbook As Workbook)
        Dim cells As Cells = workbook.Worksheets(0).Cells

        Dim range As Range = cells.CreateRange("B2", "M22")
        range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))

        range = cells.CreateRange("B24", "M30")
        range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))

        range = cells.CreateRange("B32", "M34")
        range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
        range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
    End Sub

    Private Sub CreateCellsFormatting(ByVal workbook As Workbook)
        'Creates cell formatting on the first worksheet

        Dim style As Style = workbook.Styles(workbook.Styles.Add())
        'Sets font
        style.Font.IsBold = True
        style.Font.Size = 10

        Dim cells As Cells = workbook.Worksheets(0).Cells
        cells("K4").Style = style
        cells("E11").Style = style
        cells("E12").Style = style

        style = workbook.Styles(workbook.Styles.Add())
        style.Font.IsBold = True
        style.HorizontalAlignment = TextAlignmentType.Center

        'Sets borders
        style.Borders(BorderType.TopBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.BottomBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.LeftBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.RightBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin

        Dim startColumn As Integer = CellsHelper.ColumnNameToIndex("C")
        Dim endColumn As Integer = CellsHelper.ColumnNameToIndex("L")
        Dim i As Integer
        For i = startColumn To endColumn
            cells(14, i).Style = style
        Next


        style = workbook.Styles(workbook.Styles.Add())
        style.Borders(BorderType.TopBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.BottomBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.LeftBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.RightBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin

        'Sets foreground color
        style.ForegroundColor = Color.FromArgb(&HFF, &HFF, &HCC)
        style.Pattern = BackgroundType.Solid

        For i = startColumn To endColumn
            cells(15, i).Style = style
            cells(17, i).Style = style
            cells(19, i).Style = style
        Next


        style = workbook.Styles(workbook.Styles.Add())
        style.Borders(BorderType.TopBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.BottomBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.LeftBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.RightBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin

        'Sets foreground color
        style.ForegroundColor = Color.FromArgb(&HCC, &HFF, &HCC)
        style.Pattern = BackgroundType.Solid

        For i = startColumn To endColumn
            cells(16, i).Style = style
            cells(18, i).Style = style
            cells(20, i).Style = style
        Next

        style = workbook.Styles(workbook.Styles.Add())
        style.Borders(BorderType.BottomBorder).Color = Color.FromArgb(0, 0, 128)
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin

        For i = startColumn To endColumn
            cells(24, i).Style = style
            cells(25, i).Style = style
            cells(26, i).Style = style
            cells(27, i).Style = style
            cells(28, i).Style = style
        Next

        For i = 4 To 9
            cells(32, i).Style = style
        Next
        cells(32, 11).Style = style

        For i = 15 To 20
            cells(i, 10).Style.Custom = "0"
        Next

        cells = workbook.Worksheets(2).Cells

        style = workbook.Styles(workbook.Styles.Add())
        style.ForegroundColor = Color.FromArgb(128, 0, 0)
        style.Pattern = BackgroundType.Solid
        style.Font.Color = Color.FromArgb(255, 255, 153)
        style.Font.Size = 12
        style.Font.IsBold = True

        For i = 1 To 14
            cells(1, i).Style = style
        Next

        style = workbook.Styles(workbook.Styles.Add())
        style.Borders(BorderType.TopBorder).Color = Color.Black
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.BottomBorder).Color = Color.Black
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.LeftBorder).Color = Color.Black
        style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.RightBorder).Color = Color.Black
        style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
        style.HorizontalAlignment = TextAlignmentType.Center
        style.Font.IsBold = True
        cells("B3").Style = style
        cells("B4").Style = style

        style = workbook.Styles(workbook.Styles.Add())
        style.Borders(BorderType.TopBorder).Color = Color.Black
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.BottomBorder).Color = Color.Black
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.LeftBorder).Color = Color.Black
        style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
        style.Borders(BorderType.RightBorder).Color = Color.Black
        style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
        style.HorizontalAlignment = TextAlignmentType.Center
        For i = 2 To 3
            Dim j As Integer
            For j = 2 To 14
                cells(i, j).Style = style
            Next
        Next
    End Sub

    Private Sub CreateStaticData(ByVal workbook As Workbook)
        Dim cells As Cells = workbook.Worksheets(0).Cells
        cells("E4").PutValue("ASPOSE School District")
        cells("E4").Style.Font.IsBold = True
        cells("E4").Style.Font.Size = 12

        cells("K4").PutValue("Progress Report")


        cells("E5").PutValue("Suite 180, 9 Crofts Avenue")

        cells("K5").PutValue("Date:")
        cells("K5").Style.Font.IsBold = True

        cells("L5").Formula = "=Now()"
        cells("L5").Style.Custom = "[$-409]mmmm d, yyyy;"

        cells("E6").PutValue("Hurstville, NSW, 2220")
        cells("E8").PutValue("Phone: 888.277.6734")
        cells("E9").PutValue("Fax: 866.810.9465")

        cells("E11").PutValue("Student Name")
        cells("E12").PutValue("Student SSN")

        cells("C15").PutValue("Class Name")
        cells("D15").PutValue("Teacher")
        cells("E15").PutValue("1st")
        cells("F15").PutValue("2nd")
        cells("G15").PutValue("3rd")
        cells("H15").PutValue("4th")
        cells("I15").PutValue("5th")
        cells("J15").PutValue("6th")
        cells("K15").PutValue("Final")
        cells("L15").PutValue("Letter Grade")

        cells("C16").PutValue("English")
        cells("C17").PutValue("Math")
        cells("C18").PutValue("Social Studies")
        cells("C19").PutValue("Science")
        cells("C20").PutValue("Art")
        cells("C21").PutValue("Physical Education")

        cells("C24").PutValue("Note")
        cells("C24").Style.Font.IsBold = True

        cells("D33").PutValue("Parent Signature:")
        cells("D33").Style.Font.IsBold = True

        cells("K33").PutValue("Date")
        cells("K33").Style.Font.IsBold = True

        cells = workbook.Worksheets(2).Cells

        cells("B2").PutValue("Grade Table")

        cells("B3").PutValue("Average")
        cells("C3").PutValue(0)
        cells("D3").PutValue(60)
        cells("E3").PutValue(63)
        cells("F3").PutValue(67)
        cells("G3").PutValue(70)
        cells("H3").PutValue(73)
        cells("I3").PutValue(77)
        cells("J3").PutValue(80)
        cells("K3").PutValue(83)
        cells("L3").PutValue(87)
        cells("M3").PutValue(90)
        cells("N3").PutValue(93)
        cells("O3").PutValue(97)

        cells("B4").PutValue("Letter Grade")
        cells("C4").PutValue("F")
        cells("D4").PutValue("D-")
        cells("E4").PutValue("D")
        cells("F4").PutValue("D+")
        cells("G4").PutValue("C-")
        cells("H4").PutValue("C")
        cells("I4").PutValue("C+")
        cells("J4").PutValue("B-")
        cells("K4").PutValue("B")
        cells("L4").PutValue("B+")
        cells("M4").PutValue("A-")
        cells("N4").PutValue("A")
        cells("O4").PutValue("A+")

    End Sub

    Private Sub AddImageAndChart(ByVal workbook As Workbook)
        Dim path As String = MapPath(".")
        ' path = path.Substring(0, path.LastIndexOf("\"))
        Dim imageFile As String = path + "\Image\School.jpg"

        Dim index As Integer = workbook.Worksheets(0).Pictures.Add(1, 1, imageFile)
        Dim pic As Picture = workbook.Worksheets(0).Pictures(index)
        pic.Left = 2
        pic.Top = 2


        index = workbook.Worksheets.Add(SheetType.Chart)
        Dim sheet As Worksheet = workbook.Worksheets(index)
        sheet.Name = "Grade Chart"
        sheet.Zoom = 90

        Dim chart As Chart = sheet.Charts(sheet.Charts.Add(ChartType.Bar, 0, 0, 0, 0))
        chart.NSeries.Add("'Report Card'!E16:H21", False)
        Dim i As Integer
        For i = 0 To chart.NSeries.Count - 1 Step i + 1
            chart.NSeries(i).Name = "='Report Card'!C" + (16 + i).ToString()
        Next

        chart.Legend.Position = LegendPositionType.Bottom
    End Sub

    Private Sub CreateDynamicReport(ByVal workbook As Workbook)
        Dim path As String = MapPath(".")
        ' path = path.Substring(0, path.LastIndexOf("\"))
        Dim dataFile As String = path + "\Designer\SchoolData.xls"


        Dim name As String = Me.ListBox1.SelectedItem.Text
        Dim nameArray() As String = name.Split(" "c)

        Dim cells As Cells = workbook.Worksheets(0).Cells
        cells("H11").PutValue(name)

        Dim dataWorkbook As Workbook = New Workbook()
        dataWorkbook.Open(dataFile)

        'Gets teachers' name
        Dim teachers() As String = New String(dataWorkbook.Worksheets.Count - 1) {}
        Dim i As Integer
        For i = 0 To teachers.Length - 1
            teachers(i) = dataWorkbook.Worksheets(i).Cells("L1").StringValue
        Next

        'Puts teachers' name into output workbook

        cells.ImportArray(teachers, 15, 3, True)


        'Gets students data

        Dim cell As Cell = Nothing
        Dim dataSheet As Worksheet = dataWorkbook.Worksheets(dataWorkbook.Worksheets.Count - 1)

        While True
            cell = dataSheet.Cells.FindString(nameArray(0), cell)
            If Not cell Is Nothing Then
                If dataSheet.Cells(cell.Row, cell.Column + 1).StringValue = nameArray(1) Then
                    cells("H12").PutValue(dataSheet.Cells(cell.Row, cell.Column + 2).Value)
                    Exit While
                End If
            Else
                Exit While
            End If
        End While

        For i = 0 To dataWorkbook.Worksheets.Count - 2
            Dim studentData As DataTable = dataWorkbook.Worksheets(i).Cells.ExportDataTable(1, 0, cells.MaxDataRow, 8)
            Dim row As DataRow
            For Each row In studentData.Rows
                If row(0).ToString() = nameArray(0) And row(1).ToString() = nameArray(1) Then
                    Dim j As Integer
                    For j = 2 To row.ItemArray.Length - 1
                        cells(15 + i, j + 2).PutValue(row(j))
                    Next
                End If
            Next
        Next

        For i = 15 To 20
            cells(i, 10).Formula = "=AVERAGE(E" + (i + 1).ToString() + ":J" + (i + 1).ToString() + ")"
            cells(i, 11).Formula = "=IF(K" + (i + 1).ToString() + "<>"" "",HLOOKUP(K" + (i + 1).ToString() + ",'Grade Table'!$C$3:$O$4,2),"""")"
        Next

        workbook.CalculateFormula()

        Dim courseIndex As Integer = -1
        Dim minScore As Double = -1
        For i = 15 To 20
            Dim score As Double = cells(i, 10).DoubleValue
            If score < 80 Then
                If minScore < 0 Then
                    minScore = score
                    courseIndex = i
                ElseIf score < minScore Then
                    minScore = score
                    courseIndex = i
                End If
            End If
        Next

        If courseIndex <> -1 Then
            Dim course As String = cells(courseIndex, 2).StringValue
            Dim note As String = "{0} seems to be having difficulties with {1} projects.  We offer after school tutoring sessions"
            note = String.Format(note, nameArray(0), course)

            cells("C25").PutValue(note)
            cells("C26").PutValue("which may be helpful.")
        End If


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Response.Redirect("school-report.aspx?Data=abc")
    End Sub
End Class
Products

Services

Purchase

Support

Corporate

Ventures
