Java Code Examples for org.apache.poi.hssf.usermodel.HSSFCell#getCellStyle()

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFCell#getCellStyle() . 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: ExcelExportSuper.java From phone with Apache License 2.0

7 votes

/**
 * 表头条件
 * @param sheet
 * @param t
 * @param cellCount
 * @return
 */
void writeCondtions(HSSFSheet sheet){
	T t = getConditions();
	if (t!=null) {
		HSSFRow row = sheet.createRow(getRowNumber());
		row.setHeight((short) 500);
		CellRangeAddress cra = new CellRangeAddress(getRowNumber(), getRowNumber(), 0, getColumnJson().size());
		sheet.addMergedRegion(cra);
		HSSFCell cell = row.createCell(0);
		HSSFCellStyle style = cell.getCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(true);
		cell.setCellStyle(style);
		setCellValue(cell, formatCondition(t));
		addRowNumber();
	}
}

Example 2

Source File: POIUtils.java From ermasterr with Apache License 2.0

6 votes

public static List<HSSFCellStyle> copyCellStyle(final HSSFWorkbook workbook, final HSSFRow row) {
    final List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

        final HSSFCell cell = row.getCell(colNum);
        if (cell != null) {
            final HSSFCellStyle style = cell.getCellStyle();
            final HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
            cellStyleList.add(newCellStyle);
        } else {
            cellStyleList.add(null);
        }
    }

    return cellStyleList;
}

Example 3

Source File: Prd3899IT.java From pentaho-reporting with GNU Lesser General Public License v2.1

6 votes

public void testBug() throws ResourceException, IOException, ReportProcessingException {
  final MasterReport report = DebugReportRunner.parseGoldenSampleReport( "Prd-3889.prpt" );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLS( 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();
  assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  assertEquals( "FFFF:FFFF:9999", fillForegroundColorColor.getHexString() );

  // assert that there are no extra columns ..
  final HSSFRow row8 = sheetAt.getRow( 7 );
  assertNull( row8 );

}

Example 4

Source File: Prd5391IT.java From pentaho-reporting with GNU Lesser General Public License v2.1

6 votes

@Test
public void testSlowExport() 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();
  ExcelReportUtil.createXLS( 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: Prd5391IT.java From pentaho-reporting with GNU Lesser General Public License v2.1

6 votes

@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 6

Source File: POIUtils.java From ermaster-b with Apache License 2.0

6 votes

public static List<HSSFCellStyle> copyCellStyle(HSSFWorkbook workbook,
		HSSFRow row) {
	List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

		HSSFCell cell = row.getCell(colNum);
		if (cell != null) {
			HSSFCellStyle style = cell.getCellStyle();
			HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
			cellStyleList.add(newCellStyle);
		} else {
			cellStyleList.add(null);
		}
	}

	return cellStyleList;
}

Example 7

Source File: OfficeConverter.java From BBSSDK-for-Android with Apache License 2.0

4 votes

private void xlsToHtml() throws Throwable {
	FileOutputStream output = new FileOutputStream(new File(htmlPath));
	StringBuffer htmlHeaderSB = new StringBuffer();
	htmlHeaderSB.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' "
			+ "xmlns='http://www.w3.org/TR/REC-html40'>");
	htmlHeaderSB.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>"
			+ "</head><body>");
	output.write(htmlHeaderSB.toString().getBytes());
	HSSFSheet sheet;
	HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath)); // 获整个Excel
	for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
		if (workbook.getSheetAt(sheetIndex) != null) {
			sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
			if (sheet != null) {
				int firstRowNum = sheet.getFirstRowNum(); // 第一行
				int lastRowNum = sheet.getLastRowNum(); // 最后一行
				// 构造Table
				output.write(("<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;"
						+ "border-collapse:collapse;\">").getBytes());
				for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
					if (sheet.getRow(rowNum) != null) {// 如果行不为空，
						HSSFRow row = sheet.getRow(rowNum);
						short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
						short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
						int height = (int) (row.getHeight() / 15.625); // 行的高度
						output.write(("<tr height=\"" + height + "\" style=\"border:1px solid #000;border-width:0 1px 1px 0;"
								+ "margin:2px 0 2px 0;\">").getBytes());
						for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
							HSSFCell cell = row.getCell(cellNum);
							if (cell != null) {
								if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
									StringBuffer tdStyle = new StringBuffer("<td style=\"border:1px solid #000; border-width:0 1px 1px 0;"
											+ "margin:2px 0 2px 0; ");
									HSSFCellStyle cellStyle = cell.getCellStyle();
									HSSFPalette palette = workbook.getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式
									HSSFColor hColor = palette.getColor(cellStyle.getFillForegroundColor());
									HSSFColor hColor2 = palette.getColor(cellStyle.getFont(workbook).getColor());
									String bgColor = convertToStardColor(hColor);// 背景颜色
									short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细
									short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小
									String fontColor = convertToStardColor(hColor2); // 字体颜色
									if (bgColor != null && !"".equals(bgColor.trim())) {
										tdStyle.append(" background-color:");
										tdStyle.append(bgColor);
										tdStyle.append("; ");
									}
									if (fontColor != null && !"".equals(fontColor.trim())) {
										tdStyle.append(" color:");
										tdStyle.append(fontColor);
										tdStyle.append("; ");
									}
									tdStyle.append(" font-weight:");
									tdStyle.append(boldWeight);
									tdStyle.append("; ");
									tdStyle.append(" font-size: ");
									tdStyle.append(fontHeight);
									tdStyle.append("%;");
									output.write((tdStyle + "\"").getBytes());

									int width = (int) (sheet.getColumnWidth(cellNum) / 35.7); //
									int cellRegionCol = getMergerCellRegionCol(sheet, rowNum, cellNum); // 合并的列（solspan）
									int cellRegionRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行（rowspan）
									String align = convertAlignToHtml(cellStyle.getAlignment()); //
									String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());

									output.write((" align=\"" + align + "\" valign=\"" + vAlign + "\" width=\"" + width + "\" ").getBytes());
									output.write((" colspan=\"" + cellRegionCol + "\" rowspan=\"" + cellRegionRow + "\"").getBytes());
									output.write((">" + getCellValue(cell) + "</td>").getBytes());
								}
							}
						}
						output.write("</tr>".getBytes());
					}
				}
				output.write(("</table>").getBytes());
			}
		}
	}
	output.write(("</body></html>").getBytes());
	output.close();
}

Example 8

Source File: POIUtils.java From ermasterr with Apache License 2.0

4 votes

public static void copyRow(final HSSFSheet oldSheet, final HSSFSheet newSheet, final int oldRowNum, final int newRowNum) {
    final HSSFRow oldRow = oldSheet.getRow(oldRowNum);

    final HSSFRow newRow = newSheet.createRow(newRowNum);

    if (oldRow == null) {
        return;
    }

    newRow.setHeight(oldRow.getHeight());

    if (oldRow.getFirstCellNum() == -1) {
        return;
    }

    for (int colNum = oldRow.getFirstCellNum(); colNum <= oldRow.getLastCellNum(); colNum++) {
        final HSSFCell oldCell = oldRow.getCell(colNum);
        final HSSFCell newCell = newRow.createCell(colNum);

        if (oldCell != null) {
            final HSSFCellStyle style = oldCell.getCellStyle();
            newCell.setCellStyle(style);

            final int cellType = oldCell.getCellType();
            newCell.setCellType(cellType);

            if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                newCell.setCellValue(oldCell.getBooleanCellValue());

            } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                newCell.setCellFormula(oldCell.getCellFormula());

            } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                newCell.setCellValue(oldCell.getNumericCellValue());

            } else if (cellType == Cell.CELL_TYPE_STRING) {
                newCell.setCellValue(oldCell.getRichStringCellValue());
            }
        }
    }

    POIUtils.copyMergedRegion(newSheet, getMergedRegionList(oldSheet, oldRowNum), newRowNum);
}

Example 9

Source File: XlsTable.java From Leo with Apache License 2.0

4 votes

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);

    int columnIndex = getColumnIndex(column);
    HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
            }

    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

        case HSSFCell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" +
                row + ", column=" + column);

        case HSSFCell.CELL_TYPE_BLANK:
        return null;

        case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

        case HSSFCell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row +
                ", column=" + column);

        default:
        throw new DataTypeException("Unsupported type at row=" + row +
                ", column=" + column);
    }
}

Example 10

Source File: POIUtils.java From ermaster-b with Apache License 2.0

4 votes

public static void copyRow(HSSFSheet oldSheet, HSSFSheet newSheet,
		int oldRowNum, int newRowNum) {
	HSSFRow oldRow = oldSheet.getRow(oldRowNum);

	HSSFRow newRow = newSheet.createRow(newRowNum);

	if (oldRow == null) {
		return;
	}

	newRow.setHeight(oldRow.getHeight());

	if (oldRow.getFirstCellNum() == -1) {
		return;
	}

	for (int colNum = oldRow.getFirstCellNum(); colNum <= oldRow
			.getLastCellNum(); colNum++) {
		HSSFCell oldCell = oldRow.getCell(colNum);
		HSSFCell newCell = newRow.createCell(colNum);

		if (oldCell != null) {
			HSSFCellStyle style = oldCell.getCellStyle();
			newCell.setCellStyle(style);

			int cellType = oldCell.getCellType();
			newCell.setCellType(cellType);

			if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
				newCell.setCellValue(oldCell.getBooleanCellValue());

			} else if (cellType == HSSFCell.CELL_TYPE_FORMULA) {
				newCell.setCellFormula(oldCell.getCellFormula());

			} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
				newCell.setCellValue(oldCell.getNumericCellValue());

			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
				newCell.setCellValue(oldCell.getRichStringCellValue());
			}
		}
	}

	POIUtils.copyMergedRegion(newSheet, getMergedRegionList(oldSheet,
			oldRowNum), newRowNum);
}

Example 11

Source File: TableSheetGenerator.java From ermaster-b with Apache License 2.0

4 votes

private MatrixCellStyle createMatrixCellStyle(HSSFWorkbook workbook,
		HSSFSheet sheet, CellLocation matrixCellLocation) {

	int matrixRowNum = matrixCellLocation.r;
	int matrixColumnNum = matrixCellLocation.c;

	HSSFRow matrixHeaderTemplateRow = sheet.getRow(matrixRowNum);
	HSSFCell matrixHeaderTemplateCell = matrixHeaderTemplateRow
			.getCell(matrixColumnNum);

	MatrixCellStyle matrixCellStyle = new MatrixCellStyle();

	matrixCellStyle.headerTemplateCellStyle = matrixHeaderTemplateCell
			.getCellStyle();

	matrixCellStyle.style11 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, false, true, true,
			false);

	matrixCellStyle.style12 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, false, true, true,
			true);

	matrixCellStyle.style13 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, false, false, true,
			true);

	matrixCellStyle.style21 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, true, true,
			false);

	matrixCellStyle.style22 = this
			.createMatrixCellStyle(workbook,
					matrixCellStyle.headerTemplateCellStyle, true, true,
					true, true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style22);

	matrixCellStyle.style23 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, false, true,
			true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style23);

	matrixCellStyle.style31 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, true, false,
			false);

	matrixCellStyle.style32 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, true, false,
			true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style32);

	matrixCellStyle.style33 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, false, false,
			true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style33);

	return matrixCellStyle;
}

Example 12

Source File: TableSheetGenerator.java From ermasterr with Apache License 2.0

3 votes

private MatrixCellStyle createMatrixCellStyle(final HSSFWorkbook workbook, final HSSFSheet sheet, final CellLocation matrixCellLocation) {

        final int matrixRowNum = matrixCellLocation.r;
        final int matrixColumnNum = matrixCellLocation.c;

        final HSSFRow matrixHeaderTemplateRow = sheet.getRow(matrixRowNum);
        final HSSFCell matrixHeaderTemplateCell = matrixHeaderTemplateRow.getCell(matrixColumnNum);

        final MatrixCellStyle matrixCellStyle = new MatrixCellStyle();

        matrixCellStyle.headerTemplateCellStyle = matrixHeaderTemplateCell.getCellStyle();

        matrixCellStyle.style11 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, false, true, true, false);

        matrixCellStyle.style12 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, false, true, true, true);

        matrixCellStyle.style13 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, false, false, true, true);

        matrixCellStyle.style21 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, true, true, true, false);

        matrixCellStyle.style22 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, true, true, true, true);
        setIndexMatrixColor(workbook, matrixCellStyle.style22);

        matrixCellStyle.style23 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, true, false, true, true);
        setIndexMatrixColor(workbook, matrixCellStyle.style23);

        matrixCellStyle.style31 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, true, true, false, false);

        matrixCellStyle.style32 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, true, true, false, true);
        setIndexMatrixColor(workbook, matrixCellStyle.style32);

        matrixCellStyle.style33 = this.createMatrixCellStyle(workbook, matrixCellStyle.headerTemplateCellStyle, true, false, false, true);
        setIndexMatrixColor(workbook, matrixCellStyle.style33);

        return matrixCellStyle;
    }