Java Code Examples for org.apache.poi.hssf.usermodel.HSSFSheet#setColumnWidth()

private ClientAnchorDetail fitImageToColumns(HSSFSheet sheet, int colNumber, double reqImageWidthMM, int resizeBehaviour) {
    double colWidthMM;
    double colCoordinatesPerMM;
    int pictureWidthCoordinates;
    ClientAnchorDetail colClientAnchorDetail = null;

    colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short)sheet.getColumnWidth(colNumber));

    if (colWidthMM < reqImageWidthMM) {
        if (resizeBehaviour == EXPAND_COLUMN || resizeBehaviour == EXPAND_ROW_AND_COLUMN) {
        	sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM));
        	colWidthMM = reqImageWidthMM;
            colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
            pictureWidthCoordinates = (int)(reqImageWidthMM * colCoordinatesPerMM);
            colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
        } else if (resizeBehaviour == OVERLAY_ROW_AND_COLUMN || resizeBehaviour == EXPAND_ROW) {
        	colClientAnchorDetail = calculateColumnLocation(sheet, colNumber, reqImageWidthMM);
        }
    } else {
    	colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM;
        pictureWidthCoordinates = (int)(reqImageWidthMM * colCoordinatesPerMM);
        colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates);
    }
    return(colClientAnchorDetail);
}
 

@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版本的单元格颜色");
}
 

/**
 * 自动重置列宽
 * @param sheet
 * @param columnJson
 */
void autoSheetWidth(HSSFSheet sheet){
	Set<String> keySet = getColumnJson().keySet();
	int i = 0;
	for (String key : keySet) {
		Integer width = columnMaxValue.get(key);
		if (width!=null&&width>0) {
			width+=6;
			width*=256;
			sheet.setColumnWidth(i, width);
		}
		i++;
		
	}
   }
 

/**
 * @方法名 copySheet
 * @功能 复制sheet
 * @参数 @param sheetFrom
 * @参数 @param sheetTo
 * @参数 @return
 * @返回 HSSFSheet
 * @author Administrator
 * @throws
 */
@SuppressWarnings("deprecation")
private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet 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;
	
}
 

/**
 * Determines whether the sheets columns should be re-sized to accomodate
 * the image, adjusts the columns width if necessary and creates then
 * returns a ClientAnchorDetail object that facilitates construction of
 * an HSSFClientAnchor that will fix the image on the sheet and establish
 * it's size.
 *
 * @param sheet A reference to the sheet that will 'contain' the image.
 * @param colNumber A primtive int that contains the index number of a
 *                  column on the sheet.
 * @param reqImageWidthMM A primtive double that contains the required
 *                        width of the image in millimetres
 * @param resizeBehaviour A primitve int whose value will indicate how the
 *                        width of the column should be adjusted if the
 *                        required width of the image is greater than the
 *                        width of the column.
 * @return An instance of the ClientAnchorDetail class that will contain
 *         the index number of the column containing the cell whose top
 *         left hand corner also defines the top left hand corner of the
 *         image, the index number column containing the cell whose top
 *         left hand corner also defines the bottom right hand corner of
 *         the image and an inset that determines how far the right hand
 *         edge of the image can protrude into the next column - expressed
 *         as a specific number of co-ordinate positions.
 */
private ClientAnchorDetail fitImageToColumns(HSSFSheet sheet, int colNumber,
        double reqImageWidthMM, int resizeBehaviour) {

    double colWidthMM;
    double colCoordinatesPerMM;
    int pictureWidthCoordinates;
    ClientAnchorDetail colClientAnchorDetail = null;

    // Get the colum's width in millimetres
    colWidthMM = ConvertImageUnits.widthUnits2Millimetres(
            (short)sheet.getColumnWidth(colNumber));

    // Check that the column's width will accomodate the image at the
    // required dimension. If the width of the column is LESS than the
    // required width of the image, decide how the application should
    // respond - resize the column or overlay the image across one or more
    // columns.
    if(colWidthMM < reqImageWidthMM) {

        // Should the column's width simply be expanded?
        if((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN) ||
           (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) {
            // Set the width of the column by converting the required image
            // width from millimetres into Excel's column width units.
            sheet.setColumnWidth(colNumber,
                    ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM));
            // To make the image occupy the full width of the column, convert
            // the required width of the image into co-ordinates. This value
            // will become the inset for the ClientAnchorDetail class that
            // is then instantiated.
            colWidthMM = reqImageWidthMM;
            colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS /
                colWidthMM;
            pictureWidthCoordinates = (int)(reqImageWidthMM * colCoordinatesPerMM);
            colClientAnchorDetail = new ClientAnchorDetail(colNumber,
                    colNumber, pictureWidthCoordinates);
        }
        // If the user has chosen to overlay both rows and columns or just
        // to expand ONLY the size of the rows, then calculate how to lay
        // the image out across one or more columns.
        else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) ||
                 (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) {
            colClientAnchorDetail = this.calculateColumnLocation(sheet,
                    colNumber, reqImageWidthMM);
        }
    }
    // If the column is wider than the image.
    else {
        // Mow many co-ordinate positions are there per millimetre?
        colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS /
                colWidthMM;
        // Given the width of the image, what should be it's co-ordinate?
        pictureWidthCoordinates = (int)(reqImageWidthMM * colCoordinatesPerMM);
        colClientAnchorDetail = new ClientAnchorDetail(colNumber,
                colNumber, pictureWidthCoordinates);
    }
    return(colClientAnchorDetail);
}
 

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