/** * Creates an instance of PHPExcel based of a file path. * @param $filePath * @return \PHPExcel */ public function createWorkBook($filePath) { $info = pathinfo($filePath); $this->fileExtension = $info['extension']; switch ($this->fileExtension) { // if text or csv use PHPExcel_Reader_CSV else use loader //due to inability of the PHPExcel IOfactory being //unable to handle csv/txt case 'txt': $objReader = new \PHPExcel_Reader_CSV(); $objReader->setInputEncoding($this->fileInfo['encoding']); $objReader->setDelimiter($this->fileInfo['delimiter']); $objReader->setEnclosure($this->fileInfo['enclosure']); $objReader->setSheetIndex(0); $this->workbook = $objReader->load($filePath); break; case 'csv': $objReader = new \PHPExcel_Reader_CSV(); $objReader->setInputEncoding($this->fileInfo['encoding']); $objReader->setDelimiter($this->fileInfo['delimiter']); $objReader->setEnclosure($this->fileInfo['enclosure']); $objReader->setSheetIndex(0); $this->workbook = $objReader->load($filePath); break; default: $this->workbook = \PHPExcel_IOFactory::load($filePath); break; } return $this->workbook; // return created workbook }
public function __construct($options) { set_time_limit(0); ini_set('memory_limit', '512M'); glz_importApplicationLib('PHPExcel/Classes/PHPExcel/IOFactory.php'); $csv = new PHPExcel_Reader_CSV(); $csv->setDelimiter($options['delimiter']); $csv->setEnclosure($options['enclosure']); $excel = $csv->load($options['filePath']); $this->worksheet = $excel->getActiveSheet(); }
/** * Read given csv file and write all rows to given xls file * * @param string $csv_file Resource path of the csv file * @param string $xls_file Resource path of the excel file * @param string $csv_enc Encoding of the csv file, use utf8 if null * @throws Exception */ public static function convert($csv_file, $xls_file, $csv_enc = null) { if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') { $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM; $cacheSettings = array('dir' => 'E:/PHPExcel_cache'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); } else { $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); } //open csv file $objReader = new PHPExcel_Reader_CSV(); $objReader->setDelimiter(CSVToExcelConverter::guessDelimiter($csv_file)); if ($csv_enc != null) { $objReader->setInputEncoding($csv_enc); } $objPHPExcel_CSV = $objReader->load($csv_file); $in_sheet = $objPHPExcel_CSV->getActiveSheet(); //open excel file $objPHPExcel_XLSX = new PHPExcel(); $out_sheet = $objPHPExcel_XLSX->getActiveSheet(); $out_sheet->setTitle('File Content'); //row index start from 1 $row_index = 0; foreach ($in_sheet->getRowIterator() as $row) { //if($row_index==20) // break; $row_index++; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); //column index start from 0 $column_index = -1; foreach ($cellIterator as $cell) { $column_index++; $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell->getValue()); } } //write excel file $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel_XLSX); $objWriter->save($xls_file); }
public function convertCSV_MTS() { $file = trim($this->input->post('filename')); $inputFileName = "application/csv/mts/" . $file; $objReader = new PHPExcel_Reader_CSV(); $objReader->setInputEncoding('CP1251'); $objReader->setDelimiter(';'); //$objReader->setEnclosure(''); $objPHPExcel = $objReader->load($inputFileName); $objPHPExcel->getActiveSheet(0)->insertNewRowBefore(1); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'resource')->setCellValue('B1', 'amount')->setCellValue('C1', 'date')->setCellValue('D1', 'assortment'); $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); //var_dump($sheetData); foreach ($sheetData as $rows) { echo $rows['A'] . ' ' . $rows['B'] . ' ' . $rows['C'] . ' ' . $rows['D']; } $loadedSheetNames = $objPHPExcel->getSheetNames(); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV'); foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) { $objWriter->setSheetIndex($sheetIndex); $objWriter->save('application/csv/mts/file_' . date('Y-m-d', now()) . '.csv'); unlink($inputFileName); } }
/** * Set CSV delimiter. * * @param $delimiter * * @return Reader */ public function setDelimiter($delimiter) { $this->reader->setDelimiter($delimiter); return $this; }
<?php $user_id = $_SESSION['userID'] ? $_SESSION['userID'] : 0; if (!empty($_FILES['fileToUpload'])) { $inputFileName = $_FILES['fileToUpload']['tmp_name']; /** PHPExcel_IOFactory - Reader */ include _DOC_ROOT_ . 'lib/ClassesPHPExel/PHPExcel/IOFactory.php'; // Read CSV $objReader = new PHPExcel_Reader_CSV(); // สร้าง object ของ Class PHPExcel_Reader_CSV $objReader->setInputEncoding('CP1252'); // กำหนดค่าต่างตามนี้ $objReader->setDelimiter(','); $objReader->setEnclosure(''); $objReader->setLineEnding("\r\n"); $objReader->setSheetIndex(0); $objPHPExcel = $objReader->load($inputFileName); //<====File Path $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; $dataArray = 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; $loop = 0; foreach ($headingsArray as $columnKey => $columnHeading) {
public function fromArray($resource, array $options = []) { $options = array_merge(['delimiter' => ',', 'enclosure' => '"', 'escape' => '\\', 'input_encoding' => 'UTF-8', 'parse_file' => false, 'output_format' => 'xlsx'], $options); $resource = parent::getRealResource($resource, $options); if (!is_string($resource) || !is_file($resource)) { $csv = new Csv($options); $resource = parent::tempStore(uniqid(), $csv->fromArray($resource, $options)); } PhpExcelNsWrapper::init(); $objReader = new \PHPExcel_Reader_CSV(); $objReader->setInputEncoding($options['input_encoding']); $objReader->setDelimiter($options['delimiter']); $objReader->setEnclosure($options['enclosure']); $objReader->setLineEnding(false !== strpos($resource, "\r\n") ? "\r\n" : "\n"); $objReader->setSheetIndex(0); $objPHPExcel = $objReader->load($resource); // The following statement is borrowed from PHPExcel_IOFactory switch (strtolower($options['output_format'])) { case 'xlsx': // Excel (OfficeOpenXML) Spreadsheet // Excel (OfficeOpenXML) Spreadsheet case 'xlsm': // Excel (OfficeOpenXML) Macro Spreadsheet (macros will be discarded) // Excel (OfficeOpenXML) Macro Spreadsheet (macros will be discarded) case 'xltx': // Excel (OfficeOpenXML) Template // Excel (OfficeOpenXML) Template case 'xltm': // Excel (OfficeOpenXML) Macro Template (macros will be discarded) $extensionType = 'Excel2007'; break; case 'xls': // Excel (BIFF) Spreadsheet // Excel (BIFF) Spreadsheet case 'xlt': // Excel (BIFF) Template $extensionType = 'Excel5'; break; case 'ods': // Open/Libre Offic Calc // Open/Libre Offic Calc case 'ots': // Open/Libre Offic Calc Template $extensionType = 'OOCalc'; break; case 'slk': $extensionType = 'SYLK'; break; case 'xml': // Excel 2003 SpreadSheetML $extensionType = 'Excel2003XML'; break; case 'gnumeric': $extensionType = 'Gnumeric'; break; case 'htm': case 'html': $extensionType = 'HTML'; break; case 'csv': // Do nothing // This must be handled by the CSV class break; default: break; } if (is_null($extensionType)) { throw new JigException('Unable to identify a reader for this file'); } $writer = \PHPExcel_IOFactory::createWriter($objPHPExcel, $extensionType); ob_start(); $writer->save('php://output'); $spreadsheet = ob_get_contents(); ob_end_clean(); return $spreadsheet; }