SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast subathraramasami's Avatar
    Join Date
    Jul 2008
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need edit and save a excel file in the website

    Hi all,

    I can any one help me on how to open the excel file from server at client and edit the same file and save back at server.

    How to do it?

    Please suggest ur valuable solutions.

    Regards,
    Subathra.R

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1. Link to the file on your website.
    2. Client clicks the link to download the file.
    3. Client edits the file.
    4. Client uploads the file through an upload form you put on the website.

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to be really clever, use a PHP/Excel library to convert the Excel data so that it can be rendered in an editable javascript grid (e.g. using extjs). When the page is submitted with all the grid data, update the original file using the PHP/Excel library and rewrite it back to file.

    It is possible, though I've yet to find a grid library that can handle some of the more complex Excel-style editing functions like merging cells.

    The following code snippet doesn't use anything very sophisticated for editing (no js libraries), but provides basic data editing for a single worksheet from an Excel file.
    PHP Code:
    <?php

    /**    Error reporting        **/
    error_reporting(E_ALL);


    /**    Include path        **/
    set_include_path(get_include_path() . PATH_SEPARATOR 'PHPExcel_NewCalc/Classes/');


    $maxRow 12;
    $maxCol 'F';


    $maxCol++;

    /**    If the user has submitted the form, then we need to execute a calculation **/
    if (isset($_POST['submit'])) {
        
    /**    So we include PHPExcel to perform the calculations    **/
        
    include('PHPExcel.php');

        
    /**    And instantiate a new workbook in memory            **/
        
    $objPHPExcel = new PHPExcel();
        
    $objPHPExcel->setActiveSheetIndex(0);
    }

    /**    Set values for each cell in the grid    **/
    for ($column 'A'$column != $maxCol; ++$column) {
        for (
    $row 1$row <= $maxRow; ++$row) {
            
    $cellRef $column.$row;
            
    $cellValueID $cellRef.'_hidden';
            
    $cellCalcID $cellRef.'_display';
            
    //    Set value in the form for redisplay
            
    $$cellCalcID = $$cellValueID = (isset($_POST[$cellValueID])) ? html_entity_decode($_POST[$cellValueID],ENT_QUOTES,'UTF-8') : '';
            if (
    get_magic_quotes_gpc()) { $$cellCalcID stripcslashes($$cellCalcID); }
            if (
    get_magic_quotes_gpc()) { $$cellValueID stripcslashes($$cellValueID); }
            
    /**    If the user has submitted the form, we populate the workbook as well    **/
            
    if (isset($_POST['submit'])) {
                
    /**    Set value in the worksheet for calculation    **/
                
    if (is_numeric($$cellValueID)) { $$cellValueID = (float) $$cellValueID; }
                
    $objPHPExcel->getActiveSheet()->setCellValue($cellRef, $$cellValueID);
            }
        }
    }

    /**    Perform any calculations set in the grid    **/
    if (isset($_POST['submit'])) {
        
    /**    Set values for each cell in the grid    **/
        
    for ($column 'A'$column != $maxCol; ++$column) {
            for (
    $row 1$row <= $maxRow; ++$row) {
                
    $cellRef $column.$row;
                
    $cellCalcID $cellRef.'_display';
                $
    $cellCalcID $objPHPExcel->getActiveSheet()->getCell($cellRef)->getCalculatedValue();
                if (
    is_bool($$cellCalcID)) {
                    $
    $cellCalcID = ($$cellCalcID) ? 'TRUE' 'FALSE';
                }
            }
        }
    }
    $focusedCell = (isset($_POST['editingID'])) ? $_POST['editingID'] : 'A1';

    ?>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

    <title>Worksheet Calculator</title>

    <style type="text/css">
        .unfocusedCell    { border-width: 1; border-style: solid; border-color: darkgrey; }
        .focusedCell    { border-width: 1; border-style: solid; border-color: red; }
        .cellTextBox    { border-width: 0; border-style: none; border-color: white; }
        .columnHeading    { border-width: 1; border-style: solid; border-color: darkgrey; background-color: silver; text-align: center; font-weight: bold; }
        .rowHeading        { border-width: 1; border-style: solid; border-color: darkgrey; background-color: silver; text-align: right; font-weight: bold; }
    </style>

    <script language=javascript type='text/javascript'>
        var currentCell = '';
        var currentCellValue = '';
        var currentCellCalcValue = '';
        var cellHasChanged = false;

        function clearCellFocus() {
            if (currentCell != '') {
                var previousCellName = currentCell+"_hidden";
                var previousCell = document.getElementById(previousCellName);
                previousCell.parentNode.className = 'unfocusedCell';

                currentCell = '';
                currentCellValue = '';
                currentCellCalcValue = '';

                var editingCellDest = document.getElementById("editingID");
                var editingValueDest = document.getElementById("editingValue");
                editingCellDest.value = '';
                editingValueDest.value = '';
            }
        }

        function setCellFocus(cellID) {
            var newCellName = cellID+"_hidden";
            var newCell = document.getElementById(newCellName);
            newCell.parentNode.className = 'focusedCell';

            var dataSourceName = cellID+"_hidden";
            var dataSource = document.getElementById(dataSourceName);
            var dataValueName = cellID+"_display";
            var dataValue = document.getElementById(dataValueName);

            currentCell = cellID;
            currentCellValue = dataSource.value;
            currentCellCalcValue = dataValue.value;

            var editingCellDest = document.getElementById("editingID");
            var editingValueDest = document.getElementById("editingValue");
            editingCellDest.value = cellID;
            editingValueDest.value = dataSource.value;
        }

        function displayCellFocus(cellID) {
            var displayCellName = cellID+"_display";
            var displayCell = document.getElementById(displayCellName);
            displayCell.focus();
        }

        function editCell(cellID) {
            clearCellFocus();
            setCellFocus(cellID);
        }

        function changeCellFromEditor(e) {
            var keyCode = e.keyCode;

            if (currentCell != '') {
                if ((keyCode == 16) || (keyCode == 44) || (keyCode == 17) ||        //    SHIFT, SYS REQ, CONTROL or ALT GR
                    (keyCode == 20) || (keyCode == 144) || (keyCode == 145) ||        //    CAPS LOCK, NUM LOCK, SCROLL LOCK
                    (keyCode == 45) || (keyCode == 46) ||                            //    INSERT, DELETE
                    (keyCode == 36) || (keyCode == 35) ||                            //    HOME, END
                    (keyCode == 33) || (keyCode == 34) ||                            //    PAGE UP, PAGE DOWN
                    (keyCode == 38) || (keyCode == 40) ||                            //    UP ARROW, DOWN ARROW
                    (keyCode == 39) || (keyCode == 37) ||                            //    RIGHT ARROW, LEFT ARROW
                    (keyCode == 18) || (keyCode == 19) ||                            //    ALT, PAUSE/BREAK
                    (keyCode == 91) || (keyCode == 92)                                //    WINDOWS LEFT, WINDOWS RIGHT
    //    93    SELECT
    //    112 - 123    F1 - F12
                    ) {
                    null;
                } else if ((keyCode == 9) || (keyCode == 13)) {        //    TAB or ENTER
                    refreshForm();
                } else if (keyCode == 27) {                            //    ESCAPE
                    var editingValue = document.getElementById("editingValue");
                    var cellValueName = currentCell+"_hidden";
                    var cellValue = document.getElementById(cellValueName);
                    cellValue.value = currentCellValue;
                    var cellDisplayName = currentCell+"_display";
                    var cellDisplayValue = document.getElementById(cellDisplayName);
                    cellDisplayValue.value = currentCellCalcValue;
                    clearCellFocus();
                    cellHasChanged = false;
                } else {
                    var editingValue = document.getElementById("editingValue");
                    var cellValueName = currentCell+"_hidden";
                    var cellValue = document.getElementById(cellValueName);
                    cellValue.value = editingValue.value;
                    var cellDisplayName = currentCell+"_display";
                    var cellDisplayValue = document.getElementById(cellDisplayName);
                    cellDisplayValue.value = editingValue.value;
                    if (cellValue.value !== currentCellCalcValue) {
                        cellHasChanged = true;
                    }
                }
            }
        }

        function changeCellFromCell(e) {
            var keyCode = e.keyCode;

            if (currentCell != '') {
                if ((keyCode == 16) || (keyCode == 44) || (keyCode == 17) ||        //    SHIFT, SYS REQ, CONTROL or ALT GR
                    (keyCode == 20) || (keyCode == 144) || (keyCode == 145) ||        //    CAPS LOCK, NUM LOCK, SCROLL LOCK
                    (keyCode == 45) || (keyCode == 46) ||                            //    INSERT, DELETE
                    (keyCode == 36) || (keyCode == 35) ||                            //    HOME, END
                    (keyCode == 33) || (keyCode == 34) ||                            //    PAGE UP, PAGE DOWN
                    (keyCode == 38) || (keyCode == 40) ||                            //    UP ARROW, DOWN ARROW
                    (keyCode == 39) || (keyCode == 37) ||                            //    RIGHT ARROW, LEFT ARROW
                    (keyCode == 18) || (keyCode == 19) ||                            //    ALT, PAUSE/BREAK
                    (keyCode == 91) || (keyCode == 92)                                //    WINDOWS LEFT, WINDOWS RIGHT
                    ) {
                    null;
                } else if ((keyCode == 9) || (keyCode == 13)) {        //    TAB or ENTER
                    refreshForm();
                } else if (keyCode == 27) {                            //    ESCAPE
                    var editingValue = document.getElementById("editingValue");
                    var cellValueName = currentCell+"_hidden";
                    var cellValue = document.getElementById(cellValueName);
                    cellValue.value = currentCellValue;
                    var cellDisplayName = currentCell+"_display";
                    var cellDisplayValue = document.getElementById(cellDisplayName);
                    cellDisplayValue.value = currentCellCalcValue;
                    editCell(currentCell);
                    cellHasChanged = false;
                } else {
                    var editingValue = document.getElementById("editingValue");
                    var cellDisplayName = currentCell+"_display";
                    var cellDisplayValue = document.getElementById(cellDisplayName);
                    editingValue.value = cellDisplayValue.value;
                    var cellValueName = currentCell+"_hidden";
                    var cellValue = document.getElementById(cellValueName);
                    cellValue.value = cellDisplayValue.value;
                    if (cellDisplayValue.value !== currentCellCalcValue) {
                        cellHasChanged = true;
                    }
                }
            }
        }

        function refreshForm() {
            displayCellFocus(currentCell);
            if (cellHasChanged) {
                var cellGrid = document.getElementById("cellGrid");
                cellGrid.submit.click();
            }
        }
    </script>
    </head>
    <body>
    <!--    Display the Form    -->

    <form id="cellGrid" action="gridCalculator.php" method="post">
        <b>Cell ID:</b> <input id="editingID" name="editingID" type="text" size="6" readonly tabIndex="99999" />
        <b>Cell Value:</b> <input id="editingValue" name="editingValue" type="text" size="40" onKeyUp="javascript:changeCellFromEditor(event);" onBlur="javascript:refreshForm()" tabIndex="99998" />

        <table border="0" cellpadding="0" cellspacing="0">
            <tr><td class="columnHeading">&nbsp;</td>
    <?php

    for ($column 'A'$column != $maxCol$column++) {
        echo 
    '            <td class="columnHeading">'.$column.'</td>'.PHP_EOL;
    }
    echo 
    '        </tr>'.PHP_EOL;

    for (
    $row 1$row <= $maxRow; ++$row) {
        echo 
    '        <tr><td class="rowHeading">&nbsp;'.$row.'&nbsp;</td>'.PHP_EOL;
        for (
    $column 'A'$column != $maxCol; ++$column) {
            
    $cellRef $column.$row;
            
    $cellValueID $cellRef.'_hidden';
            
    $cellCalcID $cellRef.'_display';
            if (
    $cellRef == $focusedCell) {
                echo 
    '            <td class="focusedCell">';
            } else {
                echo 
    '            <td class="unfocusedCell">';
            }
            echo 
    '                <input name="'.$cellValueID.'" id="'.$cellValueID.'" type="hidden" value="'.htmlentities($$cellValueID).'" />';
            echo 
    '                <input class="cellTextBox" name="'.$cellCalcID.'" id="'.$cellCalcID.'" type="text" size="14" value="'.htmlentities($$cellCalcID).'" onKeyUp="javascript:changeCellFromCell(event);" onFocus="javascript:editCell(\''.$cellRef.'\');" />';
            echo 
    '            </td>'.PHP_EOL;
        }
        echo 
    '        </tr>'.PHP_EOL;
    }

    ?>
        </table>
        <br /><input name="submit" type="submit" value="calculate">
    </form>


    <script language=javascript type='text/javascript'>
        setCellFocus('<?php echo (isset($_POST['editingID'])) ? $_POST['editingID'] : 'A1'?>');
        displayCellFocus(currentCell);
    </script>

    </body>
    </html>
    Note that this starts with a blank worksheet, but it could easily be modified to read from a named Excel file which populates the cells on startup, and a save button could be added that would allow the final worksheet to be written back to file.

    You will probably find it fairly unresponsive because it needs to communicate with the server after every change to a cell in order to modify the results of formulae


    My example here is no more than a proof of concept.
    This is no trivial task, and unless you have a real team of PHP/Excel/Javascript wizards, then Dan's suggestion is a far better option for the average developer.
    Last edited by Mark Baker; Oct 29, 2009 at 03:03. Reason: Recommend Dan's suggestion unless the OP has the original GoogleDocs development team at his disposal
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excel can handle XML files easily enough. Personally speaking I'd be tempted to use that instead. Of course this depends heavily on the complexity of the excel file/s involved.

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by b@rryp View Post
    Excel can handle XML files easily enough. Personally speaking I'd be tempted to use that instead. Of course this depends heavily on the complexity of the excel file/s involved.
    Depends on the version of Excel. Excel 2007 xlsx unzips to a series of XML file, though still not simple: earlier versions (xls) are a proprietary binary format.
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •