用POI实现双层标题excel打印

心已赠人 2022-09-23 05:49 157阅读 0赞

这个是将公司的一个工具栏改成的，以前工具类只能导出单层的excel

实现效果

![Center][]

写了一个方法，注解都在代码里

/** 带分类标题导出Excel的方法
    	  * 
    	  * @param title
    	  *            excel中的sheet名称
    	  * @param header_2
    	  *            两列的头的标题
    	  * @param header_cate
    	  *            分类列
    	  * @param cate_num
    	  *            分类列行数
    	  * @param header_1
    	  *            分类列行数
    	  * @param columns
    	  * 			   列名
    	  * @param result
    	  *            结果集
    	  * @param out
    	  *            输出流
    	  * @param pattern
    	  *            时间格式
    	  */ 
    	@SuppressWarnings("deprecation") 
    	public void exportoExcel1(String title, String[] header_2, String[] header_cate, int[] cate_num, String[] header_1, String[] columns, Collection<T> result, OutputStream out,
    			String pattern) throws Exception {
    
    		// 声明一个工作薄
    		HSSFWorkbook workbook = new HSSFWorkbook();
    		// 生成一个表格
    		HSSFSheet sheet = workbook.createSheet(title);
    		// 设置表格默认列宽度为20个字节
    		sheet.setDefaultColumnWidth((short) 20);
    
    		// 生成一个样式
    		HSSFCellStyle style = workbook.createCellStyle();
    		// 设置单元格背景色，设置单元格背景色以下两句必须同时设置
    		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.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);
    		// 生成一个字体
    		HSSFFont font = workbook.createFont();
    		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		// 把字体应用到当前的样式
    		style.setFont(font);
    
    		// 指定当单元格内容显示不下时自动换行
    		style.setWrapText(true);
    
    		// 声明一个画图的顶级管理器
    		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    
    		// 以下可以用于设置导出的数据的样式
    		// 产生表格标题行
    		// 表头的样式
    		HSSFCellStyle titleStyle = workbook.createCellStyle();// 创建样式对象
    		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
    		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
    		// 设置字体
    		HSSFFont titleFont = workbook.createFont(); // 创建字体对象
    		titleFont.setFontHeightInPoints((short) 15); // 设置字体大小
    		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
    		// titleFont.setFontName("黑体"); // 设置为黑体字
    		titleStyle.setFont(titleFont);
    		//第一行
    		CellRangeAddress cra=new CellRangeAddress(0, (short) 0, 0, (short) (columns.length - 1));//参数:起始行号，终止行号， 起始列号，终止列号
    		sheet.addMergedRegion(cra);
    		
    		Row row = sheet.createRow(0);
    		Cell row0=row.createCell(0);
    		row0.setCellValue(title);
    		row0.setCellStyle(titleStyle);
    		
    		for(int i=0; i<header_2.length; i++){
    			cra=new CellRangeAddress(1, 2, i, i);
    			sheet.addMergedRegion(cra);
    		}
    		int sum1 = 0;
    		int sum2 = 0;
    		for(int i=0; i<header_cate.length; i++){
    			sum1 += cate_num[i];
    			cra=new CellRangeAddress(1, 1, 1+sum2, sum1);	//
    			sheet.addMergedRegion(cra);
    			sum2 += cate_num[i];
    		}
    		
    		row = sheet.createRow(1);
    		
    		for(int i=0; i<header_2.length; i++){
    			final Cell cell = row.createCell(i);
    			cell.setCellStyle(style);	
    			cell.setCellValue(header_2[i]);
    		}
    		
    		int sum = 0;
    		for(int i=0; i<header_cate.length; i++){
    			final Cell cell = row.createCell(1+sum);	//
    			cell.setCellStyle(style);
    			cell.setCellValue(header_cate[i]);
    			sum += cate_num[i];
    		}
    		row = sheet.createRow(2);
    		for(int i=0; i<header_1.length; i++){
    			final Cell cell = row.createCell(i+1);	//
    			cell.setCellStyle(style);
    			cell.setCellValue(header_1[i]);
    		}
    		
    		// 遍历集合数据，产生数据行
    		if (result != null) {
    			int index = 3;
    			for (T t : result) {
    				row = sheet.createRow(index);
    				index++;
    				for (short i = 0; i < columns.length; i++) {
    					Cell cell = row.createCell(i);
    					String fieldName = columns[i];
    					String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    					Class tCls = t.getClass();
    					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
    					Object value = getMethod.invoke(t, new Class[] {});
    					String textValue = null;
    					if (value == null) {
    						textValue = "";
    					} else if (value instanceof Date) {
    						Date date = (Date) value;
    						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    						textValue = sdf.format(date);
    					} else if (value instanceof byte[]) {
    						// 有图片时，设置行高为60px;
    						row.setHeightInPoints(60);
    						// 设置图片所在列宽度为80px,注意这里单位的一个换算
    						sheet.setColumnWidth(i, (short) (35.7 * 80));
    						// sheet.autoSizeColumn(i);
    						byte[] bsValue = (byte[]) value;
    						HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,
    								index);
    						anchor.setAnchorType(2);
    						patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
    					} else {
    						// 其它数据类型都当作字符串简单处理
    						textValue = value.toString();
    					}
    					if (textValue != null) {
    						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
    						Matcher matcher = p.matcher(textValue);
    						if (matcher.matches()) {
    							// 是数字当作double处理
    							cell.setCellValue(Double.parseDouble(textValue));
    						} else {
    							HSSFRichTextString richString = new HSSFRichTextString(textValue);
    							cell.setCellValue(richString);
    						}
    					}
    				}
    			}
    		}
    		workbook.write(out);
    		out.flush();
    		out.close();
    	}

实现方法

@RequestMapping(InterfaceConsts.EXPORT_MONTHTESTRSTLIST)
    	@ResponseBody
    	public void export_MonthTestrstList(HttpServletRequest request, WqTestrstDForm form,
    			String jsonpCallback, HttpServletResponse response) throws Exception{
    		Map<String, Object> params = new HashMap<String, Object>();
    		params = MapUtil.toMap(form);
    		
    		response.setContentType("application/vnd.ms-excel;charset=utf-8");
    		OutputStream out = response.getOutputStream();
    		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
    		String title = sdf.format(new Date(form.getTimeStart().replaceAll("-", "/")))+wqPrjBService.getWqPrjBBySmid(Integer.valueOf(form.getSmid())).getPrjnm()+"水质监测月报表";
    		
    		String[] header_2={"检测项目"};
    		int[] cate_num={5,5,4};
    		String[] header_cate={"源水","出厂水","末梢水"};
    		String[] headers = { "原水检验次数", "原水最大值", "原水最小值", "原水平均值", "出厂水检验次数", "出厂水最大值", "出厂水最小值", "出厂水平均值", "出厂水标准值", "出厂水检验次数", "管网末梢水最大值", "管网末梢水最小值", "管网末梢水平均值", "管网末梢水标准值"};
    		String[] columns = { "itmnm", "count01", "tstv01Max", "tstv01Min", "tstv01Avg", "count02", "tstv02Max", "tstv02Min", "tstv02Avg", "ostnv", "count03", "tstv03Max", "tstv03Min", "tstv03Avg", "estnv"};
    		List<WqTestrstDMonth> houseList = wqTestrstDService.getAllMonthTestrstList(params);//我的数据源
    		ExcelUitl<WqTestrstDMonth> excelUtil = new ExcelUitl<WqTestrstDMonth>();
    		String pattern = "yyyy-MM-dd HH:mm:dd";
    		response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xls").getBytes(), "iso-8859-1"));
    		excelUtil.exportoExcel1(title, header_2, header_cate, cate_num, headers, columns, houseList, out, pattern);
    	}

达成效果

![Center 1][]

[Center]: /images/20220721/ed8c4f32b058401ab87272c9c123f3e2.png
[Center 1]: /images/20220721/a64e3e841e124eb2b9817412390f05f1.png