POI复杂EXCEL导出

对于excel导出一直懵懵懂懂，今日招投标部门提出了一个复杂的excel导出格式，就学习了一下，记录一下成果，
excel导出的表头是动态的，合并单元格也是动态不固定的

public static HSSFWorkbook exportProjFix(Map<String,List<JSONArray>> titleList,JSONArray itemData,
			JSONArray footerData,ProjFixEntity query) throws Exception {
		
		// 声明一个工作簿
		HSSFWorkbook wb = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = wb.createSheet("定标审批信息");

		// 生成一种样式:标题样式
		HSSFCellStyle style = wb.createCellStyle();
		// 设置样式
		style.setFillForegroundColor(HSSFColor.WHITE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		// 生成一种字体
		HSSFFont font = wb.createFont();
		// 设置字体
		font.setFontName("微软雅黑");
		// 设置字体大小
		font.setFontHeightInPoints((short) 10);
		// 字体加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 在样式中引用这种字体
		style.setFont(font);

		// 生成并设置另一个样式:文本样式
		HSSFCellStyle style1 = wb.createCellStyle();
		style1.setFillForegroundColor(HSSFColor.WHITE.index);
		style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style1.setWrapText(true);
		// 生成另一种字体2
		HSSFFont font1 = wb.createFont();
		// 设置字体
		font1.setFontName("微软雅黑");
		// 设置字体大小
		font1.setFontHeightInPoints((short) 10);
		// 字体加粗
		// font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 在样式2中引用这种字体
		style1.setFont(font1);
		
		// 生成并设置另一个样式:文本样式
		HSSFCellStyle style2 = wb.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.WHITE.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style2.setWrapText(true);
		// 生成另一种字体2
		HSSFFont font2 = wb.createFont();
		// 设置字体
		font2.setFontName("微软雅黑");
		// 设置字体大小
		font2.setFontHeightInPoints((short) 10);
		// 字体加粗
		// font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 在样式2中引用这种字体
		style2.setFont(font2);
		
		
		//列表内容
		/**
		 * 第九行 数据表格标题
		 */
		//数据表格合并单元格
		List<String> headnum0 = new ArrayList<String>();//合并单元格
		int widh = 0;//第二个单元格宽度
		int allSize = 0;//数据列数
		int[] widthArr = null;
		int length1 = 0;
		/**=========邀请招标流程============**/
		if(!query.getProcess().equals("inquiry")){
			JSONArray list = (JSONArray) titleList.get("1");
			Map<String, String> titleHeader = new HashMap<String, String>();//报价格式动态表头
			Map<String, Integer> titlefield = new HashMap<String, Integer>();//数据行总排序号
			Map<String, String> titleDataFormat = new HashMap<String, String>();
			int j=0;
			for(int i=0,arrSize = list.size();i<arrSize;i++){
				JSONObject json =list.getJSONObject(i);
				String title = (String)json.get("title");
				String field = (String)json.get("field");
				if (field.equals("id")) {
					continue;
				}
				JSONObject editor = (JSONObject)json.getJSONObject("editor");
				titleHeader.put(field, title);
				titlefield.put(field,j);
				j++;
				if(!editor.isNullObject()){
					titleDataFormat.put(field, editor.getString("type"));
				}
				
			}
			HSSFRow row = sheet.createRow(11);
			widthArr= new int[titlefield.size()];
			for(String field:titlefield.keySet()){
				Integer seq = titlefield.get(field);
				String title = titleHeader.get(field);
				
				HSSFCell cell = row.createCell(seq);
				cell.setCellValue(title);
				cell.setCellStyle(style);
			//	sheet.autoSizeColumn(seq, true);// 根据字段长度自动调整列的宽度
			//	widthArr[seq] = sheet.getColumnWidth(seq);
			}
			//第十一行开始： 数据列表
			int itemSize = itemData.size();
			for(int i=0;i<itemSize;i++){
				row = sheet.createRow(12+i);
				JSONObject json =itemData.getJSONObject(i);
				for(String field:titlefield.keySet()){
					Integer seq = titlefield.get(field);
					String dataString = json.getString(field);
					String dataFormat = titleDataFormat.get(field);
					
					HSSFCell cell = row.createCell(seq);
					
					if(dataFormat!=null&&dataFormat.equals("numberbox")){
						if(dataString!=null){
							Double value = Double.parseDouble(dataString);
							if(value==0){
								cell.setCellValue("");
								cell.setCellStyle(style1);
							}else{
								cell.setCellValue(value);
								style2.setDataFormat(HSSFDataFormat
										.getBuiltinFormat("0.000000"));
								cell.setCellStyle(style1);
							}
						}else{
							cell.setCellValue("");
							cell.setCellStyle(style1);
						}
						
					}else{
						style2.setDataFormat(HSSFDataFormat
								.getBuiltinFormat("General"));
						cell.setCellValue(dataString);
						cell.setCellStyle(style1);
					}
				//	sheet.autoSizeColumn(seq, true);// 自动调整宽度
				}
			}
			allSize = titlefield.size();//用户计算表头数据需要合并的单元格数
		}else{
			/**=========询比价流程============**/
			//先计算数据表格的列数算出标题需要合并的单元格
			List<JSONArray> list1 = titleList.get("1");
			List<JSONArray> list2 = titleList.get("2");
			List<JSONArray> list3 = titleList.get("3");
			Map<String, String> titleHeader1 = new HashMap<String, String>();//报价格式动态表头
			Map<String, String> titleHeader2 = new HashMap<String, String>();//供应商报价和总价 动态表头
			Map<String, String> titleHeader3 = new HashMap<String, String>();//供应商名称 动态表头
			Map<String, Integer> titlefield = new HashMap<String, Integer>();//数据行总排序号
			Map<String, Integer> titlefield1 = new HashMap<String, Integer>();//标题行第一行序号
			Map<String, Integer> titlefield2 = new HashMap<String, Integer>();//标题行第二行排序号
			Map<String, String> titleDataFormat = new HashMap<String, String>();
			for(JSONArray arr1:list1){
				for(int i=0,arrSize = arr1.size();i<arrSize;i++){
					JSONObject json1 =arr1.getJSONObject(i);
					String title = (String)json1.get("title");
					String field = (String)json1.get("field");
					titleHeader1.put(field, title);
					titlefield1.put(field,i);
				}
			}
			titlefield.putAll(titlefield1);
			length1 = titleHeader1.size();
			for(JSONArray arr1:list2){
				for(int i=0,arrSize = arr1.size();i<arrSize+length1;i++){
					if(i<length1){
						titlefield2.put(String.valueOf(i), i);
						titleHeader2.put(String.valueOf(i),"");
					}else{
						JSONObject json1 =arr1.getJSONObject(i-length1);
						String title = (String)json1.get("title");
						String field = (String)json1.get("field");
						String dataFormat = (String)json1.get("dataFormat");
						titleHeader2.put(field, title);
						titlefield2.put(field, i);
						titlefield.put(field, i);
						titleDataFormat.put(field, dataFormat);
					}
					
				}
			}
			for(JSONArray arr1:list3){
				for(int i=0,size =arr1.size();i<size;i++){
					JSONObject json1 =arr1.getJSONObject(i);
					String title = (String)json1.get("title");
					String field = (String)json1.get("field");
					titleHeader3.put(field, title);
					titlefield1.put(field,i*2+length1);
					
					titleHeader3.put(String.valueOf(i*2+1+length1), "");
					titlefield1.put(String.valueOf(i*2+1+length1),i*2+1+length1);
				}
			}
			/**
			 * 先根据数据表格计算列宽，后创建表头固定字段
			 */
			
			//第十行表头
			HSSFRow row = sheet.createRow(9);
			int itemSize = itemData.size();
			// "0,2,0,0" ===> “起始行，截止行，起始列，截止列” 动态表头合并单元格
			for(int i=0;i<length1;i++){
				headnum0.add("9,10,"+i+","+i);
			}
			for(int i=0;i<titleHeader3.size();i++){
				headnum0.add("9,9," + (length1 + i * 2) + "," + (length1 + 1 + i * 2));
			}
			//表尾合并单元格
			if(footerData!=null){
				for(int i=0,footerSize = footerData.size();i<footerSize;i++){
					headnum0.add((11+i+itemSize)+","+(11+i+itemSize)+",0,"+length1);
				}
			}
			
			titleHeader1.putAll(titleHeader3);
			//第十行表头
			for(String field:titlefield1.keySet()){
				Integer seq = titlefield1.get(field);
				String title = titleHeader1.get(field);
				
				HSSFCell cell = row.createCell(seq);
				cell.setCellValue(title);
				cell.setCellStyle(style);
			//	sheet.autoSizeColumn(seq, true);// 根据字段长度自动调整列的宽度
			}
			// 第十一行表头
			row = sheet.createRow(10);
			for (String field:titlefield2.keySet()) {
				Integer seq = titlefield2.get(field);
				String title = titleHeader2.get(field);
				
				if(seq<length1){
					HSSFCell cell = row.createCell(seq);
					cell.setCellValue("");
					cell.setCellStyle(style);
				}else{
					HSSFCell cell = row.createCell(seq);
					cell.setCellValue(title);
					cell.setCellStyle(style);
				}
			//	sheet.autoSizeColumn(seq, true);// 自动调整宽度
			}
			
			//第十一行开始： 数据列表
			widthArr= new int[titlefield.size()];
			for(int i=0;i<itemSize;i++){
				row = sheet.createRow(11+i);
				JSONObject json =itemData.getJSONObject(i);
				for(String field:titlefield.keySet()){
					Integer seq = titlefield.get(field);
					String dataString = json.getString(field);
					String dataFormat = titleDataFormat.get(field);
					
					HSSFCell cell = row.createCell(seq);
					if(dataFormat!=null&&dataFormat.equals(ExcelHelper.DATAFORMAT_DOUBLE)){

						Double value = Double.parseDouble(dataString);
						cell.setCellValue(value);
						style2.setDataFormat(HSSFDataFormat
								.getBuiltinFormat("0.000000"));
						cell.setCellStyle(style1);
					}else{
						style2.setDataFormat(HSSFDataFormat
								.getBuiltinFormat("General"));
						cell.setCellValue(dataString);
						cell.setCellStyle(style1);
					}
				//	sheet.autoSizeColumn(seq, true);// 自动调整宽度
				//	widthArr[seq] = sheet.getColumnWidth(seq);
				}
			}
		//表尾数据
			if(footerData!=null){
				for(int i=0,footerSize = footerData.size();i<footerSize;i++){
					row = sheet.createRow(11+itemSize+i);
					JSONObject json =footerData.getJSONObject(i);
					for(String field:titlefield.keySet()){
						Integer seq = titlefield.get(field);
						String dataString = json.getString(field);
						if(field.equals("itemName")){
							HSSFCell cell = row.createCell(seq);
							style2.setDataFormat(HSSFDataFormat
									.getBuiltinFormat("General"));
							cell.setCellValue(dataString);
							cell.setCellStyle(style);
						}else{
							if(dataString==null||dataString.equals("")||Double.parseDouble(dataString)==0){
								HSSFCell cell = row.createCell(seq);
								style2.setDataFormat(HSSFDataFormat
										.getBuiltinFormat("General"));
								cell.setCellValue("");
								cell.setCellStyle(style1);
							}else{
								Double value = Double.parseDouble(dataString);
								HSSFCell cell = row.createCell(seq);
								cell.setCellValue(value);
								style2.setDataFormat(HSSFDataFormat
										.getBuiltinFormat("0.000000"));
								cell.setCellStyle(style1);
							}
							
						}
					//	sheet.autoSizeColumn(seq, true);// 自动调整宽度
					}
				}
			}
			allSize = titlefield.size();//用户计算表头数据需要合并的单元格数
		}
		
		
		/****************************************表单数据 begin***************************************************/	
		 
		HSSFRow row = sheet.createRow(0);
		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(0);
				cell.setCellValue(query.getName()+" "+"招标汇总");
				cell.setCellStyle(style);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add("0,0,0,"+(allSize-1));
		/**
		 * 第二行 
		 */
		row = sheet.createRow(1);
		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("项目简述");
				cell.setCellStyle(style);
			}else if(i==1){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(query.getFixSummary());
				cell.setCellStyle(style2);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add("1,1,1,"+(allSize-1));
		
		/**
		 * 第三行 
		 */
		row = sheet.createRow(2);
		
		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("付款要求");
				cell.setCellStyle(style);
			}else if(i==1){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(query.getFixPayment());
				cell.setCellStyle(style2);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add("2,2,1,"+(allSize-1));
		
		/**
		 * 第四行 
		 */
		row = sheet.createRow(3);

		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("质保期");
				cell.setCellStyle(style);
			}else if(i==1){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(query.getQuantityDate());
				cell.setCellStyle(style2);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add("3,3,1,"+(allSize-1));
		
		/**
		 * 第五行 
		 */
		row = sheet.createRow(4);
		
		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(0);
				cell.setCellValue("工期/交货期要求");
				cell.setCellStyle(style);
			}else if(i==1){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(query.getFixDeliver());
				cell.setCellStyle(style2);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add("4,4,1,"+(allSize-1));
		
		
		int index = 0;
		if(query.getProcess().equals("inquiry")){
			/**
			 * 第六行 
			 */
			row = sheet.createRow(5);
			//创建单元格
			for(int i=0;i<allSize;i++){
				//第一个单元格
				if(i==0){
					HSSFCell cell = row.createCell(0);
					cell.setCellValue("发标");
					cell.setCellStyle(style);
				}else if(i==1){
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(query.getReleaseDateReal());
					cell.setCellStyle(style2);
				}else{
					HSSFCell cell = row.createCell(i);
					cell.setCellValue("");
					cell.setCellStyle(style2);
				}
			}
			//合并单元格
			headnum0.add("5,5,1,"+(allSize-1));
			/**
			 * 第七行 
			 */
			row = sheet.createRow(6);
			//创建单元格
			for(int i=0;i<allSize;i++){
				//第一个单元格
				if(i==0){
					HSSFCell cell = row.createCell(0);
					cell.setCellValue("开价格标");
					cell.setCellStyle(style);
				}else if(i==1){
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(query.getPriceopenDateReal());
					cell.setCellStyle(style2);
				}else{
					HSSFCell cell = row.createCell(i);
					cell.setCellValue("");
					cell.setCellStyle(style2);
				}
			}
			//合并单元格
			headnum0.add("6,6,1,"+(allSize-1));
			
		}else{
			index = 2;
			/**
			 * 第六行
			 */
			row = sheet.createRow(5);
			//创建单元格
			for(int i=0;i<allSize;i++){
				//第一个单元格
				if(i==0){
					HSSFCell cell = row.createCell(0);
					cell.setCellValue("发标");
					cell.setCellStyle(style);
				}else if(i==1){
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(query.getReleaseDateReal());
					cell.setCellStyle(style2);
				}else{
					HSSFCell cell = row.createCell(i);
					cell.setCellValue("");
					cell.setCellStyle(style2);
				}
			}
			//合并单元格
			headnum0.add("5,5,1,"+(allSize-1));
			/**
			 * 第七行
			 */
			row = sheet.createRow(6);
			//创建单元格
			for(int i=0;i<allSize;i++){
				//第一个单元格
				if(i==0){
					HSSFCell cell = row.createCell(0);
					cell.setCellValue("收标");
					cell.setCellStyle(style);
				}else if(i==1){
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(query.getTechtakeDateReal());
					cell.setCellStyle(style2);
				}else{
					HSSFCell cell = row.createCell(i);
					cell.setCellValue("");
					cell.setCellStyle(style2);
				}
			}
			//合并单元格
			headnum0.add("6,6,1,"+(allSize-1));
			/**
			 * 第八行
			 */
			row = sheet.createRow(7);
			//创建单元格
			for(int i=0;i<allSize;i++){
				//第一个单元格
				if(i==0){
					HSSFCell cell = row.createCell(0);
					cell.setCellValue("评标结束");
					cell.setCellStyle(style);
				}else if(i==1){
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(query.getTechdoneDateReal());
					cell.setCellStyle(style2);
				}else{
					HSSFCell cell = row.createCell(i);
					cell.setCellValue("");
					cell.setCellStyle(style2);
				}
			}
			//合并单元格
			headnum0.add("7,7,1,"+(allSize-1));
			/**
			 * 第九行
			 */
			row = sheet.createRow(8);
			//创建单元格
			for(int i=0;i<allSize;i++){
				//第一个单元格
				if(i==0){
					HSSFCell cell = row.createCell(0);
					cell.setCellValue("开价格标");
					cell.setCellStyle(style);
				}else if(i==1){
					HSSFCell cell = row.createCell(i);
					cell.setCellValue(query.getPriceopenDateReal());
					cell.setCellStyle(style2);
				}else{
					HSSFCell cell = row.createCell(i);
					cell.setCellValue("");
					cell.setCellStyle(style2);
				}
			}
			//合并单元格
			headnum0.add("8,8,1,"+(allSize-1));
			
		}
		
	
		/**
		 * 第八行 邀请招标第十行
		 */
		row = sheet.createRow(index+7);
		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(0);
				cell.setCellValue("进度异常说明");
				cell.setCellStyle(style);
			}else if(i==1){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(query.getFixExcept());
				cell.setCellStyle(style2);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add( (index+7)+","+(index+7)+",1,"+(allSize-1));
		/**
		 * 第九行
		 */
		row = sheet.createRow(index+8);
		//创建单元格
		for(int i=0;i<allSize;i++){
			//第一个单元格
			if(i==0){
				HSSFCell cell = row.createCell(0);
				cell.setCellValue("项目预算");
				cell.setCellStyle(style);
			}else if(i==1){
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(query.getFixBudget());
				cell.setCellStyle(style2);
			}else{
				HSSFCell cell = row.createCell(i);
				cell.setCellValue("");
				cell.setCellStyle(style2);
			}
		}
		//合并单元格
		headnum0.add( (index+8)+","+(index+8)+",1,"+(allSize-1));
	/****************************************表单数据 end***************************************************/	
		// 动态合并单元格
		for (int i = 0; i < headnum0.size(); i++) {
			sheet.autoSizeColumn(i, true);
			String[] temp = headnum0.get(i).split(",");
			Integer startrow = Integer.parseInt(temp[0]);
			Integer overrow = Integer.parseInt(temp[1]);
			Integer startcol = Integer.parseInt(temp[2]);
			Integer overcol = Integer.parseInt(temp[3]);
			sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,startcol, overcol));
		}
		//设置列宽
		for(int i =0;i<widthArr.length;i++){
			if(i==0){
				if(query.getProcess().equals("inquiry")){
					sheet.setColumnWidth(i,(short)(20*256));
				}else{
					sheet.setColumnWidth(i,(short)(40*256));
				}
			}else{
				if(query.getProcess().equals("inquiry")){
					if(i<length1){
						sheet.setColumnWidth(i,(short)(8*256*1.3));
					}else{
						sheet.setColumnWidth(i,(short)(20*256));
					}
				}else{
					sheet.setColumnWidth(i,(short)(16*256*1.3));
				}
			}
		}
		
		//设置行高
		for(int i=0;i<(index+9);i++){
			HSSFRow rowHeight = sheet.getRow(i);
			rowHeight.setHeight((short) (33*20));
		}

		return wb;
	}