/**
* 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
}
/**
* 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);
}
}
<?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) {
/**
* @author caochunhui@dachuwang.com
* @description 把输出目录里的csv都合成到一个统一的excel文件里
* 每一个csv都占据一个单独的sheet，方便打印
*/
private function _make_excel($dir = '', $out_name, $csv_enc = null)
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
//open csv file
$objReader = new PHPExcel_Reader_CSV();
if ($csv_enc != null) {
$objReader->setInputEncoding($csv_enc);
}
//下面要循环了
$list_arr = get_filenames($dir);
sort($list_arr);
$sheet_cnt = 0;
foreach ($list_arr as $idx => $item) {
if (strpos($item, '.csv') != FALSE) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $item);
//$out_sheet->setTitle($item);
$read_objPHPExcel = $objReader->load($dir . $item);
$in_sheet = $read_objPHPExcel->getActiveSheet();
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
$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_objPHPExcel->addSheet($out_sheet);
}
}
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$objWriter->save($out_name);
}
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;
}
