poi 生成excel报表一般步骤

最新推荐文章于 2024-10-22 10:02:28 发布

原创最新推荐文章于 2024-10-22 10:02:28 发布 · 145 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Excel #J# #F#

本文介绍了一种使用Java程序生成Excel报表的方法。通过实例展示了如何创建工作簿、设置样式、填充数据及进行单元格合并等操作。文章还涉及了字体设置、行高调整、边框样式等内容。

//表题

//表头

//数据

//合并区域

//是否显示网格线

//行号

//外边框加粗

//内边框普通

//设置打印区域

//打印设置

	public static  Workbook getWorkbook(ProjectStatReporter reporter){
		List<String> x=reporter.getX();
		List<String> f=reporter.getStatField();
		List<List<String>> y=reporter.getY();
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet=workbook.createSheet();
		//x行  和度量值
		Row rowx=sheet.createRow(0);
		Row rowf=sheet.createRow(1);
		
		Font font = workbook.createFont();
	    font.setFontName("宋体");
	    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

		for(int i=0;i<x.size();i++){
			//x
			RichTextString richString = new HSSFRichTextString(x.get(i));
			richString.applyFont(font);   
			createCell(workbook, rowx, i*f.size()+1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,richString);
			//度量值 
			for(int j=0;j<f.size();j++){				
				RichTextString richString2 = new HSSFRichTextString(f.get(j));
				richString2.applyFont(font);   
				createCell(workbook, rowf, i*f.size()+1+j, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,richString2);
		    }
		
		}
		
		//y数据
		
		for(int i=0;i<y.size();i++){
			Row row=sheet.createRow(i+2);
			for(int j=0;j<y.get(i).size();j++){
				if(j==0){//第一列  字体加粗
					RichTextString richString = new HSSFRichTextString(y.get(i).get(j));
					richString.applyFont(font);   
					createCell(workbook, row, j, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,richString);
				}else{
					createCell(workbook, row, j, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM,y.get(i).get(j));
				}
				
			}
		}
		//合并区域
		sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));//左上角的空白
		for(int i=0;i<x.size();i++){//合并x
	      sheet.addMergedRegion(new CellRangeAddress(0,0,i*f.size()+1,i*f.size()+1+f.size()-1));
		}
		//样式：不显示网格线 居中  行号25.5  外边框加粗   内边框普通   row0 1 字体宋体加粗  col 1 字体宋体加粗
		//不显示网格线
		sheet.setDisplayGridlines(false);
		//居中   row0 1 字体宋体加粗  col 1 字体宋体加粗
		             //调用createcell()
		//行号25.5
		for(int i=0;i<2+y.size();i++){
			Row row=sheet.getRow(i);
			row.setHeightInPoints((float) 25.5);
		}
		//外边框加粗
		
		CellRangeAddress region=new CellRangeAddress(0,2+y.size()-1,0,x.size()*f.size());
		RegionUtil.setBorderBottom(CellStyle.BORDER_THICK, region, sheet, workbook);
		RegionUtil.setBorderLeft(CellStyle.BORDER_THICK, region, sheet, workbook);
		RegionUtil.setBorderRight(CellStyle.BORDER_THICK, region, sheet, workbook);
		RegionUtil.setBorderTop(CellStyle.BORDER_THICK, region, sheet, workbook);
          
		//内边框普通
		    //调用createcell() 中设置
		
		//返回
		//测试
		Sheet sheet1 = workbook.createSheet("new sheet");

	    sheet1.groupRow( 5, 14 );
	    sheet1.groupRow( 7, 14 );
	    sheet1.groupRow( 16, 19 );

	    sheet1.groupColumn( (short)4, (short)7 );
	    sheet1.groupColumn( (short)9, (short)12 );
	    sheet1.groupColumn( (short)10, (short)11 );


		return  workbook;
	}

	private static void createCell(Workbook wb, Row row, int column, short halign, short valign,String text){
        Cell cell = row.createCell(column);
        cell.setCellValue(text);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
	
	private static void createCell(Workbook wb, Row row, int column, short halign, short valign,RichTextString richText){
        Cell cell = row.createCell(column);
        cell.setCellValue(richText);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }