Java Code Examples for org.apache.poi.hssf.usermodel.HSSFRow#createCell()

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 );
    }
}
 

@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));
		}
	}
}
 

@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));
        }
    }
}
 

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

@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++;
		}
	}		
}
 

@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_remove;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

@Override
protected void writeTableHeader(final ERDiagram diagram, final ERTable table) {
    final String sheetName = table.getPhysicalName();
    sheet = workbook.createSheet(sheetName);

    rowNum = 0;
    final HSSFRow row = sheet.createRow(rowNum++);

    int col = 0;

    for (final NormalColumn column : table.getExpandedColumns()) {
        final HSSFCell cell = row.createCell(col++);
        cell.setCellValue(new HSSFRichTextString(column.getPhysicalName()));
    }
}
 

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);
}
 

@Override
protected void writeRepeatTestData(ERTable table,
		RepeatTestData repeatTestData, String database) {

	for (int i = 0; i < repeatTestData.getTestDataNum(); i++) {
		HSSFRow row = this.sheet.createRow(this.rowNum++);

		int col = 0;

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

			RepeatTestDataDef repeatTestDataDef = repeatTestData
					.getDataDef(column);

			String value = this.getMergedRepeatTestDataValue(i,
					repeatTestDataDef, column);

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

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

}
 

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);
}
 

private void copyRow(Row inTitleRow, HSSFRow outRow) {
    // TODO copy formatting
    Iterator<Cell> it = inTitleRow.cellIterator();
    while (it.hasNext()) {
        Cell srcCell = it.next();
        outRow.createCell(srcCell.getColumnIndex(), srcCell.getCellType());
        outRow.getCell(srcCell.getColumnIndex()).setCellValue(srcCell.getStringCellValue());
    }
}
 

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);
			}
		}
	}
}
 

public static HSSFCell getCellFromReference(HSSFSheet templateSheet, int colIndex, int rowIndex) {
    HSSFRow row = templateSheet.getRow(rowIndex);
    row = row == null ? templateSheet.createRow(rowIndex) : row;
    HSSFCell cell = row.getCell(colIndex);
    cell = cell == null ? row.createCell(colIndex) : cell;
    return cell;
}
 

protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) {
    HSSFRow row = sheet.getRow(0);
    HSSFCell cell = row.createCell(columnNumber);

    cell.setCellValue(value);
    cell.setCellStyle(headerStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));
}
 

/**
 * 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));
}
 

/**
 * 根据条件，生成工作薄对象到内存。
 * @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;
}
 

@Override
protected void writeRepeatTestData(final ERTable table, final RepeatTestData repeatTestData, final String database) {

    for (int i = 0; i < repeatTestData.getTestDataNum(); i++) {
        final HSSFRow row = sheet.createRow(rowNum++);

        int col = 0;

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

            final RepeatTestDataDef repeatTestDataDef = repeatTestData.getDataDef(column);

            final String value = getMergedRepeatTestDataValue(i, repeatTestDataDef, column);

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

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

}
 

protected void addCellValue(HSSFRow row, String value, int cellNumber) {
    HSSFCell cell = row.createCell(cellNumber);
    cell.setCellValue(value);
}
 

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);
}
 

public ExcelCell ( final HSSFRow row, final int column, final HSSFCellStyle dateFormat )
{
    this.cell = row.createCell ( column );

    this.dateFormat = dateFormat;
}