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

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFWorkbook#getSheetAt() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: BasicReportTest.java    From birt with Eclipse Public License 1.0 6 votes vote down vote up
@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();
	}
}
 
Example 2
Source File: HyperlinksTest.java    From birt with Eclipse Public License 1.0 6 votes vote down vote up
@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();
	}
}
 
Example 3
Source File: BasicReportTest.java    From birt with Eclipse Public License 1.0 6 votes vote down vote up
@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();
	}
}
 
Example 4
Source File: Prd5391IT.java    From pentaho-reporting with GNU Lesser General Public License v2.1 6 votes vote down vote up
@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() );
}
 
Example 5
Source File: XLSFileNormalizer.java    From Knowage-Server with GNU Affero General Public License v3.0 6 votes vote down vote up
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;
}
 
Example 6
Source File: BoundaryPointXlsParser.java    From powsybl-core with Mozilla Public License 2.0 6 votes vote down vote up
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;
}
 
Example 7
Source File: BasicReportTest.java    From birt with Eclipse Public License 1.0 6 votes vote down vote up
@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();
	}
}
 
Example 8
Source File: CommonExcelServiceImpl.java    From jeecg with Apache License 2.0 5 votes vote down vote up
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);
		}
	}
}
 
Example 9
Source File: ExcelTempletService.java    From jeecg with Apache License 2.0 5 votes vote down vote up
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);
		}
	}
}
 
Example 10
Source File: ExcelFileParser.java    From JTAF-XCore with Apache License 2.0 5 votes vote down vote up
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);
	}
}
 
Example 11
Source File: ExcelUtil.java    From jeewx with Apache License 2.0 5 votes vote down vote up
/**
 * 读取 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;
}
 
Example 12
Source File: ExcelTopicNameExtractor.java    From wandora with GNU General Public License v3.0 5 votes vote down vote up
@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);
    }
}
 
Example 13
Source File: XlsDataSet.java    From Leo with Apache License 2.0 5 votes vote down vote up
/**
 * 从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);
    }
}
 
Example 14
Source File: XlsIntegrationTest.java    From yarg with Apache License 2.0 5 votes vote down vote up
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));
            }
        }
    }
}
 
Example 15
Source File: ExcelUtil.java    From jeewx with Apache License 2.0 5 votes vote down vote up
/**
 * 读取 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;
}
 
Example 16
Source File: DataFormatPluginTest.java    From elasticsearch-dataformat with Apache License 2.0 5 votes vote down vote up
@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());
        }
    }
}
 
Example 17
Source File: OptionsFileConverterUtil.java    From sakai with Educational Community License v2.0 4 votes vote down vote up
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;
}
 
Example 18
Source File: ExcelUtil.java    From jeewx with Apache License 2.0 4 votes vote down vote up
/**
	 * 读取 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;
	}
 
Example 19
Source File: ONSBusinessDemographyImporter.java    From TomboloDigitalConnector with MIT License 4 votes vote down vote up
@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();
}
 
Example 20
Source File: CourseLoadOverviewBean.java    From fenixedu-academic with GNU Lesser General Public License v3.0 4 votes vote down vote up
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;
}