XLS to CSV

Topics: Developer Forum, Project Management Forum
Aug 13, 2012 at 7:23 PM

this is my cod i want a CSV file whith  Only the first two column.
But in the CSV i have all the row until number 65536.
can i send my xls file? and my CSV file
please help me
<?php

error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Europe/London');

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
 $fileName1 = './sampleData/prodotti1.xls';
$objPHPExcel = PHPExcel_IOFactory::load($fileName1);
$objPHPExcel->getActiveSheet()->removeColumn('C',20);

$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->save('./sampleData/elimina11.csv');
?>

Coordinator
Aug 15, 2012 at 11:29 AM

If your Excel file has cells defined (even empty cells) in row 65536, then so will your CSV file.

Load the workbook. Test to see the last row that contains actual cell data using the worksheet's $this->getHighestDataRow() method. Reload the workbook using a read filter to load only to that row. Then save that as csv.

Aug 23, 2012 at 3:40 PM
Edited Aug 23, 2012 at 3:41 PM

 

the issue is also important for the columns.

I try in this way:

 

<?php

error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Europe/London');

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>PHPExcel Reader Raffa </title>

</head>
<body>

<h1>PHPExcel Reader Raffa </h1>
<h2>Simple File Reader Using a Configurable Read Filter</h2>
<?php

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';


$inputFileType = 'Excel5';
//	$inputFileType = 'Excel2007';
//	$inputFileType = 'Excel2003XML';
//	$inputFileType = 'OOCalc';
//	$inputFileType = 'Gnumeric';
$inputFileName = './sampleData/prodotti.xls';
$sheetname = 'Foglio 01';

$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($inputFileName);

$objPHPExcel->getActiveSheet()->removeColumn( 'B',2);


$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();

$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];

$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
    $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
    if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
        ++$r;
        foreach($headingsArray as $columnKey => $columnHeading) {
            $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
        }
    }
}
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('./sampleData/cazzone.xls');
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->save('./sampleData/cazzone.csv');
echo '<pre>';
var_dump($namedDataArray);
echo '</pre><hr />';



$inputFileType = 'Excel5';
$inputFileName1 = './sampleData/cazzone.xls';
$sheetname1 = 'Foglio 01';

$objReader1 = PHPExcel_IOFactory::createReader('Excel5');
$objReader1->setReadDataOnly(true);

$objPHPExcel1 = $objReader1->load($inputFileName1);




$objWorksheet1 = $objPHPExcel1->setActiveSheetIndex(0);
$highestRow1 = $objWorksheet1->getHighestRow();
$highestColumn1 = $objWorksheet1->getHighestColumn();

$headingsArray1 = $objWorksheet1->rangeToArray('A1:'.$highestColumn1.'1',null, true, true, true);
$headingsArray1 = $headingsArray1[1];
$objPHPExcel1->getActiveSheet()->removeColumn( 'C',20);
$r = -1;
$namedDataArray1 = array();
for ($row = 2; $row <= $highestRow1; ++$row) {
    $dataRow1 = $objWorksheet1->rangeToArray('A'.$row.':'.$highestColumn1.$row,null, true, true, true);
    if ((isset($dataRow1[$row]['A'])) && ($dataRow1[$row]['A'] > '')) {
        ++$r;
        foreach($headingsArray1 as $columnKey => $columnHeading) {
            $namedDataArray1[$r][$columnHeading] = $dataRow1[$row][$columnKey];
        }
    }
}
$objWriter1 = new PHPExcel_Writer_CSV($objPHPExcel1);
$objWriter1->save('./sampleData/cazzone1.csv');

echo '<pre>';
var_dump($namedDataArray1);
echo '</pre><hr />';

 

 

But when i remove the column C,20  I find the last row with all columns and therefore saves me 20 columns in csv file, I'm going to go crazy

I just have to take a xls file column A and column D, and then save it in CSV