//***************************************************************************************************************************************************
//
// Copyright (C) 2021 Selim Temizer.
//
// This file is part of Course Statistics.
//
// Course Statistics is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License
// as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
//
// Course Statistics is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public License along with Course Statistics. If not, see <http://www.gnu.org/licenses/>.
//
//***************************************************************************************************************************************************




//***************************************************************************************************************************************************
//
// Problem       : Apache POI does not have (any ?) support for programmatically creating excel charts
// Solution idea : Filling in data for dummy charts (already existing) in template excel files
// Influenced by : https://blog.cacoveanu.com/2015/2015.08.27.15.15.charts.apache.poi.html
//
//***************************************************************************************************************************************************




//***************************************************************************************************************************************************

package domain ;

//***************************************************************************************************************************************************




//***************************************************************************************************************************************************

import lib.VerifiedFileChooser                         ;
import java.io.File                                    ;
import java.io.FileOutputStream                        ;
import org.apache.poi.openxml4j.opc.OPCPackage         ;
import org.apache.poi.xssf.usermodel.XSSFSheet         ;
import org.apache.poi.xssf.usermodel.XSSFWorkbook      ;
import org.apache.poi.ss.util.CellUtil                 ;
import org.apache.poi.ss.util.RegionUtil               ;
import org.apache.poi.ss.util.CellRangeAddress         ;
import org.apache.poi.ss.usermodel.Row                 ;
import org.apache.poi.ss.usermodel.Font                ;
import org.apache.poi.ss.usermodel.Name                ;
import org.apache.poi.ss.usermodel.Cell                ;
import org.apache.poi.ss.usermodel.CellStyle           ;
import org.apache.poi.ss.usermodel.BorderStyle         ;
import org.apache.poi.ss.usermodel.IndexedColors       ;
import org.apache.poi.ss.usermodel.FillPatternType     ;
import org.apache.poi.ss.usermodel.VerticalAlignment   ;
import org.apache.poi.ss.usermodel.HorizontalAlignment ;

//***************************************************************************************************************************************************




//***************************************************************************************************************************************************

public class Generator
{
  //=================================================================================================================================================

  private final String              codeAndTitle     ;
  private final GradingSystem       grading          ;
  private final String              year             ;
  private final String              semester         ;
  private final String              coordinator      ;
  private final int                 numStudents      ;
  private final int                 numAssessments   ;
  private final int                 numCLOs          ;
  private final DataSource          dataSource       ;
  private final VerifiedFileChooser excelFileChooser ;

  //=================================================================================================================================================

  public Generator
  (
   String              codeAndTitle     ,
   GradingSystem       grading          ,
   String              year             ,
   String              semester         ,
   String              coordinator      ,
   int                 numStudents      ,
   int                 numAssessments   ,
   int                 numCLOs          ,
   DataSource          dataSource       ,
   VerifiedFileChooser excelFileChooser 
  )
  {
    this.codeAndTitle     = codeAndTitle     ;
    this.grading          = grading          ;
    this.year             = year             ;
    this.semester         = semester         ;
    this.coordinator      = coordinator      ;
    this.numStudents      = numStudents      ;
    this.numAssessments   = numAssessments   ;
    this.numCLOs          = numCLOs          ;
    this.dataSource       = dataSource       ;
    this.excelFileChooser = excelFileChooser ;
  }

  //=================================================================================================================================================

  public void createNamedRange ( XSSFWorkbook workbook , String rangeName , String reference )
  {
    Name namedRange = workbook.createName() ;

    namedRange.setNameName       ( rangeName ) ;
    namedRange.setRefersToFormula( reference ) ;
  }

  //=================================================================================================================================================

  public void modifyNamedRange ( XSSFWorkbook workbook , String rangeName , String reference )
  {
    Name namedRange = workbook.getName( rangeName ) ;

    namedRange.setRefersToFormula( reference ) ;
  }

  //=================================================================================================================================================

  public XSSFWorkbook build () throws Exception
  {
    //-----------------------------------------------------------------------------------------------------------------------------------------------

    XSSFWorkbook workbook      = new XSSFWorkbook ( OPCPackage.open( "CSTemplate.xlsx" ) ) ;
    XSSFSheet    sheetGrades   = workbook.getSheet( "Grades"                             ) ;
    XSSFSheet    sheetStats    = workbook.getSheet( "Statistics"                         ) ;
    CellStyle    styleCentered = workbook.createCellStyle()                                ;
    Font         fontBold      = workbook.createFont     ()                                ;

    styleCentered.setAlignment        ( HorizontalAlignment.CENTER ) ;
    styleCentered.setVerticalAlignment( VerticalAlignment  .CENTER ) ;
    fontBold     .setBold             ( true                       ) ;

    //-----------------------------------------------------------------------------------------------------------------------------------------------
    // Build "Grades" Sheet

    // Create all required cells

    for ( int zRow = 6 ; zRow <= 10 ; zRow++ )
    {
      Row row = sheetGrades.getRow( zRow ) ;
      
      for ( int zCol = 2 ; zCol < 2 + numAssessments + 2 ; zCol++ )
      {
        Cell cell = row.createCell( zCol ) ;    cell.setCellStyle( styleCentered ) ;

        if ( ! ( ( ( zRow <  8 ) && ( zCol == 2 + numAssessments ) ) ||
                 ( ( zRow < 10 ) && ( zCol == 3 + numAssessments ) )  ) )
        {
          CellRangeAddress region = new CellRangeAddress( zRow , zRow , zCol , zCol ) ;

          RegionUtil.setBorderTop   ( BorderStyle.THIN , region , sheetGrades ) ;
          RegionUtil.setBorderBottom( BorderStyle.THIN , region , sheetGrades ) ;
          RegionUtil.setBorderLeft  ( BorderStyle.THIN , region , sheetGrades ) ;
          RegionUtil.setBorderRight ( BorderStyle.THIN , region , sheetGrades ) ;
        }

        if ( zRow == 10 )  { CellUtil.setFont( cell , fontBold ) ; }
      }
    }

    for ( int zRow = 11 ; zRow < 11 + numStudents ; zRow++ )
    {
      Row row = sheetGrades.createRow( zRow ) ;
      
      for ( int zCol = 0 ; zCol < 2 + numAssessments + 2 ; zCol++ )
      {
        Cell             cell   = row.createCell( zCol )                            ;
        CellRangeAddress region = new CellRangeAddress( zRow , zRow , zCol , zCol ) ;

        if ( zCol != 0 )  { cell.setCellStyle( styleCentered ) ; }

        RegionUtil.setBorderTop   ( BorderStyle.THIN , region , sheetGrades ) ;
        RegionUtil.setBorderBottom( BorderStyle.THIN , region , sheetGrades ) ;
        RegionUtil.setBorderLeft  ( BorderStyle.THIN , region , sheetGrades ) ;
        RegionUtil.setBorderRight ( BorderStyle.THIN , region , sheetGrades ) ;

        if ( zCol == 3 + numAssessments )  { CellUtil.setFont( cell , fontBold ) ; }
      }
    }

    // Create named ranges

    createNamedRange( workbook, "AssessmentWeights"   , new SCell(6,2).toRange(0,numAssessments-1).withSheet("Grades").fixAll().toString() ) ;
    createNamedRange( workbook, "AssessmentMaxGrades" , new SCell(7,2).toRange(0,numAssessments-1).withSheet("Grades").fixAll().toString() ) ;
    createNamedRange( workbook, "AssessmentMeanGrades", new SCell(8,2).toRange(0,numAssessments-1).withSheet("Grades").fixAll().toString() ) ;

    for ( int a = 1 ; a <= numAssessments ; a++ )
    {
      String name = String.format( "Assessment%dGrades" , a ) ;

      createNamedRange( workbook, name, new SCell(11,1+a).toRange(numStudents-1,0).withSheet("Grades").fixAll().toString() ) ;
    }

    createNamedRange( workbook, "OverallGrades", new SCell(11,2+numAssessments).toRange(numStudents-1,0).withSheet("Grades").fixAll().toString() ) ;
    createNamedRange( workbook, "LetterGrades" , new SCell(11,3+numAssessments).toRange(numStudents-1,0).withSheet("Grades").fixAll().toString() ) ;

    // Enter course info

    sheetGrades.getRow( 0 ).getCell( 2 ).setCellValue( codeAndTitle          ) ;
    sheetGrades.getRow( 1 ).getCell( 2 ).setCellValue( grading.getName()     ) ;
    sheetGrades.getRow( 2 ).getCell( 2 ).setCellValue( year + " " + semester ) ;
    sheetGrades.getRow( 3 ).getCell( 2 ).setCellValue( coordinator           ) ;
    sheetGrades.getRow( 4 ).getCell( 2 ).setCellValue( numStudents           ) ;

    // Enter assessments info

    String assessmentMeanFormula  = "IFERROR(ROUND(AVERAGEIFS(Assessment%dGrades,LetterGrades,\"<>I\",LetterGrades,\"<>W\"),2),0)"  ;
    String assessmentStDevFormula = "IFERROR(ROUND(STDEV(IF(LetterGrades<>\"I\",IF(LetterGrades<>\"W\",Assessment%dGrades))),2),0)" ;

    for ( int a = 1 ; a <= numAssessments ; a++ )
    {
        sheetGrades.getRow(  6 ).getCell( a + 1 ).setCellValue  ( dataSource.getAssessmentWeight(         a ) ) ;
        sheetGrades.getRow(  7 ).getCell( a + 1 ).setCellValue  ( dataSource.getAssessmentMaxGrade(       a ) ) ;
        sheetGrades.getRow(  8 ).getCell( a + 1 ).setCellFormula( String.format( assessmentMeanFormula  , a ) ) ;
      //sheetGrades.getRow(  9 ).getCell( a + 1 ).setCellFormula( String.format( assessmentStDevFormula , a ) ) ;  // Inserted as array formula below
        sheetGrades.getRow( 10 ).getCell( a + 1 ).setCellValue  ( dataSource.getAssessmentName(           a ) ) ;

      // Array formula
      CellRangeAddress stdevRange = new CellRangeAddress( /* First Row */ 9 , /* Last  Row */ 9 , /* First Col */ a + 1 , /* Last  Col */ a + 1 ) ;

      sheetGrades.setArrayFormula( String.format( assessmentStDevFormula , a ) , stdevRange ) ;
    }

    // Enter total grade headers

    sheetGrades.getRow( 10 ).getCell( 2 + numAssessments ).setCellValue( "OVERALL" ) ;
    sheetGrades.getRow( 10 ).getCell( 3 + numAssessments ).setCellValue( "LETTER"  ) ;

    // Enter overall mean formula

    String meanFormula = "IFERROR(ROUND(AVERAGEIFS(OverallGrades,LetterGrades,\"<>I\",LetterGrades,\"<>W\"),2),0)" ;

    sheetGrades.getRow( 8 ).getCell( 2 + numAssessments ).setCellFormula( meanFormula ) ;

    // Enter overall stdev formula

    String           stdevFormula = "IFERROR(ROUND(STDEV(IF(LetterGrades<>\"I\",IF(LetterGrades<>\"W\",OverallGrades))),2),0)" ;
    CellRangeAddress stdevRange   = new CellRangeAddress( /* First Row */ 9                  ,
                                                          /* Last  Row */ 9                  ,
                                                          /* First Col */ 2 + numAssessments ,
                                                          /* Last  Col */ 2 + numAssessments ) ;

    sheetGrades.setArrayFormula( stdevFormula , stdevRange ) ;

    // Enter overall/letter grade formulas

    for ( int zRow = 11 ; zRow < 11 + numStudents ; zRow++ )
    {
      SRange           sStudentGradesRange = new SCell( zRow , 2 ).toRange( 0 , numAssessments - 1 )    ;
      CellRangeAddress overallGradeRange   = new CellRangeAddress( /* First Row */ zRow               ,
                                                                   /* Last  Row */ zRow               ,
                                                                   /* First Col */ 2 + numAssessments ,
                                                                   /* Last  Col */ 2 + numAssessments ) ;

      String overallGradeFormula = String.format( "IFERROR(ROUND(SUM((%s/AssessmentMaxGrades)*AssessmentWeights),2),0)" , sStudentGradesRange ) ;

      sheetGrades.setArrayFormula( overallGradeFormula , overallGradeRange ) ;

      SCell  sOverallGradeCell  = new SCell( zRow , 2 + numAssessments )                   ;
      Cell   letterGradeCell    = sheetGrades.getRow( zRow ).getCell( 3 + numAssessments ) ;
      String letterGradeFormula = grading.getFormula( sOverallGradeCell.toString()       ) ;

      letterGradeCell .setCellFormula( letterGradeFormula ) ;
    }

    // Highlight cells

    CellStyle assessmentInfoStyle = workbook.createCellStyle() ;
    CellStyle assessmentStatStyle = workbook.createCellStyle() ;
    CellStyle   tableHeadersStyle = workbook.createCellStyle() ;
    CellStyle   overallGradeStyle = workbook.createCellStyle() ;
    CellStyle    letterGradeStyle = workbook.createCellStyle() ;

    assessmentInfoStyle.cloneStyleFrom        ( sheetGrades.getRow(  6 ).getCell( 2 ).getCellStyle()                  ) ;
    assessmentInfoStyle.setFillForegroundColor( IndexedColors  .LIGHT_GREEN.getIndex()                                ) ;
    assessmentInfoStyle.setFillPattern        ( FillPatternType.SOLID_FOREGROUND                                      ) ;

    assessmentStatStyle.cloneStyleFrom        ( sheetGrades.getRow(  8 ).getCell( 2 ).getCellStyle()                  ) ;
    assessmentStatStyle.setFillForegroundColor( IndexedColors  .LEMON_CHIFFON.getIndex()                              ) ;
    assessmentStatStyle.setFillPattern        ( FillPatternType.SOLID_FOREGROUND                                      ) ;

    tableHeadersStyle  .cloneStyleFrom        ( sheetGrades.getRow( 10 ).getCell( 0 ).getCellStyle()                  ) ;
    tableHeadersStyle  .setFillForegroundColor( IndexedColors  .LIGHT_CORNFLOWER_BLUE.getIndex()                      ) ;
    tableHeadersStyle  .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                                      ) ;

    overallGradeStyle  .cloneStyleFrom        ( sheetGrades.getRow( 11 ).getCell( 2 + numAssessments ).getCellStyle() ) ;
    overallGradeStyle  .setFillForegroundColor( IndexedColors  .LIGHT_TURQUOISE.getIndex()                            ) ;
    overallGradeStyle  .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                                      ) ;

    letterGradeStyle   .cloneStyleFrom        ( sheetGrades.getRow( 11 ).getCell( 3 + numAssessments ).getCellStyle() ) ;
    letterGradeStyle   .setFillForegroundColor( IndexedColors  .LEMON_CHIFFON.getIndex()                              ) ;
    letterGradeStyle   .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                                      ) ;

    for ( int zCol = 2 ; zCol < 2 + numAssessments ; zCol++ )
    {
      sheetGrades.getRow( 6 ).getCell( zCol ).setCellStyle( assessmentInfoStyle ) ;
      sheetGrades.getRow( 7 ).getCell( zCol ).setCellStyle( assessmentInfoStyle ) ;

      sheetGrades.getRow( 8 ).getCell( zCol ).setCellStyle( assessmentStatStyle ) ;
      sheetGrades.getRow( 9 ).getCell( zCol ).setCellStyle( assessmentStatStyle ) ;
    }

    for ( int zCol = 0 ; zCol < 4 + numAssessments ; zCol++ )
    {
      sheetGrades.getRow( 10 ).getCell( zCol ).setCellStyle( tableHeadersStyle ) ;
    }

    for ( int zRow = 11 ; zRow < 11 + numStudents ; zRow++ )
    {
      sheetGrades.getRow( zRow ).getCell( 2 + numAssessments ).setCellStyle( overallGradeStyle ) ;
      sheetGrades.getRow( zRow ).getCell( 3 + numAssessments ).setCellStyle(  letterGradeStyle ) ;
    }

    sheetGrades.getRow( 8 ).getCell( 2 + numAssessments ).setCellStyle( overallGradeStyle ) ;
    sheetGrades.getRow( 9 ).getCell( 2 + numAssessments ).setCellStyle( overallGradeStyle ) ;

    CellUtil.setFont( sheetGrades.getRow( 8 ).getCell( 2 + numAssessments ) , fontBold ) ;
    CellUtil.setFont( sheetGrades.getRow( 9 ).getCell( 2 + numAssessments ) , fontBold ) ;

    // Autosize columns (except the first column)

    for ( int zCol = 1 ; zCol < 2 + numAssessments + 2 ; zCol++ )  { sheetGrades.autoSizeColumn( zCol ) ; }

    //-----------------------------------------------------------------------------------------------------------------------------------------------
    // Build "Statistics" Sheet

    String [][] table        = grading.getTable() ;
    int         numTableRows = table.length       ;

    // Create all required cells

    for ( int zRow = 29 ; zRow < 29 + numTableRows ; zRow++ )
    {
      Row row = sheetStats.createRow( zRow ) ;

      for ( int zCol = 1 ; zCol < 5 ; zCol++ )
      {
        Cell cell = row.createCell( zCol ) ;

        if ( zCol >= 2 )  { cell.setCellStyle( styleCentered ) ; }
      }
    }  // Borders will be added later

    for ( int zCol = 10 ; zCol < 10 + numCLOs ; zCol++ )
    {
      sheetStats.getRow( 27 ).createCell( zCol ).setCellStyle( styleCentered ) ;

      CellRangeAddress region = new CellRangeAddress( 27 , 27 , zCol , zCol ) ;

      RegionUtil.setBorderTop   ( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderBottom( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderLeft  ( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderRight ( BorderStyle.THIN , region , sheetStats ) ;
    }

    for ( int zCol = 10 ; zCol < 10 + numCLOs + 1 ; zCol++ )
    {
      sheetStats.getRow( 28 ).createCell( zCol ).setCellStyle( styleCentered ) ;

      CellRangeAddress region = new CellRangeAddress( 28 , 28 , zCol , zCol ) ;

      RegionUtil.setBorderTop   ( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderBottom( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderLeft  ( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderRight ( BorderStyle.THIN , region , sheetStats ) ;
    }

    for ( int zRow = 29 ; zRow < 29 + numAssessments ; zRow++ )
    {
      Row row = sheetStats.getRow( zRow ) ;    if ( row == null )  { row = sheetStats.createRow( zRow ) ; }

      for ( int zCol = 9 ; zCol < 9 + 1 + numCLOs + 1 ; zCol++ )
      {
        Cell cell = row.createCell( zCol ) ;

        if ( zCol >= 10 )  { cell.setCellStyle( styleCentered ) ; }

        CellRangeAddress region = new CellRangeAddress( zRow , zRow , zCol , zCol ) ;

        RegionUtil.setBorderTop   ( BorderStyle.THIN , region , sheetStats ) ;
        RegionUtil.setBorderBottom( BorderStyle.THIN , region , sheetStats ) ;
        RegionUtil.setBorderLeft  ( BorderStyle.THIN , region , sheetStats ) ;
        RegionUtil.setBorderRight ( BorderStyle.THIN , region , sheetStats ) ;
      }
    }

    // Modify/Create named ranges

    modifyNamedRange( workbook, "GradeLabels"     , new SCell(29, 2).toRange(numTableRows-1,0).withSheet("Statistics").fixAll().toString() ) ;
    modifyNamedRange( workbook, "GradeFrequencies", new SCell(29, 3).toRange(numTableRows-1,0).withSheet("Statistics").fixAll().toString() ) ;

    modifyNamedRange( workbook, "CLOAchievements" , new SCell(27,10).toRange(0,numCLOs-1)     .withSheet("Statistics").fixAll().toString() ) ;
    modifyNamedRange( workbook, "CLOLabels"       , new SCell(28,10).toRange(0,numCLOs-1)     .withSheet("Statistics").fixAll().toString() ) ;

    for ( int c = 1 ; c <= numCLOs ; c++ )
    {
      String name = String.format( "CLO%dAssessmentWeights" , c ) ;

      createNamedRange( workbook, name, new SCell(29,9+c).toRange(numAssessments-1,0).withSheet("Statistics").fixAll().toString() ) ;
    }

    // Enter course info

    sheetStats.getRow( 0 ).getCell( 2 ).setCellValue( codeAndTitle          ) ;
    sheetStats.getRow( 1 ).getCell( 2 ).setCellValue( grading.getName()     ) ;
    sheetStats.getRow( 2 ).getCell( 2 ).setCellValue( year + " " + semester ) ;
    sheetStats.getRow( 3 ).getCell( 2 ).setCellValue( coordinator           ) ;
    sheetStats.getRow( 4 ).getCell( 2 ).setCellValue( numStudents           ) ;

    // Fill grade distribution table

    for ( int zRow = 29 ; zRow < 29 + numTableRows ; zRow++ )
    {
      SCell  gradeCell         = new SCell( zRow , 2 )                                                          ;
      SCell  countCell         = new SCell( zRow , 3 )                                                          ;
      String countFormula      = String.format( "COUNTIF(LetterGrades,%s)"                        , gradeCell ) ;
      String percentageFormula = String.format( "ROUND(100*%s/SUM(GradeFrequencies),2) & \" %%\"" , countCell ) ;

      sheetStats.getRow( zRow ).getCell( 1 ).setCellValue  ( table[zRow-29][0] ) ;
      sheetStats.getRow( zRow ).getCell( 2 ).setCellValue  ( table[zRow-29][1] ) ;
      sheetStats.getRow( zRow ).getCell( 3 ).setCellFormula( countFormula      ) ;
      sheetStats.getRow( zRow ).getCell( 4 ).setCellFormula( percentageFormula ) ;
    }

    // Fill achievement formulas

    for ( int c = 1 ; c <= numCLOs ; c++ )
    {
      String formula = String.format(
        "IFERROR("                                                                                                            +
        "SUM((100*AssessmentMeanGrades/AssessmentMaxGrades)*(AssessmentWeights/100)*(TRANSPOSE(CLO%dAssessmentWeights)/100))" +
        "/"                                                                                                                   +
        "SUM((AssessmentWeights/100)*(TRANSPOSE(CLO%dAssessmentWeights)/100))"                                                +
        ",0)"                                                                                                                 ,
        c , c                                                                                                                 ) ;

      CellRangeAddress formulaRange = new CellRangeAddress( /* First Row */ 27    ,
                                                            /* Last  Row */ 27    ,
                                                            /* First Col */ 9 + c ,
                                                            /* Last  Col */ 9 + c ) ;

      sheetStats.setArrayFormula( formula , formulaRange ) ;
    }

    // Fill CLO headers

    for ( int c = 1 ; c <= numCLOs + 1 ; c++ )
    {
      Cell   cell = sheetStats.getRow( 28 ).getCell( 9 + c )                 ;
      String text = ( c <= numCLOs ) ? String.format( "CLO-%d" , c ) : "SUM" ;

      cell.setCellValue( text ) ;

      CellUtil.setFont( cell , fontBold ) ;
    }

    // Fill body of CLO table

    for ( int a = 1 ; a <= numAssessments ; a++ )
    {
                        sheetStats.getRow(28+a).getCell(9) . setCellValue( dataSource.getAssessmentName( a ) ) ;
      CellUtil.setFont( sheetStats.getRow(28+a).getCell(9) , fontBold                                        ) ;

      for ( int c = 1 ; c <= numCLOs ; c++ )
      {
        sheetStats.getRow(28+a).getCell(9+c).setCellValue( dataSource.getAssessmentCLO( a , c ) ) ;
      }

      SRange assessmentWeightsRange = new SCell(28+a,10).toRange(0,numCLOs-1)             ;
      String sumFormula             = String.format( "SUM(%s)" , assessmentWeightsRange ) ;

      sheetStats.getRow(28+a).getCell(10+numCLOs).setCellFormula( sumFormula ) ;
    }

    // Highlight cells

    CellStyle studentCountStyle = workbook.createCellStyle() ;
    CellStyle   percentageStyle = workbook.createCellStyle() ;
    CellStyle  achievementStyle = workbook.createCellStyle() ;
    CellStyle   cloHeadersStyle = workbook.createCellStyle() ;
    CellStyle       weightStyle = workbook.createCellStyle() ;
    CellStyle          sumStyle = workbook.createCellStyle() ;
    CellStyle   rowHeadersStyle = workbook.createCellStyle() ;

    studentCountStyle.cloneStyleFrom        ( sheetStats.getRow( 29 ).getCell(  3 ).getCellStyle()           ) ;
    studentCountStyle.setFillForegroundColor( IndexedColors  .LIGHT_TURQUOISE.getIndex()                     ) ;
    studentCountStyle.setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    percentageStyle  .cloneStyleFrom        ( sheetStats.getRow( 29 ).getCell(  4 ).getCellStyle()           ) ;
    percentageStyle  .setFillForegroundColor( IndexedColors  .LEMON_CHIFFON.getIndex()                       ) ;
    percentageStyle  .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    achievementStyle .cloneStyleFrom        ( sheetStats.getRow( 27 ).getCell( 10 ).getCellStyle()           ) ;
    achievementStyle .setFillForegroundColor( IndexedColors  .LEMON_CHIFFON.getIndex()                       ) ;
    achievementStyle .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    cloHeadersStyle  .cloneStyleFrom        ( sheetStats.getRow( 28 ).getCell( 10 ).getCellStyle()           ) ;
    cloHeadersStyle  .setFillForegroundColor( IndexedColors  .LIGHT_CORNFLOWER_BLUE.getIndex()               ) ;
    cloHeadersStyle  .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    weightStyle      .cloneStyleFrom        ( sheetStats.getRow( 29 ).getCell( 10 ).getCellStyle()           ) ;
    weightStyle      .setFillForegroundColor( IndexedColors  .LIGHT_GREEN.getIndex()                         ) ;
    weightStyle      .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    sumStyle         .cloneStyleFrom        ( sheetStats.getRow( 29 ).getCell( 10 + numCLOs ).getCellStyle() ) ;
    sumStyle         .setFillForegroundColor( IndexedColors  .LIGHT_TURQUOISE.getIndex()                     ) ;
    sumStyle         .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    rowHeadersStyle  .cloneStyleFrom        ( sheetStats.getRow( 29 ).getCell(  9 ).getCellStyle()           ) ;
    rowHeadersStyle  .setFillForegroundColor( IndexedColors  .LIGHT_CORNFLOWER_BLUE.getIndex()               ) ;
    rowHeadersStyle  .setFillPattern        ( FillPatternType.SOLID_FOREGROUND                               ) ;

    for ( int zRow = 29 ; zRow < 29 + numTableRows ; zRow++ )
    {
      sheetStats.getRow( zRow ).getCell( 3 ).setCellStyle( studentCountStyle ) ;
      sheetStats.getRow( zRow ).getCell( 4 ).setCellStyle( percentageStyle   ) ;
    }

    for ( int zCol = 10 ; zCol < 10 + numCLOs ; zCol++ )
    {
      sheetStats.getRow( 27 ).getCell( zCol ).setCellStyle( achievementStyle ) ;
      sheetStats.getRow( 28 ).getCell( zCol ).setCellStyle(  cloHeadersStyle ) ;
    }

    sheetStats.getRow( 28 ).getCell( 10 + numCLOs ).setCellStyle( cloHeadersStyle ) ;

    for ( int zRow = 29 ; zRow < 29 + numAssessments ; zRow++ )
    {
      for ( int zCol = 10 ; zCol < 10 + numCLOs ; zCol++ )
      {
        sheetStats.getRow( zRow ).getCell( zCol ).setCellStyle( weightStyle ) ;
      }
    }

    for ( int zRow = 29 ; zRow < 29 + numAssessments ; zRow++ )
    {
      sheetStats.getRow( zRow ).getCell(  9           ).setCellStyle( rowHeadersStyle ) ;
      sheetStats.getRow( zRow ).getCell( 10 + numCLOs ).setCellStyle( sumStyle        ) ;
    }

    // Add borders that were skipped before (for not interfering with cell styles)
    {
      CellRangeAddress region = new CellRangeAddress( 29 , 29 + numTableRows - 1 , 1 , 4 ) ;

      RegionUtil.setBorderTop   ( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderBottom( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderLeft  ( BorderStyle.THIN , region , sheetStats ) ;
      RegionUtil.setBorderRight ( BorderStyle.THIN , region , sheetStats ) ;
    }

    // Autosize columns

    for ( int zCol = 0 ; zCol < 10 + numCLOs + 1 ; zCol++ )  { sheetStats.autoSizeColumn( zCol ) ; }

    //-----------------------------------------------------------------------------------------------------------------------------------------------

    return workbook ;

    //-----------------------------------------------------------------------------------------------------------------------------------------------
  }

  //=================================================================================================================================================

  public void save ( XSSFWorkbook workbook ) throws Exception
  {
    File file = excelFileChooser.getSaveTarget( "Save File" , "Confirm Overwrite" ) ;

    if ( file == null )  { return ; }

    FileOutputStream fos = new FileOutputStream( file ) ;

    workbook.write( fos ) ;
    fos     .close(     ) ;
  }

  //=================================================================================================================================================
}

//***************************************************************************************************************************************************