Fix bug importing Test Actions & Expected Result from XLS

[loannguyen]

I create an Excel file with 4 columns:
- Test Case Name
- Summary
- Actions
- Expected Results
I enter test cases. I use Import Test Case function, select XLS option, browse to the Excel file and click Import button. The system displays results stating that test cases are imported successfully.

However, I viewed the test cases and found that only Test Case Name and Summary are imported. Actions and Expected Results are NOT imported.

I have modified the source code of .....\testlink\lib\testcases\tcImport.php to import Actions and Expected result.

If you want to fix the bug, find and replace source code of the function "create_xml_tcspec_from_xls" with the following code:

------------------------------------------------
function create_xml_tcspec_from_xls($xls_filename,$xml_filename)
{
define('FIRST_DATA_ROW',2);
define('IDX_COL_NAME',1);
define('IDX_COL_SUMMARY',2);
define('IDX_COL_ACTIONS',3);
define('IDX_COL_EXPRESULTS',4);

$xls_handle = new Spreadsheet_Excel_Reader();

$xls_handle->setOutputEncoding(config_get('charset'));
$xls_handle->read($xls_filename);
$xls_rows = $xls_handle->sheets[0]['cells'];
$xls_row_qty = sizeof($xls_rows);

if($xls_row_qty < FIRST_DATA_ROW)
{
return; // >>>----> bye!
}

$xmlFileHandle = fopen($xml_filename, 'w') or die("can't open file");
fwrite($xmlFileHandle,"<testcases>\n");

for($idx = FIRST_DATA_ROW; $idx <= $xls_row_qty; $idx++ )
{
$name = htmlspecialchars($xls_rows[$idx][IDX_COL_NAME]);

// $summary = htmlspecialchars(iconv("CP1252","UTF-8",$xls_rows[$idx][IDX_COL_SUMMARY]));
// 20090117 - contribution - BUGID 1992 // 20090402 - BUGID 1519
// $summary = str_replace('…',"...",$xls_rows[$idx][IDX_COL_SUMMARY]);
$summary = convert_special_char($xls_rows[$idx][IDX_COL_SUMMARY]);
$summary = nl2p(htmlspecialchars($summary));

// 20090117 - BUGID 1991,1992 // 20090402 - BUGID 1519
// $steps = str_replace('…',"...",$xls_rows[$idx][IDX_COL_ACTIONS]);
//$steps = convert_special_char($xls_rows[$idx][IDX_COL_ACTIONS]);
//$steps = nl2p(htmlspecialchars($steps));
//fwrite($xmlFileHandle,"<steps><![CDATA[".$steps."]]></steps>\n");

$step_number = 1;

$actions = convert_special_char($xls_rows[$idx][IDX_COL_ACTIONS]);
$actions = nl2p(htmlspecialchars($actions));

// 20090117 - BUGID 1991,1992 // 20090402 - BUGID 1519
// $expresults = str_replace('…',"...",$xls_rows[$idx][IDX_COL_EXPRESULTS]);
$expresults = convert_special_char($xls_rows[$idx][IDX_COL_EXPRESULTS]);
$expresults = nl2p(htmlspecialchars($expresults));
//fwrite($xmlFileHandle,"<expectedresults><![CDATA[".$expresults."]]></expectedresults>\n");


fwrite($xmlFileHandle,"<testcase name=" . '"' . $name. '"'.">\n");

fwrite($xmlFileHandle,"<summary><![CDATA[" . $summary . "]]></summary>\n");

//generate xml data for step
fwrite($xmlFileHandle,"<steps>\n");
fwrite($xmlFileHandle,"<step>\n");
fwrite($xmlFileHandle,"<step_number>" . $step_number . "</step_number>\n");
fwrite($xmlFileHandle,"<actions><![CDATA[" . $actions . "]]></actions>\n");
fwrite($xmlFileHandle,"<expectedresults><![CDATA[" . $expresults . "]]></expectedresults>\n");
fwrite($xmlFileHandle,"</step>\n");
fwrite($xmlFileHandle,"</steps>\n");


fwrite($xmlFileHandle,"</testcase>\n");
}
fwrite($xmlFileHandle,"</testcases>\n");
fclose($xmlFileHandle);

}
------------------------------------------------
Note: With this function, you can import test case with only One step.

[istream]

This is great. I replaced the original function with your code and able to import from xls file. Can you pls pls make it work for multiple lines in a cell, i.e have test cases with multiple steps in one cell.
Thanks.

[jyotsna]

Hi istream,

Thanks for the code provided to import test cases with test case name, summary,test steps and expected results.
But i need to import pre -condition also. How can i do that. I'm unable to get the precondition data. Could you please let me know, if you know the solution for this.

[santosh]

How can i import multiple test cases from excel ? As per the fix it will only import single row, If i need to import all the test cases from excel what steps needs to be done?

Thanks
Santosh

[krazywar]

Hi istream,

Great work! I was wondering if it would also be possible to import from xls with custom fields and what that change would need to be.

Thanks!

[amalbakri]

thnak you loan,

i propose a script that can import more than testcase with steps.
It's based on your script:
function create_xml_tcspec_from_xls($xls_filename,$xml_filename)
{
define('FIRST_DATA_ROW',2);
define('IDX_COL_NAME',1);
define('IDX_COL_SUMMARY',2);
define('IDX_COL_ACTIONS',3);
define('IDX_COL_EXPRESULTS',4);
$next=11;

$xls_handle = new Spreadsheet_Excel_Reader();

$xls_handle->setOutputEncoding(config_get('charset'));
$xls_handle->read($xls_filename);
$xls_rows = $xls_handle->sheets[0]['cells'];
$xls_row_qty = sizeof($xls_rows);
$xmlFileHandle = fopen($xml_filename, 'w') or die("can't open file");
fwrite($xmlFileHandle,"<testcases>\n");
for($idx = FIRST_DATA_ROW; $idx <= $xls_row_qty; $idx= $idx + $next )
{

if($xls_row_qty < FIRST_DATA_ROW)
{
return; // >>>----> bye!
}







if($idx==FIRST_DATA_ROW)
{

$name = htmlspecialchars($xls_rows[$idx][IDX_COL_NAME]);

// $summary = htmlspecialchars(iconv("CP1252","UTF-8",$xls_rows[$idx][IDX_COL_SUMMARY]));
// 20090117 - contribution - BUGID 1992 // 20090402 - BUGID 1519
// $summary = str_replace('…',"...",$xls_rows[$idx][IDX_COL_SUMMARY]);
$summary = convert_special_char($xls_rows[$idx][IDX_COL_SUMMARY]);
$summary = nl2p(htmlspecialchars($summary));

// 20090117 - BUGID 1991,1992 // 20090402 - BUGID 1519
// $steps = str_replace('…',"...",$xls_rows[$idx][IDX_COL_ACTIONS]);
//$steps = convert_special_char($xls_rows[$idx][IDX_COL_ACTIONS]);
//$steps = nl2p(htmlspecialchars($steps));
//fwrite($xmlFileHandle,"<steps><![CDATA[".$steps."]]></steps>\n");

/* amal*/
$step_number = 1;
fwrite($xmlFileHandle,"<testcase name=" . '"' . $name. '"'.">\n");

fwrite($xmlFileHandle,"<summary><![CDATA[" . $summary . "]]></summary>\n");
fwrite($xmlFileHandle,"<steps>\n");
}
else
{

$name2 = htmlspecialchars($xls_rows[$idx][1]);
$name = htmlspecialchars($xls_rows[$idx-1][1]);

if ($name2 <> $name)
{

$name = htmlspecialchars($xls_rows[$idx][IDX_COL_NAME]);

// $summary = htmlspecialchars(iconv("CP1252","UTF-8",$xls_rows[$idx][IDX_COL_SUMMARY]));
// 20090117 - contribution - BUGID 1992 // 20090402 - BUGID 1519
// $summary = str_replace('…',"...",$xls_rows[$idx][IDX_COL_SUMMARY]);
$summary = convert_special_char($xls_rows[$idx][IDX_COL_SUMMARY]);
$summary = nl2p(htmlspecialchars($summary));

// 20090117 - BUGID 1991,1992 // 20090402 - BUGID 1519
// $steps = str_replace('…',"...",$xls_rows[$idx][IDX_COL_ACTIONS]);
//$steps = convert_special_char($xls_rows[$idx][IDX_COL_ACTIONS]);
//$steps = nl2p(htmlspecialchars($steps));
//fwrite($xmlFileHandle,"<steps><![CDATA[".$steps."]]></steps>\n");

/* amal*/
$step_number = 1;
fwrite($xmlFileHandle,"<testcase name=" . '"' . $name. '"'.">\n");

fwrite($xmlFileHandle,"<summary><![CDATA[" . $summary . "]]></summary>\n");
fwrite($xmlFileHandle,"<steps>\n");
}
}

$idx2=$idx;

if($idx== FIRST_DATA_ROW)
{

$name2 = htmlspecialchars($xls_rows[$idx2][1]);
$name = htmlspecialchars($xls_rows[$idx2][1]);
}
else
{

$name2 = htmlspecialchars($xls_rows[$idx2][1]);
$name = htmlspecialchars($xls_rows[$idx2+1][1]);

}
while ($name2==$name)
{

$actions = convert_special_char($xls_rows[$idx2][IDX_COL_ACTIONS]);
$actions = nl2p(htmlspecialchars($actions));

// 20090117 - BUGID 1991,1992 // 20090402 - BUGID 1519
// $expresults = str_replace('…',"...",$xls_rows[$idx2][IDX_COL_EXPRESULTS]);
$expresults = convert_special_char($xls_rows[$idx2][IDX_COL_EXPRESULTS]);
$expresults = nl2p(htmlspecialchars($expresults));
//fwrite($xmlFileHandle,"<expectedresults><![CDATA[".$expresults."]]></expectedresults>\n");




//generate xml data for step
fwrite($xmlFileHandle,"<step>\n");
fwrite($xmlFileHandle,"<step_number>" . $step_number . "</step_number>\n");
fwrite($xmlFileHandle,"<actions><![CDATA[" . $actions . "]]></actions>\n");
fwrite($xmlFileHandle,"<expectedresults><![CDATA[" . $expresults . "]]></expectedresults>\n");
fwrite($xmlFileHandle,"</step>\n");


$idx2++;
$step_number ++;
$next=$step_number-1;
$name2 = htmlspecialchars($xls_rows[$idx2][1]);
$name = htmlspecialchars($xls_rows[$idx2-1][1]);

}
//}

fwrite($xmlFileHandle,"</steps>\n");
/*amal*/
fwrite($xmlFileHandle,"</testcase>\n");




}
fwrite($xmlFileHandle,"</testcases>\n");
fclose($xmlFileHandle);
}