java对Poi的操作(样式和对表格的填充)

最新推荐文章于 2024-07-08 02:40:23 发布

不念过往--不语未来

最新推荐文章于 2024-07-08 02:40:23 发布

阅读量1.8k

点赞数

分类专栏：工具类文章标签： java poi java对Excel的操作

本文链接：https://blog.csdn.net/bestxianfeng163/article/details/81083225

版权

工具类专栏收录该内容

26 篇文章 0 订阅

订阅专栏

public String getExcel(Context context) throws EMPException{
		Connection connection = null;
		try {
			connection = this.getConnection(context);
			//通过组件服务实例化业务组件
			TabelMakeBaseComponent comp = (TabelMakeBaseComponent)CMISFactory.getComponent(TabelMakeBaseConstance.TABEL_MAKE_BASE_ID);
			//查询机构列表数据
			List<TabelMakeBase> list = null;
			list = comp.queryTabelNameList(null, connection);
			//创建工作薄
			HSSFWorkbook  wb = new HSSFWorkbook();  // or new XSSFWorkbook();
			//创建工作薄中第一个sheet(sheetOne) 
			HSSFSheet sheetOne=wb.createSheet("修订记录");
			//创建sheetOne中的行和列
			HSSFRow sheetOneRow = wb.getSheet("修订记录").createRow(0);
			HSSFCell cellEveryRowCellOfColSheetOne = sheetOneRow.createCell(0);
			cellEveryRowCellOfColSheetOne.setCellValue("修订记录");
			cellEveryRowCellOfColSheetOne=sheetOneRow.createCell(1);
			cellEveryRowCellOfColSheetOne.setCellValue("修订人");
			//设置每一个行中每列单元格的列宽
			sheetOne.setColumnWidth(0, 10000);
			sheetOne.setColumnWidth(1, 3000);
			
			//创建工作薄中第二个sheet(sheetTwo) 
	  		HSSFSheet sheetTwo = wb.createSheet("表目录");
	  		sheetTwo.setColumnWidth(0, 10000);
	  		
	  		HSSFRow sheetTwoRow = wb.getSheet("表目录").createRow(0); 
	  		HSSFCell cellEveryRowCellOfColSheetTwo = sheetTwoRow.createCell(0);
	  		//获取样式
	  		HSSFCellStyle cellStyle=setExcelStyle(wb,1);
	  		HSSFCellStyle cellStyleNoBackColor=setExcelStyle(wb,2);
            cellEveryRowCellOfColSheetTwo.setCellValue("表名");
            cellEveryRowCellOfColSheetTwo.setCellStyle(cellStyle);
            cellEveryRowCellOfColSheetTwo = sheetTwoRow.createCell(1);
            cellEveryRowCellOfColSheetTwo.setCellValue("备注");  
            cellEveryRowCellOfColSheetTwo.setCellStyle(cellStyle);
            sheetTwo.setColumnWidth(0, 10000);
            sheetTwo.setColumnWidth(1, 10000);

            //第三个sheet 表目录
			for (int i = 0; i < list.size(); i++) {
				//获取表结构
				Map paramMap = new HashMap();
				paramMap.put("table_name",list.get(i).getTableNamem());
				//创建其他sheet
				HSSFSheet sheetOther = wb.createSheet(list.get(i).getTableNamem().trim());
				sheetOther.setColumnWidth(0, 10000);
				HSSFRow rowOther = wb.getSheet(list.get(i).getTableNamem().trim()).createRow(0);
				HSSFCell cell3 = rowOther.createCell(0);
		  		//样式
		        //cellStyle.setFillBackgroundColor(IndexedColors.g.getIndex());
		         cell3.setCellValue("字段名");
		         cell3.setCellStyle(cellStyle);
		         cell3 = rowOther.createCell(1);
		         cell3.setCellValue("字段类型");
		         cell3.setCellStyle(cellStyle);
		         cell3 = rowOther.createCell(2);
		         cell3.setCellValue("长度");
		         cell3.setCellStyle(cellStyle);
		         cell3 = rowOther.createCell(3);
		         cell3.setCellValue("字段注释"); 
		         cell3.setCellStyle(cellStyle);
	        	 List<TabelMakeBase> listStruct=comp.queryByTabelNameGetList(paramMap, connection);
					for (int j = 0; j < listStruct.size(); j++) {
						if(j==0){
							HSSFRow rowTwo=sheetTwo.createRow(i+1);
							HSSFCell cellTem = rowTwo.createCell(0);
							cellTem.setCellStyle(cellStyleNoBackColor);
							cellTem.setCellValue(listStruct.get(j).getTableNamem());  	
							 Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);  
				                // "#"表示本文档    "明细页面"表示sheet页名称  "A10"表示第几列第几行  
				            hyperlink.setAddress("'"+list.get(i).getTableNamem().trim()+"'!A1");  
				            cellTem.setHyperlink(hyperlink);
				            HSSFCell cellTem2 = rowTwo.createCell(1);
				            cellTem2.setCellStyle(cellStyleNoBackColor);
				            cellTem2.setCellValue(listStruct.get(j).getTableCommentt());
				            sheetOther.setColumnWidth(0, 5000);
				            sheetOther.setColumnWidth(1, 5000);
				            // 点击进行跳转  	
						}
						HSSFRow row33=sheetOther.createRow(j+1);
						HSSFCell cellTem33 = row33.createCell(0);
						cellTem33.setCellStyle(cellStyleNoBackColor);
						cellTem33.setCellValue(listStruct.get(j).getTableCollName());
						cellTem33=row33.createCell(1);
						cellTem33.setCellStyle(cellStyleNoBackColor);
						cellTem33.setCellValue(listStruct.get(j).getTableCollType());
						cellTem33=row33.createCell(2);
						cellTem33.setCellStyle(cellStyleNoBackColor);
						cellTem33.setCellValue(listStruct.get(j).getTableCollLength());
						cellTem33=row33.createCell(3);
						cellTem33.setCellStyle(cellStyleNoBackColor);
						cellTem33.setCellValue(listStruct.get(j).getTableColCommentt());;
						
				       // sheet3.autoSizeColumn((short)0); //调整第一列宽度
						sheetOther.setColumnWidth(0, 8000);
						sheetOther.setColumnWidth(1, 6000);
						sheetOther.setColumnWidth(2, 3000);
						sheetOther.setColumnWidth(3, 8000);
				       /* sheet3.autoSizeColumn((short)1); //调整第二列宽度
					        sheet3.autoSizeColumn((short)2); //调整第三列宽度
					        sheet3.autoSizeColumn((short)3); //调整第四列宽度
*/						}
		         }
				//加入sheet1的行列	
			FileOutputStream fileOut = new FileOutputStream("d:/cc.xls");
			wb.write(fileOut);
			fileOut.flush();
			fileOut.close();
			context.addDataField("flag", "success");			
		} 
		catch(Exception e){
				e.printStackTrace();
				throw new AsynException(e);
			} finally {
				if (connection != null)
					this.releaseConnection(context, connection);
			}
		return "good";
	}

其中有对表格样式的操作，对生成表填充的样式操作，其他的自己看着来操作。