【Java】小工具:Xmind解析为Excel(原创)
xmind自带的转excel表格不是自己期望的,主要有:
1)xmind层级和excel表的列一一对应,个人更希望xmind除了叶子结点为预期结果单独列出外,其它部分都算测试步骤中,以“—”隔开
2)xmind中除了测试用例,可能还包含一些测试步骤或备注,步骤可放到excel表中,但备注一般不需要,希望可以做出区分
3)可以标记出优先级
4)测试结果包含一些默认的下拉选项
5)可以自动生成冒烟测试、一轮、二轮测试等
大概像这样:
xmind文件:

excel文件:


缺点很明显:一个java小工具,只适合个人使用(个人更喜欢用xmind写用例,用excel测试),不能共享,每次修改xmind文件都需要重新转换
测试xmind文件为xmind8,对其它xmind文件不一定能友好支持
打包的jar文件:链接 https://pan.baidu.com/s/1-BlO9tKx2BMbZt9dgU8OJQ 提取码 5fae
1. 导包
install:install-file -Dfile=C:\Users\Administrator\Desktop\xmind-3.7.jar
-DgroupId=com.dave.local
-DartifactId=xmind
-Dversion=3.7
-Dpackaging=jar
2. POM文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.dave.exp</groupId>
<artifactId>xmind_demo</artifactId>
<version>1.0</version>
<packaging>jar</packaging>
<name>xmind_demo</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>com.dave.local</groupId>
<artifactId>xmind</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<finalName>Xmind解析</finalName>
<resources>
<resource>
<directory>src/main/resources</directory>
<filtering>false</filtering>
<excludes>
<exclude>**/*.txt</exclude>
</excludes>
</resource>
</resources>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-jar-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
<!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
<plugin>
<artifactId>maven-site-plugin</artifactId>
<version>3.7.1</version>
</plugin>
<plugin>
<artifactId>maven-project-info-reports-plugin</artifactId>
<version>3.0.0</version>
</plugin>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<configuration>
<appendAssemblyId>false</appendAssemblyId>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
<archive>
<manifest>
<!-- 此处指定main方法入口的class -->
<mainClass>com.dave.main.Main</mainClass>
</manifest>
</archive>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals>
<goal>assembly</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
3. Java代码
- 代码结构

- JXLTitle
package com.dave.bean; public class JXLTitle { private String titleName; private int titleLength; public JXLTitle(){ super(); } public JXLTitle(String titleName, int titleLength){ this.titleName = titleName; this.titleLength = titleLength; } public String getTitleName() { return titleName; } public void setTitleName(String titleName) { this.titleName = titleName; } public int getTitleLength() { return titleLength; } public void setTitleLength(int titleLength) { this.titleLength = titleLength; } }
- TestCase
package com.dave.bean; /** * 测试用例类 */ public class TestCase { private String marker; //测试用例标记,标记步骤或优先级 private String context; //测试用例内容 public TestCase() { } public TestCase(String context){ this.context = context; } public TestCase(String marker, String context) { this.marker = marker; this.context = context; } public String getMarker() { return marker; } public void setMarker(String marker) { this.marker = marker; } public String getContext() { return context; } public void setContent(String context) { this.context = context; } @Override public String toString() { return context+" "+marker; } }
- Mark
package com.dave.enu; public enum Mark { P1("[MKRRef#priority-1]","P1"), P2("[MKRRef#priority-2]","P2"), P3("[MKRRef#priority-3]","P3"), P4("[MKRRef#priority-4]","P4"),P("[MKRRef#flag-red]","P"),red("[MKRRef#star-red]","Red"), NULL("[]",""); private String markRef; private String mark; //构造方法 private Mark(String markRef, String mark){ this.markRef = markRef; this.mark = mark; } //get set方法 public String getMarkRef() { return markRef; } public void setMarkRef(String markRef) { this.markRef = markRef; } public String getMark() { return mark; } public void setMark(String mark) { this.mark = mark; } //获取标签 public static String getMark(String markRef){ for(Mark mark:Mark.values()){ if(mark.getMarkRef().equals(markRef)){ return mark.getMark(); } } return ""; } }
- Main
package com.dave.main; import com.dave.service.SwingService; import javax.swing.*; public class Main { public static void main(String[] args) { SwingUtilities.invokeLater(new Runnable() { @Override public void run() { new SwingService().createAndShowGUI(); } }); } }
- SwingService
package com.dave.service; import com.dave.bean.JXLTitle; import com.dave.bean.TestCase; import com.dave.util.POIExcelUtil; import com.dave.util.XmindUtil; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.swing.*; import javax.swing.filechooser.FileFilter; import java.awt.*; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.io.File; import java.util.ArrayList; import java.util.List; public class SwingService { public void createAndShowGUI(){ JFrame frame = new JFrame("xmind用例转换"); frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); frame.setSize(650,120); frame.setLocationRelativeTo(null); final JPanel panel = new JPanel(); panel.setLayout(null); final JTextField filePath = new JTextField(20); filePath.setBounds(10, 25, 400, 35); filePath.setFont(new Font("宋体", Font.PLAIN, 24)); filePath.setEditable(false); panel.add(filePath); JButton scanButton = new JButton("浏览"); scanButton.setBounds(420, 25, 100, 35); scanButton.setFont(new Font("宋体", Font.BOLD, 24)); panel.add(scanButton); JButton confirmButton = new JButton("转换"); confirmButton.setBounds(530, 25, 100, 35); confirmButton.setFont(new Font("宋体", Font.BOLD, 24)); panel.add(confirmButton); frame.add(panel); frame.setResizable(false); frame.setVisible(true); scanButton.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { JFileChooser jfc = new JFileChooser(); jfc.setPreferredSize(new Dimension(600,400)); setFileChooserFont(jfc.getComponents()); jfc.setFileFilter(new FileFilter() { @Override public boolean accept(File f) { if(f.getName().endsWith(".xmind")){ return true; } return false; } @Override public String getDescription() { return "*.xmind"; } }); jfc.setFileSelectionMode(JFileChooser.FILES_ONLY); jfc.showDialog(new JLabel(), "选择"); File file = jfc.getSelectedFile(); if (file != null && file.isFile()) { filePath.setText(file.getAbsolutePath()); } } }); confirmButton.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { String path = filePath.getText(); if(path.equals("") || !path.endsWith(".xmind")){ JOptionPane.showMessageDialog(panel, "未获取到xmind路径", "信息",JOptionPane.WARNING_MESSAGE); return; } XSSFWorkbook xwb = null; try { java.util.List<TestCase> testCases = XmindUtil.xmindToList(path); xwb = POIExcelUtil.createWorkbook(); List<JXLTitle> sheetTitles = new ArrayList<JXLTitle>(); sheetTitles.add(new JXLTitle("序号",8)); sheetTitles.add(new JXLTitle("优先级",10)); sheetTitles.add(new JXLTitle("测试用例",120)); sheetTitles.add(new JXLTitle("是否通过",15)); sheetTitles.add(new JXLTitle("备注",60)); TestCaseService testCaseService = new TestCaseService(); testCaseService.getTestCase(xwb, "研发自测", sheetTitles, 1, 0, "P1", testCases); testCaseService.getTestCase(xwb, "冒烟测试", sheetTitles, 1, 0, "P1", testCases); testCaseService.getTestCase(xwb, "一轮测试", sheetTitles, 1, 0, "All", testCases); testCaseService.getTestCase(xwb, "二轮测试", sheetTitles, 1, 0, "All", testCases); testCaseService.getTestCase(xwb, "上线测试", sheetTitles, 1, 0, "Online", testCases); JOptionPane.showMessageDialog(panel, "转换完成", "信息",JOptionPane.WARNING_MESSAGE); POIExcelUtil.closeWorkbook(xwb,path.replace(".xmind",".xlsx")); } catch (Exception e1) { e1.printStackTrace(); } } }); } private static void setFileChooserFont(Component[] comp) { for(int x = 0; x < comp.length; x++) { if(comp[x] instanceof Container) setFileChooserFont(((Container)comp[x]).getComponents()); try{comp[x].setFont(new Font("宋体",Font.PLAIN,16));} catch(Exception e){}//do nothing } } }
- TesCaseService
package com.dave.service; import com.dave.bean.JXLTitle; import com.dave.bean.TestCase; import com.dave.util.POIExcelUtil; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.*; import java.util.List; public class TestCaseService { /** * 生成测试用例sheet * @param xwb 工作簿 * @param sheetName 工作表名称 * @param sheetTiles 工作表标题(名称和列宽) * @param freezeRowNum 冻结行 * @param freezeColNum 冻结列 * @param caseType 用例类型:P1:P1级用例(用于冒烟测试和研发自测用例,只有用例) All:所有用例,包括注释和步骤 * * Online:线上测试(P1、P2用例) * @param testCases 测试用例(Xmind解析生成的用例链表) */ public void getTestCase(XSSFWorkbook xwb, String sheetName, List<JXLTitle> sheetTiles, int freezeRowNum, int freezeColNum, String caseType, List<TestCase> testCases){ //创建sheet和标题 XSSFSheet sheet = POIExcelUtil.createSheet(xwb, sheetName, sheetTiles, freezeRowNum, freezeColNum); //内容样式 XSSFCellStyle contentXcs = POIExcelUtil.setWCF(xwb, "宋体", 12, false, (short)-1, true, false, true, (short)-1, BorderStyle.THIN); XSSFCellStyle contentXcs_center = POIExcelUtil.setWCF(xwb, "宋体", 12, false, (short)-1, true, true, true, (short)-1, BorderStyle.THIN); //注释样式 XSSFCellStyle markXcs = POIExcelUtil.setWCF(xwb, "宋体", 12, false, IndexedColors.RED.index, true, false, true, (short)-1, BorderStyle.THIN); XSSFCellStyle markXcs_center = POIExcelUtil.setWCF(xwb, "宋体", 12, false, IndexedColors.RED.index, true, true, true,(short)-1, BorderStyle.THIN); //步骤样式 XSSFCellStyle processXcs = POIExcelUtil.setWCF(xwb, "宋体", 12, false, IndexedColors.BLUE.index, true, false, true, (short)-1, BorderStyle.THIN); XSSFCellStyle processXcs_center = POIExcelUtil.setWCF(xwb, "宋体", 12, false, IndexedColors.BLUE.index, true, true, true, (short)-1, BorderStyle.THIN); XSSFRow row; XSSFCell cell; String marker; String[] results = {"通过","不通过","通过但有风险","阻塞","跳过"," "}; //用于生成下拉列表(数据有效性) XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); //设置下拉列表数据 XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(results); //创建下拉列表数据范围 CellRangeAddressList addressList; //绑定下拉列表与范围 XSSFDataValidation validation; /** * 获取研发自测/冒烟测试用例 或 上线测试用例 */ if(caseType.equals("P1") || caseType.equals("Online")){ int rowid = 1; for(TestCase testCase : testCases){ marker = testCase.getMarker(); if(marker.equals("P1") || marker.equals("P2")){ if(caseType.equals("P1") && marker.equals("P2")){ continue; } row = sheet.createRow(rowid); cell = row.createCell(0); //序列号 cell.setCellValue(rowid); cell.setCellStyle(contentXcs_center); cell = row.createCell(1); //优先级列 cell.setCellValue(marker);
cell.setCellStyle(contentXcs_center); cell = row.createCell(2); //测试用例列 cell.setCellValue(testCase.getContext()); cell.setCellStyle(contentXcs); cell = row.createCell(3); //测试结果列,先设置单元格样式,for循环后设置为下拉列表 cell.setCellStyle(contentXcs_center); cell = row.createCell(4); //备注列 cell.setCellStyle(contentXcs); rowid++; } } /** * 测试结果列添加下拉列表 */ //设置第4列的1~rowid-1行为下拉列表 if(rowid > 1){ addressList = new CellRangeAddressList(1,rowid-1,3,3); //绑定下拉列表与范围 validation = (XSSFDataValidation)dvHelper.createValidation( dvConstraint,addressList); sheet.addValidationData(validation); } } /** * 获取所有测试用例 */ if(caseType.equals("All")){ for(int i=0; i<testCases.size(); i++){ marker = testCases.get(i).getMarker(); if(marker.equals("P")){ //步骤 row = sheet.createRow(i+1); cell = row.createCell(0); //序号列 cell.setCellValue(i+1); cell.setCellStyle(contentXcs_center); cell = row.createCell(1); //优先级列 cell.setCellValue("步骤"); cell.setCellStyle(processXcs_center); cell = row.createCell(2); //测试用例列 cell.setCellValue(testCases.get(i).getContext()); cell.setCellStyle(processXcs); row.createCell(3).setCellStyle(contentXcs_center); //测试结果列,空值 }else if(marker.equals("Red")){ row = sheet.createRow(i+1); cell = row.createCell(0); //序号列 cell.setCellValue(i+1); cell.setCellStyle(contentXcs_center); cell = row.createCell(1); //优先级列 cell.setCellValue("注释"); cell.setCellStyle(markXcs_center); cell = row.createCell(2); //测试用例列 cell.setCellValue(testCases.get(i).getContext()); cell.setCellStyle(markXcs); row.createCell(3).setCellStyle(contentXcs_center); //测试结果列,空值 }else{ row = sheet.createRow(i+1); cell = row.createCell(0); //序号列 cell.setCellValue(i+1); cell.setCellStyle(contentXcs_center); cell = row.createCell(1); //优先级列 cell.setCellValue(marker); cell.setCellStyle(contentXcs_center); cell = row.createCell(2); //测试用例列 cell.setCellValue(testCases.get(i).getContext()); cell.setCellStyle(contentXcs); cell = row.createCell(3); //测试结果列 //设置当前行的第4列为下拉列表 addressList = new CellRangeAddressList(i+1,i+1,3,3); //绑定下拉列表与范围 validation = (XSSFDataValidation)dvHelper.createValidation( dvConstraint,addressList); sheet.addValidationData(validation); cell.setCellStyle(contentXcs_center); //测试结果列 } row.createCell(4).setCellStyle(contentXcs); //备注列 } } } }
- POIExcelUtil
package com.dave.util; import com.dave.bean.JXLTitle; import jxl.write.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.xssf.usermodel.*; import java.io.*; import java.util.List; public class POIExcelUtil { /** * 创建工作簿 * @return */ public static XSSFWorkbook createWorkbook(){ return new XSSFWorkbook(); } /** * 创建可编辑的工作表,设置好工作表的标题列及列宽 * @param xwb 工作簿,在此工作簿上创建工作表 * @param sheetName 工作表名称 * @param sheetTitles 工作表标题(设置标题名称、宽度) * @param freezeRowNum 行冻结(一般用于冻结首行) 值小于1时,不冻结 * @param freezeColNum 列冻结 值小于1时,不冻结 */ public static XSSFSheet createSheet(XSSFWorkbook xwb, String sheetName, List<JXLTitle> sheetTitles, int freezeRowNum, int freezeColNum){ XSSFSheet sheet = xwb.createSheet(sheetName); XSSFRow row = null; XSSFCell cell = null; XSSFCellStyle xcs = setWCF(xwb, "宋体" ,12, true, (short)-1, true, true, false, IndexedColors.LIGHT_BLUE.index, BorderStyle.THIN); //标题样式 row = sheet.createRow(0); //首行,标题行 for(int i=0; i<sheetTitles.size(); i++){ //添加标题内容 cell = row.createCell(i); cell.setCellValue(sheetTitles.get(i).getTitleName()); cell.setCellStyle(xcs); sheet.setColumnWidth(i, sheetTitles.get(i).getTitleLength()*256); } sheet.createFreezePane(freezeColNum, freezeRowNum); return sheet; } /** * 设置单元格样式 * @param xwb 工作簿 * @param fontName 字体类型(宋体,楷体...) * @param fontSize 字体大小 * @param bold 字体是否加粗 * @param fontColor 字体颜色 * @param middle 单元格是否上下居中 * @param center 单元格是否左右居中 * @param swap 单元格是否自动换行 * @param bgColor 单元格颜色,纯色填充 * @param bs 边框样式(全边框) * @return */ public static XSSFCellStyle setWCF(XSSFWorkbook xwb, String fontName, int fontSize, boolean bold, short fontColor, boolean middle, boolean center, boolean swap, short bgColor, BorderStyle bs){ XSSFCellStyle xcs = xwb.createCellStyle(); XSSFFont font = xwb.createFont(); font.setFontName(fontName); font.setFontHeight(fontSize); if(bold){ font.setBold(true); } if(fontColor >= 0 && fontColor <= 64){ font.setColor(fontColor); } xcs.setFont(font); if(middle){ xcs.setVerticalAlignment(VerticalAlignment.CENTER); } if(center){ xcs.setAlignment(HorizontalAlignment.CENTER); } if(swap){ xcs.setWrapText(true); } if(bgColor >=0 && bgColor <= 64){ xcs.setFillPattern(FillPatternType.SOLID_FOREGROUND); xcs.setFillForegroundColor(bgColor); } xcs.setBorderLeft(bs); xcs.setBorderTop(bs); xcs.setBorderRight(bs); xcs.setBorderBottom(bs); return xcs; } /** * 关闭工作簿 * @param xwb 待关闭的工作簿 * @param excelPath 工作簿文件 * @throws IOException */ public static void closeWorkbook(XSSFWorkbook xwb, String excelPath) throws IOException { FileOutputStream fos = new FileOutputStream(new File(excelPath)); xwb.write(fos); fos.close(); } }
- XmindUtil
package com.dave.util; import com.dave.bean.TestCase; import com.dave.enu.Mark; import org.xmind.core.*; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; public class XmindUtil { /** * 获取工作簿 * IWorkbook:表示整个思维导图 * @param xmindPath:xmind文件路径 */ public static IWorkbook getIWorkbook(String xmindPath) throws IOException, CoreException { if (builder == null){ builder = Core.getWorkbookBuilder();// 初始化builder } return builder.loadFromFile(new File(xmindPath)); } /** * 获取默认工作表的根主题 * xmind关系:工作簿(IWorkbook)——>工作表(ISheet)——>主题(ITopic) * @param iWorkbook:xmind工作簿 */ public static ITopic getRootTopic(IWorkbook iWorkbook){ return iWorkbook.getPrimarySheet().getRootTopic(); } /** * 获取从根目录到每一个叶子节点的的路径 */ public static List<TestCase> getAllTestCase(ITopic rootTopic){ if(rootTopic == null){ return null; } return getAllTestCaseIter(new TestCase(Mark.getMark(rootTopic.getMarkerRefs().toString()),getContext(rootTopic)), rootTopic.getAllChildren()); } public static List<TestCase> getAllTestCaseIter(TestCase parentTestCase,List<ITopic> childrenINodes){ for(ITopic childrenINode:childrenINodes){ String parentContext = parentTestCase.getContext(); String parentMarker = parentTestCase.getMarker(); String childrenContext = getContext(childrenINode); String childrenMarker = Mark.getMark(childrenINode.getMarkerRefs().toString()); if(!childrenMarker.equals("")){ parentMarker = childrenMarker; } if(childrenINode.getAllChildren().size() == 0){ list.add(new TestCase(parentMarker,parentContext+" — "+childrenContext)); }else{ getAllTestCaseIter(new TestCase(parentMarker,parentContext+" — "+childrenContext), childrenINode.getAllChildren()); } } return list; } /** * 解析Xmind文件,获取所有的行 */ public static List<TestCase> xmindToList(String xmindPath) throws IOException, CoreException { return getAllTestCase(getRootTopic(getIWorkbook(xmindPath))); } /** * 获取某节点的内容,去掉换行 * @param iTopic: 当前节点 */ public static String getContext(ITopic iTopic){ return iTopic.getTitleText().replaceAll("\r|\n"," "); //去掉换行 } private static IWorkbookBuilder builder = null; private static List<TestCase> list = new ArrayList<TestCase>(); }
4.支持的xmind标签

5.打包命令(可执行的jar包)
mvn clean package -Dmaven.test.skip=true assembly:assembly

浙公网安备 33010602011771号