EXCEL import and export based on PHPExcel Library

PHPExcel-based EXCEL import and export PHPExcel class is a php excel table processing plug-in. recently, this plug-in has been used to complete the excel import function. Now I will sort out the main logic and share it with you. if you need it, please refer to it.

The following example shows how to upload and download an excel file. some operations such as setting table styles and merging cells are not shown here. you can refer to the PHPExcel manual, the examples are followed by some common PHPExcel operations for your reference:

The API provided by PHPExcel is encapsulated into a class for upload and download as needed. it is actually two functions, one for uploading and the other for download. here I paste the code directly.

 CanRead ($ file) {/* if PHPExcel_Reader_Excel2007 cannot read excel, destroy the created object and use PHPExcel_Reader_Excel5 to read */unset ($ phpReader ); $ phpReader = new PHPExcel_Reader_Excel5 ();} if (! $ PhpReader-> canRead ($ file) {/* the file cannot be read. an empty array */return array ();} is returned ();} $ phpExcel = $ phpReader-> load ($ file);/* The current implementation only reads the first Worksheet */$ currentSheet = $ phpExcel-> getSheet (0 ); /* get the number of rows and columns of the worksheet */$ allRows = $ currentSheet-> getHighestRow (); $ allColumns = $ currentSheet-> getHighestColumn (); $ allColumns ++; $ currentColumn = 'a';/* parse the first row and record the row to be read in $ fields */while ($ currentColumn! = $ AllColumns) {$ title = $ currentSheet-> getCell ($ currentColumn. '1')-> getValue (); $ field = array_search ($ title, $ fields); $ columnKey [$ currentColumn] = $ field? $ Field: ''; $ currentColumn ++;} $ dataList = array ();/* skip the header line (the first line) start to read data */for ($ currentRow = 2; $ currentRow <= $ allRows; $ currentRow ++) {$ currentColumn = 'a'; $ data = new stdclass (); $ ignore = true;/* ignore empty rows. here, a tag is recorded for processing */while ($ currentColumn! = $ AllColumns) {$ cellValue = trim ($ currentSheet-> getCell ($ currentColumn. $ currentRow)-> getValue (); if (empty ($ columnKey [$ currentColumn]) {$ currentColumn ++; continue ;} $ field = $ columnKey [$ currentColumn]; $ currentColumn ++; if (empty ($ cellValue) {$ data-> $ field = '';} else {$ data-> $ field = $ cellValue; $ ignore = false ;}} if ($ ignore = true) {continue ;} /* set data not read from excel */foreach (array _ Keys ($ fields) as $ key) {if (! Isset ($ data-> $ key) {$ data-> $ key = '';}}$ dataList [] = $ data ;}return $ dataList ;} public function setExcelFiled ($ count) {$ letter = 'a'; for ($ I = 1; $ I <= $ count; $ I ++) $ letter ++; return $ letter;}/*** write an object to a file ** @ param $ data: data to be written to an excel file * kind: Page signature * fields: header line, the field * rows to be included in the input file: Object Array. for each data, the data is filtered by fields and only the content of fields is retained (the format is the same as that of the return value of excel2array) * fileName: name of the file to be saved * @ param $ fileTy Pe: type of the input file, including xls and xlsx * @ param $ savePath: file path ** @ access public ** @ return object array. each row of data is an object, array size is the number of rows (excluding the title) * if the input is empty, it may be because the parameter is incorrect or the excel file cannot be read */public function export2excel ($ data, $ savePath = '') {$ this-> phpExcel = new phpExcel (); $ this-> rawExcelData = $ data; $ this-> fields = $ this-> rawExcelData-> fields; $ this-> rows = $ this-> rawExcelData-> rows; $ this-> fieldsKey = array_keys ($ this-> fi Elds); if (! $ This-> rawExcelData-> fileName) $ this-> rawExcelData-> fileName = $ this-> rawExcelData-> kind; $ this-> excelKey = array (); for ($ I = 0; $ I <count ($ this-> fieldsKey); $ I ++) $ this-> excelKey [$ this-> fieldsKey [$ I] = $ this-> setExcelFiled ($ I ); /* Set file base property */$ excelProps = $ this-> phpExcel-> getProperties (); $ excelProps-> setCreator ('Ricky '); $ excelProps-> setLastModifiedBy ('Ricky '); $ excelProps-> setTi Tle ('Office XLS document'); $ excelProps-> setSubject ('Office XLS document'); $ excelProps-> setDescription ('document generated by PHPExcel. '); $ excelProps-> setKeywords ('Office excel PHPExcel'); $ excelProps-> setCategory ('result file '); /* process the first tab */$ this-> phpExcel-> setActiveSheetIndex (0); $ sheetTitle = $ this-> rawExcelData-> kind; $ excelSheet = $ this-> phpExcel-> getActiveSheet ();/* set the page signature name */if ($ sheetT Itle) $ excelSheet-> setTitle ($ sheetTitle); foreach ($ this-> fields as $ key => $ field) $ excelSheet-> setCellValueExplicit ($ this-> excelKey [$ key]. '1', $ field, PHPExcel_Cell_DataType: TYPE_STRING); $ I = 1; foreach ($ this-> rows as $ num => $ row) {$ I ++; foreach ($ row as $ key =>$ value) {if (isset ($ this-> excelKey [$ key]) {$ excelSheet-> setCellValueExplicit ($ this-> excelKey [$ key]. $ I, $ value, PHPExcel_Cell_Da TaType: TYPE_STRING) ;}}/ * urlencode the filename for ie. */$ fileName = $ this-> rawExcelData-> fileName; if (strpos ($ _ SERVER ['http _ USER_AGENT '], 'msi ')! = False | strpos ($ _ SERVER ['http _ USER_AGENT '], 'trigger ')! = False) $ fileName = urlencode ($ fileName); $ excelWriter = PHPExcel_IOFactory: createWriter ($ this-> phpExcel, 'excel5 '); $ excelWriter-> setPreCalculateFormulas (false); if ($ savePath = '') {header ('content-Type: application/vnd. ms-excel '); header ("Content-Disposition: attachment; filename = \" audio cached filename=.xls \ ""); header ('cache-Control: max-age = 0 '); $ excelWriter-> save ('php: // output');} else {$ excelWriter-> save ($ savePath );}}}

Test code:

The excel file is uploaded and downloaded in the test code: select an excel file from the client to import the file, and then download the file to the client after parsing.

The HTML code is very simple. without css and js, there is only one file space and one submit button. As follows:

The corresponding background code is as follows:

 "; Print_r ($ var); echo"";} Include_once 'parseexcel. class. php ';/* process uploaded FILES */if ($ _ FILES ["file"] ["error"]> 0) {echo "Error :". $ _ FILES ["file"] ["error"]."
"; Exit;} move_uploaded_file ($ _ FILES [" file "] [" tmp_name "], $ _ FILES [" file "] [" name "]); /*** excel format: ** name | gender ** ----------------- * ricky | male * xxxxx | xxx * // * defines the column to be read, the array value must be consistent with the title of each row in excel or a subset */$ fileds = array ('name' => 'name ', 'Sex' => 'gender ',); $ parse = new parseExcel (); /* parse data from the uploaded file */$ rows = $ parse-> excel2array ($ _ FILES ["file"] ["name"], $ fileds ); /* Note: When testing the import, open the comment line. when testing the download, close the comment line * // output ($ rows); exit; /* write the data intact into a new file for users to download */$ data = new stdClass (); /* excel file name */$ data-> fileName = 'ceshi';/* tab name */$ data-> kind = 'ceshi '; /* excel Title */$ data-> fields = $ fileds;/* data to be written */$ data-> rows = $ rows; $ parse-> export2excel ($ data );

The above example only completes the basic function of uploading and downloading excel files. some other settings such as table styles and cell merging are not reflected here. you can refer to the PHPExcel manual, the following are some of the common PHPExcel operations I have extracted for your reference:

Create an excel $ objPHPExcel = new PHPExcel (); create a worksheet $ objPHPExcel-> createSheet (); $ objWriter = PHPExcel_IOFactory: createWriter ($ objExcel, 'excel5 '); $ objWriter-save ('php: // output'); save the excel file (2007) $ objWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel); non-2007 format: $ objWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel); $ objWriter-> save ("xxx.xlsx"); directly output the header to the browser for download ('content-Type: application/vnd. ms-excel '); header ("Content-Disposition: attachment; filename = \" audio cached filename=.xls \ ""); header ('cache-Control: max-age = 0 '); $ excelWriter-> save ('php: // output'); set the excel attributes: Creator $ objPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw "); last modified by $ objPHPExcel-> getProperties ()-> setLastModifiedBy ("Maarten Balliauw"); Title $ objPHPExcel-> getProperties () -> setTitle ("Office 2007 XLSX Test Document"); question $ objPHPExcel-> getProperties ()-> setSubject ("Office 2007 XLSX Test Document "); description $ objPHPExcel-> getProperties ()-> setDescription ("Test document for Office 2007 XLSX, generated using PHP classes. "); keyword $ objPHPExcel-> getProperties ()-> setKeywords (" office 2007 openxml php "); type $ objPHPExcel-> getProperties () -> setCategory ("Test result file"); set the current sheet $ objPHPExcel-> setActiveSheetIndex (0); set the sheet name $ objPHPExcel-> getActiveSheet () -> setTitle ('simple'); set the cell value $ objPHPExcel-> getActiveSheet ()-> setCellValue ('A1', 'string'); $ objPHPExcel-> getActiveSheet () -> setCellValue ('A1', 12); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('A1', true); $ objPHPExcel-> getActiveSheet () -> setCellValue ('A1', '= SUM (C2: C4)'); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('A1', '= MIN (B2: c5) '); merge cells $ objPHPExcel-> getActiveSheet ()-> mergeCells ('a18: e22'); separate cells $ objPHPExcel-> getActiveSheet () -> unmergeCells ('a28: b28'); set the width of $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setAutoSize (true ); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (12); set the 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 ('b1 ')-> getFont ()-> setBold (true); set the alignment mode $ objPHPExcel-> getActiveSheet ()-> getStyle ('C1 ') -> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT); $ objPHPExcel-> getActiveSheet ()-> getStyle ('C1 ')-> getAlignment () -> setHorizontal (usage: HORIZONTAL_JUSTIFY); $ objPHPExcel-> getActiveSheet ()-> getStyle ('C1 ')-> getAlignment ()-> setVertical (usage: VERTICAL_CENTER ); set cell border $ objPHPExcel-> getActiveSheet ()-> getStyle ('d1 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); set border color $ objPHPExcel-> getActiveSheet ()-> getStyle ('e1 ')-> getBorders ()-> getLeft ()-> getColor () -> setARGB ('ff993300 '); $ objPHPExcel-> getActiveSheet ()-> getStyle ('e1')-> getBorders ()-> getRight ()-> getColor () -> setARGB ('ff993300 '); $ objPHPExcel-> getActiveSheet ()-> getStyle ('e1')-> getBorders ()-> getTop ()-> getColor () -> setARGB ('ff993300 '); $ objPHPExcel-> getActiveSheet ()-> getStyle ('e1')-> getBorders ()-> getBottom ()-> getColor () -> setARGB ('ff993300 '); set the fill color $ objPHPExcel-> getActiveSheet ()-> getStyle ('F1')-> getFill ()-> setFillType (PHPExcel_Style_Fill :: FILL_SOLID); $ objPHPExcel-> getActiveSheet ()-> getStyle ('F1')-> getFill ()-> getStartColor ()-> setARGB ('ff80808080 '); attach an image $ 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 drawing ();