【java】java处理测试用例excel转xml,自定义格式处理,灵活处理

可扩展标记语言(XML)文件是一种标准的文本文件,它使用特定的标记来描述文档的结构以及其他特性。通常,我们可以通过格式转换的方式来得到XML格式的文件。本文,将通过Java代码介绍如何实现由Excel到XML格式的转换。可用与excel测试用例,xml格式导入testlink等

第一步:导入处理excel相关的包

//java基础包
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.util.Collection;
import java.util.HashMap;

//poi包处理excel
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

//正则表达式处理单元格内容
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.filechooser.FileSystemView;

//当excel单元格变量值为空,取默认值
import java.util.Optional;

第二步:读取本地excel文件,xlsx或者xls格式

private static HashMap<String, File> map = new HashMap<>();

	/**
	 * 输入文件路径,对于嵌套的目录不进行读取
	 *
	 * @param inPath
	 * @return
	 */
	public static HashMap<String, File> getFiles(String inPath) {
		if (null == inPath || inPath.trim().length() == 0)
			return null;
		File filex = new File(inPath);
		if (filex.isDirectory()) {
			File[] files = filex.listFiles();
			if (files.length > 0) {
				for (File file : files) {
					if (file.isFile() && (file.getName().contains(".xlsx") || file.getName().contains(".xls")))
						map.put(file.getName(), file);
					System.out.println("The  filename is:" + file.getName());
					// System.out.println("The file is:" );
				}
			}
		} else if (filex.isFile()) {
			map.put(filex.getName(), filex);

		}
		return map;
	}

第三步:处理读取的excel单元格内容,自动转换为xml格式,并写入本地,此部分可根据实际测试用例编写情况灵活修改代码

/**
	 *
	 * @param map
	 * @param outPath
	 */
	public static void excels2Xml(HashMap<String, File> map, String outPath) {

		if (map == null) {
			return;
		}
		map.forEach((fileName, file) -> {
			BufferedWriter bw = null;
			InputStream is = null;
			try {
				String fn = fileName.replace(".xlsx", ".xml");
				if (fn == fileName) {
					fn = fileName.replace(".xls", ".xml");
				}
				bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(outPath + fn)), "utf-8"));
				is = new FileInputStream(file.getPath());
				Workbook hssfWorkbook = WorkbookFactory.create(is);
				// 获取sheets页
				int sheets = hssfWorkbook.getNumberOfSheets();
				System.out.println("sheets页个数分别是:" + sheets);

				// int sheets = hssfWorkbook.getMaxRows();
				// System.out.println("sheets页个数分别是:"+sheets);

				if (null != hssfWorkbook)
				bw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
				bw.write("<testsuite id=\"\" name=\"\" >\n");
				bw.write("<node_order><![CDATA[]]></node_order>\n");
				bw.write("<details><![CDATA[]]></details> \n");
				
				for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
					Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
					if (hssfSheet == null) {
						continue;
					}
					String summary = "";
					for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
						Row hssfRow = hssfSheet.getRow(rowNum);
						if (hssfRow != null) {
							//用例模块获取
							Cell line0 = hssfRow.getCell(0, Row.CREATE_NULL_AS_BLANK);
							if(!line0.getStringCellValue().equals(summary)){
								summary = line0.getStringCellValue();
								if(rowNum >1){
									bw.write("</testsuite>\n");
								}
								//用例模块
								bw.write("<testsuite name=\""+ summary +"\">\n");
							}
							Cell line1 = hssfRow.getCell(1, Row.CREATE_NULL_AS_BLANK);
							Cell line2 = hssfRow.getCell(2, Row.CREATE_NULL_AS_BLANK);
							
							// 用例优先级
							// Cell line5 = hssfRow.getCell(5);
							int line5 = 1;
							if (hssfRow.getCell(5, Row.CREATE_NULL_AS_BLANK).toString().equals("高")) {
								line5 = 3;
							} else if (hssfRow.getCell(5, Row.CREATE_NULL_AS_BLANK).toString().equals("中")) {
								line5 = 2;
							} else if (hssfRow.getCell(5, Row.CREATE_NULL_AS_BLANK).toString().equals("低")) {
								line5 = 1;
							}
							;
                            //执行类型:手动,自动化
							Cell line6 = hssfRow.getCell(6, Row.CREATE_NULL_AS_BLANK);

							//用例步骤分割符
							String regEx = "#2\\.|#3\\.|#4\\.|#5\\.|#6\\.|#7\\.|#8\\.|#9\\.|#10\\.|#11\\.|#12\\.|#13\\.|#14\\.|#15\\.|#16\\.|#17\\.|#18\\.|#19\\.|#20\\.";
							//String[] regEx1 ={"2.","3.","4.","5.","6.","7.","8.","9."};
							//String regEx="[2-9]\\d\\.";							
							Pattern p = Pattern.compile(regEx);// 字符串转数组处理
							
							// 获取执行步骤
							// String line3_temp =Optional.ofNullable(hssfRow.getCell(3).toString()).orElse("N/A");
							Cell line3_temp = hssfRow.getCell(3, Row.CREATE_NULL_AS_BLANK);														 
							Matcher m3 = p.matcher(line3_temp.getStringCellValue());
							String[] line3 = p.split(line3_temp.getStringCellValue());
							/* 将结束符连接到相应的句子前 */
							if (line3.length > 0) {
								int count = 1;
								while (count < line3.length) {
									if (m3.find()) {
										line3[count] = m3.group() + line3[count];
									}
									count++;
								}

							}
							// 获取期望结果
							Cell line4_temp = hssfRow.getCell(4, Row.CREATE_NULL_AS_BLANK);												
							// (hssfRow.getCell(4)==null)?cell1:line4_temp1;												
							Matcher m4 = p.matcher(line4_temp.getStringCellValue());
							String[] line4 = p.split(line4_temp.getStringCellValue());
							if (line4.length > 0) {
								int count = 1;
								while (count < line4.length) {
									if (m4.find()) {
										line4[count] = m4.group() + line4[count];
									}
									count++;
								}
							}
							
							//用例模块
//							bw.write("<testsuite name=\""+ line0 +"\">\n");
							// 测试用例标题line1
							bw.write("<testcase name=\"" + line1 + "\">\n");
							// 所属模块、摘要line0
							bw.write("<summary><![CDATA[<p>" + line0 + "</p>]]></summary>\n");
							// 前置条件line2
							bw.write("<preconditions><![CDATA[<p>" + line2 + "</p>]]></preconditions>\n");
							// 测试步骤line3、预期结果line4、测试方式\执行类型line6,1手工,2自动
							bw.write("<steps>");
							// 判断执行步骤多还是期望结果多
							if (line3.length >= line4.length) {
								// 判断执行步骤比期望步骤多了几步
								// int k=line3.length-line4.length;

								for (int i = 0; i < line3.length; i++) {
									System.out.println("line30" + i + ":" + line3[i]);
									int j = i + 1;
									// 先按照期望结果循环数据循环
									if (i < line4.length) {

										bw.write("<step><step_number><![CDATA[" + j
												+ "]]></step_number><actions><![CDATA[<p>" + line3[i]
												+ "</p>]]></actions><expectedresults><![CDATA[<p>" + line4[i]
												+ "</p>]]></expectedresults><execution_type><![CDATA[" + line6
												+ "]]></execution_type></step>\n");
									} else {
										bw.write("<step><step_number><![CDATA[" + j
												+ "]]></step_number><actions><![CDATA[<p>" + line3[i]
												+ "</p>]]></actions><expectedresults><![CDATA[<p></p>]]></expectedresults><execution_type><![CDATA["
												+ line6 + "]]></execution_type></step>\n");
									}
								}
							} else {
								for (int i = 0; i < line4.length; i++) {
									int j = i + 1;
									System.out.println("line40" + i + ":" + line4[i]);
									// 先按照执行步骤循环
									if (i < line3.length) {
										bw.write("<step><step_number><![CDATA[" + j
												+ "]]></step_number><actions><![CDATA[<p>" + line3[i]
												+ "</p>]]></actions><expectedresults><![CDATA[<p>" + line4[i]
												+ "</p>]]></expectedresults><execution_type><![CDATA[" + line6
												+ "]]></execution_type></step>\n");
									} else {
										bw.write("<step><step_number><![CDATA[" + j
												+ "]]></step_number><actions><![CDATA[<p></p>]]></actions><expectedresults><![CDATA[<p>"
												+ line4[i] + "</p>]]></expectedresults><execution_type><![CDATA["
												+ line6 + "]]></execution_type></step>\n");
									}
								}
							}
							bw.write("</steps>");
							// 优先级:3高,2中,1低
							bw.write("<importance><![CDATA[" + line5 + "]]></importance>\n");
							// 用例状态,共7种:1草稿,2待评审,3评审中
							bw.write("<status>2</status>\n");
							//
							bw.write("<is_open>1</is_open>\n");
							//
							bw.write("<active>1</active>\n");
							bw.write("</testcase>\n\n");
							
						}
					}
				}
	//			bw.write("</testcases>\n");
				bw.write("</testsuite>\n");
				bw.write("</testsuite>\n");

			} catch (FileNotFoundException e) {
				System.out.println("FileNotFoundException...");
				e.printStackTrace();
			} catch (InvalidFormatException e) {
				System.out.println("InvalidFormatException...");
				e.printStackTrace();
			} catch (IOException e) {
				System.out.println("IOException...");
				e.printStackTrace();
			} finally {
				try {
					if (bw != null)
						bw.close();
					if (is != null)
						is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		});
	}

 第四步:打印转换耗时

public static void run(String path1, String path2) throws Exception {
        long begin = System.nanoTime();;
        System.out.println("begin:"+begin);
        excels2Xml(getFiles(path1), path2);
        long end = System.nanoTime();
        System.out.println("end:"+end);
        System.out.println("The program Run Time : " + (end - begin) / 1000 /1000/ 60.0 + "  ms");
    }

第五步:主函数自动处理excel所在文件读写路径

public static void main(String[] args) {
        //System.out.println(System.getProperty("user.dir"));
		
		FileSystemView fsv = FileSystemView.getFileSystemView();
		File com = fsv.getHomeDirectory();
		System.out.println("桌面路径:" + com);
		//String path = com.toString().replaceAll("\\\\", "//");
		String path = System.getProperty("user.dir").toString().replaceAll("\\\\", "//");
		String path1 = path + "//excel2xml";
		System.out.println("桌面路径path1:" + path1);
		File file = new File(path1);
		if (!file.exists()) {// 如果文件夹不存在
			file.mkdir();// 创建文件夹
		}

		String path2 = path + "//excel2xml//fileoutpath//";
		System.out.println("桌面路径path2:" + path2);
		File file2 = new File(path2);
		if (!file2.exists()) {// 如果文件夹不存在
			file2.mkdir();// 创建文件夹
		}

		try {
			run(path1, path2);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
  • 0
    点赞
  • 2
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术工厂 设计师:CSDN官方博客 返回首页
评论

打赏作者

bulabula2022

1毛钱是我持续编写创作的动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值