Geek Answers Handbook

Convert (xls, xlsx) to CSV before downloading via PHP

I am trying to convert an Excel file to CSV when the user clicks the refresh button.

It works when I upload one excel file to a folder, then I get this file using PHPExcel and read all the 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 the above code, first of all, I find all excel files from the folder and create one file.csv file.

What I want to do is when the user selects any file in <input type="file" name="upload"/> and presses the UPLOAD button after the first process first converts the Excel file to CSV BEFORE it moves to folder to download.

+5
source share
1 answer

As I can see, your code works to save Excel data with CSV . But first you want to download first from FORM , then you want to convert. Here is what you can do:

first you need HTML for FORM and first upload the file if there is a POST file. Take a look at the 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>
+1
source

More articles:


All Articles