tcimport.php,testlink添加导入导出Excel文件功能

最近领导要求在testlink上添加Excel格式的导入导出。研究了下php，参考了一些相关文档，最终完成改造工作。

参考文档地址：

http://blog.csdn.net/garinwang/article/details/5910352

http://blog.csdn.net/louishu_hu/article/details/52412553

参考第二篇文档，在testcase.class.php和testsuite.class.php中添加了Excel的导入导出配置

var $import_file_types = array("XML" => "XML" ,"XLS" => "XLS");

var $export_file_types = array("XML" => "XML" ,"XLS" => "XLS");

修改tcImport.php，添加导入部分的代码，在这里主要使用了PHPExcel第三方的插件来完成对Excel的读取工作。

1、添加判断读取Excel类型的文件

switch($args->importType)

{

case 'XML':

$pcheck_fn = "check_xml_tc_tsuite";

$pimport_fn = "importTestCaseDataFromXML";

break;

case 'XLS':

$pcheck_fn = null;

$pimport_fn = "importTestCaseDataFromSpreadsheet";

break;

}

2、读取Excel，并转换为XML类型

function loadContentsToXML(&$db,$content,$parentID,$tproject_id,$userID,$options=null,$nouse,&$isOk){

$xmlTCs = null;

$resultMap = null;

$my = array();

$my['options'] = array('useRecursion' => false, 'importIntoProject' => 0,

'duplicateLogic' => array('hitCriteria' => 'name', 'actionOnHit' => null));

$my['options'] = array_merge($my['options'], (array)$options);

foreach($my['options'] as $varname => $value)

{

$$varname = $value;

}

$xml = simplexml_load_string($content);

$isOk = $xml;

if($xml !== FALSE)

{

$xmlKeywords = $xml->xpath('//keywords');

$kwMap = null;

if ($xmlKeywords)

{

$tproject = new testproject($db);

$loop2do = sizeof($xmlKeywords);

for($idx = 0; $idx < $loop2do ;$idx++)

{

$tproject->importKeywordsFromSimpleXML($tproject_id,$xmlKeywords[$idx]);

}

$kwMap = $tproject->get_keywords_map($tproject_id);

$kwMap = is_null($kwMap) ? null : array_flip($kwMap);

}

if (!$useRecursion && ($xml->getName() == 'testcases') )

{

$resultMap = importTestCasesFromSimpleXML($db,$xml,$parentID,$tproject_id,$userID,$kwMap,$duplicateLogic);

}

if ($useRecursion && ($xml->getName() == 'testsuite'))

{

$resultMap = importTestSuitesFromSimpleXML($db,$xml,intval($parentID),intval($tproject_id),$userID,

$kwMap,$importIntoProject,$duplicateLogic);

}

}

return $resultMap;

}

function importTestCaseDataFromSpreadsheet(&$db,$fileName,$parentID,$tproject_id,$userID,$bRecursive,$importIntoProject = 0,&$isOk){

$xmlTCs = null;

$resultMap = null;

$xml_filename=$fileName . '.xml';

global $args;

$content = "";

if($args->useRecursion)

{

parseSuiteExcelToXml($fileName,$content );

}else{

create_xml_tcspec_from_xls($fileName,$content);

}

$resultMap = loadContentsToXML($db,$content,$parentID,$tproject_id,$userID,$bRecursive,$importIntoProject,$isOk);

return $resultMap;

}

// --------------------------------------------------------------------------------------

/*

function: create_xml_tcspec_from_xls

Using an XSL file, that contains testcase specifications

creates an XML testlink test specification file.

XLS format:

Column Description

1 test case name

2 summary

3 steps

4 expectedresults

First row contains header: name,summary,steps,expectedresults

and must be skipped.

args: xls_filename

xml_filename

returns:

*/

function create_xml_tcspec_from_xls($xls_filename, &$content) {

/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/

$PHPReader = new PHPExcel_Reader_Excel2007();

if(!$PHPReader->canRead($xls_filename)){

$PHPReader = new PHPExcel_Reader_Excel5();

if(!$PHPReader->canRead($xls_filename)){

tLog('no Excel');

return ;

}

}

define('FIRST_DATA_ROW',2);

define('IDX_COL_NAME',2);

define('IDX_COL_SUMMARY',3);

define('IDX_COL_PRECONDITIONS',4);

define('IDX_COL_STEPS',5);

define('IDX_COL_EXPRESULTS',6);

define('IDX_COL_IMPORTANCE',8);

$PHPExcel = $PHPReader->load($xls_filename);

$currentSheet = $PHPExcel -> getSheet(0);

$allRow = $currentSheet -> getHighestRow();

if($allRow < FIRST_DATA_ROW){

return; // >>>----> bye!

}

// $xmlFileHandle = fopen($xml_filename, 'w') or die("can't open file");

$content .= "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";

$content .= "\n";

for($idx = FIRST_DATA_ROW;$idx <= $allRow ; $idx++ ){

$iStepNum = 1;

$name = $currentSheet -> getCellByColumnAndRow(IDX_COL_NAME,$idx) -> getValue();

If ($name <> ""){

$iStepNum = 1;

if ($idx != FIRST_DATA_ROW){

$content .= "\n";

$content .= "\n";

}

//fwrite($xmlFileHandle,"\n");

$summary = $currentSheet -> getCellByColumnAndRow(IDX_COL_SUMMARY,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

$preConditions = $currentSheet -> getCellByColumnAndRow(IDX_COL_PRECONDITIONS,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

$importance = $currentSheet -> getCellByColumnAndRow(IDX_COL_IMPORTANCE,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$step = $currentSheet -> getCellByColumnAndRow(IDX_COL_STEPS,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

$expresults = $currentSheet -> getCellByColumnAndRow(IDX_COL_EXPRESULTS,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$content .= "\n";

$content .= "\n";

$content .="\n";

$content .="\n";

$content .="\n";

$content .="\n";

$content .= "\n";

$content .= "\n";

$content .= "\n";

}else{

//fwrite($xmlFileHandle,"\n");

$iStepNum++;

//fwrite($xmlFileHandle,"\n");

$step = $currentSheet -> getCellByColumnAndRow(IDX_COL_STEPS,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

$expresults = $currentSheet -> getCellByColumnAndRow(IDX_COL_EXPRESULTS,$idx) -> getValue();

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$content .="\n";

$content .= "\n";

$content .= "\n";

$content .= "\n";

}

}

$content .= "\n";

$content .= "\n";

$content .= "\n";

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

// fwrite($xmlFileHandle,"\n");

// fclose($xmlFileHandle);

}

// --------------------------------------------------------------------------------------

/*

function: parseSuiteExcelToXml create_xml_tsspec_from_xls

Using an XSL file, that contains more than one sheet, with each sheet a testsuite

creates an XML testlink test specification file.

XLS format:

First row contains header: name,summary,steps,expectedresults

and must be skipped.

args: xls_filename

xml_filename

returns:

*/

function parseSuiteExcelToXml($fileName,&$content){

tLog("this is fileName:" .$fileName);

/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/

$PHPReader = new PHPExcel_Reader_Excel2007();

if(!$PHPReader->canRead($fileName)){

$PHPReader = new PHPExcel_Reader_Excel5();

if(!$PHPReader->canRead($fileName)){

tLog('no Excel');

return ;

}

}

define('IDX_COL_NAME',2);

define('IDX_COL_SUMMARY',3);

define('IDX_COL_PRECONDITIONS',4);

define('IDX_COL_STEPS',5);

define('IDX_COL_EXPRESULTS',6);

define('IDX_COL_IMPORTANCE',8);

$PHPExcel = $PHPReader->load($fileName);

//$sheetNum = $PHPExcel -> getSheetCount();

//$xmlFileHandle = fopen($xml_filename, 'w') or die("can't open file");

$content .= "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";

$content .= "\n";

$startRow = 2;

//echo "this is rowNumber :" . $allRow . "
";

$loadedSheetNames = $PHPExcel->getSheetNames();

foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {

//echo $sheetIndex,' -> ',$loadedSheetName,'
';

$currentSheet = $PHPExcel -> getSheet($sheetIndex);

$allRow = $currentSheet -> getHighestRow();

if($allRow < $startRow){

return;

}

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$iStepNum = 1;

for($i=$startRow;$i<= $allRow;$i++){

//$cellValue = $activeSheet -> getCellByColumnAndRow(2,$i) -> getValue();

$name = $currentSheet -> getCellByColumnAndRow(IDX_COL_NAME,$i) -> getValue();

if($name <> ""){

$iStepNum = 1;

if($i<>$startRow){

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$content .= "\n";

}

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$summary = $currentSheet -> getCellByColumnAndRow(IDX_COL_SUMMARY,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$preConditions = $currentSheet -> getCellByColumnAndRow(IDX_COL_PRECONDITIONS,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$importance = $currentSheet -> getCellByColumnAndRow(IDX_COL_IMPORTANCE,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .="\n";

$content .="\n";

$content .="\n";

$content .="\n";

$step = $currentSheet -> getCellByColumnAndRow(IDX_COL_STEPS,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$expresults = $currentSheet -> getCellByColumnAndRow(IDX_COL_EXPRESULTS,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$content .= "\n";

}else{

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$iStepNum++;

//fwrite($xmlFileHandle,"\n");

$content .="\n";

$step = $currentSheet -> getCellByColumnAndRow(IDX_COL_STEPS,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$expresults = $currentSheet -> getCellByColumnAndRow(IDX_COL_EXPRESULTS,$i) -> getValue();

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$content .= "\n";

}

}

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

$content .= "\n";

$content .= "\n";

}

//fwrite($xmlFileHandle,"\n");

$content .= "\n";

//fclose($xmlFileHandle);

}

修改tcExport.php文件，添加导出Excel。这里主要是读取生成的XML，使用PHPExcel再次生成Excel文件。

1、添加导出Excel判断

switch($args->exportType)

{

case 'XML':

$gui->export_filename .= ".xml";

$pfn = 'exportTestSuiteDataToXML';

if ($gui->oneTestCaseExport)

{

$pfn = 'exportTestCaseDataToXML';

}

break;

case 'XLS':

$gui->export_filename .= ".xls";

$pfn = 'exportTestSuiteDataToXML';

if ($gui->oneTestCaseExport)

{

$pfn = 'exportTestCaseDataToXML';

}

break;

}

switch($args->exportType){

case 'XML':

downloadContentsToFile($content,$gui->export_filename);

exit();

break;

case 'XLS':

downloadContentsToXls($content,$gui->export_filename);

exit();

break;

}

2、添加导出代码

function downloadContentsToXls($content,$fileName){

//$fileName .= ".xls";

//创建一个excel

$objPHPExcel = new PHPExcel();

//获取默认的sheet,设置选中的sheet

$objPHPExcel->setActiveSheetIndex(0);

createHeadValue($objPHPExcel);

//sheet的数量,跟suite相关

$sheetNum = 0;

//from second

$cellNum =2;

$xmlDoc = new DOMDocument();

$xmlDoc->loadXML($content);

$x = $xmlDoc -> documentElement;

if($x->nodeName==="testsuite"){

//单层结构的suite

if($x -> childNodes[0] ->nodeName != "testsuite"){

foreach($x->attributes as $b){

parseTestSuiteAttributesToExcel($objPHPExcel,$b);

}

}

foreach($x->childNodes as $a){

parseTestSuiteToExcel($objPHPExcel,$a,$sheetNum,$cellNum);

if($a -> nodeName === "testsuite"){

$sheetNum++;

}

}

}else if($x->nodeName==="testcases"){

foreach($x->childNodes as $c){

if($c -> nodeName === "testcase"){

parseTestcasesToExcel($objPHPExcel,$c,$cellNum);

$cellNum ++ ;

}

}

$objPHPExcel->getActiveSheet()->setTitle(str_replace(".xls","",$fileName));

}

ob_end_clean();

header("Content-Type:application/vnd.ms-execl");

header("Content-Type:application/octet-stream");

header("Content-Type:application/download");;

header('Content-Disposition: attachment; filename="' .$fileName. '"');

//保存

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

}

function parseTestSuiteToExcel(&$objPHPExcel,$x,$sheetNum,&$cellNum){

//$docs = "";

if($x -> nodeName === "testsuite"){

if($sheetNum <> 0){

$cellNum = 2; //new create sheet

$objPHPExcel->createSheet();

//chose active sheet

$objPHPExcel->setActiveSheetIndex($sheetNum);

createHeadValue($objPHPExcel);

}

foreach($x->attributes as $b){

parseTestSuiteAttributesToExcel($objPHPExcel,$b);

}

foreach($x->childNodes as $a){

// check is testcase , execel con't have next sheet to create suite

if($a -> nodeName === "testcase"){

parseTestcasesToExcel($objPHPExcel,$a,$cellNum);

$cellNum++;

}

}

}else if($x -> nodeName === "testcase"){ //single level

parseTestcasesToExcel($objPHPExcel,$x,$cellNum);

$cellNum++;

}

}

//作为suite的名称

function parseTestSuiteAttributesToExcel(&$objPHPExcel,$x){

//作为sheet的名称

if($x->name == "name"){

$objPHPExcel->getActiveSheet()->setTitle($x->value);

}

}

//测试用例名称解析

function parseTestCaseAttributesToExcel(&$objPHPExcel,$x,$cellNum){

if($x->name == "name"){ //测试用例名称

$objPHPExcel->getActiveSheet()->setCellValue('C' . $cellNum, $x->value);

}

//if($x->name == "internalid"){ //测试用例名称

//$objPHPExcel->getActiveSheet()->setCellValue('B' . $cellNum, $x->value);

//}

}

//step have multiple cell,so cellNum have to return

function parseTestcasesToExcel(&$objPHPExcel,$x,&$cellNum){

foreach($x->attributes as $b){

parseTestCaseAttributesToExcel($objPHPExcel,$b,$cellNum);

}

foreach($x-> childNodes as $a){

if($a->nodeName==="summary"){ //摘要

$objPHPExcel->getActiveSheet()->setCellValue('D' . $cellNum, replaceStr($a->nodeValue));

}else if($a->nodeName==="preconditions"){ //前置条件

$objPHPExcel->getActiveSheet()->setCellValue('E' . $cellNum, replaceStr($a->nodeValue));

}else if($a->nodeName=== "importance"){//优先级

$objPHPExcel->getActiveSheet()->setCellValue('I' . $cellNum, replaceStr($a->nodeValue));

}else if($a->nodeName=== "externalid"){ //测试用例Id

$objPHPExcel->getActiveSheet()->setCellValue('B' . $cellNum, replaceStr($a->nodeValue));

}else if($a->nodeName==="steps"){

$stepLen = $a-> getElementsByTagName("step")->length;

$steps = $a -> getElementsByTagName("step");

for($i=0;$i

$b = $steps[$i];

if($b->nodeName==="step"){ //多个步骤需要加cellNum

foreach($b->childNodes as $c){

if($c->nodeName === "actions"){

//$docs .= "this is 步骤 actions :" . $c->nodeValue . "
";

$objPHPExcel->getActiveSheet()->setCellValue('F' . $cellNum, replaceStr($c->nodeValue));

}else if($c->nodeName==="expectedresults"){

//$docs .= "this is 期望结果 expectedresults :" . $c->nodeValue . "
";

$objPHPExcel->getActiveSheet()->setCellValue('G' . $cellNum, replaceStr($c->nodeValue));

}

}

}

if(($stepLen-1) <> $i){

$cellNum++;

}

}

}

}

}

function createHeadValue(&$objPHPExcel){

$objPHPExcel->getActiveSheet()->setCellValue('A1', '测试用例集名称');

$objPHPExcel->getActiveSheet()->setCellValue('B1', '测试用例编号');

$objPHPExcel->getActiveSheet()->setCellValue('C1', '测试用例名称');

$objPHPExcel->getActiveSheet()->setCellValue('D1', '摘要');

$objPHPExcel->getActiveSheet()->setCellValue('E1', '前置条件');

$objPHPExcel->getActiveSheet()->setCellValue('F1', '测试步骤');

$objPHPExcel->getActiveSheet()->setCellValue('G1', '预测结果');

$objPHPExcel->getActiveSheet()->setCellValue('H1', '实际结果');

$objPHPExcel->getActiveSheet()->setCellValue('I1', '优先级');

//SET HEIGHT

$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);

//SET WIDTH

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(18);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(24);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(24);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(24);

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(36);

$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(36);

$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);

$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);

//SET AUTO CHANGE LINE

$objPHPExcel->getActiveSheet()->getStyle('A:I')->getAlignment()->setWrapText(true);

//4f81bd

$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFill()->getStartColor()-> setARGB('4f81bd');

//SET FONT COLOR

$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

//SET HEIGHT CENTER

$objPHPExcel->getActiveSheet()->getStyle('A:I')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//SET WIDTH CENTER

$objPHPExcel->getActiveSheet()->getStyle('A:I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

}

function replaceStr($str){

$s = str_replace("

","",$str);

$s1 = str_replace("

","\n",$s);

$s2 = str_replace("

","",$s1);

return str_replace("

","\n",$s2);

}

最后不要忘了在tcExport.php和tcImport.php中分别引入PHPExcel

require_once('codeplex/PHPExcel.php');

require_once('codeplex/PHPExcel/Writer/Excel5.php');

require_once('codeplex/PHPExcel.php');

require_once('codeplex/PHPExcel/Reader/Excel5.php');

require_once('codeplex/PHPExcel/Reader/Excel2007.php');