org.apache.poi.hssf.usermodel.HSSFCell Java Examples

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFCell. 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 vote down vote up
/**
 * 表头条件
 * @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: ExcelUtil.java    From phone with Apache License 2.0 6 votes vote down vote up
/**
   * 初始化表头
   * @param sheet
   * @param columnJson
   * @param rowNumber
   */
  private static void writeSheetHead(HSSFSheet sheet,JSONObject columnJson,int rowNumber){
if (logger.isDebugEnabled()) {
	logger.debug("writeSheetHead(HSSFSheet, JSONObject, int) - start"); //$NON-NLS-1$
}

Set<String> keySet = columnJson.keySet();
int cellNumber = 0;
HSSFRow row = sheet.createRow(rowNumber);
for (String k : keySet) {//k:GOODS_NO
	String name = columnJson.getString(k);//品项编码
	sheet.autoSizeColumn(cellNumber);
	HSSFCell cell = row.createCell(cellNumber++);
	cell.setCellValue(name);
}

if (logger.isDebugEnabled()) {
	logger.debug("writeSheetHead(HSSFSheet, JSONObject, int) - end"); //$NON-NLS-1$
}
  }
 
Example #3
Source File: XlsResource.java    From nextreports-server with Apache License 2.0 6 votes vote down vote up
@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new HSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       HSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			HSSFCell cell = headerRow.createCell(col);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(new HSSFRichTextString(s));
			col++;
		}
	}		
}
 
Example #4
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
public static String getCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);

    if (row == null) {
        return null;
    }

    final HSSFCell cell = row.getCell(c);

    if (cell == null) {
        return null;
    }

    final HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}
 
Example #5
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
public static boolean getBooleanCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);

    if (row == null) {
        return false;
    }

    final HSSFCell cell = row.getCell(c);

    if (cell == null) {
        return false;
    }

    try {
        return cell.getBooleanCellValue();
    } catch (final RuntimeException e) {
        System.err.println("Exception at sheet name:" + sheet.getSheetName() + ", row:" + (r + 1) + ", col:" + (c + 1));
        throw e;
    }
}
 
Example #6
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
public static CellLocation findCell(final HSSFSheet sheet, final String str, final int colNum) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }
        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (!Check.isEmpty(cellValue.getString())) {
            if (cellValue.getString().equals(str)) {
                return new CellLocation(rowNum, (short) colNum);
            }
        }
    }

    return null;
}
 
Example #7
Source File: POIUtils.java    From ermaster-b with Apache License 2.0 6 votes vote down vote up
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 #8
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
public static void copyRow(final HSSFSheet oldSheet, final HSSFSheet newSheet, final int oldStartRowNum, final int oldEndRowNum, final int newStartRowNum) {
    final HSSFRow oldAboveRow = oldSheet.getRow(oldStartRowNum - 1);

    int newRowNum = newStartRowNum;

    for (int oldRowNum = oldStartRowNum; oldRowNum <= oldEndRowNum; oldRowNum++) {
        POIUtils.copyRow(oldSheet, newSheet, oldRowNum, newRowNum++);
    }

    final HSSFRow newTopRow = newSheet.getRow(newStartRowNum);

    if (oldAboveRow != null) {
        for (int colNum = newTopRow.getFirstCellNum(); colNum <= newTopRow.getLastCellNum(); colNum++) {
            final HSSFCell oldAboveCell = oldAboveRow.getCell(colNum);
            if (oldAboveCell != null) {
                final HSSFCell newTopCell = newTopRow.getCell(colNum);
                newTopCell.getCellStyle().setBorderTop(oldAboveCell.getCellStyle().getBorderBottom());
            }
        }
    }
}
 
Example #9
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
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 #10
Source File: AbstractSheetGenerator.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
protected Map<String, String> buildKeywordsValueMap(final HSSFSheet wordsSheet, final int columnNo, final String[] keywords) {
    final Map<String, String> keywordsValueMap = new HashMap<String, String>();

    for (final String keyword : keywords) {
        final CellLocation location = POIUtils.findCell(wordsSheet, keyword, columnNo);
        if (location != null) {
            final HSSFRow row = wordsSheet.getRow(location.r);

            final HSSFCell cell = row.getCell(location.c + 2);
            final String value = cell.getRichStringCellValue().getString();

            if (value != null) {
                keywordsValueMap.put(keyword, value);
            }
        }
    }

    return keywordsValueMap;
}
 
Example #11
Source File: ImportService.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
private boolean parseBooleanCell(HSSFCell cell) {
if (cell != null) {
    String value;
    try {
	cell.setCellType(CellType.STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return false;
	}
	value = cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(CellType.NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	value = new Long(new Double(d).longValue()).toString();
    }
    if (StringUtils.equals(value, "1") || StringUtils.equalsIgnoreCase(value, "true")) {
	return true;
    }
}
return false;
   }
 
Example #12
Source File: AbstractSheetGenerator.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
protected void setColumnData(final Map<String, String> keywordsValueMap, final ColumnTemplate columnTemplate, final HSSFRow row, final NormalColumn normalColumn, final TableView tableView, final int order) {

        for (final int columnNum : columnTemplate.columnTemplateMap.keySet()) {
            final HSSFCell cell = row.createCell(columnNum);
            final String template = columnTemplate.columnTemplateMap.get(columnNum);

            String value = null;
            if (KEYWORD_ORDER.equals(template)) {
                value = String.valueOf(order);

            } else {
                value = getColumnValue(keywordsValueMap, normalColumn, tableView, template);
            }

            try {
                final double num = Double.parseDouble(value);
                cell.setCellValue(num);

            } catch (final NumberFormatException e) {
                final HSSFRichTextString text = new HSSFRichTextString(value);
                cell.setCellValue(text);
            }
        }
    }
 
Example #13
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
public static Integer findMatchColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            continue;
        }

        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (cellValue.getString().matches(str)) {
            return Integer.valueOf(colNum);
        }
    }

    return null;
}
 
Example #14
Source File: POIUtils.java    From ermasterr with Apache License 2.0 6 votes vote down vote up
public static Integer findColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            final HSSFRichTextString cellValue = cell.getRichStringCellValue();

            if (str.equals(cellValue.getString())) {
                return Integer.valueOf(colNum);
            }
        }
    }

    return null;
}
 
Example #15
Source File: ExportEventsImpl.java    From neoscada with Eclipse Public License 1.0 6 votes vote down vote up
private void makeHeader ( final List<Field> columns, final HSSFSheet sheet )
{
    final Font font = sheet.getWorkbook ().createFont ();
    font.setFontName ( "Arial" );
    font.setBoldweight ( Font.BOLDWEIGHT_BOLD );
    font.setColor ( HSSFColor.WHITE.index );

    final CellStyle style = sheet.getWorkbook ().createCellStyle ();
    style.setFont ( font );
    style.setFillForegroundColor ( HSSFColor.BLACK.index );
    style.setFillPattern ( PatternFormatting.SOLID_FOREGROUND );

    final HSSFRow row = sheet.createRow ( 0 );

    for ( int i = 0; i < columns.size (); i++ )
    {
        final Field field = columns.get ( i );

        final HSSFCell cell = row.createCell ( i );
        cell.setCellValue ( field.getHeader () );
        cell.setCellStyle ( style );
    }
}
 
Example #16
Source File: DBUnitXLSTestDataCreator.java    From ermaster-b with Apache License 2.0 6 votes vote down vote up
@Override
protected void writeDirectTestData(ERTable table,
		Map<NormalColumn, String> data, String database) {
	HSSFRow row = this.sheet.createRow(this.rowNum++);

	int col = 0;

	for (NormalColumn column : table.getExpandedColumns()) {
		HSSFCell cell = row.createCell(col++);

		String value = Format.null2blank(data.get(column));

		if (value == null || "null".equals(value.toLowerCase())) {

		} else {
			cell.setCellValue(new HSSFRichTextString(value));
		}
	}
}
 
Example #17
Source File: ExcelOperator.java    From minsx-framework with Apache License 2.0 6 votes vote down vote up
@SuppressWarnings("deprecation")
private static String getCellData(Cell cell) {
    String value = null;
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
            value = dataFormatter.formatCellValue(cell);
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            value = null;
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = "#ERROR#";
            break;
    }
    return value;
}
 
Example #18
Source File: XLSFileNormalizer.java    From Knowage-Server with GNU Affero General Public License v3.0 6 votes vote down vote up
private int getColumnPosition(String columnName, HSSFRow row){
	int cells = row.getPhysicalNumberOfCells();
	logger.debug("\nROW " + row.getRowNum() + " has " + cells
			+ " cell(s).");
	for (int c = 0; c < cells; c++) {
		//get single cell
		HSSFCell cell = row.getCell(c);
		
		String valueField = null;
		try {
			valueField = parseCell(cell);
			
			if (valueField.equals(columnName)){
				return c;
			}
			
		} catch(Throwable t) {
			throw new RuntimeException("Impossible to parse cell [" + c + "]", t);
		}

	}
	return -1;
	
}
 
Example #19
Source File: GenerateDoc.java    From danyuan-application with Apache License 2.0 6 votes vote down vote up
@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
 
Example #20
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 #21
Source File: Prd3899IT.java    From pentaho-reporting with GNU Lesser General Public License v2.1 6 votes vote down vote up
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 #22
Source File: TestExternalRelvarXLS3.java    From Rel with Apache License 2.0 5 votes vote down vote up
private static void insert(int rowNum, HSSFSheet sheet, HSSFRow row, HSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 
Example #23
Source File: TestExternalRelvarXLS3.java    From Rel with Apache License 2.0 5 votes vote down vote up
@Before
public void testXLS1() throws IOException {
       try (HSSFWorkbook workbook = new HSSFWorkbook()) {
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_count;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 
Example #24
Source File: AccountSummaryController.java    From primefaces-blueprints with The Unlicense 5 votes vote down vote up
public void postProcessXLS(Object document) {  
    HSSFWorkbook wb = (HSSFWorkbook) document;  
    HSSFSheet sheet = wb.getSheetAt(0);  
    HSSFRow header = sheet.getRow(0);  
    HSSFCellStyle cellStyle = wb.createCellStyle();    
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);  
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
      
    for(int i=0; i < header.getPhysicalNumberOfCells();i++) {  
        HSSFCell cell = header.getCell(i);  
          
        cell.setCellStyle(cellStyle);  
    }  
    Row row=sheet.createRow((short)sheet.getLastRowNum()+3);
    Cell cellDisclaimer = row.createCell(0);
    HSSFFont customFont= wb.createFont();
    customFont.setFontHeightInPoints((short)10);
    customFont.setFontName("Arial");
    customFont.setColor(IndexedColors.BLACK.getIndex());
    customFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    customFont.setItalic(true);
    
    cellDisclaimer.setCellValue("Disclaimer");
    HSSFCellStyle cellStyleDisclaimer = wb.createCellStyle();
    cellStyleDisclaimer.setFont(customFont);
    cellDisclaimer.setCellStyle(cellStyleDisclaimer);
    
    Row row1=sheet.createRow(sheet.getLastRowNum()+2);
    Cell cellDisclaimerContent1 = row1.createCell(0);
    cellDisclaimerContent1.setCellValue("The information contained in this website is for information purposes only, and does not constitute, nor is it intended to constitute, the provision of financial product advice.");	    
    Row row2=sheet.createRow(sheet.getLastRowNum()+1);
    Cell cellDisclaimerContent2 = row2.createCell(0);
    cellDisclaimerContent2.setCellValue("This website is intended to track the investor account summary information,investments and transaction in a partcular period of time. ");
    
}
 
Example #25
Source File: AbstractExcelReportBuilder.java    From bdf3 with Apache License 2.0 5 votes vote down vote up
public void fillCellValue(Cell cell, Object value, SimpleDateFormat sdf) {
	if (value != null) {
		if (value instanceof Date) {
			String result = sdf.format(value);
			if (result.endsWith("00:00:00")) {
				result = result.substring(0, 11);
			}
			cell.setCellValue(result);
		} else if (value instanceof Double) {
			cell.setCellValue((Double) value);
		} else if (value instanceof Integer) {
			cell.setCellValue((Integer) value);
		} else if (value instanceof Byte) {
			cell.setCellValue((Byte) value);
		} else if (value instanceof Short) {
			cell.setCellValue((Short) value);
		} else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else if (value instanceof Long) {
			cell.setCellValue((Long) value);
		} else if (value instanceof Float) {
			cell.setCellValue((Float) value);
		} else {
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(value.toString());
		}
	} else {
		cell.setCellValue("");
	}
}
 
Example #26
Source File: ExcelExportSuper.java    From phone with Apache License 2.0 5 votes vote down vote up
/**
 * 写入表头
 * @param sheet
 */
void writeHead(HSSFSheet sheet){
	LinkedHashMap<String, Object> columnJson = getColumnJson();
	Set<String> keySet = columnJson.keySet();
	int cellNumber = 0;
	HSSFRow row = sheet.createRow(addRowNumber());
	for (String k : keySet) {
		Object name = columnJson.get(k);//品项编码
		sheet.autoSizeColumn(cellNumber);
		HSSFCell cell = row.createCell(cellNumber++);
		setCellValue(cell, name);
		pubMaxValue(k,name);
	}
}
 
Example #27
Source File: JRXlsExporter.java    From jasperreports with GNU Lesser General Public License v3.0 5 votes vote down vote up
protected void createMergeRegion(JRExporterGridCell gridCell, int colIndex, int rowIndex, HSSFCellStyle cellStyle)
{
	boolean isCollapseRowSpan = getCurrentItemConfiguration().isCollapseRowSpan();
	int rowSpan = isCollapseRowSpan ? 1 : gridCell.getRowSpan();
	if (gridCell.getColSpan() > 1 || rowSpan > 1)
	{
		sheet.addMergedRegion(new CellRangeAddress(rowIndex, (rowIndex + rowSpan - 1), 
				colIndex, (colIndex + gridCell.getColSpan() - 1)));

		for(int i = 0; i < rowSpan; i++)
		{
			HSSFRow spanRow = sheet.getRow(rowIndex + i);
			if (spanRow == null)
			{
				spanRow = sheet.createRow(rowIndex + i);
			}
			for(int j = 0; j < gridCell.getColSpan(); j++)
			{
				HSSFCell spanCell = spanRow.getCell((colIndex + j));
				if (spanCell == null)
				{
					spanCell = spanRow.createCell((colIndex + j));
				}
				spanCell.setCellStyle(cellStyle);
			}
		}
	}
}
 
Example #28
Source File: JasperReportsUtilsTests.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
@SuppressWarnings("resource")
private void assertXlsOutputCorrect(byte[] output) throws Exception {
	HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(output));
	HSSFSheet sheet = workbook.getSheetAt(0);
	assertNotNull("Sheet should not be null", sheet);
	HSSFRow row = sheet.getRow(3);
	HSSFCell cell = row.getCell((short) 1);
	assertNotNull("Cell should not be null", cell);
	assertEquals("Cell content should be Dear Lord!", "Dear Lord!", cell.getRichStringCellValue().getString());
}
 
Example #29
Source File: POIUtils.java    From ermaster-b with Apache License 2.0 5 votes vote down vote up
public static String getCellValue(HSSFSheet sheet, CellLocation location) {
	HSSFRow row = sheet.getRow(location.r);
	HSSFCell cell = row.getCell(location.c);

	HSSFRichTextString cellValue = cell.getRichStringCellValue();

	return cellValue.toString();
}
 
Example #30
Source File: AbstractExcelView.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
/**
 * Convenient method to obtain the cell in the given sheet, row and column.
 * <p>Creates the row and the cell if they still doesn't already exist.
 * Thus, the column can be passed as an int, the method making the needed downcasts.
 * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)
 * @param row the row number
 * @param col the column number
 * @return the HSSFCell
 */
protected HSSFCell getCell(HSSFSheet sheet, int row, int col) {
	HSSFRow sheetRow = sheet.getRow(row);
	if (sheetRow == null) {
		sheetRow = sheet.createRow(row);
	}
	HSSFCell cell = sheetRow.getCell(col);
	if (cell == null) {
		cell = sheetRow.createCell(col);
	}
	return cell;
}