Java Code Examples for org.apache.poi.hssf.usermodel.HSSFWorkbook#getSheetAt()

@Test
public void testRunReportWithJpegXls() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("SimpleWithJpeg.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Simple Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( false, sheet.isDisplayFormulas() );
		assertEquals( true, sheet.isDisplayGridlines() );
		assertEquals( true, sheet.isDisplayRowColHeadings() );
		assertEquals( true, sheet.isDisplayZeros() );
		performSimpleWithJpegTests(sheet);
		
		// Unfortunately it's not currently possible/easy to check the dimensions of images using POI
		// So the XL file has to be opened manually for verification
	} finally {
		inputStream.close();
	}
}
 

@Test
public void testHyperlinksXls() throws BirtException, IOException {

	debug = false;
	InputStream inputStream = runAndRenderReport("Hyperlinks.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );

		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 2002, this.firstNullRow(sheet));

		for(int i = 1; i < 2000; ++i ) {
			assertEquals( "http://www.spudsoft.co.uk/?p=" + i,              sheet.getRow(i).getCell(0).getHyperlink().getAddress());

			assertEquals( "_BK" + (i + 1000), sheet.getRow(i).getCell(1).getHyperlink().getAddress());
		}
	
	} finally {
		inputStream.close();
	}
}
 

@Test
public void testRunReportWithJpegXlsGridlinesReport() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("SimpleWithJpegHideGridlines.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Simple Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( false, sheet.isDisplayFormulas() );
		assertEquals( false, sheet.isDisplayGridlines() );
		assertEquals( true, sheet.isDisplayRowColHeadings() );
		assertEquals( true, sheet.isDisplayZeros() );
		performSimpleWithJpegTests(sheet);
	} finally {
		inputStream.close();
	}
}
 

@Test
public void testFastExport() throws ResourceException, ReportProcessingException, IOException {
  // This establishes a baseline for the second test using the slow export.

  final MasterReport report = DebugReportRunner.parseLocalReport( "Prd-5391.prpt", Prd5391IT.class );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  FastExcelReportUtil.processXls( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );

  // assert that we are in the correct export type ..
  final HSSFCellStyle cellStyle = cell0.getCellStyle();
  final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
  final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
  Assert.assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  Assert.assertEquals( "FFFF:8080:8080", fillForegroundColorColor.getHexString() );

  HSSFFont font = cellStyle.getFont( wb );
  Assert.assertEquals( "Times New Roman", font.getFontName() );
}
 

private HSSFSheet getSheet(HSSFWorkbook workbook) {
	HSSFSheet sheet;
	
	int numberOfSheets = workbook.getNumberOfSheets();
if ((xslSheetNumber != null) && (!xslSheetNumber.isEmpty())){
	
	int sheetNumber = Integer.parseInt(xslSheetNumber)-1;
	if (sheetNumber > numberOfSheets){
		logger.error("Wrong sheet number, using first sheet as default");
		//if not specified take first sheet
		sheet = workbook.getSheetAt(0);
	}
	sheet = workbook.getSheetAt(sheetNumber);

} else {
	//if not specified take first sheet
	sheet = workbook.getSheetAt(0);

}

return sheet;
}
 

public Map<String, BoundaryPoint> parse(InputStream is) throws IOException {
    Map<String, BoundaryPoint> boundaryPoints = new HashMap<>();
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();
    rowIterator.next();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell boundaryPointNameCell = row.getCell(13);
        Cell borderFromCell = row.getCell(14);
        Cell borderToCell = row.getCell(15);
        String boundaryPointName = boundaryPointNameCell.getStringCellValue();
        if (boundaryPointName.equals("-")) {
            continue;
        }
        Country borderFrom = toCountry(borderFromCell.getStringCellValue());
        Country borderTo = toCountry(borderToCell.getStringCellValue());
        boundaryPoints.put(boundaryPointName, new BoundaryPoint(boundaryPointName, borderFrom, borderTo));
    }
    return boundaryPoints;
}
 

@Test
public void testRunReportWithJpegXlsZerosReport() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("SimpleWithJpegHideZeros.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Simple Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( false, sheet.isDisplayFormulas() );
		assertEquals( true, sheet.isDisplayGridlines() );
		assertEquals( true, sheet.isDisplayRowColHeadings() );
		assertEquals( false, sheet.isDisplayZeros() );
		performSimpleWithJpegTests(sheet);
	} finally {
		inputStream.close();
	}
}
 

public static void setBlankRows(int rows,int columns,HSSFWorkbook workbook){
	//得到第一页
	Sheet sheet = workbook.getSheetAt(0);
	//样式
	CellStyle cellStyle = getOneStyle(workbook);
	for (int i = 1; i <=rows; i++) {
		Row row = sheet.createRow(i);
		for (int j = 0; j < columns; j++) {
			 row.createCell(j).setCellStyle(cellStyle);
		}
	}
}
 

public static void setBlankRows(int rows,int columns,HSSFWorkbook workbook){
	//得到第一页
	Sheet sheet = workbook.getSheetAt(0);
	//样式
	CellStyle cellStyle = getOneStyle(workbook);
	for (int i = 1; i <=rows; i++) {
		Row row = sheet.createRow(i);
		for (int j = 0; j < columns; j++) {
			 row.createCell(j).setCellStyle(cellStyle);
		}
	}
}
 

public ExcelFileParser(String fileName, boolean isXlsx) throws Exception {
	if (isXlsx) {
		workBookXlsx = new XSSFWorkbook(new FileInputStream(fileName));
		workBookSheetXlsx = workBookXlsx.getSheetAt(0);
	} else {
		workBookXls = new HSSFWorkbook(new FileInputStream(fileName));
		workBookSheetXls = workBookXls.getSheetAt(0);
	}
}
 

/**
 * 读取 Excel文件内容
 * 
 * @param excel_name
 * @return
 * @throws Exception
 */
public static List<List<Object>> readExcelByList(String excel_name)
		throws Exception {
	// 结果集
	List<List<Object>> list = new ArrayList<List<Object>>();

	HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
			excel_name));

	// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
	HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

	// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
	for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
		HSSFRow hssfrow = hssfsheet.getRow(j);
		if (hssfrow != null) {
			int col = hssfrow.getPhysicalNumberOfCells();
			// 单行数据
			List<Object> arrayString = new ArrayList<Object>();
			for (int i = 0; i < col; i++) {
				HSSFCell cell = hssfrow.getCell(i);
				if (cell == null) {
					arrayString.add("");
				} else if (cell.getCellType() == 0) {
					arrayString.add(new Double(cell.getNumericCellValue())
							.toString());
				} else {// 如果EXCEL表格中的数据类型为字符串型
					arrayString.add(cell.getStringCellValue().trim());
				}
			}
			list.add(arrayString);
		}
	}
	return list;
}
 

@Override
public void processWorkbook(HSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

/**
 * 从Excel的工作簿中创建XlsDataSet.
 */
@Deprecated
public XlsDataSet(HSSFWorkbook workbook) throws IOException, DataSetException {
    _tables = super.createTableNameMap();

    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        ITable table = new XlsTable(workbook, workbook.getSheetName(i),
                workbook.getSheetAt(i));
        _tables.add(table.getTableMetaData().getTableName(), table);
    }
}
 

private void compareFiles(String etalonFile, String resultFile) throws IOException {
    HSSFWorkbook result = new HSSFWorkbook(FileUtils.openInputStream(new File(etalonFile)));
    HSSFWorkbook etalon = new HSSFWorkbook(FileUtils.openInputStream(new File(resultFile)));

    HSSFSheet resultSheet = result.getSheetAt(0);
    HSSFSheet etalonSheet = etalon.getSheetAt(0);

    for (int row = 0; row < 10; row++) {
        HSSFRow resultRow = resultSheet.getRow(row);
        HSSFRow etalonRow = etalonSheet.getRow(row);
        if (resultRow == null && etalonRow == null) {
            continue;
        } else if ((resultRow == null) || (etalonRow == null)) {
            Assert.fail("fail on row [" + row + "]");
        }

        for (int cell = 0; cell < 10; cell++) {
            HSSFCell resultCell = resultRow.getCell(cell);
            HSSFCell etalonCell = etalonRow.getCell(cell);

            if (resultCell != null && etalonCell != null) {
                Assert.assertEquals(String.format("fail on cell [%d,%d]", row, cell), etalonCell.getNumericCellValue(), resultCell.getNumericCellValue());
            } else if ((resultCell == null && etalonCell != null) || (resultCell != null)) {
                Assert.fail(String.format("fail on cell [%d,%d]", row, cell));
            }
        }
    }
}
 

/**
 * 读取 Excel文件内容
 * 
 * @param excel_name
 * @return
 * @throws Exception
 */
public static List<List<Object>> readExcelByInputStream(
		InputStream inputstream) throws Exception {
	// 结果集
	List<List<Object>> list = new ArrayList<List<Object>>();

	HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);

	// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
	HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

	// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数

	// //org.jeecgframework.core.util.LogUtil.info("excel行数： "+hssfsheet.getPhysicalNumberOfRows());
	for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
		HSSFRow hssfrow = hssfsheet.getRow(j);
		if (hssfrow != null) {
			int col = hssfrow.getPhysicalNumberOfCells();
			// 单行数据
			List<Object> arrayString = new ArrayList<Object>();
			for (int i = 0; i < col; i++) {
				HSSFCell cell = hssfrow.getCell(i);
				if (cell == null) {
					arrayString.add("");
				} else if (cell.getCellType() == 0) {
					arrayString.add(new Double(cell.getNumericCellValue())
							.toString());
				} else {// 如果EXCEL表格中的数据类型为字符串型
					arrayString.add(cell.getStringCellValue().trim());
				}
			}
			list.add(arrayString);
		}
	}
	return list;
}
 

@Test
public void dumpExcelSimple() throws IOException {
    try (CurlResponse response = createRequest(node, path, paramsXls).execute()) {
        try (InputStream is = response.getContentAsStream()) {
            final POIFSFileSystem fs = new POIFSFileSystem(is);
            final HSSFWorkbook book = new HSSFWorkbook(fs);
            final HSSFSheet sheet = book.getSheetAt(0);
            assertEquals(docNumber, sheet.getLastRowNum());
        }
    }
}
 

public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
    List<String> optionsList = new ArrayList<String>();
    try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
        Iterator<Row> iterator;
        switch(FileMagic.valueOf(bufferedInputStream)) {
            case OOXML:
                log.debug("Input file detected as OOXML.");
                XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
                XSSFSheet datatypeSheet = workbook.getSheetAt(0);
                iterator = datatypeSheet.iterator();
                break;
            case OLE2:
                log.debug("Input file detected as OLE2.");
                HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
                HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
                iterator = legacyDatatypeSheet.iterator();
                break;
            default:
                log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
                try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
                    while(scanner.hasNext()){
                        String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
                        if(StringUtils.isNotBlank(inputString)){
                            optionsList.add(inputString);
                        }
                    }
                } catch(Exception ex){
                    throw new IOException("Error processing the file as text type.", ex);
                }
                return optionsList;
        }

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Iterator<Cell> cellIterator = currentRow.iterator();
            if(cellIterator.hasNext()) {
                Cell currentCell = cellIterator.next();
                switch(currentCell.getCellType()) {
                    case STRING:
                        if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
                            optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
                        }
                        break;
                    case NUMERIC:
                         optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
                         break;
                    case BOOLEAN:
                        optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
                        break;
                    case FORMULA:
                    case BLANK:
                    case _NONE:
                    case ERROR:
                    default:
                        break;
                }
            }
        }
    } catch (Exception e) {
        throw new IOException("Error converting the file to options list.");
    }

    return optionsList;
}
 

/**
	 * 读取 Excel文件内容
	 * 
	 * @param excel_name
	 * @return
	 * @throws Exception
	 */
	public static List<String[]> readExcel(String excel_name) throws Exception {
		// 结果集
		List<String[]> list = new ArrayList<String[]>();

		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
				excel_name));

		// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

		// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
		for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
			HSSFRow hssfrow = hssfsheet.getRow(j);
			if(hssfrow!=null){
			int col = hssfrow.getPhysicalNumberOfCells();
			// 单行数据
			String[] arrayString = new String[col];
			for (int i = 0; i < col; i++) {
				HSSFCell cell = hssfrow.getCell(i);
				if (cell == null) {
					arrayString[i] = "";
				} else if (cell.getCellType() == 0) {
					// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
					if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { 
						  if (HSSFDateUtil.isCellDateFormatted(cell)) {    
						    Date d = cell.getDateCellValue();    
//						    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");    
						     DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						    arrayString[i] = formater.format(d);   
						   } else {    
						       arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue()+"";    
						}
					}
				} else {// 如果EXCEL表格中的数据类型为字符串型
					arrayString[i] = cell.getStringCellValue().trim();
				}
			}
			list.add(arrayString);
		}
		}
		return list;
	}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    String fileLocation = DatasourceId.ONSNewBusinessSurvival.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    List<TimedValue> timedValues = new ArrayList<TimedValue>();
    HSSFWorkbook workbook = new HSSFWorkbook(isr);

    // Looping through the excell sheets
    for (int sheet = 13; sheet <= 17; sheet++){
        Sheet datatypeSheet = workbook.getSheetAt(sheet);
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        int ignore = 0;
        while (ignore++ < 6) {
            rowIterator.next();
        }
        // Hardcoded year of survey
        String year = null;
        switch (sheet){
            case 13:
                year = "2011";
                break;
            case 14:
                year = "2012";
                break;
            case 15:
                year = "2013";
                break;
            case 16:
                year = "2014";
                break;
            case 17:
                year = "2015";
                break;
        }
        Row rowAttribute = datatypeSheet.getRow(6);
        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geography =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geography);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geography);
            // Checking if subject is null
            if (subject != null) {
                // loop through attribute columns
                for (int i=3; i<=12;i++){
                    String attributeName = String.valueOf(rowAttribute.getCell(i)).trim();
                    LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
                    try {
                        Double record;
                        if (attributeName.contains("per cent")) {
                            record = row.getCell(i).getNumericCellValue() / 100;
                            log.info("Value for " + subject.getLabel()+". Appears as: "+ row.getCell(i).getNumericCellValue()+
                            " Saving as: "+record);
                        } else {
                            record = row.getCell(i).getNumericCellValue();
                        }
                        row.getCell(i).getNumericCellValue();
                        Attribute attribute = AttributeId.getAttributeIdByEqual(attributeName).attribute;
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    } catch (IllegalStateException | NullPointerException e) {
                        log.warn("Invalid value for subject " + subject.getLabel()+". Skipping");
                        continue;
                    }
                }
            }
        }
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 

public StyledExcelSpreadsheet getInconsistencySpreadsheet() {
    final StyledExcelSpreadsheet spreadsheet =
            new StyledExcelSpreadsheet(BundleUtil.getString(Bundle.ACADEMIC, "label.course.load.inconsistency.filename")
                    + "_" + executionSemester.getExecutionYear().getYear().replace('/', '_') + "_"
                    + executionSemester.getSemester());
    CellStyle normalStyle = spreadsheet.getExcelStyle().getValueStyle();
    normalStyle.setAlignment(HorizontalAlignment.CENTER);

    HSSFWorkbook wb = spreadsheet.getWorkbook();
    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.BLACK.index);
    font.setFontHeightInPoints((short) 8);
    HSSFCellStyle redStyle = wb.createCellStyle();
    redStyle.setFont(font);
    redStyle.setAlignment(HorizontalAlignment.CENTER);
    redStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
    redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    HSSFCellStyle yellowStyle = wb.createCellStyle();
    yellowStyle.setFont(font);
    yellowStyle.setAlignment(HorizontalAlignment.CENTER);
    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    spreadsheet.newHeaderRow();
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.department"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.degree"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shift"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shiftType"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.competenceCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.curricularCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lesson.count"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances.count"));

    for (final ExecutionCourse executionCourse : executionSemester.getAssociatedExecutionCoursesSet()) {
        for (final CourseLoad courseLoad : executionCourse.getCourseLoadsSet()) {
            for (final Shift shift : courseLoad.getShiftsSet()) {
                spreadsheet.newRow();
                spreadsheet.addCell(getDepartmentString(executionCourse));
                spreadsheet.addCell(executionCourse.getDegreePresentationString());
                spreadsheet.addCell(executionCourse.getName());
                spreadsheet.addCell(shift.getNome());
                spreadsheet.addCell(courseLoad.getType().getFullNameTipoAula());
                final BigDecimal competenceCourseLoad =
                        new BigDecimal(getCompetenceCourseLoad(courseLoad)).setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal curricularCourseLoad =
                        new BigDecimal(getCurricularCourseLoad(courseLoad)).setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal executionLoad = courseLoad.getTotalQuantity().setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal shiftCourseLoad = getShiftCourseLoad(shift).setScale(2, RoundingMode.HALF_EVEN);
                if (competenceCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCompetenceCourseLoadStrings(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(competenceCourseLoad);
                }
                if (!competenceCourseLoad.equals(curricularCourseLoad) || curricularCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCurricularCourseLoadString(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(curricularCourseLoad);
                }
                if (!executionLoad.equals(curricularCourseLoad)) {
                    spreadsheet.addCell(executionLoad, redStyle);
                } else {
                    spreadsheet.addCell(executionLoad);
                }
                if (!shiftCourseLoad.equals(executionLoad)) {
                    if (isLargeDifference(shiftCourseLoad, executionLoad,
                            competenceCourseLoad.divide(new BigDecimal(14), 2, RoundingMode.HALF_EVEN))) {
                        spreadsheet.addCell(shiftCourseLoad, redStyle);
                    } else {
                        spreadsheet.addCell(shiftCourseLoad, yellowStyle);
                    }
                } else {
                    spreadsheet.addCell(shiftCourseLoad);
                }
                spreadsheet.addCell(shift.getAssociatedLessonsSet().size());
                spreadsheet.addCell(getLessonInstanceCount(shift));
            }
        }
    }

    final HSSFSheet sheet = wb.getSheetAt(0);
    sheet.createFreezePane(0, 1, 0, 1);
    sheet.autoSizeColumn(1, true);
    sheet.autoSizeColumn(2, true);
    sheet.autoSizeColumn(3, true);
    sheet.autoSizeColumn(4, true);
    sheet.autoSizeColumn(5, true);
    sheet.autoSizeColumn(6, true);
    sheet.autoSizeColumn(7, true);
    sheet.autoSizeColumn(8, true);
    sheet.autoSizeColumn(9, true);

    return spreadsheet;
}