PHPExcel使用の一般的な説明を解析し、PHPExcelをCIフレームワークに統合する紹介

22375 ワード

Excelの書き込みと生成操作:

 
  
include 'PHPExcel.php'; 
  
include 'PHPExcel/Writer/Excel2007.php'; 
  
//  include 'PHPExcel/Writer/Excel5.php';     .xls  
  
include 'PHPExcel/IOFactory.php';//phpexcel    
  
//    excel 
  
$objPHPExcel = new PHPExcel(); 
  
//  excel―2007   
  
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
  
//      
  
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007"); 
  
//  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);  2007   
  
$objWriter->save("xxx.xlsx"); 
  
//         
  
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 
  
header("Pragma: public"); 
  
header("Expires: 0″); 
  
header("Cache-Control:must-revalidate, post-check=0, pre-check=0″); 
  
header("Content-Type:application/force-download"); 
  
header("Content-Type:application/vnd.ms-execl"); 
  
header("Content-Type:application/octet-stream"); 
  
header("Content-Type:application/download");; 
  
header('Content-Disposition:attachment;filename="resume.xls"'); 
  
header("Content-Transfer-Encoding:binary"); 
  
$objWriter->save('php://output'); 
  
//       
  
$objWriterr->save(‘   '); 
  
//  excel   ： 
  
//    
  
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw"); 
  
//      
  
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw"); 
  
//   
  
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document"); 
  
//   
  
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document"); 
  
//   
  
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes."); 
  
//    
  
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php"); 
  
//   
  
$objPHPExcel->getProperties()->setCategory("Test result file"); 
  
//     sheet 
  
$objPHPExcel->setActiveSheetIndex(0); 
  
//  sheet name 
  
$objPHPExcel->getActiveSheet()->setTitle('Simple'); 
  
//        
  
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String'); 
  
$objPHPExcel->getActiveSheet()->setCellValue('A2', 12); 
  
$objPHPExcel->getActiveSheet()->setCellValue('A3', true); 
  
$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)'); 
  
$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)'); 
  
//      
  
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); 
  
//      
  
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); 
  
//  cell 
  
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection! 
  
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel'); 
  
//     
  
// Set cell number formats 
  
echo date('H:i:s') . " Set cell number formats
"; 
  
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); 
  
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' ); 
  
//   width 
  
// Set column widths 
  
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); 
  
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); 
  
//  font 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara'); 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20); 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); 
  
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); 
  
$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true); 
  
$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true); 
  
//  align 
  
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
  
$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
  
$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
  
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); 
  
//     
  
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
  
  column border 
  
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
  
$objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
  
$objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
  
$objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
  
$objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
  
  border color 
  
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300'); 
  
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); 
  
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); 
  
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); 
  
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); 
  
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300'); 
  
       
  
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); 
  
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); 
  
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080'); 
  
//    
  
$objDrawing = new PHPExcel_Worksheet_Drawing(); 
  
$objDrawing->setName('Logo'); 
  
$objDrawing->setDescription('Logo'); 
  
$objDrawing->setPath('./images/officelogo.jpg'); 
  
$objDrawing->setHeight(36); 
  
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); 
  
$objDrawing = new PHPExcel_Worksheet_Drawing(); 
  
$objDrawing->setName('Paid'); 
  
$objDrawing->setDescription('Paid'); 
  
$objDrawing->setPath('./images/paid.png'); 
  
$objDrawing->setCoordinates('B15'); 
  
$objDrawing->setOffsetX(110); 
  
$objDrawing->setRotation(25); 
  
$objDrawing->getShadow()->setVisible(true); 
  
$objDrawing->getShadow()->setDirection(45); 
  
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); 
  
//         
  
//         UTF-8  ，      ，            ，    ： 
  
 $str  = iconv('gb2312', 'utf-8', $str); 
  
                   ： 
  
function convertUTF8($str) 
  
{ 
  
   if(empty($str)) return ''; 
  
   return  iconv('gb2312', 'utf-8', $str); 
  
}

EXcel 1を読み込む.Excelをインポートする最も簡単な方法は、PHPExelのIO Factoryを使用して、PHPExcel_を呼び出すことです.IOFactoryクラスの静的法loadは、Excel 2007、Excel 2003 XML、OOCalcSYLK、Gnumeric、CSVなど、ドキュメントフォーマットを自動的に認識します.PHPExcelのインスタンスを返します.

 
  
 //      
  
include'PHPExcel/IOFactory.php'; 
  
//    xls     
  
$inputFileName = './sampleData/example1.xls'; 
  
/**  PHPExcel_IOFactory load    excel      **/ 
  
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName); 
  
//        ，  toArray  ，          
  
$sheetData =$objPHPExcel->getActiveSheet()->toArray(null,true,true,true); 
  
var_dump($sheetData);

1.ExcelReaderを作成してExcelドキュメントをロードします.このExcelドキュメントのフォーマットを知っている場合は、読み込むExcelドキュメントをロードするために対応するReaderを作成できます.ただし、誤ったドキュメントタイプをロードすると、予知できないエラーが発生します.

 
  
$inputFileName = './sampleData/example1.xls'; 
  
/** Create a new Excel5 Reader  **/ 
  
$objReader = new PHPExcel_Reader_Excel5(); 
  
//    $objReader = new PHPExcel_Reader_Excel2007(); 
  
//    $objReader = new PHPExcel_Reader_Excel2003XML(); 
  
//    $objReader = new PHPExcel_Reader_OOCalc(); 
  
//    $objReader = new PHPExcel_Reader_SYLK(); 
  
//    $objReader = new PHPExcel_Reader_Gnumeric(); 
  
//    $objReader = new PHPExcel_Reader_CSV(); 
  
/** Load $inputFileName to a PHPExcel Object  **/ 
  
$objPHPExcel = $objReader->load($inputFileName); 
  
//      sheet 
  
$curSheet =$objPHPExcel->getActiveSheet(); 
  
//                
  
$sheetData = $curSheet->toArray(null,true,true,true); 
  
var_dump($sheetData);

PHPExcelも使えますIOFactoryのcreateReaderメソッドは、ファイルを読み込むフォーマットを知る必要がなく、Readerオブジェクトを取得します.

 
  
$inputFileType = 'Excel5'; 
  
//    $inputFileType = 'Excel2007'; 
  
//    $inputFileType = 'Excel2003XML'; 
  
//    $inputFileType = 'OOCalc'; 
  
//    $inputFileType = 'SYLK'; 
  
//    $inputFileType = 'Gnumeric'; 
  
//    $inputFileType = 'CSV'; 
  
$inputFileName = './sampleData/example1.xls'; 
  
/**  Create a new Reader of the type defined in $inputFileType  **/ 
  
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
  
/**  Load $inputFileName to a PHPExcel Object  **/ 
  
$objPHPExcel = $objReader->load($inputFileName); 
  
//      sheet 
  
$curSheet = $objPHPExcel->getActiveSheet(); 
  
//                
  
$sheetData = $curSheet->toArray(null,true,true,true); 
  
var_dump($sheetData);

ファイルを読み込む前にファイル形式が不明な場合は、IOFactoryのidentify()メソッドでファイルタイプを取得し、createReader()メソッドでファイルリーダーを着用できます.

 
  
$inputFileName = './sampleData/example1.xls'; 
  
/**             **/ 
  
$inputFileType = PHPExcel_IOFactory::identify($inputFileName); 
  
/**            **/ 
  
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
  
/**            **/ 
  
$objPHPExcel = $objReader->load($inputFileName);

2.Excelの読み取りオプションを設定load()メソッドを使用してファイルをロードする前に、読み取りオプションを設定してloadの動作を制御することができる.
2.1.ReadingOnly Data from a Spreadsheet FilesetReadData Only()メソッドは、リーダーがテーブルデータのデータ型に関係なくstring形式で返されるように構成します.

 
  
$inputFileType = 'Excel5'; 
  
$inputFileName = './sampleData/example1.xls'; 
  
/**  Create a new Reader of the type defined in $inputFileType  **/ 
  
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
  
/**                  **/ 
  
$objReader->setReadDataOnly(true); 
  
/**  Load $inputFileName to a PHPExcel Object  **/ 
  
$objPHPExcel = $objReader->load($inputFileName); 
  
$sheetData =$objPHPExcel->getActiveSheet()->toArray(null,true,true,true); 
  
var_dump($sheetData);

データを返します.

 
  
array(8) { 
  
  [1]=> 
  
  array(6) { 
  
   ["A"]=> 
  
   string(15) "Integer Numbers" 
  
   ["B"]=> 
  
    string(3)"123" 
  
   ["C"]=> 
  
    string(3)"234" 
  
   ["D"]=> 
  
    string(4)"-345" 
  
   ["E"]=> 
  
    string(3)"456" 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [2]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(22) "Floating PointNumbers" 
  
    ["B"]=> 
  
    string(4) "1.23" 
  
    ["C"]=> 
  
    string(5) "23.45" 
  
    ["D"]=> 
  
    string(10) "0.00E+0.00" 
  
    ["E"]=> 
  
    string(6) "-45.68" 
  
    ["F"]=> 
  
    string(7) "£56.78" 
  
  } 
  
  [3]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(7) "Strings" 
  
    ["B"]=> 
  
    string(5) "Hello" 
  
    ["C"]=> 
  
    string(5) "World" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    string(8) "PHPExcel" 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [4]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(8) "Booleans" 
  
    ["B"]=> 
  
    bool(true) 
  
    ["C"]=> 
  
    bool(false) 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [5]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(5) "Dates" 
  
    ["B"]=> 
  
    string(16) "19 December 1960" 
  
    ["C"]=> 
  
    string(15) "10 October 2010" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [6]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(5) "Times" 
  
    ["B"]=> 
  
    string(4) "9:30" 
  
   ["C"]=> 
  
    string(5) "23:59" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [7]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(8) "Formulae" 
  
    ["B"]=> 
  
    string(3) "468" 
  
    ["C"]=> 
  
    string(7) "-20.998" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [8]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(6) "Errors" 
  
    ["B"]=> 
  
    string(4) "#N/A" 
  
    ["C"]=> 
  
    string(7) "#DIV/0!" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
} 
  
 
          ：
array(8) { 
  
  [1]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(15) "Integer Numbers" 
  
    ["B"]=> 
  
    float(123) 
  
    ["C"]=> 
  
    float(234) 
  
    ["D"]=> 
  
    float(-345) 
  
    ["E"]=> 
  
    float(456) 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [2]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(22) "Floating Point Numbers" 
  
    ["B"]=> 
  
    float(1.23) 
  
    ["C"]=> 
  
    float(23.45) 
  
    ["D"]=> 
  
    float(3.45E-6) 
  
    ["E"]=> 
  
    float(-45.678) 
  
    ["F"]=> 
  
    float(56.78) 
  
  } 
  
  [3]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(7) "Strings" 
  
    ["B"]=> 
  
    string(5) "Hello" 
  
    ["C"]=> 
  
    string(5) "World" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    string(8) "PHPExcel" 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [4]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(8) "Booleans" 
  
    ["B"]=> 
  
    bool(true) 
  
    ["C"]=> 
  
    bool(false) 
  
   ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [5]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(5) "Dates" 
  
    ["B"]=> 
  
    float(22269) 
  
    ["C"]=> 
  
    float(40461) 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [6]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(5) "Times" 
  
    ["B"]=> 
  
    float(0.39583333333333) 
  
    ["C"]=> 
  
    float(0.99930555555556) 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [7]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(8) "Formulae" 
  
    ["B"]=> 
  
    float(468) 
  
    ["C"]=> 
  
    float(-20.99799655) 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
  [8]=> 
  
  array(6) { 
  
    ["A"]=> 
  
    string(6) "Errors" 
  
    ["B"]=> 
  
    string(4) "#N/A" 
  
    ["C"]=> 
  
    string(7) "#DIV/0!" 
  
    ["D"]=> 
  
    NULL 
  
    ["E"]=> 
  
    NULL 
  
    ["F"]=> 
  
    NULL 
  
  } 
  
}

Reading Only Data from a SpreadsheetFile applies to Readers:
       Excel2007      YES         Excel5            YES         Excel2003XML YES
       OOCalc          YES         SYLK              NO          Gnumeric       YES
       CSV         NO
2.2.ReadingOnly Name WorkSheets from a FilesetLoadSheetsOnly()は、読み込むworksheetを設定し、worksheetの名前をパラメータとして受け入れます.

 
  
/** PHPExcel_IOFactory */ 
  
include'PHPExcel/IOFactory.php'; 
  
$inputFileType = 'Excel5'; 
  
//  $inputFileType = 'Excel2007'; 
  
//  $inputFileType = 'Excel2003XML'; 
  
//  $inputFileType = 'OOCalc'; 
  
//  $inputFileType = 'Gnumeric'; 
  
$inputFileName ='./sampleData/example1.xls'; 
  
$sheetname = 'Data Sheet #2'; 
  
 
  
echo 'Loading file',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a definedreader type of ',$inputFileType,'
'; 
  
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
  
echo 'Loading Sheet"',$sheetname,'" only
'; 
  
$objReader->setLoadSheetsOnly($sheetname); 
  
$objPHPExcel =$objReader->load($inputFileName); 
  
echo ''; 
  
echo$objPHPExcel->getSheetCount(),' worksheet',(($objPHPExcel->getSheetCount()== 1) ? '' : 's'),' loaded

'; 
  
$loadedSheetNames =$objPHPExcel->getSheetNames(); 
  
foreach($loadedSheetNames as$sheetIndex => $loadedSheetName) { 
  
    echo $sheetIndex,' -> ',$loadedSheetName,'
'; 
  
}

複数のworksheetを読み込む場合は、1つの配列を渡すことができます.

 
  
$inputFileType = 'Excel5'; 
  
$inputFileName = './sampleData/example1.xls'; 
  
$sheetnames = array('Data Sheet #1','Data Sheet #3'); 
  
/**  Create a new Reader of the type defined in $inputFileType  **/ 
  
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
  
/**  Advise the Reader of which WorkSheets we want to load  **/ 
  
$objReader->setLoadSheetsOnly($sheetnames); 
  
/**  Load $inputFileName to a PHPExcel Object  **/ 
  
$objPHPExcel = $objReader->load($inputFileName);

すべてのワークシートを読み込む場合はsetLoadAllSheets()を呼び出します.

Laravel5.5でAWS SES からGmail経由にメール送信変更

C++はどのように文字列の中の小文字を大文字に変えます