Problem setCellValueExplicit with PHPExcel_Cell_DataType::TYPE_STRING

Mar 27, 2013 at 4:36 PM
Hello,

I've a problem to format the the cells with a string but I get all the time a error message when I would like to open the Excel file (corrupt file or incorrect xlsx...) it seems that the problem is at "Loop" see marked in Bold but I don't find a solution.
Sorry for add the whole code.
I hope you can help me.
<?php require_once('../Connections/Connection.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

/** require the PHPExcel file 1.0*/
    require '../Classes/PHPExcel.php';

/** Set Memory Limit 1.0*/
    ini_set("memory_limit","1000M");

/** Caching to discISAM 1.0 */
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
$cacheSettings = array( 'dir'  => '/usr/local/tmp'
                      );
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    mysql_connect($hostname_Connection,$username_Connection,$password_Connection);
    mysql_select_db($database_Connection);



/** Query 1.0  */
    $query = "SELECT DISTINCT tblpsrmonth.PartNo,
tbltechnical.DrawingNo,
tbltechnical.Geometry,
tbltechnical.LabelCategory,
tbltechnical.Perforation,
tbltechnical.LengthPart,
tbltechnical.WidthPart,
tbltechnical.ThicknessPart,
tbltechnical.LengthLiner,
tbltechnical.WidthLiner,
tbltechnical.LabelMaterial,
tbltechnical.TestSpec,
tbltechnical.DeliverySpec,
tbltechnical.MatSpec,
tbltechnical.MatNo,
tbltechnical.MatCodeManuf,
tbltechnical.Adhesive,
tbltechnical.AdhesiveEsp,
tbltechnical.ColorRal,
tbltechnical.ColorPantone,
tbltechnical.Warntext,
tbltechnical.Comment
FROM tblpsrmonth LEFT JOIN tbltechnical ON tblpsrmonth.PartNo = tbltechnical.PartNo
";

    if ($result = mysql_query($query) or die(mysql_error())) {

/** Create a new PHPExcel object 1.0 */
   $objPHPExcel = new PHPExcel();
   $objPHPExcel->getActiveSheet()                         
                          
->setTitle('Technical Data');
   }  
 
 /** Format Numbers*/
    $objPHPExcel->getActiveSheet()                        
                          
->getStyle('F:G')->getNumberFormat()
    ->setFormatCode('#,##0.00');
    $objPHPExcel->getActiveSheet()                        
                          
->getStyle('I:J')->getNumberFormat()
    ->setFormatCode('#,##0.00');
    $objPHPExcel->getActiveSheet()                        
                              
->getStyle('H')->getNumberFormat()
    ->setFormatCode('#,##0.0000');

____/** Loop 1.0 */
    $rowNumber = 1;
    while ($row = mysql_fetch_row($result)) {
       $col = 'A';
       foreach($row as $cell) {
          $objPHPExcel->getActiveSheet()->setCellValueExplicit($col.$rowNumber,$cell,PHPExcel_Cell_DataType::TYPE_STRING);
          $col++;
       }
       $rowNumber++;
}__
/** Create Header*/
    $objPHPExcel->getActiveSheet()                        
                          
                                  ->setCellValue('A1', 'Part No')
                                  ->setCellValue('B1', 'Drawing No.')
                                  ->setCellValue('C1', 'Geometry')
                                  ->setCellValue('D1', 'Category')
                                  ->setCellValue('E1', 'Perforation')
                                  ->setCellValue('F1', 'Length Part')
                                  ->setCellValue('G1', 'Width Part')
                                  ->setCellValue('H1', 'Thickness')
                                  ->setCellValue('I1', 'Length Liner')
                                  ->setCellValue('J1', 'Width Liner')
                                  ->setCellValue('K1', 'Label Material')
                                  ->setCellValue('L1', 'Test Spec.')
                                  ->setCellValue('M1', 'Delivery Spec.')
                                  ->setCellValue('N1', 'Material Spec.')
                                  ->setCellValue('O1', 'Material No.')
                                  ->setCellValue('P1', 'Material Code Manuf.')
                                  ->setCellValue('Q1', 'Adhesive')
                                  ->setCellValue('R1', 'Adhesive Esp.')
                                  ->setCellValue('S1', 'RAL Color')
                                  ->setCellValue('T1', 'Pantone Color')
                                  ->setCellValue('U1', 'Warntext')
                                  ->setCellValue('V1', 'Comment');
                                  
/** Background Header */
$objPHPExcel->getActiveSheet()                        
                          
->getStyle('A1:V1')->getFill()->applyFromArray(
    array(
        'type'       => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array('rgb' => 'a9a9a9'),
    )
);

/** Autosize */
for ($col = 'A'; $col != 'W'; $col++) {
    $objPHPExcel->getActiveSheet()                        
->getColumnDimension($col)->setAutoSize(true);
}

/** Border */
$styleArray = array(
       'borders' => array(
             'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => '000000'),
             ),
       ),
);
$objPHPExcel->getActiveSheet()                        
->getStyle('A1:V6000')->applyFromArray($styleArray);                              

/** Alignment function*/
$alignleft  = array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                    'rotation'   => 0
                );
$alignright  = array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
                    'rotation'   => 0
                );
$aligncenter  = array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'rotation'   => 0
                );

$objPHPExcel->getActiveSheet()                        
->getStyle('A1:V6000')->getAlignment()->applyFromArray($alignleft);


/** Autofilter */
$objPHPExcel->getActiveSheet()                        
->setAutoFilter($objPHPExcel->getActiveSheet()                        
->calculateWorksheetDimension());

/** Create Excel 2007 file with writer 1.0 */
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Technical.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
exit;

?>
Coordinator
Mar 28, 2013 at 10:04 AM
Whenever you get a message in Excel saying that the file is corrupted, open the file in a text editor and look for leading or trailing whitespace characters, a BOM marker, or for any obvious plaintext PHP error messages to help identify the problem
Mar 28, 2013 at 7:40 PM
Hello,

I've tried to use the Testfiles like 01simple-download-xlsx there is the same problem
that the file can't open -> directly or after saving.

The error message fileformat or file extension not valid or corrupt/damaged.

Also I've tried to search the error with the text editior but without success.

I've windows 8
PHP Version 5.4.4

I hope you can help me
Coordinator
Mar 28, 2013 at 7:50 PM
First off, have you opened any of these files in a text editor? If so, was there anything at all in the file?

If the standard test examples don't work, then please provide full details of your environment, because they work on every environment I test them on, so clearly there's something about your system that is different that I need to identify.
Mar 28, 2013 at 8:05 PM
Edited Mar 28, 2013 at 8:08 PM
Hello Mark,

1.) Yes I've opened with a text editor the 01simple-download-xlsx and my own file there was some hieroglyphs in.
2.) Please explain exactly what kind of informations do you need.
Coordinator
Mar 28, 2013 at 10:10 PM
Output from phpinfo() would be a useful start.
It's clearly creating some output, but incorrect output; and I have no idea what could be causing that... I've just rerun those test scripts successfully using PHP5.4.7 on Windows 8, and opened the generated files with various versions of MS Excel, Open and Libre Office and Gnumeric without issue; so the only thing I can think of is something in your PHP configuration that isn't being tested for.
Mar 29, 2013 at 11:48 AM
Hello Mark,
I think I've found the failure.
1.) I've deleted and reinstall the PHPExcel Class and now the the script is running but I've still the problem that I need in the whole column B a String because the data has partically leading zeros as you can see I've datas from a query so I can't use $objPHPExcel->getActiveSheet()->setCellValueExplicit($col.$rowNumber,$cell,PHPExcel_Cell_DataType::TYPE_STRING);
but if I use the above script I'll see in the Text editor the following error message:
<b>Notice</b>: iconv_substr(): Detected an illegal character in input string in <b>C:\xampp\htdocs\labelmanagementDB\Classes\Classes\PHPExcel\Shared\String.php</b> on line <b>576</b><br />
<br />
<b>Notice</b>: iconv_substr(): Detected an illegal character in input string in <b>C:\xampp\htdocs\labelmanagementDB\Classes\Classes\PHPExcel\Shared\String.php</b> on line <b>576</b><br />

do you have any idea to solve the problem.

Thanks for your help.
Coordinator
Mar 29, 2013 at 12:03 PM
Without knowing the actual string value, probably not... all string values should be converted to UTF-8 before saving them, though this shouldn't matter for a real numeric value which would normally be pure ASCII characters... but something in this value isn't a normal ASCII character, nor a UTF-8 character
Mar 29, 2013 at 12:11 PM
Edited Mar 29, 2013 at 12:17 PM
Possible values are for example:
DV201030
00060601
CH123456
156782222ANE
VN9721782A
0000567844B
N.A.
TBD
....
....
....

is there a possibility to show the leading zeros with this kind of values

FYI The Kollation is utf8_general_ci in the MySql database
Mar 29, 2013 at 2:34 PM
Hello Mark,

now I've just solved the problem it was the column "comment" there was the problem with to much datas in.
The leading zeros are now also solved with $objPHPExcel->getActiveSheet()->setCellValueExplicit($col.$rowNumber,$cell);
Nevertheless thanks for your help.

BR
Frank
Jun 19, 2013 at 1:52 AM
Thank..