poi导出excel

最新推荐文章于 2023-06-14 09:41:37 发布

迷了路你就往前走

最新推荐文章于 2023-06-14 09:41:37 发布

阅读量369

点赞数

CC 4.0 BY-SA版权

分类专栏： java

本文链接：https://blog.youkuaiyun.com/WNsshssm/article/details/95594067

java 专栏收录该内容

11 篇文章

订阅专栏

博主记录了使用POI导出Excel的功能，导出样式需原样输出，代码中有详细注释，并提及了导出效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近做了一个通过poi导出excel的功能,当做笔记记录一下

导出的样式为

需要原样输出,代码中注释挺详细的

/*
	 * 描述:导出填报任务
	 * @author wangn
	 * @date  2019/7/9-12:28
	 * @param [jsonObject]
	 * @return void
	 */
	@Override
	public void downLoadFillinTask(JSONObject jsonObject, OutputStream out) {
		//获取fillinid
		Integer fillinId = jsonObject.getInteger("fillinId");
		try {
			//通过fillinid查询得到fillinstoreassign的信息和关联的title表的信息
			JSONObject fillinAndTitle = findTitleByFillinId(fillinId);
			//创建excel文件
			HSSFWorkbook workbook = new HSSFWorkbook();
			
			//获取title表中的信息
			JSONArray titleArray = (JSONArray) fillinAndTitle.get("FillinTitleList");

			for (int titleIndex = 0 ; titleIndex < titleArray.size() ; titleIndex++){
				//创建sheet页
				HSSFSheet sheet = workbook.createSheet(titleArray.getJSONObject(titleIndex).getString("titleName"));
				
				//通过titleId查询title以及dimension表相关信息
				Integer titleId = titleArray.getJSONObject(titleIndex).getInteger("titleId");
				FillinStoreAssignTitle title = fillinStoreAssignTitleMapper.selectByPrimaryKey(titleId);
				//通过titleId查询维度值
				List<FillinStoreAssignDimension> dimensionsList = fillinStoreAssignDimensionMapper.findByTitleId(titleId);
				//固定维度值
				Integer stationDimension = 0;
				//固定维度最大行数(上一个维度的最大行数)
				Integer stationDimensionRowNum = 0;
				//循环维度值
				for(FillinStoreAssignDimension dimension : dimensionsList){
					 //初始化当前维度为0
					Integer currentDimension = 0;
					
					//获取当前维度名放置的位置
					Integer dimensionValueRow = stationDimensionRowNum == 0 ? 0 : stationDimensionRowNum;
					//初始化行
					Row row = sheet.createRow(dimensionValueRow);
					row.createCell(0).setCellValue(dimension.getDimensionName());
					//得到class集合
					List<FillinStoreAssignClass> classList1 =  dimension.getFillinStoreAssignClass();
					//手动控制y轴值,一行一行拼接
					//定义某一固定y值得最大行数初始化为0
					Integer stationLastRowNum = 0;
					stationLastRowNum += stationDimensionRowNum;
					//定义某一固定y值
					Integer stationY_coordinate = 0;
					for (int y_coordinate = 0; y_coordinate < classList1.size() ; y_coordinate++) {
						//用于封装某一行的数据
						List<FillinStoreAssignClass> classList = new ArrayList<>();
						//控制取一行的数据
						for(FillinStoreAssignClass clazz : classList1){
							if (clazz.getY_coordinate() == y_coordinate){
								classList.add(clazz);
							}
						}
						if (classList.size() == 0){
							break;
						}
						for (FillinStoreAssignClass clazz : classList) {
							//横向偏移量
							int x_coordinate = clazz.getX_coordinate();
							//解析最后一行的行数
							Integer lastRowNum;
							if (y_coordinate == stationY_coordinate){
								lastRowNum = stationLastRowNum;
							}else{
								lastRowNum = sheet.getLastRowNum() == 0 ? 0 : sheet.getLastRowNum() + 1;
								//改变固定y值和lastRowNum
								stationY_coordinate = y_coordinate;
								stationLastRowNum = lastRowNum;
								
							}
							//相同维度横向添加初始化行
							row = sheet.getRow(lastRowNum) == null ? sheet.createRow(lastRowNum) : sheet.getRow(lastRowNum);
							//横向合并单元格并且放置值
							CellRangeAddress regionCol = new CellRangeAddress(lastRowNum, lastRowNum, 5 * x_coordinate + 1, 5 * x_coordinate + 5);
							sheet.addMergedRegion(regionCol);
							row.createCell(5 * x_coordinate + 1).setCellValue(clazz.getClassName());
							//放置固定标题
							//初始化开始行数(能取到就取,取不到创建一个新的,防止覆盖之前的数据)
							row = (sheet.getRow(lastRowNum + 1) == null) ? sheet.createRow(lastRowNum + 1) : sheet.getRow(lastRowNum + 1);

							for (int i = 1; i < FILLIN_TASK_TITLE.split(",").length + 1; i++) {
								row.createCell(5 * x_coordinate + i).setCellValue(FILLIN_TASK_TITLE.split(",")[i - 1]);
							}
							//通过classId查询detail
							List<FillinStoreAssignDetail> detailList = fillinStoreAssignDetailMapper
									.findClassById4SystemName(clazz.getClassId());
							//循环detail
							//初始化开始行
							int starLine = lastRowNum + 2;
							//序号初始值
							int Index = 1;
							for (FillinStoreAssignDetail detail : detailList) {
								//初始化开始列
								int starColunm = 5 * x_coordinate;
								//在excel中封装数据
								row = (sheet.getRow(starLine) == null) ? sheet.createRow(starLine) : sheet.getRow(starLine);
								//序号
								row.createCell(++starColunm).setCellValue(Index++);
								//二级类别
								row.createCell(++starColunm).setCellValue(detail.getClassTwo());
								//采集名称
								row.createCell(++starColunm).setCellValue(detail.getCollectionName());
								if(detail.getNumber() == null){
									++starColunm;
								}else {
									//数值
									row.createCell(++starColunm).setCellValue(Double.valueOf(String.valueOf(detail.getNumber())));
								}
								//单位
								row.createCell(++starColunm).setCellValue(detail.getUnit());
								starLine++;
							}

						}
					}
					//纵向合并单元格并放置值
					Integer dimensionStarRow =  stationDimensionRowNum == 0 ? 0 : stationDimensionRowNum;
					CellRangeAddress regionRow = new CellRangeAddress(dimensionStarRow, sheet.getLastRowNum(), 0, 0);
					sheet.addMergedRegion(regionRow);
					currentDimension++;
					if(currentDimension != stationDimension){
						stationDimensionRowNum = sheet.getLastRowNum() == 0 ? 0 : sheet.getLastRowNum() + 1;
						stationDimension = currentDimension;
					}
				}
					
			}
			workbook.write(out);//保存Excel文件
			out.flush();
			out.close();//关闭文件流
			workbook.close();//关闭工作薄对象
			System.out.println("OK!");
			
		}catch (Exception e){
			e.printStackTrace();
		}
	}

导出效果: