PHPEXCEL精简版之导出导入

来源：互联网发布：windows cmd telnet 编辑：程序博客网时间：2024/05/18 21:39

我们知道PHPEXCEL原版非常大。classes文件大小达到20M之多，当然里面的功能也是非常丰富的，但是当我只需要导出与导入功能时就显得非常大了

精简后的PHPexcel大小2M都不到，并且能够完全满足导出与导入，当然里面还是有一些冗余用不到的但是已经很小了

文件下载我会在后面给出链接

案例说明：

1.目录结构

|--PHPExcel phpexcel核心文件夹

|--PHPExcel.php phpexcel核心文件

|--read.php excel导入案例

|--write.php excel导出案例

|--xabc.xlsx xabcd.xls write.xlsx案例中使用或生成的excel文件

2.文件详细说明

2.1读取文件 read.php读取xabc.xlsx,xabcd.xls

里面分别介绍了如何导入xls xlsx两种格式文件的导入并将其转换为数组，或者直接另存为新的excel文件

转换为数组之后我们可以进行导入到数据库或者直接展示等操作

<?phprequire_once './PHPExcel/IOFactory.php';$file = 'xabc.xlsx';//$file = 'xabcd.xls';$fileArr = explode('.',$file);$exts = $fileArr[1];if (!file_exists($file)) {exit("no file.\n");}if ($exts == 'xls') {$type = 'Excel5';} else if ($exts == 'xlsx') {$type = 'Excel2007';} $objReader = PHPExcel_IOFactory::createReader($type); try{$objPHPExcel = $objReader->load($file);}catch(Exception $e){}$allWorksheets = $objPHPExcel->getAllSheets();$i = 0;        foreach($allWorksheets as $objWorksheet){            $sheetname=$objWorksheet->getTitle();            $allRow = $objWorksheet->getHighestRow();//how many rows            $highestColumn = $objWorksheet->getHighestColumn();//how many columns            $allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);            $array[$i]["Title"] = $sheetname;             $array[$i]["Cols"] = $allColumn;             $array[$i]["Rows"] = $allRow;             $arr = array();            $isMergeCell = array();            foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells                foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {                    $isMergeCell[$cellReference] = true;                }            }            for($currentRow = 1 ;$currentRow<=$allRow;$currentRow++){                 $row = array();                 for($currentColumn=0;$currentColumn<$allColumn;$currentColumn++){;                                    $cell =$objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);                    $afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn+1);                    $bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn-1);                    $col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);                    $address = $col.$currentRow;                    $value = $objWorksheet->getCell($address)->getValue();                    if(substr($value,0,1)=='='){                        return array("error"=>0,'message'=>'can not use the formula!');                        exit;                    }                    if($cell->getDataType()==PHPExcel_Cell_DataType::TYPE_NUMERIC){                        $cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();                        $formatcode=$cellstyleformat->getFormatCode();                        if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {                            $value=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));                        }else{                            $value=PHPExcel_Style_NumberFormat::toFormattedString($value,$formatcode);                        }                                    }  if($isMergeCell[$col.$currentRow]&&$isMergeCell[$afCol.$currentRow]&&!empty($value)){                        $temp = $value;                    }elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$col.($currentRow-1)]&&empty($value)){                        $value=$arr[$currentRow-1][$currentColumn];                    }elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$bfCol.$currentRow]&&empty($value)){                        $value=$temp;                    }  $row[$currentColumn] = (string)$value;                 }                 $arr[$currentRow] = $row;             }             $array[$i]["Content"] = $arr;             $i++;        }print_r($array); //直接打印数组 根据需求展示或存入数据库/*另存未新的EXCEL$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->save(str_replace('.php', '.xlsx', __FILE__));*/

2.2 导出文件write.php生成write.xlsx

导出文件我是直接使用的官方的代码修改不大

增加了导出另存为xlsx和xls

<?phprequire_once './PHPExcel.php';// Create new PHPExcel object$objPHPExcel = new PHPExcel();// Set document properties$objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file");// Add some data$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')->setCellValue('A2', 'Lastname:')->setCellValue('A3', 'Fullname:')->setCellValue('B1', 'Maarten')->setCellValue('B2', 'Balliauw')->setCellValue('B3', '=B1 & " " & B2');// Define named ranges$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('PersonName', $objPHPExcel->getActiveSheet(), 'B1') );$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('PersonLN', $objPHPExcel->getActiveSheet(), 'B2') );// Rename named ranges$objPHPExcel->getNamedRange('PersonName')->setName('PersonFN');// Rename worksheet$objPHPExcel->getActiveSheet()->setTitle('Person');// Create a new worksheet, after the default sheet$objPHPExcel->createSheet();// Add some data to the second sheet, resembling some different data types$objPHPExcel->setActiveSheetIndex(1);$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')->setCellValue('A2', 'Lastname:')->setCellValue('A3', 'Fullname:')->setCellValue('B1', '=PersonFN')->setCellValue('B2', '=PersonLN')->setCellValue('B3', '=PersonFN & " " & PersonLN');// Rename worksheet$objPHPExcel->getActiveSheet()->setTitle('Person (cloned)');// Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);//直接保存文件// Save Excel 2007 file$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->save(str_replace('.php', '.xlsx', __FILE__));/*//导出另存为-Excel2007// Redirect output to a client’s web browser (Excel2007)header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="test.xlsx"');header('Cache-Control: max-age=0');$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->save('php://output');*///导出另存为-Excel5// Redirect output to a client’s web browser (Excel5)header('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="test.xls"');header('Cache-Control: max-age=0');$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');$objWriter->save('php://output');

下载地址：http://download.csdn.net/detail/slyjit/9834441

0 0