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

<!DOCTYPE html>
<html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=gbk" />
    <title>PHP导入与导出xml格式的Excel</title>
    <style type="text/css">
        body{ font-size:14px;}
        input{ vertical-align:middle; margin:0; padding:0}
        .file-box{ position:relative;width:340px}
        .txt{ height:22px; border:1px solid #cdcdcd; width:180px;}
        .btn{ background-color:#FFF; border:1px solid #CDCDCD;height:24px; width:70px;}
        .file{ position:absolute; top:0; right:80px; height:24px; filter:alpha(opacity:0);
            opacity: 0;width:260px }
    </style>
    </head>

<body>
    <p class="file-box">
        <form action="" method="post" enctype="multipart/form-data">
            <input type=&#39;text&#39; name=&#39;textfield&#39; id=&#39;textfield&#39; class=&#39;txt&#39; />  
            <input type=&#39;button&#39; class=&#39;btn&#39; value=&#39;浏览...&#39; />
            <input type="file" name="fileField" class="file" id="fileField" size="28" 
                onchange="document.getElementById(&#39;textfield&#39;).value=this.value" />
            <input type="submit" name="submit" class="btn" value="上传" />            
        </form>
        <a href="export.php">下载销售报表模板</a>
    </p>
</body>
</html>

Copy after login

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.

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s23" ss:DefaultColumnWidth="54"
   ss:DefaultRowHeight="18.75">

Copy after login

Change to

<Table  x:FullColumns="1"   x:FullRows="1" ss:StyleID="s23" ss:DefaultColumnWidth="54"
   ss:DefaultRowHeight="18.75">

Copy after login

##5 PHP export Excel business logic code (Excel.php)

<?php

/**
 * Excel.php
 *
 * Excel操作
 *
 * Copyright (c) 2015 http://blog.csdn.net/CleverCode
 *
 * modification history:
 * --------------------
 * 2015/5/14, by CleverCode, Create
 *
 */
class Excel{

    /**
     * 导出excel
     *
     * @param int $userid 用户编号
     * @return string $xmlStr
     */
    public static function export($userid){
        
        // 根据不同用户的权限，获取不同的数据
        $data = self::getExportData($personid);
        
        // 获取字符串，如果excel的列是固定的可以通过Smarty方式获取
        // 但是如果excel的列需要通过动态生成，则可以通过php组合字符串。
        // $xmlStr = self::getXmlStrBySmarty($data);
        
        // 这个需要根据当前日期动态的生成有几个季度
        $xmlStr = self::getXmlStrByPHP($data);
        
        return $xmlStr;
    }

    /**
     * 生成excel数据
     *
     * @param int $userid 用户编号
     * @return array 结果数据
     */
    public static function getExportData($userid){
        if (!is_int($userid)) {
            return array();
        }
        
        $infoBJ = array(
            &#39;city&#39; => &#39;北京&#39;,
            &#39;order_1&#39; => 100,
            &#39;money_1&#39; => 10000,
            &#39;order_2&#39; => 200,
            &#39;money_2&#39; => 40000 
        );
        
        $infoTJ = array(
            &#39;city&#39; => &#39;天津&#39;,
            &#39;order_1&#39; => 50,
            &#39;money_1&#39; => 1000,
            &#39;order_2&#39; => 100,
            &#39;money_2&#39; => 2000 
        );
        
        $infoGZ = array(
            &#39;city&#39; => &#39;广州&#39;,
            &#39;order_1&#39; => 50,
            &#39;money_1&#39; => 1000,
            &#39;order_2&#39; => 100,
            &#39;money_2&#39; => 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 &#39;Smarty.class.php&#39;;
        $smarty = new Smarty();
        
        $tpl = &#39;file/export.tpl&#39;;
        
        $smarty->assign(&#39;list&#39;, $data);
        
        // capture the output
        // 捕获输出
        $xml = $smarty->fetch($tpl);
        
        return $xml;
    }

    /**
     * 通过PHP组合字符串方式获取xml字符串（可以动态扩展列）
     *
     * @param array $data 结果集
     * @return string $xmlStr
     */
    public static function getXmlStrByPHP($data){
        $xml = &#39;
               <?xml version="1.0"?>
                <?mso-application progid="Excel.Sheet"?>
                <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                 xmlns:o="urn:schemas-microsoft-com:office:office"
                ............
                  </Style>
                 </Styles>
                 <Worksheet ss:Name="Sheet1">
                  <Table x:FullColumns="1"
                   x:FullRows="1" ss:StyleID="s16" ss:DefaultColumnWidth="54"
                   ss:DefaultRowHeight="18.75">
         &#39;;
        
        //可以根据季度的多少动态扩展列，这里不做说明，请自行尝试。
        $xml. = &#39;
           <Row ss:AutoFitHeight="0">
            <Cell ss:MergeDown="1" ss:StyleID="m42513364"><Data ss:Type="String">城市</Data></Cell>
            <Cell ss:MergeAcross="1" ss:StyleID="s25"><Data ss:Type="String">2015一季度</Data></Cell>
            <Cell ss:MergeAcross="1" ss:StyleID="m42513344"><Data ss:Type="String">2015二季度</Data></Cell>
           </Row>
           <Row ss:AutoFitHeight="0">
            <Cell ss:Index="2" ss:StyleID="s17"><Data ss:Type="String">订单</Data></Cell>
            <Cell ss:StyleID="s17"><Data ss:Type="String">销售额</Data></Cell>
            <Cell ss:StyleID="s17"><Data ss:Type="String">订单</Data></Cell>
            <Cell ss:StyleID="s17"><Data ss:Type="String">销售额</Data></Cell>
           </Row>
        &#39;;
        
        // 输出数据
        foreach ( $data as $row ) {
            $xml .= &#39;
               <Row ss:AutoFitHeight="0">
                <Cell ss:StyleID="s18"><Data ss:Type="String">&#39; . $row[&#39;city&#39;] . &#39;</Data></Cell>
                <Cell ss:StyleID="s19"><Data ss:Type="Number">&#39; . $row[&#39;order_1&#39;] . &#39;</Data></Cell>
                <Cell ss:StyleID="s19"><Data ss:Type="Number">&#39; . $row[&#39;money_1&#39;] . &#39;</Data></Cell>
                <Cell ss:StyleID="s19"><Data ss:Type="Number">&#39; . $row[&#39;order_2&#39;] . &#39;</Data></Cell>
                <Cell ss:StyleID="s19"><Data ss:Type="Number">&#39; . $row[&#39;money_2&#39;] . &#39;</Data></Cell>
               </Row>               
            &#39;;
        }
        
        $xml .= &#39;
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          ...........
        </Workbook>
        &#39;;
        return $xml;
    }
}

Copy after login

6 PHP export Excel client code (export.php)

<?php
/**
 * export.php
 *
 * 导出excel
 *
 * Copyright (c) 2015 http://blog.csdn.net/CleverCode
 *
 * modification history:
 * --------------------
 * 2015/5/14, by CleverCode, Create
 *
 */

// Excel类
include_once (&#39;Excel.php&#39;);

/*
 * 客户端类
 * 让客户端和业务逻辑尽可能的分离，降低客户端和业务逻辑算法的耦合，
 * 使业务逻辑的算法更具有可移植性
 */
class Client{

    public function main(){
        
        // 获取xml格式字符串
        $xmlStr = Excel::export(1);
        // 头部
        $filename = &#39;销售报表模板&#39;;
        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");
        
        // 输出字符串
        echo $xmlStr;
        
        exit();
    }
}

/**
 * 程序入口
 */
function start(){
    $client = new Client();
    $client->main();
}

start();

?>

Copy after login

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.

<?php

/**
 * Excel.php
 *
 * Excel操作
 *
 * Copyright (c) 2015 http://blog.csdn.net/CleverCode
 *
 * modification history:
 * --------------------
 * 2015/5/14, by CleverCode, Create
 *
 */
class Excel{    
    /*
     * 读取Excel格式的数据（可以读取xml格式数据）
     *
     * @param string $filename excel文件
     * @param string $startRow 开始行
     * @param string $endRow 结束行
     * @param string $startColumn 开始列
     * @param string $endColumn 结束列
     * @return array excel结果集数据
     */
    public static function read($filename, $startRow = 1, $endRow = null, $startColumn = 0, $endColumn = null){
        $excelData = array();
        if (!file_exists($filename)) {
            return $excelData;
        }
        
        require_once &#39;PHPExcel/PHPExcel.php&#39;;
        require_once &#39;PHPExcel/PHPExcel/IOFactory.php&#39;;
        
        // 加载excel文件
        $objPHPExcel = PHPExcel_IOFactory::load($filename);
        
        // 获取焦点Sheet
        $objWorksheet = $objPHPExcel->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;
    }
}

Copy after login

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

<?php
/**
 * import.php
 *
 * 导入excel
 *
 * Copyright (c) 2015 http://blog.csdn.net/CleverCode
 *
 * modification history:
 * --------------------
 * 2015/5/14, by CleverCode, Create
 *
 */

// Excel类
include_once (&#39;Excel.php&#39;);

/*
 * 客户端类
 * 让客户端和业务逻辑尽可能的分离，降低客户端和业务逻辑算法的耦合，
 * 使业务逻辑的算法更具有可移植性
 */
class Client{

    public function main(){
        if (!$_FILES[&#39;file&#39;]) {
            exit();
        }
        
        // 从第3行开始读取Excel数据
        $datas = Excel::read($_FILES[&#39;file&#39;][&#39;tmp_name&#39;], 3);
        
        // 将$datas保存到数据库
        // ....
    }
}

/**
 * 程序入口
 */
function start(){
    $client = new Client();
    $client->main();
}

start();

?

Copy after login

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 of this Website

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

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

3 weeks ago By DDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

2 weeks ago By DDD

Where to find the Crane Control Keycard in Atomfall

3 weeks ago By DDD

Assassin's Creed Shadows - How To Find The Blacksmith And Unlock Weapon And Armour Customisation

1 months ago By DDD

Roblox: Dead Rails - How To Complete Every Challenge

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7627

CakePHP Tutorial

1389

What is the format of the account name of steam

win11 activation key permanent

nyt connections hints and answers

140

Related knowledge

PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian Dec 24, 2024 pm 04:42 PM

PHP 8.4 brings several new features, security improvements, and performance improvements with healthy amounts of feature deprecations and removals. This guide explains how to install PHP 8.4 or upgrade to PHP 8.4 on Ubuntu, Debian, or their derivati

7 PHP Functions I Regret I Didn't Know Before Nov 13, 2024 am 09:42 AM

If you are an experienced PHP developer, you might have the feeling that you’ve been there and done that already.You have developed a significant number of applications, debugged millions of lines of code, and tweaked a bunch of scripts to achieve op

How To Set Up Visual Studio Code (VS Code) for PHP Development Dec 20, 2024 am 11:31 AM

Visual Studio Code, also known as VS Code, is a free source code editor — or integrated development environment (IDE) — available for all major operating systems. With a large collection of extensions for many programming languages, VS Code can be c

Explain JSON Web Tokens (JWT) and their use case in PHP APIs. Apr 05, 2025 am 12:04 AM

JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,

How do you parse and process HTML/XML in PHP? Feb 07, 2025 am 11:57 AM

This tutorial demonstrates how to efficiently process XML documents using PHP. XML (eXtensible Markup Language) is a versatile text-based markup language designed for both human readability and machine parsing. It's commonly used for data storage an

PHP Program to Count Vowels in a String Feb 07, 2025 pm 12:12 PM

A string is a sequence of characters, including letters, numbers, and symbols. This tutorial will learn how to calculate the number of vowels in a given string in PHP using different methods. The vowels in English are a, e, i, o, u, and they can be uppercase or lowercase. What is a vowel? Vowels are alphabetic characters that represent a specific pronunciation. There are five vowels in English, including uppercase and lowercase: a, e, i, o, u Example 1 Input: String = "Tutorialspoint" Output: 6 explain The vowels in the string "Tutorialspoint" are u, o, i, a, o, i. There are 6 yuan in total

Explain late static binding in PHP (static::). Apr 03, 2025 am 12:04 AM

Static binding (static::) implements late static binding (LSB) in PHP, allowing calling classes to be referenced in static contexts rather than defining classes. 1) The parsing process is performed at runtime, 2) Look up the call class in the inheritance relationship, 3) It may bring performance overhead.

What are PHP magic methods (__construct, __destruct, __call, __get, __set, etc.) and provide use cases? Apr 03, 2025 am 12:03 AM

What are the magic methods of PHP? PHP's magic methods include: 1.\_\_construct, used to initialize objects; 2.\_\_destruct, used to clean up resources; 3.\_\_call, handle non-existent method calls; 4.\_\_get, implement dynamic attribute access; 5.\_\_set, implement dynamic attribute settings. These methods are automatically called in certain situations, improving code flexibility and efficiency.

See all articles