简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary

Home  >  Article  >  Backend Development  >  How to import and export data in php with Excel graphics and text code sharing in xml format

How to import and export data in php with Excel graphics and text code sharing in xml format

黄舟
黄舟Original
2017-07-17 15:33:343256browse

1 Introduction

1.1 Export

In actual work projects, it is often necessary to export data stored in some important databases into Excel, such as exporting attendance reports and exporting financial statements. Export performance reports, export sales reports, etc. CleverCode used PHPExcel for two years to create Excel export data, but found that it was too troublesome to use PHPExcel to generate Excel, especially controlling the color of cells, merging cells, setting lengths for cells, etc. It usually takes a day to design one of these in Excel. Later, CleverCode discovered a simple method to export Excel in xml format using PHP. It used to take a day's work, but now it can be done in half an hour. It's really twice the result with half the effort!

1.2 Import

At the same time, some projects also need to import some Excel data into the database. For example, the bank statements provided by the bank and the sales reports are imported into the database. The usual approach is to use PHPExcel.

Although you can use Xml parser, SimpleXML, XMLReader, DOMDocument and other methods to read Excel in xml format, CleverCode has tried to use these methods and found that they are too complicated and laborious, and are not as useful as PHPExcel.

So when you need to read Excel (including xml format), CleverCode recommends using the PHPExcel library.

2 Requirement

A certain group needs the person in charge of each region to import the orders and sales of the city stations they are responsible for into the database.
1) The website provides an imported sales report template.
2) Each person in charge can only upload and download data for the city they are responsible for (permission check).
3) Only upload to generate all quarters owned by the current year and that day. For example, today is 2015-05-26. Then only the first quarter and second quarter of 2015 are generated.
If it is 2015-12-01. You need to generate the first, second, third and fourth quarter of 2015.
4) Display the data of previous quarters of this quarter.
5) The data for this quarter are all 0 by default.
6) Only the data for this quarter can be modified.

3 Programming source code download

4 Design website page

4.1 Display


4.2 display.php code 



    
    
    PHP导入与导出xml格式的Excel
    
    




    

        

              
            
            
                        
        

        下载销售报表模板

5 PHP exports Excel in xml format (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 the data in xml format.

4) Find table information. Delete ss:ExpandedColumnCount="5" ss:ExpandedRowCount="6". This restriction limits the length and width of the table, so it must be removed. 

 Change to 

##5 PHP export Excel business logic code (Excel.php) 
 '北京',
            'order_1' => 100,
            'money_1' => 10000,
            'order_2' => 200,
            'money_2' => 40000 
        );
        
        $infoTJ = array(
            'city' => '天津',
            'order_1' => 50,
            'money_1' => 1000,
            'order_2' => 100,
            'money_2' => 2000 
        );
        
        $infoGZ = array(
            'city' => '广州',
            'order_1' => 50,
            'money_1' => 1000,
            'order_2' => 100,
            'money_2' => 2000 
        );
        
        // 根据不同用户的权限，获取不同的数据
        if (is_admin($userid)) {
            
            $data[] = $infoBJ;
            $data[] = $infoTJ;
            $data[] = $infoGZ;
        } else {
            $data[] = $infoBJ;
        }
        
        return $data;
    }

    /**
     * 通过Smarty方式获取xml字符串
     *
     * @param array $data 结果集
     * @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
        // 捕获输出
        $xml = $smarty->fetch($tpl);
        
        return $xml;
    }

    /**
     * 通过PHP组合字符串方式获取xml字符串（可以动态扩展列）
     *
     * @param array $data 结果集
     * @return string $xmlStr
     */
    public static function getXmlStrByPHP($data){
        $xml = '
               
                
                
                 
                 
                  

         ';
        
        //可以根据季度的多少动态扩展列，这里不做说明，请自行尝试。
        $xml. = '
           
            城市
            2015一季度
            2015二季度
           
            订单
            销售额
            订单
            销售额
           
        ';
        
        // 输出数据
        foreach ( $data as $row ) {
            $xml .= '
               
                ' . $row['city'] . '
                ' . $row['order_1'] . '
                ' . $row['money_1'] . '
                ' . $row['order_2'] . '
                ' . $row['money_2'] . '
                              
            ';
        }
        
        $xml .= '
        
          ...........
        
        ';
        return $xml;
    }
} 
6 PHP export Excel client code (export.php) 
main();
}

start();

?>
6 PHP import Excel in xml format
1) After downloading the sales template, fill in the data and click on the page Click the upload button to upload Excel data. 



#2) PHP imported Excel business logic code (Excel.php). The PHPExcel library is used here. 
getActiveSheet();
        
        // 获取总行
        $totalRows = $objWorksheet->getHighestRow(); // 获取总行数
                                                     
        // 获取总列
        $highestColumn = $objWorksheet->getHighestColumn();
        $totalColumns = PHPExcel_Cell::columnIndexFromString($highestColumn);
        
        // 开始行
        if (!is_int($startRow) || $startRow < 1) {
            $startRow = 1;
        }
        
        // 结束行
        if ($endRow == null || !is_int($endRow) || $endRow > $totalRows) {
            $endRow = $totalRows;
        }
        
        // 开始列
        if (!is_int($startColumn) || $startColumn < 0) {
            $startColumn = 0;
        }
        
        // 结束列
        if ($endColumn == null || !is_int($endColumn) || $endColumn > $totalColumns) {
            $endColumn = $totalColumns;
        }
        
        // 读取数据
        for($rowNum = $startRow; $rowNum <= $endRow; $rowNum++) {
            for($colNum = $startColumn; $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) 
main();
}

start();

?
The above is the detailed content of How to import and export data in php with Excel graphics and text code sharing in xml format. For more information, please follow other related articles on the PHP Chinese website!
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Sample code on how PHP calls Python to quickly send high-concurrency emailsNext article：Sample code on how PHP calls Python to quickly send high-concurrency emails
Related articles
See more