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

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

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

private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    cell.setCellType(CellType.STRING);
    if (cell.getStringCellValue() != null) {
	return cell.getStringCellValue().trim();
    }
}
return null;
   }
 

/**
 * 替换Excel模板文件内容
 *
 * @param datas
 *            文档数据
 * @param sourceFilePath
 *            Excel模板文件路径
 * @param targetFilePath
 *            Excel生成文件路径
 */
public static boolean replaceModel(List<ExcelReplaceDataVO> datas,
		String sourceFilePath, String targetFilePath) {
	boolean bool = true;
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				sourceFilePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		for (ExcelReplaceDataVO data : datas) {
			// 获取单元格内容
			HSSFRow row = sheet.getRow(data.getRow());
			HSSFCell cell = row.getCell((short) data.getColumn());
			String str = cell.getStringCellValue();
			// 替换单元格内容
			str = str.replace(data.getKey(), data.getValue());
			// 写入单元格内容
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(str);
		}
		// 输出文件
		FileOutputStream fileOut = new FileOutputStream(targetFilePath);
		wb.write(fileOut);
		fileOut.close();
	} catch (Exception e) {
		bool = false;
		e.printStackTrace();
	}
	return bool;
}
 

public static String getCellStringValue(HSSFCell cell) {
	String cellValue = "";
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING:
		cellValue = cell.getStringCellValue();
		if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) {
			cellValue = " ";
		}
		break;
	case HSSFCell.CELL_TYPE_NUMERIC:
		// cellValue = String.valueOf(cell.getNumericCellValue());
		DecimalFormat formatter = new DecimalFormat("######");
		cellValue = formatter.format(cell.getNumericCellValue());
		break;
	case HSSFCell.CELL_TYPE_FORMULA:
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cellValue = String.valueOf(cell.getNumericCellValue());
		break;
	case HSSFCell.CELL_TYPE_BLANK:
		cellValue = " ";
		break;
	case HSSFCell.CELL_TYPE_BOOLEAN:
		break;
	case HSSFCell.CELL_TYPE_ERROR:
		break;
	default:
		break;
	}
	return cellValue;
}
 

/**
 * 设置单元格
 * 
 * @param index
 *            列号
 * @param value
 *            单元格填充值
 */
public void setCell(int index, double value) {
	HSSFCell cell = this.row.createCell((short) index);
	cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
	cell.setCellValue(value);
	HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
	HSSFDataFormat format = workbook.createDataFormat();
	cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
	cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}
 

private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    try {
	cell.setCellType(CellType.STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return null;
	}
	// log.debug("string cell value: '"+cell.getStringCellValue().trim()+"'");
	return cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(CellType.NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	// log.debug("numeric cell value: '"+d+"'");
	return (new Long(new Double(d).longValue()).toString());
    }
}
return null;
   }
 

/**
 * 导出函数有三个参数，主内容数据，标题数组，到处文件名
 */
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 );
    }
}
 

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

/**
 * 根据条件，生成工作薄对象到内存。
 * @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 createDetailCell(int column, Object element) {
	HSSFCell cell = detailRow.createCell(column);
	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(new HSSFRichTextString(element.toString()));
}
 

protected void setDateCell(HSSFCell cell, Date value, HSSFWorkbook workbook) 
    {
//        double excelDateValue = HSSFDateUtil.getExcelDate(value);
//        cell.setCellValue(excelDateValue);
//        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        long timeMillis = value.getTime();
        cell.setCellValue( (double)timeMillis );
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(this.dateCellStyle);
        
//      System.out.println(HSSFDataFormat.getBuiltinFormats());
        // TODO Find out correct cell styles for date objects
//        HSSFCellStyle cellStyleDate = workbook.createCellStyle();
//        cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
//
//        HSSFCellStyle cellStyleDateTimeWithSeconds = workbook.createCellStyle();
//        cellStyleDateTimeWithSeconds.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
//
//        HSSFDataFormat dataFormat = workbook.createDataFormat();
//        HSSFCellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setDataFormat(dataFormat.getFormat("dd/mm/yyyy hh:mm:ss"));
//
//        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
//        SimpleDateFormat formatter2 = new SimpleDateFormat("dd/MM/yyyy");
//        SimpleDateFormat formatter3 = new SimpleDateFormat("HH:mm:ss.SSS");
//
//        
//        Date dateValue = (Date)value;
//        Calendar cal = null;
//        
//        // If it is a date value that does not have seconds
//        if(dateValue.getTime() % 60000 == 0){
////            cellStyle = cellStyleDate;
//            cal=Calendar.getInstance();
//            cal.setTimeInMillis(dateValue.getTime());
//
//            cell.setCellValue(cal);
//            cell.setCellStyle(cellStyle);
////            cell.setCellValue(cal);
//        }
//        else {
////            HSSFDataFormatter formatter = new HSSFDataFormatter();
//            
//            // If we have seconds assume that it is only h:mm:ss without date
//            // TODO Clean implementation where user can control date formats would be nice
////            double dateDouble = dateValue.getTime() % (24*60*60*1000);
//            cal = get1900Cal(dateValue);
//            
//            String formatted = formatter3.format(dateValue);
//            //TODO Format ...
////            cellStyle = cellStyleDateTimeWithSeconds;
//            System.out.println("date formatted:"+formatted);
////            HSSFRichTextString s = new HSSFRichTextString(formatted);
////            cell.setCellValue(s);
//            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//            cell.setCellValue((double)dateValue.getTime());
//            cell.setCellStyle(cellStyleDateTimeWithSeconds);
//        }

    }
 

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

/**
 * 设置单元格
 * 
 * @param index
 *            列号
 * @param value
 *            单元格填充值
 */
@SuppressWarnings("deprecation")
public void setCell(int index, String value) {
	HSSFCell cell = this.row.createCell((short) index);
	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(value);
}
 

/**
 * 设置单元格
 * 
 * @param index
 *            列号
 * @param value
 *            单元格填充值
 */
public void setCell(int index, int value) {
	HSSFCell cell = this.row.createCell((short) index);
	cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
	cell.setCellValue(value);
}
 

/**
 * Convenient method to set a String as text content in a cell.
 * @param cell the cell in which the text must be put
 * @param text the text to put in the cell
 */
protected void setText(HSSFCell cell, String text) {
	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(text);
}
 

/**
 * Convenient method to set a String as text content in a cell.
 * @param cell the cell in which the text must be put
 * @param text the text to put in the cell
 */
protected void setText(HSSFCell cell, String text) {
	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(text);
}