PHP How to implement data import and export XML format of Excel text code share

1 Introduction

1.1 Export

In the actual work project, often need to export some important data stored in the database into Excel, such as export Attendance Report, export financial statements, export performance reports, export sales reports. Clevercode used two years of Phpexcel to make Excel export data, but it is too troublesome to find Excel with Phpexcel, especially to control the color of cells, to merge cells, to set the length of cells, and so on. It usually takes a day to design an Excel. Later, Clevercode found a simple way to PHP export XML format of Excel, previously required a day's work, now half an hour to get it done, it is a multiplier!

1.2 Import

At the same time, some projects also need to import some Excel data into the database. For example, bank to the Bank of water, Sales report import database. The usual practice is to use phpexcel.

Although Excel reading XML can use XML parser, SimpleXML, XMLReader, DOMDocument, and so on, but Clevercode tried to use these methods, found too complex, too laborious, not phpexcel.

So when it comes to reading Excel (including XML), Clevercode recommends using the Phpexcel library.

2 Requirements

A group needs the head of each region to import orders and sales from its responsible city station into the database.
1) The website provides an imported sales report template.
2) Each person responsible can only upload and download the data (permission check) of the respective city.
3) Use upload only to generate the year, the day of ownership of all quarters. For example, today is 2015-05-26. Then only generate 20,151 quarters, with two quarters.
If it's 2015-12-01. You need to generate 20,151, two, three, four quarters.
4) Show data for the previous quarter of the quarter.
5) The data for this quarter is all 0 by default.
6) You can only modify the data for this quarter.

3 Programming Source Download

4 Design Site page

4.1 Display

4.2 display.php Code

<! DOCTYPE html>
5 PHP Export in XML format Excel (Export Sales report template)

1) Create a new "sales report. xlsx". The design is as follows.




2) Save the "sales report. xlsx" file as "Sales report. Xml"





3) Open "Sales report. xml" to see data in XML format.

4) Locate the table information. Remove ss:expandedcolumncount= "5" ss:expandedrowcount= "6". This limit dies the length and width of the table, so it must be removed.

<table ss:expandedcolumncount= "5" ss:expandedrowcount= "6" x:fullcolumns= "1"   x:fullrows= "1" ss:StyleID= "S23" Ss:defaultcolumnwidth= "ss:defaultrowheight=" "   18.75" >
Change into

<table  x:fullcolumns= "1"   x:fullrows= "1" ss:styleid= "S23" ss:defaultcolumnwidth= "si"   ss:d efaultrowheight= "18.75" >


5 PHP Export Excel business logic code (excel.php)

<?php/** * excel.php * * Excel Operation * * Copyright (c) http://blog.csdn.net/CleverCode * * Modification HISTORY: *--- -----------------* 2015/5/14, by Clevercode, Create * */class excel{/** * Export Excel * * @param int $userid        User number * @return string $XMLSTR */public static function export ($userid) {////Based on different user permissions, get different data                $data = Self::getexportdata ($personid);        Gets the string, if the Excel column is fixed, can be obtained through the Smarty method, but if the Excel column needs to be generated dynamically, the string can be combined via PHP.                $XMLSTR = Self::getxmlstrbysmarty ($data);                This needs to be generated dynamically based on the current date for several quarters $xmlStr = self::getxmlstrbyphp ($data);    return $xmlStr; }/** * generates Excel data * * @param int $userid user number * @return Array result data */public static function get        ExportData ($userid) {if (!is_int ($userid)) {return array (); } $infoBJ = Array (' City ' = ' Beijing ', ' order_1 ' = +, ' money_1 ' + 10000, ' order_2 ', ' money_2 ' + 40000);            $infoTJ = Array (' City ' = ' Tianjin ', ' order_1 ' and ' = ', ' money_1 ' = 1000,                ' order_2 ' = +, ' money_2 ' + 2000);            $infoGZ = Array (' City ' = ' Guangzhou ', ' order_1 ' and ' = ', ' money_1 ' = 1000,                ' order_2 ' = +, ' money_2 ' + 2000);            Depending on the permissions of different users, get different data if (Is_admin ($userid)) {$data [] = $infoBJ;            $data [] = $infoTJ;        $data [] = $infoGZ;        } else {$data [] = $infoBJ;    } return $data; }/** * Gets the XML string by smarty Way * * @param array $data result set * @return string $XMLSTR */public static        function Getxmlstrbysmarty ($data) {require_once ' Smarty.class.php ';                $smarty = new Smarty (); $tpl= ' FILE/EXPORT.TPL ';                $smarty->assign (' list ', $data);                Capture the output//capture $xml = $smarty->fetch ($TPL);    return $xml; }/** * Gets the XML string (can dynamically extend the column) by combining PHP strings * * @param array $data result set * @return string $XMLSTR */Pub Lic static function getxmlstrbyphp ($data) {$xml = ' <?xml version= ' 1.0 '?> <?m                 So-application progid= "Excel.Sheet"?> <workbook xmlns= "Urn:schemas-microsoft-com:office:spreadsheet" xmlns:o= "Urn:schemas-microsoft-com:office:office" ... </style& "."                 Gt                   </Styles> <worksheet ss:name= "Sheet1" > <table x:fullcolumns= "1"         x:fullrows= "1" ss:styleid= "s16" ss:defaultcolumnwidth= "si" ss:defaultrowheight= "18.75" >                ';    You can expand the column dynamically based on how many quarters you have, and try it yourself if you don't specify it.    $xml. = ' <row ss:autofitheight= ' 0 "> <cell ss:mergedown=" 1 "ss:styleid=" m42513364 "><data SS: Type= "String" > City </Data></Cell> <cell ss:mergeacross= "1" ss:styleid= "S25" ><data ss:type= "String" >2015 first quarter </Data></Cell> <cell ss:mergeacross= "1" ss:styleid= "m42513344" ><data SS            : type= "String" >2015 two quarter </Data></Cell> </Row> <row ss:autofitheight= "0" > <cell ss:index= "2" ss:styleid= "S17" ><data ss:type= "String" > Orders </Data></Cell> < Cell ss:styleid= "S17" ><data ss:type= "String" > Sales </Data></Cell> <cell ss:styleid= "S17" &G T;<data ss:type= "String" > Order </Data></Cell> <cell ss:styleid= "S17" ><data ss:type= "Stri                Ng "> Sales </Data></Cell> </Row>";         Output data foreach ($data as $row) {   $xml. = ' <row ss:autofitheight= ' 0 "> <cell ss:styleid=" s18 "><data ss:type=" String ">". $row [' City ']. ' </Data></Cell> <cell ss:styleid= ' s19 ' ><data ss:type= ' number ' > '. $row [' order_1 ']. ' </Data></Cell> <cell ss:styleid= ' s19 ' ><data ss:type= ' number ' > '. $row [' Money_1 ']. ' </Data></Cell> <cell ss:styleid= ' s19 ' ><data ss:type= ' number ' > '. $row [' order_2 ']. ' </Data></Cell> <cell ss:styleid= ' s19 ' ><data ss:type= ' number ' > '. $row [' money_2 '].        ' </Data></Cell> </Row> '; } $xml. = ' <worksheetoptions xmlns= "Urn:schemas-microsoft-com:office:excel" > ....        .... </Workbook> ';    return $xml; }}
6 PHP Export Excel client code (export.php)

<?php/** * export.php * * Export Excel * * Copyright (c) http://blog.csdn.net/CleverCode * * Modification History: *-- ------------------* 2015/5/14, by Clevercode, Create * *///Excel class include_once (' excel.php ');/* Client class * To isolate the client and business logic as possible, Reduces coupling between client and business logic algorithms, * makes business logic algorithms more portable */class client{public    function main () {                //Get XML format string        $xmlStr = Excel:: Export (1);        Head        $filename = ' Sales report template ';        Header ("Content-type:application/vnd.ms-excel; Charset=utf-8 ");        Header ("Content-disposition:inline; Filename=\ "$filename. Xls\");        Header ("Content-transfer-encoding:binary");        Header ("Pragma:public");        Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0");                Output string        echo $xmlStr;                Exit ();}    } /** * Program Entry */function start () {    $client = new Client ();    $client->main ();} Start ();? >
6 PHP Import in XML format Excel

1) After downloading the sales template, fill in the data, then click the Upload button in the page to upload the Excel data.



2) PHP imported Excel business logic code (excel.php). The Phpexcel library is used here.

<?php/** * excel.php * * Excel Operation * * Copyright (c) http://blog.csdn.net/CleverCode * * Modification HISTORY: *---     -----------------* 2015/5/14, by Clevercode, Create * */class excel{* * * Read data in Excel format (can read XML format data) * * @param string $filename Excel file * @param string $startRow start line * @param string $endRow End line * @param string $s Tartcolumn Start column * @param string $endColumn End column * @return array Excel result set data */public static function read ($fi        Lename, $startRow = 1, $endRow = null, $startColumn = 0, $endColumn = null) {$excelData = array ();        if (!file_exists ($filename)) {return $excelData;        } require_once ' phpexcel/phpexcel.php ';                Require_once ' phpexcel/phpexcel/iofactory.php ';                Load Excel File $objPHPExcel = Phpexcel_iofactory::load ($filename);                Get Focus Sheet $objWorksheet = $objPHPExcel->getactivesheet (); Get Total $totalRows = $objWOrksheet->gethighestrow (); Get total number of rows//Get totals column $highestColumn = $objWorksheet->get        Highestcolumn ();                $totalColumns = phpexcel_cell::columnindexfromstring ($highestColumn);        Start line if (!is_int ($startRow) | | $startRow < 1) {$startRow = 1; }//End line if ($endRow = = NULL | |!is_int ($ENDROW) | | $endRow > $totalRows) {$endRow =        $totalRows;        }//Start column if (!is_int ($startColumn) | | $startColumn < 0) {$startColumn = 0;            }//End column if ($endColumn = = NULL | |!is_int ($endColumn) | | $endColumn > $totalColumns) {        $endColumn = $totalColumns; }//Read data for ($rowNum = $startRow; $rowNum <= $endRow; $rowNum + +) {for ($colNum = $sta Rtcolumn; $colNum < $endColumn; $colNum + +) {$item = $objWorksheet->getcellbycolumnaNdrow ($colNum, $rowNum);                $exValue = Trim ($item->getvalue ());            $excelData [$rowNum] [$colNum] = $exValue;    }} return $excelData; }}

3) PHP Imported Excel client code (import.php)

<?php/** * import.php * * Import Excel * * Copyright (c) http://blog.csdn.net/CleverCode * * Modification History: *-- ------------------* 2015/5/14, by Clevercode, Create * *///Excel class include_once (' excel.php ');/* Client class * To isolate the client and business logic as possible, Reduces the coupling between client and business logic algorithms, * makes the algorithm of business logic more portable */class client{public    function main () {        if (!$_files[' file ']) {            exit ();        }                Read Excel data from line 3rd        $datas = excel::read ($_files[' file ' [' Tmp_name '], 3);                Save $datas to database        //...    }} /** * Program Entry */function start () {    $client = new Client ();    $client->main ();} Start ();?