javascript - 如何使Excel文件成为交互式网页？

Question

如何使我的Excel文件作为网页工作，人们可以在其中输入数据并将其保留在那里？我是新来的!

Answer 1

该练习所需的技术:JavaScript，JQuery，PHP，AJAX和MySQL。和Excel!所有这一切都需要进行较小的编辑，仅向生成的html页面添加3行文本。
http://ip.jawfin.net上正在运行演示
我想指出，Excel另存为html的格式已被严重破坏-Excel 2013生成了非常干净的html，其样式内置于注释中(因此仅生成了1个文件，没有CSS)，它的缩进和简单易用读书。唯一的缺点是样式的类名称相当随意!没有JavaScript，只有纯HTML，这是一个不错的开始，然后在Notepad ++或您喜欢的编辑器中进行优化。请注意，缺少CSS可能会再次咬住您，为广泛使用，我建议提取注释以​​从中构建CSS。
首先，我们的Excel。这是我制作的，这是一个简单的应用程序，它将您的IP放在屏幕上选择的单元格中。 (此方法的实际用途是安排一个月的调度程序，这种“选择颜色”可以很好地演示所有功能，希望可以轻松适应。)不过，有一项更改是，我将访问者的IP用作保护。
这是我的Excel文件:http://ip.jawfin.net/ip.xlsx
我们不会导出整个电子表格，只需选择要构成网页的单元格即可。就我而言:-

选择单元格后，进入"file" /“另存为”，选择文件夹，另存为网页(* .htm)，仅选择“选择项”，如果需要，为其命名，将其命名为index.htm，然后保存!

现在，将index.htm重命名为index.php，因为我们将php代码放入其中。现在，使这3行代码成为一个交互式网页。在底部的</body>和</html>之间插入以下3行:-

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<?php include 'excel.php';?>
<script src="excel.js"></script>

所以我们有:-

</body>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<?php include 'excel.php';?>
<script src="excel.js"></script>

</html>

导入JQuery的第一行可让我们的AJAX正常工作。
第二是获取用户的IP。注意，如果需要的话，这可以做更多的事情，例如，在我的实现中，我使用它登录到phpBB论坛并获取登录的用户名和权限。此php还显示了如何将服务器php变量传递给本地javascript。
第三行是大脑，负责所有工作并与服务器进行通信以更新和获取数据。
(您知道，如果我将脚本行作为回显方式嵌入到php中，那么这实际上可能只是一行代码，但是我认为这会失去可读性。)
对于数据库:在Web服务器上，登录到面板并为此应用程序创建一个新数据库。
然后登录到phpMyAdmin创建您的应用程序所需的表。
在这种情况下，它是一个表，称为cell_ip。其结构:-

field:cell; type:integer; index:primary + unique 
field:ip; type:text 

或如phpMyAdmin中所示:-

该项目使用的文件是:-

index.php the one we just created in Excel, goes in the web folder

excel.php the file which fetches server-side variables (note we could also use this to inject dynamically created controls)

excel.js the script running local interface, also communicates with the server

server.php the database handler on the server, deals with the AJAX requests and database manipulations

settings.php just a means of storing the database configuration in a safe place

所有这些文件都进入Web文件夹 的根目录中，但 settings.php进入了根目录，该目录位于Web上方的文件夹中，Internet无法在其中找到，但我们的server.php可以。
请注意，这4个php文件的工作完全不同:index.php是他们看到的网页，是唯一可见的php文件。 excel.php将代码注入(inject)index.php和网页中，从而可以获取客户端的服务器端设置。 server.php就像一个单独在服务器上运行的程序，我们的网页调用该应用程序将数据保存并加载到服务器端数据库中。 settings.php只是一个光荣的ini文件，是一种在Internet看不见的情况下快速存储敏感信息的方法。
所有消息来源都有解释过程的相关注释-但是我愿意回答任何我可以回答的问题，尽管我不是专家，但请注意。因此，事不宜迟，工作源代码就可以了。
(编辑:由于我不能在此处提供完整的源代码，因此我将提供链接。请告诉我是否有一种更聪明/更理想的方式。因为这样，我将它们重命名为.txt，所以它的行为不像网页!)
index.php-太大，无法包含，请下载或根据上面的说明自行制作。
excel.php

<?php //excel.php
//Let's create a string specific to the user without giving away private info 
$ip = $_SERVER['REMOTE_ADDR']; //I'm going with hashing the md5 of their IP, then mod'ing back to 12 digits (from 38)
$hash = fmod(hexdec(md5($ip)),1e12);
echo '<script>'; //clever means of getting a php variable into javascript
echo 'var user_ip = ' . json_encode($hash) . ';'; //replace $hash with $ip you want to see the real address!
echo '</script>';
?>

excel.js

//excel.js
$.ajaxSetup({ cache: false }); //needed to stop IE/Edge from caching AJAX GET requests

var cells = document.getElementsByTagName("td"); //array of all cells, excel assigns them type HTML table cell "td"
var updating = false; //prevent flicker if writing to the dataset and a read overwrites our status messages
var reentry = false; //prevent overlapping refresh. polls 1 a sec, 5 second time-out, could lead to massive overlap!
var addingStr = ' -- Adding IP --'; //const not used for backwards compatibility, IE9 etc.
var removeStr = ' -- Removing IP --';
var replaceStr = ' -- Replacing IP --';

for (var i = 0; i < cells.length; i++) { //you can assign different aspects by the .innerHTML
        cells[i].style.cursor = 'cell'; //or 'pointer' - the default is the edit caret cursor which looks ugly
        cells[i].id = i; //tag our elements, but note this is happening AFTER the DCOM scan so getElementById() won't work
        cells[i].onclick = function() { cellClick(this); };
}
refreshScreen();
setInterval(function() { //ajax poll, refresh every second!
    refreshScreen();
}, 1000); 

function cellClick(cell) {
    updating = true; //don't allow the refresh to remove our status comment in the cell
    var mydata = 'action=';
    if (cell.innerHTML == "" || cell.innerHTML == "&nbsp;") {
    cell.innerHTML = addingStr;
      mydata += 'c'; //write into this cell the users IP
    } else {
        if (cell.innerHTML == user_ip) { //let's be smart and allow them to remove their own IP :)
            cell.innerHTML = removeStr;
            mydata += 'd'; //clear/delete this cell
        } else { //it's someone else, hijack them!!
            cell.innerHTML = replaceStr;
            mydata += 'u'; //update this cell
        }
    }
    mydata += '&cell=' + cell.id + '&ip=' + user_ip; //add our parameters
  $.ajax({ //JQuery ajax, so much cleaner and safer than using JavaScript ajax
        url: "server.php", //our server-side worker
        type: 'POST', //database changes, use POST, we don't want a webcrawler or a cache hitting a GET with parameters
        data: mydata,
        timeout: 5000, //5 second should be ample, but if they lose connectivity allow it to fail 
        success: 
          function(data) {
                updating = false;
                if (data != 1) { //no matter what, in this CRUD only 1 record should have been affected
                    alert('Data update error. Parameters: ' + data + '. Result: ' + data);
                }
                refreshScreen(); //refresh screen with new data
            },
        error: 
          function(xhr, ajaxOptions, thrownError) { //put better error handling in if this fires too often!
//              alert('Data = ' + mydata + '. Error # = ' + xhr.status + '. Message = '+thrownError); //uncommend to debug
                updating = false;
                refreshScreen();
            }
    });
}

function refreshScreen() {
    if (updating) return; //just wait a second
    if (reentry) return; //we're already pending a refresh
    reentry = true;
    var mydata = 'action=r'; //we want to Read the data - stored in a varible in case we turn on our error alert
    $.ajax({
    url: "server.php",
      type: 'POST',
        data: mydata,
        timeout: 5000,
//      dataType: 'json', //just saves us a line of formatting text on success. !!commented out, not debug friendly
        success: function(data) {         //on recieve of reply
            reentry = false;
          if (updating) return; //they clicked between the request and the return here, just wait it out, next second!
            data = JSON.parse(data); //i prefer this instead of dataType:'json' so I check server script errors in the throw
            var results = []; //get our data into a 2 dimensional array (has 2 dimensions as our query returned 2 fields
            for(var x in data) { //get all the data ready before we touch the screen, cut down any possibly latency
                results.push(data[x]);
            }
            for (var i = 0; i < cells.length; i++) { //reset screen
                if (cells[i].innerHTML != removeStr) { //skipping the "Remove" messages (dirty read) - will clean up below if they were removed!
                    cells[i].innerHTML = ""; //clear field
                }
            }
            for (var i = 0; i < results.length; i++) { //now we stuff our cells with the IPs we have
                cells[results[i][0]].innerHTML = results[i][1]; //first array element is cell number, the second is the IP 
            }
            //as the "Remove" status was skipped above need to loop again to clear any found
            for (var i = 0; i < cells.length; i++) { //backwards clean
                if (cells[i].innerHTML == removeStr) { //text is here as this cell didn't come through the SELECT
                    cells[i].innerHTML = ""; //clear field
                }
            }
        },
        error: 
          function(xhr, ajaxOptions, thrownError) { //uncomment alert for debugging
            reentry = false;
//              alert('Query = ' + mydata + '. Error # = ' + xhr.status + '. Message = '+thrownError);
              //Note that xhr.status == 200 is an OK from the server but JSON invalid, so check for that first!
          }
    });
}

server.php

<?php //server.php
if (!isset($_POST['action'])) exit; //no action parameter, just leave. could echo an error message if required though
$action = $_POST['action'];
if (!strpos(' crud',$action)) { //note the space out front, or else it'll fail on 'c' as it returns zero, which = false
    echo("Unknown action='$action'"); // took me ages to debug that, resulting in this line of code!!!!
    exit; //otherwise not one of ours, quit before SQL stuff starts
}   

require "../settings.php"; //lazy place to easily store settings out of reach from the Internet, parent of the web root. 
//Be aware I used ".." - if this server.php is not in the webroot then the database.php is not out of reach. 
//Note this method is easier than loading an .ini file and parsing it within an array.
if ($db_server == "") { //ASSERT: this should never fire as the require would fail on not find, but can't be too sure
    echo "Problem getting database settings.";
    exit;
}

$connection = mysql_connect($db_server, $db_username, $db_password); // Establishing Connection with Server
if (!$connection) {
    echo "Error connecting to database.";
    exit;
}
$db = mysql_select_db($db_database_ip, $connection); // Selecting Database
if (!$db) {
    echo "Specific database not found.";
    exit;
}

if ($action == 'c') { //note this will fail if there is a blank record against this cell, so check here
    $cell = mysql_real_escape_string($_POST['cell']); //extract our parameter
    $ip = mysql_real_escape_string($_POST['ip']);
    if ($ip == "") {
        echo "Cannot add blank IP in cell '$cell'";
    } else {
        $query = mysql_query("INSERT INTO cell_ip(cell, ip) values ('$cell', '$ip')"); //Create Query
        echo $query; //always return the result, even if its unexpected. can hold error messages for debugging
    }
}

if ($action == 'r') { //JSON our database back to the client. 
//Note I am using the POST protol instead of GET, tidier on this server.php keeping all my server requests in one file
    $result = mysql_query("SELECT cell, ip FROM cell_ip"); //Read Query
    while ($row = mysql_fetch_row($result)) {
        $table_data[] = $row;
    }
    echo json_encode($table_data); //return the whole dataset
}

if ($action == 'u') { //pinching someone else's cell with our ip, make sure the new ip exists too
    $cell = mysql_real_escape_string($_POST['cell']); 
    $ip = mysql_real_escape_string($_POST['ip']);     
    if ($ip == "") {
        echo "Cannot edit to a blank IP in cell '$cell'";
    } else {
        $query = mysql_query("UPDATE cell_ip SET ip = '$ip' WHERE cell = '$cell'"); //Update Query
        echo $query;
    }
}

if ($action == 'd') { //delete this cell
    $cell = mysql_real_escape_string($_POST['cell']); //extract our parameter
    $query = mysql_query("DELETE FROM cell_ip WHERE cell = '$cell'"); //Delete Query
    echo $query;
}

mysql_close($connection); //Note this was never opened if the caller failed the "action" validation, being polite to our sql
?>

settings.php(这进入根目录，更改其中的值以适合)

<?php //settings.php
$db_server='localhost';
$db_username='root';
$db_password='root_password';
$db_database_ip='excel_ip';
?>

注意事项:-
如果您的数据库依赖于单元格号，则在您编辑Excel并重新发布html时，它们可能会更改。
该演示仅依赖于单元格编号，因为它只是一个演示-例如，您可以在单元格中放置文本，然后使用它来标识相关单元格。
如果您在CloudFlare上运行服务器并更改任何.js或.php文件，它们将被缓存，因此您需要在上传文件后在CloudFlare(缓存)中清除这些文件。
安全!!!由于JS在客户端上运行，因此最终用户将无法使用您的JavaScript，无论它们如何打包或加密都可以发现。请勿包含密码或知识产权。这也意味着您的AJAX请求可能被恶意启动，请确保在服务器端php中进行大量处理和过滤。我还建议使用 token + session 处理程序来验证调用客户端。
最后说明:我写这篇文章有两个原因。首先，我想在10年的时间里读到这篇文章，这样我就可以了解自己现在是如何变态和变新(这是我作为Web开发人员的第二天)，其次，我不希望任何人经历我的可耻和羞辱的经历在StackOverflow上为此项目寻求帮助时所做的事情(尽管您找不到我的问题，但该问题已删除)。我希望这篇文章对某人有用，我花了一整天的时间来设计它:)