poi之Excel下载之详细设置

发布时间:2017-7-9 7:24:45编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"poi之Excel下载之详细设置 ",主要涉及到poi之Excel下载之详细设置 方面的内容,对于poi之Excel下载之详细设置 感兴趣的同学可以参考一下。

1、设置标题格式

       /**	 * HEAD样式	 * 	 * @param workbook	 * @param sheet	 */	public void setHeadCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {		headStyle = workbook.createCellStyle();		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);		HSSFFont font = workbook.createFont();		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);		font.setFontName("宋体");		font.setFontHeightInPoints((short) 16);// 设置字体大小		headStyle.setFont(font);	} 

  

2、设置列头样式

     /**	 * 列头样式	 * @param workbook	 * @param sheet	 */	public void setTitleCellStyles(HSSFWorkbook workbook, HSSFSheet sheet)         {		titleStyle = workbook.createCellStyle();		// 设置边框		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);		// 设置背景色		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);		// 设置居中		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);		// 设置字体		HSSFFont font = workbook.createFont();		font.setFontName("宋体");		font.setFontHeightInPoints((short) 11); // 设置字体大小		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示		titleStyle.setFont(font);// 选择需要用到的字体格式		// 设置自动换行		titleStyle.setWrapText(true);		// 设置列宽 ,第一个参数代表列id(从0开始),第2个参数代表宽度值		sheet.setColumnWidth(0, 7000);////		sheet.setColumnWidth(1, 7000);//		sheet.setColumnWidth(1, 4000);//		sheet.setColumnWidth(2, 4000);//		sheet.setColumnWidth(3, 4000);//		sheet.setColumnWidth(4, 7000);//		sheet.setColumnWidth(5, 7000);//		sheet.setColumnWidth(6, 4000);//		sheet.setColumnWidth(7, 4000);//		sheet.setColumnWidth(8, 4000);//		sheet.setColumnWidth(9, 4000);//		sheet.setColumnWidth(10, 4000);//}

  

3、设置数据样式

       /**	 * 数据样式	 * 	 * @param workbook	 * @param sheet	 */	public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {		dataStyle = workbook.createCellStyle();		// 设置边框		dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);		dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);		dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);		dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);		// 设置背景色		dataStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);		dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);		// 设置居中		dataStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);		// 设置字体		HSSFFont font = workbook.createFont();		font.setFontName("宋体");		font.setFontHeightInPoints((short) 11); // 设置字体大小		dataStyle.setFont(font);// 选择需要用到的字体格式		// 设置自动换行		dataStyle.setWrapText(true);	} 

  

4、创建隐藏页和数据域(省市区三级联动下拉框数据隐藏域设置)

        /**	 * 创建隐藏页和数据域(省市区三级联动下拉框数据隐藏域设置)	 * @param workbook	 * @param hideSheetName	 */	public void creatHideSheet(HSSFWorkbook workbook) {		/****************************************************** 创建省市区 ***************************************************/		HSSFSheet factoryAndModelSheet = workbook.createSheet("factoryAndModelSheet");// 隐藏一些信息		/*************************************************** 省-市 start ***************************************************/		//省份		List<Area> proviceList = 获得省份列表;		List<String> rowList = null;		String provinceid = "";		String provinceCity = "";		Name name;		for(int i=0;i<proviceList.size();i++){			HSSFRow pfModelRow = factoryAndModelSheet.createRow(i);			rowList = new ArrayList<String>();			provinceid = proviceList.get(i).getId().toString();			provinceCity = proviceList.get(i).getProvinceCity();			rowList.add(provinceCity + "_" + provinceid);			List<Area> cityList = 根据省份获得城市列表;			// 添加“省--->市” 名称			name = workbook.createName();			name.setNameName( provinceCity+ "_" + provinceid);			for(int j=0; j<cityList.size(); j++){				Area area = cityList.get(j);				rowList.add(area.getProvinceCity()+"_"+area.getId().toString());			}			this.creatRow(pfModelRow, rowList);			name.setRefersToFormula("factoryAndModelSheet!$B$" + (i + 1) + ":$"					+ this.getcellColumnFlag(cityList.size() + 1) + "$"					+ (i + 1));		}		name = workbook.createName();		name.setNameName("provice");		name.setRefersToFormula("factoryAndModelSheet!$A$1:$A$"+ proviceList.size());		/*************************************************** 省-市 end ***************************************************/		/*************************************************** 市-区 start ***************************************************/		//市		int sm = proviceList.size()+1;		List<String> cityNList = null;		List<Area> cityareaList = new LinkedList<Area>();		String cityid = "";		String cityCity = "";		//将所有的市加之cityareaList		for(int i=0;i<proviceList.size();i++){			provinceid = proviceList.get(i).getId().toString();			List<Area> cityList = 获得城市列表;			cityareaList.addAll(cityList);//加至List		}		for(int i=0;i<cityareaList.size();i++){			HSSFRow pfModelRow = factoryAndModelSheet.createRow(sm+i);			cityNList = new ArrayList<String>();			cityid = cityareaList.get(i).getId().toString();			cityCity = cityareaList.get(i).getProvinceCity();			cityNList.add(cityCity + "_" + cityid);			List<Area> cityList = 根据城市查询区域列表;			// 添加“市--->区” 名称			name = workbook.createName();			name.setNameName( cityCity+ "_" + cityid);			for(int j=0; j<cityList.size(); j++){				Area area = cityList.get(j);				cityNList.add(area.getProvinceCity()+"_"+area.getId().toString());			}			this.creatRow(pfModelRow, cityNList);			name.setRefersToFormula("factoryAndModelSheet!$B$" + (sm+i + 1) + ":$"					+ this.getcellColumnFlag(cityList.size() + 1) + "$"					+ (sm+i + 1));		}		name = workbook.createName();		name.setNameName("citycityname");		name.setRefersToFormula("factoryAndModelSheet!$A$"+sm+":$A$"+ cityareaList.size());		/*************************************************** 市-区 end ***************************************************/		// 设置隐藏页标志		workbook.setSheetHidden(workbook.getSheetIndex("factoryAndModelSheet"),true);}

  

5、创建标题和列头数据

/**	 * 创建标题应用列头	 * @param userinfosheet1	 * @param userName	 */	public void creatAppRowHead(HSSFSheet userinfosheet1, String headName) {		// 设置标题		HSSFRow rowHead = userinfosheet1.createRow(0);		userinfosheet1.addMergedRegion(new Region(0, (short) 0, 0, (short) 21));		POIUtils.createCell(rowHead, (short) 0, headName, headStyle);		rowHead.setHeight((short) (27 * 20));		// 设置表头		HSSFRow row = userinfosheet1.createRow(1);		HSSFCell merchantIdCell = row.createCell(0);		merchantIdCell.setCellValue("一");		merchantIdCell.setCellStyle(titleStyle);//		HSSFCell mrchtNameCell = row.createCell(1);//		mrchtNameCell.setCellValue("二");//		mrchtNameCell.setCellStyle(titleStyle);		HSSFCell merchantEnameCell = row.createCell(1);		merchantEnameCell.setCellValue("二");		merchantEnameCell.setCellStyle(titleStyle);		HSSFCell abbrCnameCell = row.createCell(2);		abbrCnameCell.setCellValue("三");		abbrCnameCell.setCellStyle(titleStyle);		HSSFCell abbrEnameCell = row.createCell(3);		abbrEnameCell.setCellValue("四");		abbrEnameCell.setCellStyle(titleStyle);		HSSFCell mccIdCell = row.createCell(4);		mccIdCell.setCellValue("五");		mccIdCell.setCellStyle(titleStyle);		HSSFCell addressCell = row.createCell(5);		addressCell.setCellValue("六");		addressCell.setCellStyle(titleStyle);		HSSFCell provinceCell = row.createCell(6);		provinceCell.setCellValue("七");		provinceCell.setCellStyle(titleStyle);		HSSFCell cityNoCell = row.createCell(7);		cityNoCell.setCellValue("八");		cityNoCell.setCellStyle(titleStyle);				HSSFCell zoneCell = row.createCell(8);		zoneCell.setCellValue("九");		zoneCell.setCellStyle(titleStyle);				HSSFCell telephoneCell = row.createCell(9);		telephoneCell.setCellValue("十");		telephoneCell.setCellStyle(titleStyle);				HSSFCell managerCell = row.createCell(10);		managerCell.setCellValue("十一");		managerCell.setCellStyle(titleStyle);}

  

6、设置下拉框

/**	 * 添加下拉框限制		 * @param sheet1	 */	public void createSelectValidate(HSSFSheet sheet1) {						//省,第7列		DVConstraint constraintPosType = DVConstraint.createFormulaListConstraint("provice");		// 作用域:起始行、终止行、起始列、终止列		CellRangeAddressList regionsPosType = new CellRangeAddressList(2, 499,6, 6);		DataValidation data_validation_PosType = new HSSFDataValidation(regionsPosType, constraintPosType);		sheet1.addValidationData(data_validation_PosType);				//市,第8列		DVConstraint constraintcityNo = DVConstraint.createFormulaListConstraint("INDIRECT($G$3:$G$500)");		// 作用域:起始行、终止行、起始列、终止列		CellRangeAddressList regionscityNo = new CellRangeAddressList(2, 499,7, 7);		DataValidation data_validation_cityNo = new HSSFDataValidation(regionscityNo, constraintcityNo);		sheet1.addValidationData(data_validation_cityNo);				//区,第9列		DVConstraint constraintmanager = DVConstraint.createFormulaListConstraint("INDIRECT($H$3:$H$500)");		// 作用域:起始行、终止行、起始列、终止列		CellRangeAddressList regionsmanager = new CellRangeAddressList(2, 499,8, 8);		DataValidation data_validation_manager = new HSSFDataValidation(regionsmanager, constraintmanager);		sheet1.addValidationData(data_validation_manager);		}

  

7、工具方法-创建一列数据

        /**	 * 创建一列数据	 * 	 * @param currentRow	 * @param textList	 */	public void creatRow(HSSFRow currentRow, List<String> textList) {		if (textList != null && textList.size() > 0) {			int i = 0;			for (String cellValue : textList) {				HSSFCell userNameLableCell = currentRow.createCell(i++);				userNameLableCell.setCellValue(cellValue);			}		}	}		// 根据数据值确定单元格位置(比如:28-AB)	private String getcellColumnFlag(int num) {		String columFiled = "";		int chuNum = 0;		int yuNum = 0;		if (num >= 1 && num <= 26) {			columFiled = this.doHandle(num);		} else {			chuNum = num / 26;			yuNum = num % 26;			columFiled += this.doHandle(chuNum);			columFiled += this.doHandle(yuNum);		}		return columFiled;	}	private String doHandle(final int num) {		String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",				"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",				"W", "X", "Y", "Z" };		return charArr[num - 1].toString();	}


上一篇:【构造】Gym - 101411F - Figure ans Spots
下一篇:小试cordova

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款