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

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

6 votes

@Override
protected void writeDirectTestData(final ERTable table, final Map<NormalColumn, String> data, final String database) {
    final HSSFRow row = sheet.createRow(rowNum++);

    int col = 0;

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

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

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

        } else {
            cell.setCellValue(new HSSFRichTextString(value));
        }
    }
}

Example 2

Source File: ExportEventsImpl.java From neoscada with Eclipse Public License 1.0

6 votes

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 3

Source File: XlsResource.java From nextreports-server with Apache License 2.0

6 votes

@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: XlsDataSetWriter.java From Leo with Apache License 2.0

6 votes

protected void setNumericCell(HSSFCell cell, BigDecimal value, HSSFWorkbook workbook)
{
    if(logger.isDebugEnabled())
        logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start", 
            new Object[] {cell, value, workbook} );

    cell.setCellValue( ((BigDecimal)value).doubleValue() );

    HSSFDataFormat df = workbook.createDataFormat();
    int scale = ((BigDecimal)value).scale();
    short format;
    if(scale <= 0){
        format = df.getFormat("####");
    }
    else {
        String zeros = createZeros(((BigDecimal)value).scale());
        format = df.getFormat("####." + zeros);
    }
    if(logger.isDebugEnabled())
        logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);
    
    HSSFCellStyle cellStyleNumber = workbook.createCellStyle();
    cellStyleNumber.setDataFormat(format);
    cell.setCellStyle(cellStyleNumber);
}

Example 5

Source File: ExcelUtil.java From phone with Apache License 2.0

6 votes

/**
   * 初始化表头
   * @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 6

Source File: JU_Excel2003Color.java From hy.common.report with Apache License 2.0

5 votes

@Test
public void test_001()
{
    HSSFWorkbook v_Workbook = new HSSFWorkbook();
    HSSFSheet    v_Sheet    = v_Workbook.createSheet("测试单元格颜色");
    
    v_Sheet.setColumnWidth(0 ,2560);
    
    for (int v_RowIndex=0; v_RowIndex<4000; v_RowIndex++)
    {
        HSSFRow v_Row = v_Sheet.createRow(v_RowIndex);
        
        for (int v_ColIndex=0; v_ColIndex<1; v_ColIndex++)
        {
            HSSFCell      v_Cell = v_Row.createCell(v_ColIndex);
            HSSFCellStyle v_CellStyle = v_Workbook.createCellStyle();
            
            v_CellStyle.setFillForegroundColor((short)(v_RowIndex + 1));
            v_CellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            
            v_Cell.setCellStyle(v_CellStyle);
            v_Cell.setCellValue("" + (v_RowIndex + 1));
        }
    }
    
    ExcelHelp.save(v_Workbook ,"/Users/hy/Downloads/测试2003版本的单元格颜色");
}

Example 7

Source File: TestExternalRelvarXLS4.java From Rel with Apache License 2.0

5 votes

@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,7,8,9);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\";" +
			"END;\n" +
			"true";
	testEquals("true", src);
}

Example 8

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

5 votes

public static void setCellValue(HSSFSheet sheet, CellLocation location,
		String value) {
	HSSFRow row = sheet.getRow(location.r);
	HSSFCell cell = row.getCell(location.c);

	HSSFRichTextString text = new HSSFRichTextString(value);
	cell.setCellValue(text);
}

Example 9

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

5 votes

protected void setColumnData(Map<String, String> keywordsValueMap,
		ColumnTemplate columnTemplate, HSSFRow row,
		NormalColumn normalColumn, TableView tableView, int order) {

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

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

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

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

		} catch (NumberFormatException e) {
			HSSFRichTextString text = new HSSFRichTextString(value);
			cell.setCellValue(text);
		}
	}
}

Example 10

Source File: ExcelExportSuper.java From phone with Apache License 2.0

5 votes

/**
	 * 为单位格设置值
	 * @param cell
	 * @param value
	 */
	void setCellValue(HSSFCell cell,Object value){
		if (value==null) {
			cell.setCellValue(getNullValue());
		}else{
//			if (org.springframework.beans.BeanUtils..isNotCollection(value)) {
				cell.setCellValue(value.toString());
//			}else{
//				logger.warn("initSheetBody#is not collection:"+key);
//			}
		}
	}

Example 11

Source File: ExportUtil.java From jumbune with GNU Lesser General Public License v3.0

5 votes

/**
 * Adds a cell to the row
 * @param row the row
 * @param cellStyle cell style
 * @param index index of the cell
 * @param value value to be added
 */
private static void addCell(HSSFRow row, HSSFCellStyle cellStyle, int index, String value) {
	HSSFCell cell = row.createCell(index);
	if (cellStyle != null) {
		cell.setCellStyle(cellStyle);
	}
	cell.setCellValue(new HSSFRichTextString(value));
}

Example 12

Source File: PoiUtil.java From dk-fitting with Apache License 2.0

5 votes

/**
 * 导出函数有三个参数，主内容数据，标题数组，到处文件名
 */
public void createxls(Object[][] messages, String[] titles, String filename) {
    try {
        FileOutputStream fos = new FileOutputStream( new File( filename ) );
        HSSFWorkbook book = new HSSFWorkbook();// 所有execl的父节点
        HSSFSheet sheet = book.createSheet( "sheet1" );//此处可以随意设置
        HSSFRow hssfrow = sheet.createRow( 0 );//创建首行标题
        HSSFCell hssfcell = null;
        for (int i = 0; i < titles.length; i++) {//创建标题栏目，也就是表格第一行
            hssfcell = hssfrow.createCell( i );
            hssfcell.setCellType( HSSFCell.ENCODING_UTF_16 );
            hssfcell.setCellValue( titles[i] );
        }

        System.out.println( "message:" + messages.length );
        for (int i = 0; i < messages.length; i++) {//添加表格中的内容
            hssfrow = sheet.createRow( i + 1 );//创建表格第二行，由于标记为0,这里设置为一，主要为了区别标题和内容
            Object[] obj = messages[i];
            for (int j = 0; j < obj.length; j++) {
                hssfcell = hssfrow.createCell( j );
                hssfcell.setCellType( HSSFCell.ENCODING_UTF_16 );//关于数据编码的问题
                hssfcell.setCellValue( obj[j] + "" );//转换为字符串的方式
                System.out.print( obj[j] + "\t" );
            }
            System.out.println();
        }
        book.write( fos );
        fos.flush();
        fos.close();
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println( e );
    }
}

Example 13

Source File: TestExternalRelvarXLS3.java From Rel with Apache License 2.0

5 votes

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 14

Source File: TestExternalRelvarXLS1.java From Rel with Apache License 2.0

5 votes

@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,7,8,9);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" autokey;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}

Example 15

Source File: XLSFileNormalizer.java From Knowage-Server with GNU Affero General Public License v3.0

5 votes

private void addColumnHeader(HSSFRow row){
	//Get all existing columns names
	List<String> columnsNames = new ArrayList<String>();
	int cells = row.getPhysicalNumberOfCells();
	this.setNumberOfColumns(cells);

	for (int c = 0; c < cells; c++) {
		//get single cell
		HSSFCell cell = row.getCell(c);
		
		String valueField = null;
		try {
			valueField = parseCell(cell);
			columnsNames.add(valueField);
			
			
		} catch(Throwable t) {
			throw new RuntimeException("Impossible to parse cell [" + c + "]", t);
		}
	}
	
	String newColumnName = "ref_"+levelName;
	
	//Check if exist already a column with the same name, otherwise generate a new name
	while( columnsNames.contains(newColumnName)){
		int count = 2;
		newColumnName = newColumnName+"_"+count;
		count++;
	}
	
	//Add a new column header
	HSSFCell newCell = row.createCell(cells);
	newCell.setCellValue(newColumnName);
	
	this.setNewColumnName(newColumnName);
}

Example 16

Source File: TestExternalRelvarXLS4.java From Rel with Apache License 2.0

5 votes

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 17

Source File: WordEmbedsTest.java From sun-wordtable-read with Apache License 2.0

5 votes

private static void listEmbeds2(XWPFDocument doc) throws Exception {
	for (final PackagePart pPart : doc.getAllEmbedds()) {
		final String contentType = pPart.getContentType();
		System.out.println(contentType + "\n");
		if (contentType.equals("application/vnd.ms-excel")) {
			final HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());

			for (int sheet = 0; sheet < embeddedWorkbook.getNumberOfSheets(); sheet++) {
				final HSSFSheet activeSheet = embeddedWorkbook.getSheetAt(sheet);
				if (activeSheet.getSheetName().equalsIgnoreCase("Sheet1")) {
					for (int rowIndex = activeSheet.getFirstRowNum(); rowIndex <= activeSheet
							.getLastRowNum(); rowIndex++) {
						final HSSFRow row = activeSheet.getRow(rowIndex);
						for (int cellIndex = row.getFirstCellNum(); cellIndex <= row
								.getLastCellNum(); cellIndex++) {
							final HSSFCell cell = row.getCell(cellIndex);
							if (cell != null) {
								if (cell.getCellType() == Cell.CELL_TYPE_STRING)
									System.out.println("Row:" + rowIndex + " Cell:" + cellIndex + " = "
											+ cell.getStringCellValue());
								if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
									System.out.println("Row:" + rowIndex + " Cell:" + cellIndex + " = "
											+ cell.getNumericCellValue());

									cell.setCellValue(cell.getNumericCellValue() * 2); // update
																						// the
																						// value
								}
							}
						}
					}
				}
			}
		}
	}
}

Example 18

Source File: MailTests.java From spring-boot-101 with Apache License 2.0

4 votes

@Test
public void sendMailWithExcel() throws IOException {
	String[] headers = {"col1","col2","col3"};
	// 声明一个工作薄
	HSSFWorkbook wb = new HSSFWorkbook();
	// 生成一个表格
	HSSFSheet sheet = wb.createSheet();
	HSSFRow row = sheet.createRow(0);
	for (int i = 0; i < headers.length; i++) {
		HSSFCell cell = row.createCell(i);
		cell.setCellValue(headers[i]);
	}
	int rowIndex = 1;

	for(int j=0; j<3; j++){
		row = sheet.createRow(rowIndex);
		rowIndex++;
		HSSFCell cell1 = row.createCell(0);
		cell1.setCellValue(j);
		cell1 = row.createCell(1);
		cell1.setCellValue(j+1);
		cell1 = row.createCell(2);
		cell1.setCellValue(j+2);
	}
	for (int i = 0; i < headers.length; i++) {
		sheet.autoSizeColumn(i);
	}

	ByteArrayOutputStream os = new ByteArrayOutputStream(1000);
	wb.write(os);
	wb.close();

	InputStreamSource iss = new ByteArrayResource(os.toByteArray());
	os.close();

	mailService.sendAttachmentsMail("[email protected]",
			"attachmentMail subject",
			"I have an attachment",
			iss, "abc1.xlsx");

}

Example 19

Source File: ReportStudentsUTLCandidates.java From fenixedu-academic with GNU Lesser General Public License v3.0

4 votes

protected void addHeaders(HSSFSheet sheet) {
    sheet.createRow(0);
    sheet.createRow(1);

    addHeaderCell(sheet, getHeaderInBundle("institutionCode"), 0);
    addHeaderCell(sheet, getHeaderInBundle("institutionName"), 1);
    addHeaderCell(sheet, getHeaderInBundle("candidacyNumber"), 2);
    addHeaderCell(sheet, getHeaderInBundle("studentNumberForPrint"), 3);
    addHeaderCell(sheet, getHeaderInBundle("studentName"), 4);
    addHeaderCell(sheet, getHeaderInBundle("documentTypeName"), 5);
    addHeaderCell(sheet, getHeaderInBundle("documentNumber"), 6);
    addHeaderCell(sheet, getHeaderInBundle("degreeCode"), 7);
    addHeaderCell(sheet, getHeaderInBundle("degreeName"), 8);
    addHeaderCell(sheet, getHeaderInBundle("degreeTypeName"), 9);
    addHeaderCell(sheet, getHeaderInBundle("code"), 10);
    addHeaderCell(sheet, getHeaderInBundle("countNumberOfDegreeChanges"), 11);
    addHeaderCell(sheet, getHeaderInBundle("hasMadeDegreeChange"), 12);
    addHeaderCell(sheet, getHeaderInBundle("firstEnrolmentOnCurrentExecutionYear"), 13);
    addHeaderCell(sheet, getHeaderInBundle("regime"), 14);
    addHeaderCell(sheet, getHeaderInBundle("code"), 15);

    HSSFRow row = sheet.getRow(0);
    HSSFCell cell = row.createCell(16);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies"));
    cell.setCellStyle(headerStyle);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 18));

    cell = sheet.getRow(1).createCell(16);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.year"));
    cell.setCellStyle(headerStyle);

    cell = sheet.getRow(1).createCell(17);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.count"));
    cell.setCellStyle(headerStyle);

    cell = sheet.getRow(1).createCell(18);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.integral.count"));
    cell.setCellStyle(headerStyle);

    addHeaderCell(sheet, getHeaderInBundle("numberOfDoneECTS"), 19);
    addHeaderCell(sheet, getHeaderInBundle("numberOfDegreeCurricularYears"), 20);
    addHeaderCell(sheet, getHeaderInBundle("curricularYearOneYearAgo"), 21);
    addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledEctsOneYearAgo"), 22);
    addHeaderCell(sheet, getHeaderInBundle("numberOfApprovedEctsOneYearAgo"), 23);
    addHeaderCell(sheet, getHeaderInBundle("curricularYearInCurrentYear"), 24);
    addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledECTS"), 25);
    addHeaderCell(sheet, getHeaderInBundle("gratuityAmount"), 26);
    addHeaderCell(sheet, getHeaderInBundle("numberOfMonthsExecutionYear"), 27);
    addHeaderCell(sheet, getHeaderInBundle("firstMonthOfPayment"), 28);
    addHeaderCell(sheet, getHeaderInBundle("ownerOfCETQualification"), 29);
    addHeaderCell(sheet, getHeaderInBundle("degreeQualificationOwner"), 30);
    addHeaderCell(sheet, getHeaderInBundle("masterQualificationOwner"), 31);
    addHeaderCell(sheet, getHeaderInBundle("phdQualificationOwner"), 32);
    addHeaderCell(sheet, getHeaderInBundle("ownerOfCollegeQualification"), 33);
    addHeaderCell(sheet, getHeaderInBundle("observations"), 34);
    addHeaderCell(sheet, getHeaderInBundle("lastEnrolledExecutionYear"), 35);
    addHeaderCell(sheet, getHeaderInBundle("nif"), 36);
    addHeaderCell(sheet, getHeaderInBundle("last.conclusion.academic.facts"), 37);
}

Example 20

Source File: ExcelUtil.java From util with Apache License 2.0

4 votes

/**
 * 根据条件，生成工作薄对象到内存。
 * @param sheetName 工作表对象名称
 * @param fieldName 首列列名称
 * @param data 数据
 * @return HSSFWorkbook
 */
private HSSFWorkbook makeWorkBook(String sheetName,String[] fieldName
		, List<Object[]> data){
	//用来记录最大列宽,自动调整列宽。
	Integer collength[]=new Integer[fieldName.length];
	
	// 产生工作薄对象
	HSSFWorkbook workbook = new HSSFWorkbook();
	// 产生工作表对象
	HSSFSheet sheet = workbook.createSheet();
	// 为了工作表能支持中文,设置字符集为UTF_16
	workbook.setSheetName(0, sheetName);
	// 产生一行
	HSSFRow row = sheet.createRow(0);
	// 产生单元格
	HSSFCell cell;
	// 写入各个字段的名称
	for (int i = 0; i < fieldName.length; i++) {
		// 创建第一行各个字段名称的单元格
		cell = row.createCell((short) i);
		// 设置单元格内容为字符串型
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		// 为了能在单元格中输入中文,设置字符集为UTF_16
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		// 给单元格内容赋值
		cell.setCellValue(new HSSFRichTextString(fieldName[i]));
		//初始化列宽
		collength[i]=fieldName[i].getBytes().length;
	}
	//临时单元格内容
	String tempCellContent="";
	// 写入各条记录,每条记录对应excel表中的一行
	for (int i = 0; i < data.size(); i++) {
		Object[] tmp = data.get(i);
		// 生成一行
		row = sheet.createRow(i + 1);
		for (int j = 0; j < tmp.length; j++) {
			cell = row.createCell((short) j);
			//设置单元格字符类型为String
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			tempCellContent=(tmp[j] == null) ? "" : tmp[j].toString();
			cell.setCellValue(new HSSFRichTextString(tempCellContent));
			
			//如果自动调整列宽度。
			if(autoColumnWidth){
				if(j>=collength.length){	// 标题列数小于数据列数时。
					collength=CollectionUtil.addObjectToArray(collength, tempCellContent.getBytes().length);
				}else{
					//如果这个内容的宽度大于之前最大的，就按照这个设置宽度。
					if(collength[j]<tempCellContent.getBytes().length){
						collength[j]=tempCellContent.getBytes().length;
					}
				}
			}
		}
	}
	
	//自动调整列宽度。
	if(autoColumnWidth){
		//调整列为这列文字对应的最大宽度。
		for(int i=0 ; i< fieldName.length ; i++){
			sheet.setColumnWidth(i,collength[i]*2*256);
		}
	}
	return workbook;
}