栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

java生成excel表格

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

java生成excel表格

首先说下用java生成excel表格时候,需要准备的jar包:poi-ooxml-3.15.jar,这里都是测试数据,所以我只讲思想还有步骤,剩下的就看自己举一反三了。

//这里是定义的方法这里面的参数需要定义什么就传什么
public void test(String a,String b) throws IOException {
		XSSFWorkbook book = new XSSFWorkbook();
		//这个是封装的方法这里的参数都是我随便起的
		test1(book,String a,String b);
		String realFilePathName = targetDirPath + File.separator + fileName;
		//最后再把文件生成输出
		book.write(new FileOutputStream(realFilePathName));
	}

这里设置的内容数据统一都用数字来代替

private void test1(XSSFWorkbook book, String a,Sting b)
		Sheet sheet = book.createSheet(fileName);
		Drawing drawing = sheet.createDrawingPatriarch(); //画图器

		// ---------- 设置表头格式 -----------
		XSSFCellStyle reportNameStyle = book.createCellStyle();
		XSSFFont reportNameFont = book.createFont();
		reportNameFont.setFontName("宋体");
		reportNameFont.setFontHeightInPoints((short) 12);// 字号
		reportNameFont.setBold(true);// 加粗
		reportNameStyle.setFont(reportNameFont);
		reportNameStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
		reportNameStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		reportNameStyle.setWrapText(true);
		reportNameStyle.setBorderBottom(BorderStyle.THIN);
		reportNameStyle.setBorderLeft(BorderStyle.THIN);
		reportNameStyle.setBorderRight(BorderStyle.THIN);
		reportNameStyle.setBorderTop(BorderStyle.THIN);

		// ---------- 设置单元格长度 -----------
		Cell cell = null;  // 在所在的行设置所在的单元格(相当于列,初始从0开始,对应的就是A列)
		int width = 256*16;
		for (int i=0; i<16; i++) {
			sheet.setColumnWidth(i, width);
		}
		sheet.setColumnWidth(2, 256*4);
		sheet.setColumnWidth(6, 256*22);
		// ---------- 设置表头 -----------
		Row row = sheet.createRow(0);   // 设置相应的行(初始从0开始)
		row.setHeight((short) 600);
		// 写入相关数据到设置的行列中去。
		for (int i=0; i<16; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(reportNameStyle);
		}
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
		cell = row.getCell(0);
		cell.setCellValue("testA");

		XSSFCellStyle leftStyle = getXssfCellStyle(book,XSSFFont.BOLDWEIGHT_NORMAL,XSSFCellStyle.ALIGN_LEFT);
		cell.setCellStyle(leftStyle);

	fillExcelLogo(book,watermarkCompanyLogo,sheet,row.getCell(10),row,6);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 15));


		// ---------- 设置表体格式 -----------
		XSSFCellStyle reportStyle = book.createCellStyle();
		XSSFFont reportFont = book.createFont();
		reportFont.setFontName("黑体");
		reportFont.setFontHeightInPoints((short) 8);// 字号
		reportStyle.setFont(reportFont);
		reportStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
		reportStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		reportStyle.setWrapText(true);
		reportStyle.setBorderBottom(BorderStyle.THIN);
		reportStyle.setBorderLeft(BorderStyle.THIN);
		reportStyle.setBorderRight(BorderStyle.THIN);
		reportStyle.setBorderTop(BorderStyle.THIN);
		// ---------- 设置第二行 -----------
		row = sheet.createRow(1);
		row.setHeight((short) 460);
		for (int i=0; i<16; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(reportStyle);
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
		cell = row.getCell(0);
		cell.setCellValue("1");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 8));
		cell = row.getCell(2);
		cell.setCellValue("2");

		cell = row.getCell(9);
		cell.setCellValue("3");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 11));
		cell = row.getCell(10);
		cell.setCellValue("4");

		cell = row.getCell(12);
		cell.setCellValue("5");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 15));
		cell = row.getCell(13);
		cell.setCellValue(startD + "-" + endD);
		cell.setCellStyle(reportStyle);

		// ---------- 设置第三行列头格式 -----------
		XSSFCellStyle reportStyleHead = book.createCellStyle();
		reportStyleHead.setFont(reportFont);
		reportStyleHead.setAlignment(HorizontalAlignment.CENTER);// 左右居中
		reportStyleHead.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		reportStyleHead.setWrapText(true);
		reportStyleHead.setBorderBottom(BorderStyle.THIN);
		reportStyleHead.setBorderLeft(BorderStyle.THIN);
		reportStyleHead.setBorderRight(BorderStyle.THIN);
		reportStyleHead.setBorderTop(BorderStyle.THIN);
		reportStyleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		reportStyleHead.setFillForegroundColor(new XSSFColor(new java.awt.Color(189,215,238)));
		// ---------- 设置第三行(列头) -----------
		row = sheet.createRow(2);
		row.setHeight((short) 500);
		for (int i=0; i<16; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(reportStyleHead);
		}
		cell = row.getCell(0);
		cell.setCellValue("1");
		cell = row.getCell(1);
		cell.setCellValue("2");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
		cell = row.getCell(2);
		cell.setCellValue("3");
		cell = row.getCell(4);
		cell.setCellValue("4");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
		cell = row.getCell(5);
		cell.setCellValue("5");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
		cell = row.getCell(7);
		cell.setCellValue("6");
		cell = row.getCell(9);
		cell.setCellValue("7");
		cell = row.getCell(10);
		cell.setCellValue("8");
		cell = row.getCell(11);
		cell.setCellValue("9");
		cell = row.getCell(12);
		cell.setCellValue("10");
		cell = row.getCell(13);
		cell.setCellValue("11");
		cell = row.getCell(14);
		cell.setCellValue("12");
		cell = row.getCell(15);
		cell.setCellValue("13");

		// ---------- 设置第四行 -----------
		int workerNo = 1;
		Double totalSalary = 0d;
		Double calculateSalary = 0d;
		Double personPay=0d;
		Double grouppPay=0d;
		for (User user : list) {
			int rowNo = workerNo+2;
			row = sheet.createRow(rowNo);
			row.setHeight((short) 500);
			for (int i=0; i<16; i++) {
				cell = row.createCell(i);
				cell.setCellStyle(reportStyle);
			}
			cell = row.getCell(0);
			cell.setCellValue(workerNo);
			cell = row.getCell(1);
			cell.setCellValue("1");
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 2, 3));
			cell = row.getCell(2);
			cell.setCellValue("2");
			cell = row.getCell(4);
			cell.setCellValue("3");
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 5, 6));
			cell = row.getCell(5);
			cell.setCellValue("4");
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 7, 8));
			cell = row.getCell(7);
			cell.setCellValue("5");
			cell = row.getCell(9);
			cell.setCellValue("6");
			cell = row.getCell(10);
			cell.setCellValue("7");
			cell = row.getCell(11);
			cell.setCellValue("8");
			cell = row.getCell(12);
			cell.setCellValue("9");
			cell = row.getCell(13);
			cell.setCellValue("10");
			cell = row.getCell(14);
	//这是插入图片的方法
	fillExcelLogo(book,signUrl,sheet,row.getCell(15),row,15);
			workerNo++;
		}

		// ---------- 设置第五行 -----------
		row = sheet.createRow(workerNo+2);
		row.setHeight((short) 500);
		for (int i=11; i<16; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(reportStyle);
		}
		XSSFCellStyle allMiddle = book.createCellStyle();
		allMiddle.setFont(reportFont);
		allMiddle.setAlignment(HorizontalAlignment.RIGHT);// 右对齐
		allMiddle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		allMiddle.setWrapText(true);
		allMiddle.setBorderBottom(BorderStyle.THIN);
		allMiddle.setBorderLeft(BorderStyle.THIN);
		allMiddle.setBorderRight(BorderStyle.THIN);
		allMiddle.setBorderTop(BorderStyle.THIN);

		for (int i=0; i<11; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(allMiddle);
		}
		sheet.addMergedRegion(new CellRangeAddress(workerNo+2, workerNo+2, 0, 10));
		cell = row.getCell(0);
		cell.setCellValue("13");
		cell = row.getCell(11);
		cell.setCellValue(calculateSalary);
		cell = row.getCell(12);
		cell.setCellValue(calculateSalary);
		cell = row.getCell(13);
		cell.setCellValue(calculateSalary);
		cell = row.getCell(14);
		cell.setCellValue(calculateSalary);



//这里是做了动态扩展行的
		// ---------- 设置第六行 -----------
		XSSFCellStyle firstStart = book.createCellStyle();
		firstStart.setFont(reportFont);
		firstStart.setAlignment(HorizontalAlignment.RIGHT);// 右对齐
		firstStart.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		firstStart.setWrapText(true);
		firstStart.setBorderBottom(BorderStyle.THIN);
		firstStart.setBorderLeft(BorderStyle.THIN);
		firstStart.setBorderRight(BorderStyle.THIN);
		firstStart.setBorderTop(BorderStyle.THIN);

		int index=3;
		int size = list.size();
		//获取取余前除了多少次
		int a=size/3;
		//获取取余的个数,单独再扩展一行
		int count=(size%3);
		if(count!=0){
			count=1;
		}
		//总共需要扩展行
		int totalCount=a+count;
		int countA=0;
		//创建好相对应的行以及单元格
		for(int i=0;i
			row = sheet.createRow(workerNo + 3 + i);
			//提前合并单元格
			sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 2, 4));
			sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 5, 6));
			sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 7, 9));
			sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 10, 11));
			sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 12, 15));
			row.setHeight((short) 600);
			for (int c = 0; c < 16; c++) {
				cell = row.createCell(c);
				cell.setCellStyle(firstStart);
			}
			List newList=new ArrayList<>();
			i=i+2*i;
			if(i+index>size){
				//获取余数
				int h=(size%3);
				//最后一个数如果没有达到三的话则截取i+余数防止截取报错。
				newList = list.subList(i, i+h);
			}else {
				newList = list.subList(i, i + index);
			}
			cell = row.getCell(0);
			cell.setCellValue(newList.get(0).getA()+"内容:");
			fillExcelLogo(book, newList.get(0).getSignUrl(), sheet, row.getCell(2), row, 2);
			cell = row.getCell(5);
			if(newList.size()>1) {
				cell.setCellValue(newList.get(1).getA() + "内容:");
				fillExcelLogo(book, newList.get(1).getSignUrl(), sheet, row.getCell(7), row, 7);

			}
			if(newList.size()>2){
				cell = row.getCell(10);
				cell.setCellValue(newList.get(2).getA()+"内容:");
				fillExcelLogo(book, newList.get(2).getSignUrl(), sheet, row.getCell(12), row, 12);
			}
			i=i-(2*countA);
			countA++;
		}

		

		row = sheet.createRow(workerNo+4+totalCount);
		for (int i=0; i<16; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(lastStyle);
		}
		sheet.addMergedRegion(new CellRangeAddress(workerNo+4+totalCount, workerNo+4+totalCount, 0, 15));
		cell = row.getCell(0);
		cell.setCellValue("制表时间:" +date + "(北京时间)                       导出人:" + name);
	}

这里是插入图片的方法

private void fillExcelLogo(XSSFWorkbook book, String image, Sheet sheet1, Cell finalCell,Row row,int leftTopCellNum) throws IOException {
		if (StringUtils.isBlank(image)) {
			return;
		}
		Cell cell1 = null;
		Cell cell2 = finalCell;
		HttpURLConnection conn = null;
		InputStream inputStream = null;
		BufferedImage image = null;
		try {

			URL url = new URL(waterMarkCompanyLogo);
			conn = (HttpURLConnection) url.openConnection();
			conn.setRequestMethod("GET");
			conn.setConnectTimeout(20 * 1000);
			ByteArrayOutputStream output = new ByteArrayOutputStream();

			inputStream = conn.getInputStream();
			IOUtils.copy(inputStream, output);
			//  必须准备两份资源!!!
			ByteArrayInputStream inputStreamPic = new ByteArrayInputStream(output.toByteArray());
			ByteArrayInputStream imageStream = new ByteArrayInputStream(output.toByteArray());
			image = ImageIO.read(imageStream);

			int pictureIdx = book.addPicture(inputStreamPic, Workbook.PICTURE_TYPE_JPEG);
			float initWith = 0;
			float initHigh = 0;
			float v = row.getHeightInPoints() / 72f;
			//  1 inch = 72 point = 96 px
			initHigh = v * 96f;
			float highScaler = initHigh/(float) image.getHeight();
			initWith = (float)image.getWidth() * highScaler;


			int i1 = cell2.getColumnIndex() + 1 - leftTopCellNum;
			int allColumWithInPx = 0;
            for (int i = 0; i < i1; i++) {
                allColumWithInPx = allColumWithInPx + Math.round(sheet1.getColumnWidthInPixels(row.getCell(leftTopCellNum+i).getColumnIndex()));
            }
            int columWidthInPx = allColumWithInPx / i1;
//			int columWidthInPx = Math.round(sheet1.getColumnWidthInPixels(cell2.getColumnIndex()));
			int i = Math.round(initWith)/ columWidthInPx;

			if (i > 0 && i < (finalCell.getColumnIndex() - leftTopCellNum)) {
				cell1 = row.getCell(cell2.getColumnIndex() - i);
			} else if (i >= (finalCell.getColumnIndex() - leftTopCellNum)) {
				cell1 = row.getCell(leftTopCellNum);
			} else {
				cell1 = cell2;
			}

			CreationHelper helper = sheet1.getWorkbook().getCreationHelper();
			ClientAnchor anchor = helper.createClientAnchor();

			//  图像右上角所在单元格的图像右上角坐标
			anchor.setDx2(Units.pixelToEMU(columWidthInPx));
			anchor.setDy2(Units.toEMU(row.getHeightInPoints())); //

			//  图像左下角所在单元格的图像左下角坐标
			anchor.setDx1(0); // TODO: 2022/3/18 理应事实计算
			anchor.setDy1(0);

			//  图像左下角所处单元格与图像右上角所处单元格
			anchor.setCol1(cell1.getColumnIndex());
			anchor.setRow1(cell1.getRowIndex());
			anchor.setCol2(cell2.getColumnIndex());
			anchor.setRow2(cell2.getRowIndex());

			//  边框自适应(拉动单元格时,图片追随)
			anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
			//  最终插入图片
			Drawing drawing = sheet1.createDrawingPatriarch();
			drawing.createPicture(anchor, pictureIdx);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			inputStream.close();
		}
	}
转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/958962.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号