Java Code Examples for org.apache.poi.ss.usermodel.Sheet#autoSizeColumn()

/**
 * 设置每列宽度
 *
 * @param colMaxWidthMap 列最大宽度Map
 * @param sheet          sheet
 * @param maxColIndex    最大列索引
 */
protected void setColWidth(Map<Integer, Integer> colMaxWidthMap, Sheet sheet, int maxColIndex) {
    if (WidthStrategy.isAutoWidth(widthStrategy)) {
        if (sheet instanceof SXSSFSheet) {
            throw new UnsupportedOperationException("SXSSF does not support automatic width at this time");
        }
        for (int i = 0; i <= maxColIndex; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    colMaxWidthMap.forEach((key, value) -> {
        int contentLength = value << 1;
        if (contentLength > 255) {
            contentLength = 255;
        }
        sheet.setColumnWidth(key, contentLength << 8);
    });
}
 

private void buildAutoColumnSize(SheetData sdata){
	Sheet sheet = sdata.getSheet();
	if(sheet.getPhysicalNumberOfRows()>0 && sdata.getSheetModel().isAutoSizeColumn()){
		int cellCount = sheet.getRow(0).getPhysicalNumberOfCells();
		boolean useMerged = sdata.getSheetModel().isUseMergedCells();
		for (int i = 0; i < cellCount; i++) {
			sheet.autoSizeColumn(i, useMerged);
		}
	}else{
		Map<Short, Boolean> columSize = sdata.getSheetModel().getAutoSizeColumnMap();
		if(ExcelUtils.isEmpty(columSize))
			return ;
		for(Entry<Short, Boolean> entry : columSize.entrySet()){
			sheet.autoSizeColumn(entry.getKey(), entry.getValue());
		}
	}
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet			= (cfSpreadSheetData)parameters.get(1);
String column		= parameters.get(0).getString();
	
// Not a single number; lets try the string method
Sheet	sheet = spreadsheet.getActiveSheet();
Set<Integer>	numbers	= tagUtils.getNumberSet( column );
Iterator<Integer> it	= numbers.iterator();
while ( it.hasNext() ){
	sheet.autoSizeColumn( it.next() - 1 );
}

	return cfBooleanData.TRUE;
}
 

private static Row createHeaders(Map<String, String> headerMap,
		Sheet sheet) {
	CellStyle style = sheet.getWorkbook().createCellStyle();
	style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

	Row header = sheet.createRow(0);
	int columnIndex = 0;
	for (Map.Entry<String, String> entry : headerMap.entrySet()) {
		String headerText = StringUtils.defaultString(entry.getValue());
		Cell cell = createCell(header, columnIndex);
		cell.setCellValue(headerText);
		cell.setCellStyle(style);
		sheet.autoSizeColumn(columnIndex);
		columnIndex++;
	}

	return header;
}
 

private void adjustColumnWidth(Sheet sheet, CellPosition end) {
    for (int i = getStartColumn(); i < autoSizeColumn.size(); i++) {
        if (autoSizeColumn.get(i) == 0) {
            sheet.autoSizeColumn(i, true);
        } else {
            sheet.setColumnWidth(i, autoSizeColumn.get(i) * 256);
        }
    }
}
 

@Override
public void accept(final Sheet sheet) {
    sheet.autoSizeColumn(this.value, this.ismerged);
}
 

@Override
protected void buildExcelDocument(Map<String, Object> model,
		Workbook workbook, HttpServletRequest request,
		HttpServletResponse response) throws Exception {
	
	Sheet sheet = workbook.createSheet("sheet 1");
	
	List<HrmsLogin> users = (List<HrmsLogin>) model.get("allUsers");
	
	Row row = null;
	Cell cell = null;
	int r = 0;
	int c = 0;
	
	//Style for header cell
	CellStyle style = workbook.createCellStyle();
	style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	
	//Create header cells
	row = sheet.createRow(r++);
	
	cell = row.createCell(c++);
	cell.setCellStyle(style);
	cell.setCellValue("Employee ID");
	
	cell = row.createCell(c++);
	cell.setCellStyle(style);
	cell.setCellValue("Username");
	
	cell = row.createCell(c++);
	cell.setCellStyle(style);
	cell.setCellValue("Password");
	
	cell = row.createCell(c++);
	cell.setCellStyle(style);
	cell.setCellValue("Role");
	
	
	//Create data cell
	for(HrmsLogin user: users){
		row = sheet.createRow(r++);
		c = 0;
		row.createCell(c++).setCellValue(user.getHrmsEmployeeDetails().getEmpId());
		row.createCell(c++).setCellValue(user.getUsername());
		row.createCell(c++).setCellValue(user.getPassword());
		row.createCell(c++).setCellValue(user.getRole());

	}
	for(int i = 0 ; i < 4; i++)
		sheet.autoSizeColumn(i, true);
	
}
 

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());
	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}
 

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());
	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}