Menu
I am trying to convert an Excel file to CSV when user click on update button.
Its working when i upload one excel file in folder then i get that file using PHPExcel and read all data and convert it to CSV.
Here is the code.
<html>
<head>
<meta charset="UTF-8">
</head>
<?php
ini_set("display_errors", "1");
ini_set("memory_limit", "-1");
ini_set("max_execution_time", "-1");
error_reporting(1);
require_once "PHPExcel.php";
$dir = "../excel2csv/"; // Main Directory Name
$file_arr = array();
$file_ext_arr = array('xls','xlsx'); // Valid Extensions of Excel File
// From Directory get only Excel Files in Array
if(is_dir($dir))
{
if($dh = opendir($dir))
{
while(($file = readdir($dh)) !== false)
{
$info = new SplFileInfo($file);
$ext = $info->getExtension(); // Get Extension of Current File
if(in_array($ext,$file_ext_arr))
{
array_push($file_arr, $file);
}
}
closedir($dh);
}
}
// Make CSV File
$fp = fopen('file.csv', 'a');
$list = array();
foreach($file_arr as $val)
{
$arr_data = array();
$objPHPExcel = PHPExcel_IOFactory::load($dir . $val);
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
foreach($cell_collection as $cell)
{
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will / should be in row 1 only. of course this can be modified to suit your need.
// Skip Rows From Top if you have header in Excel then Change 0 to 1
if($row == 0)
{
$header[$row][$column] = $data_value;
}
else
{
$arr_data[$row]['row'] = $row;
$arr_data[$row][$column] = $data_value;
}
}
$data = $arr_data;
foreach($data as $val1)
{
$num_col = sizeof($val1) - 1; // get number of columns in Excel
break;
}
$lwrcol=array();
foreach($data as $val2)
{
$alphaArr = range('A','Z');
$colArr = range('A',$alphaArr[$num_col - 1]);
foreach($colArr as $col)
{
$lwrcol[$col] = isset($val2[$col]) ? utf8_decode($val2[$col]) : "";
fwrite($fp,$lwrcol[$col].",");
}
fwrite($fp,"\n");
}
chmod(getcwd()."/file.csv", 0777);
}
fclose($fp);
?>
</html>
In above code first of all i am finding all excel files from folder and make one file.csv file.
What i want to do is when user select any file in <input type="file" name="upload"/> and click on UPLOAD button after then in backend first process is convert Excel file to CSV BEFORE it move to upload folder.
327
As i can see your code is working for saving data of Excel from CSV. But first you want to upload first from FORM then you want to convert. So this is what you can do is:
first you need HTML for Form and first upload file if POST file is there. Take a look in following snippet:
<html>
<head>
<meta charset="UTF-8">
</head>
<?php
ini_set("display_errors", "1");
ini_set("memory_limit", "-1");
ini_set("max_execution_time", "-1");
error_reporting(1);
require_once "PHPExcel.php";
$dir = "../excel2csv/"; // Main Directory Name
$file_arr = array();
$file_ext_arr = array('xls','xlsx'); // Valid Extensions of Excel File
if(!empty($_FILES)) {
//uploading file first
$info = pathinfo($_FILES['userFile']['name']);
$ext = $info['extension']; // get the extension of the file
$newname = "excelfile.".$ext;
$target = $dir.$newname;
move_uploaded_file( $_FILES['userFile']['tmp_name'], $target);
//upload finish wiring csv file now
writecsv();
}
function wirtecsv(){
// From Directory get only Excel Files in Array
if(is_dir($dir))
{
if($dh = opendir($dir))
{
while(($file = readdir($dh)) !== false)
{
$info = new SplFileInfo($file);
$ext = $info->getExtension(); // Get Extension of Current File
if(in_array($ext,$file_ext_arr))
{
array_push($file_arr, $file);
}
}
closedir($dh);
}
}
// Make CSV File
$fp = fopen('file.csv', 'a');
$list = array();
foreach($file_arr as $val)
{
$arr_data = array();
$objPHPExcel = PHPExcel_IOFactory::load($dir . $val);
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
foreach($cell_collection as $cell)
{
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
//header will / should be in row 1 only. of course this can be modified to suit your need.
// Skip Rows From Top if you have header in Excel then Change 0 to 1
if($row == 0)
{
$header[$row][$column] = $data_value;
}
else
{
$arr_data[$row]['row'] = $row;
$arr_data[$row][$column] = $data_value;
}
}
$data = $arr_data;
foreach($data as $val1)
{
$num_col = sizeof($val1) - 1; // get number of columns in Excel
break;
}
$lwrcol=array();
foreach($data as $val2)
{
$alphaArr = range('A','Z');
$colArr = range('A',$alphaArr[$num_col - 1]);
foreach($colArr as $col)
{
$lwrcol[$col] = isset($val2[$col]) ? utf8_decode($val2[$col]) : "";
fwrite($fp,$lwrcol[$col].",");
}
fwrite($fp,"\n");
}
chmod(getcwd()."/file.csv", 0777);
}
fclose($fp);
}
?>
<form action='' method='POST' enctype='multipart/form-data'>
<input type='file' name='userFile'><br>
<input type='submit' name='upload_btn' value='upload'>
</form>
</html>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
