【在公司写crud测试用例的excel文档】使用poi-ooxml的XSSFWorkbook建立sheet工作表，终于不用当机器人啦！

已于 2024-11-23 22:35:03 修改
阅读量202 收藏
点赞数 3
CC 4.0 BY-SA版权
文章标签： 测试用例 excel
于 2024-11-23 21:01:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_71817701/article/details/143998499

先看效果图：

目录

1.引依赖：poi-ooxml

2.TableFileds实体

3.Tables实体

4.运行代码

5.效果截图：

1.引依赖：poi-ooxml

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version> 
        </dependency>

2.TableFileds实体


import lombok.Data;

@Data
public class TableFields {

	//字段名
	private String field;

	//字段类型
	private String type;
	
	//字段排序规则
//	private String collation;
	//是否为空
	private String Null;

	//是否为主键
//	private String key;

	//默认值
//	private String Default;

	//其他信息
//	private String Extra;

	//注释
	private String comment;

//	private String privileges;


}

SHOW FULL FIELDS FROM 数据库里的表名

3.Tables实体

import lombok.Data;

@Data
public class Tables {

	private String name;
	private String comment;
}
select * from information_schema.tables where table_schema ='数据库名字' order by table_name

select table_name as name,table_comment as comment from information_schema.tables where table_schema ='数据库名字' order by table_name

4.运行代码



import com.zaxxer.hikari.HikariDataSource;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import word.dto.TableFields;
import word.dto.Tables;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelDemo {

	public static void main(String[] args) throws Exception {
		//数据源
		ExcelDemo excelDemo = new ExcelDemo();

		List<Tables> tableList = excelDemo.getTableList(jdbcTemplate);


		// 调用
		excelDemo.writeToExcel(tableList, jdbcTemplate);
	}

	//

	private static final String DB_URL = "jdbc:mysql://您的数据库IP地址:3306/您的数据库名字?useSSL=false&serverTimezone=UTC";
	private static final String DB_USERNAME = "用户名";
	private static final String DB_PASSWORD = "密码";
	private static final String DB_DRIVER_CLASSNAME = "com.mysql.cj.jdbc.Driver";

	private static final String TABLE_SCHEMA = "您的数据库名字";

	/**
	 * 生成文件名前缀
	 */
	public static final String FILE_NAME = "文件名";

	/**
	 * 生成文件地址
	 */
	public static String FILE_PATH = "D:\\文件夹";//最后文件存放位置:D:\文件夹\文件名.doc";

	public static String OPERATE_NAME="您的名字";

	// 用于获取数据库连接的数据源
	private  DataSource dataSource;

	// 用于执行数据库查询的模板对象
	private static JdbcTemplate jdbcTemplate;


	//目标数据库表名
	private static final String[] targetTableNames = {
		"sps_attendance_reward_punishment_management",
		"sps_borrowing_management",
		"sps_budget_management",

	};

	// 列名相关常量
	private static final String CASE_NUMBER = "用例编号";
	private static final String MODULE = "模块";
	private static final String CASE_NAME = "用例名称";
	private static final String CASE_LEVEL = "用例级别";
	private static final String PRECONDITION = "预置条件";
	private static final String OPERATION_STEPS = "操作步骤";
	private static final String EXPECTED_RESULT = "预期结果";
	private static final String ACTUAL_RESULT = "实际结果";
	private static final String DESIGNER = "设计人";
	private static final String EXECUTOR = "执行人";
	private static final String REMARK = "备注";

	// columnsmodular列名相关常量
	private static final String ADD_1 = "新增1";
	private static final String ADD_2 = "新增2";
	private static final String MODIFY_1 = "修改1";
	private static final String MODIFY_2 = "修改2";
	private static final String DELETE = "删除";

	// 数据库相关常量
	private static final String SQL_SELECT_TABLES = "select table_name as name,table_comment as comment from information_schema.tables where table_schema =? order by table_name";
	private static final String SQL_SHOW_FULL_FIELDS = "SHOW FULL FIELDS FROM ";

	public ExcelDemo() {
		// 初始化数据源和JdbcTemplate
		initializeDataSourceAndJdbcTemplate();
	}

	/**
	 * 初始化数据源和JdbcTemplate对象
	 */
	private void initializeDataSourceAndJdbcTemplate() {
		dataSource = new HikariDataSource();
		((HikariDataSource) dataSource).setJdbcUrl(DB_URL);
		((HikariDataSource) dataSource).setUsername(DB_USERNAME);
		((HikariDataSource) dataSource).setPassword(DB_PASSWORD);
		((HikariDataSource) dataSource).setDriverClassName(DB_DRIVER_CLASSNAME);
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public void writeToExcel(List<Tables> spsTables, JdbcTemplate template) throws IOException {


		// 创建一个新的Excel工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();

		// 创建一个工作表
		XSSFSheet sheet = workbook.createSheet("Sheet1");

		// 创建Excel表头
		createExcelHeader(sheet);

		// 遍历spsTables列表，为每张表生成并填充包含各种操作情况的测试用例数据
		for (int i = 0, spsTablesSize = spsTables.size(); i < spsTablesSize; i++) {
			Tables table = spsTables.get(i);

			Integer indexId=000;

			// 为每张表分别生成新增1的测试用例数据并填充到Excel
			generateAndFillTestCaseData(sheet, table, ADD_1, template,indexId+1);

			// 为每张表分别生成新增2的测试用例数据并填充到Excel
			generateAndFillTestCaseData(sheet, table, ADD_2, template,indexId+2);

			// 为每张表分别生成修改1的测试用例数据并填充到Excel
			generateAndFillTestCaseData(sheet, table, MODIFY_1, template,indexId+3);

			// 为每张表分别生成修改2的测试用例数据并填充到Excel
			generateAndFillTestCaseData(sheet, table, MODIFY_2, template,indexId+4);

			// 为每张表分别生成删除的测试用例数据并填充到Excel
			generateAndFillTestCaseData(sheet, table, DELETE, template,indexId+5);
		}
		// 创建或清理指定文件名的文件
		String docFileName = FILE_PATH + "\\" + FILE_NAME + "-" + ".xlsx";
		try{
			createDirectoryIfNotExists(FILE_PATH);
			createOrCleanFile(docFileName);
		}catch (Exception e){
			e.printStackTrace();
		}

		try (FileOutputStream outputStream = new FileOutputStream(docFileName)) {
			// 将工作簿写入到文件
			workbook.write(outputStream);
			System.out.println("数据已成功写入到Excel文件。");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭工作簿，释放资源
				workbook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 创建指定路径的文件夹，如果不存在的话
	 *
	 * @param directoryPath 文件夹路径
	 */
	private void createDirectoryIfNotExists(String directoryPath) {
		File dir = new File(directoryPath);
		dir.mkdirs();
	}

	/**
	 * 创建或清理指定文件名的文件
	 *
	 * @param fileName 文件名
	 */
	private void createOrCleanFile(String fileName) throws Exception {
		File file = new File(fileName);
		if (file.exists() && file.isFile()) {
			file.delete();
		}
		file.createNewFile();
	}

	private void createExcelHeader(XSSFSheet sheet) {
		Map<Integer, String> columnsToRead = new HashMap<>();
		columnsToRead.put(0, CASE_NUMBER);
		columnsToRead.put(1, MODULE);
		columnsToRead.put(2, CASE_NAME);
		columnsToRead.put(3, CASE_LEVEL);
		columnsToRead.put(4, PRECONDITION);
		columnsToRead.put(5, OPERATION_STEPS);
		columnsToRead.put(6, EXPECTED_RESULT);
		columnsToRead.put(7, ACTUAL_RESULT);
		columnsToRead.put(8, DESIGNER);
		columnsToRead.put(9, EXECUTOR);
		columnsToRead.put(10, REMARK);

		// 创建表头行
		Row row1 = sheet.createRow(0);
		for (int i = 0, size = columnsToRead.size(); i < size; i++) {
			Cell cell = row1.createCell(i);
			cell.setCellValue(columnsToRead.get(i));
		}
	}

	private void generateAndFillTestCaseData(XSSFSheet sheet, Tables table, String operationType, JdbcTemplate template,Integer indexId) {
		int rowIndex = getNextAvailableRowIndex(sheet);

		// 创建行并存储到数组中（这里假设你可能还需要保留行对象数组的逻辑，如果不需要可省略）
		Row row = sheet.createRow(rowIndex);

		// 填充当前行的各个单元格数据
		fillCellData(row, table, operationType, template,indexId);
	}

	private int getNextAvailableRowIndex(Sheet sheet) {
		return sheet.getLastRowNum() + 1;
	}

	private void fillCellData(Row row, Tables table, String operationType, JdbcTemplate template, Integer indexId) {
		Map<Integer, String> columnsmodular = new HashMap<>();
		columnsmodular.put(0, ADD_1);
		columnsmodular.put(1, ADD_2);
		columnsmodular.put(2, MODIFY_1);
		columnsmodular.put(3, MODIFY_2);
		columnsmodular.put(4, DELETE);

		// 填充用例编号单元格数据
		Cell caseNumberCell = row.createCell(0);
		caseNumberCell.setCellValue(table.getName() + "--0" +indexId);

		// 填充模块单元格数据
		Cell moduleCell = row.createCell(1);
		moduleCell.setCellValue(table.getComment());

		// 填充用例名称单元格数据
		Cell caseNameCell = row.createCell(2);
		// 根据操作类型生成用例名称（这里只是示例，可根据实际情况修改）
		caseNameCell.setCellValue( operationType);

		// 填充用例级别单元格数据
		Cell caseLevelCell = row.createCell(3);
		caseLevelCell.setCellValue("高");

		// 填充预置条件单元格数据
		Cell preconditionCell = row.createCell(4);
		StringBuffer preconditionOperation = new StringBuffer();
		switch (operationType) {
			case ADD_1:
			case ADD_2:
				preconditionOperation.append("1.点击新增按钮");
				break;
			case MODIFY_1:
			case MODIFY_2:
				preconditionOperation.append("1.点击修改规则");
				break;
			case DELETE:
				preconditionOperation.append("1.勾选数据\n" +
					"2.点击删除按钮");
				break;
		}
		preconditionCell.setCellValue(String.valueOf(preconditionOperation));
		preconditionOperation.setLength(0);

		// 填充操作步骤单元格数据
		Cell operationStepsCell = row.createCell(5);
		StringBuffer operation = new StringBuffer();
		// 获取表字段
		String sql2 = SQL_SHOW_FULL_FIELDS + table.getName();
		List<TableFields> fileds = template.query(sql2, new BeanPropertyRowMapper<>(TableFields.class));

		// 筛选出create_time等字段
		fileds.removeIf(field ->field.getField().contains("create_time") ||
			field.getField().contains("update_time") ||
			field.getField().contains("create_user") ||
			field.getField().contains("create_user") ||
			field.getField().contains("create_dept") ||
			field.getField().contains("update_dept") ||
			field.getField().contains("is_deleted") ||
			field.getField().contains("tenant_id") ||
			field.getField().contains("status")||
			field.getField().contains("id"));
		int index = 1;

		boolean isMatched=false;
		for (TableFields field : fileds) {
			if(isMatched){
				break;
			}
			switch (operationType) {
				case ADD_1:
				case ADD_2:
					if (field.getType().contains("datetime") || field.getType().contains("tinyint") ) {
						operation.append(index + ".").append("选择").append(field.getComment());
					} else if (field.getComment().contains("附件") || field.getComment().contains("图片") ) {
						operation.append(index + ".").append("上传").append(field.getComment());
					} else {
						operation.append(index + ".").append("输入").append(field.getComment());
					}
					break;

				case MODIFY_1:
				case MODIFY_2:
					operation.append(index + ".").append("修改").append(field.getComment());

					break;
				case DELETE:
					operation.append("1.点击删除按钮");
					isMatched=true;
					break;

			}
			index++;
		}
		switch (operationType) {
			case ADD_1:
				operation.append(index + ".").append("点击保存");
				break;
			case ADD_2:
				operation.append(index + ".").append("点击取消");
				break;

			case MODIFY_1:
				break;
			case MODIFY_2:
				operation.append(index + ".").append("点击取消");

				break;
			case DELETE:

				break;

		}
		operationStepsCell.setCellValue(String.valueOf(operation));
		operation.setLength(0);

		// 填充预期结果单元格数据
		Cell expectedResultCell = row.createCell(6);
		switch (operationType) {
			case ADD_1:
				expectedResultCell.setCellValue("1.保存成功，列表数据显示新增的数据");
				break;
			case ADD_2:
				expectedResultCell.setCellValue("1.取消成功，列表没有新增的数据");
			case MODIFY_1:
				expectedResultCell.setCellValue("1.修改成功，列表对应修改信息回显");
				break;
			case MODIFY_2:
				expectedResultCell.setCellValue("1.取消成功，列表没有更新的数据信息");
				break;
			case DELETE:
				expectedResultCell.setCellValue("1.数据删除成功");
				break;

		}

		// 填充实际结果单元格数据
		Cell actualResultCell = row.createCell(7);
		actualResultCell.setCellValue("");

		// 填充设计人单元格数据
		Cell designerCell = row.createCell(8);
		designerCell.setCellValue(OPERATE_NAME);

		// 填充执行人单元格数据
		Cell executorCell = row.createCell(9);
		executorCell.setCellValue(OPERATE_NAME);

		// 填充备注单元格数据
		Cell remarkCell = row.createCell(10);
		remarkCell.setCellValue("");
	}

	public List<Tables> getTableList(JdbcTemplate template) {
		List<Tables> tables = jdbcTemplate.query(SQL_SELECT_TABLES, new BeanPropertyRowMapper<>(Tables.class), TABLE_SCHEMA);

		List<Tables> spsTables = new ArrayList<>();
		
		for (Tables table : tables) {
			for (String targetTableName : targetTableNames) {
				if (table.getName().equals(targetTableName)) {
					spsTables.add(table);
					break; // 找到匹配的表名就添加到spsTables并跳出内层循环，继续下一个表的判断
				}
			}
//			if (table.getName().startsWith("sps_")) {
//				spsTables.add(table);
//			}
		}
		return spsTables;
	}


}

5.效果截图：

小郭学JAVA.
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值