javascript spreadsheet implode to MYSQL
Moderator: General Moderators
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
javascript spreadsheet implode to MYSQL
hii all..
i've been trying to render a spreadsheet in a web browser for 1 month, atlast i succeeded..
now i have to save that spreadsheet as .xls sheet, and import the same to MYSQL table containing same number of fields as of spreadsheet.
now i'm implodin data from excel sheet to mysql table easily by startin a COM application defining the excel sheet path then defining the columns of excel sheet and then inserting data.itz a mundane task.
but now i need to save the spreadsheet on client machine as .XLS and then import it to DB in either xls or csv formats..
as we have idea about google spreadsheets, which end user has choice of loading an existing MS EXCEL sheet or saving the current spreadsheet as MS EXCEL.. iwant to do the same in my application.
please give any suggestions..
Many Regards.
i've been trying to render a spreadsheet in a web browser for 1 month, atlast i succeeded..
now i have to save that spreadsheet as .xls sheet, and import the same to MYSQL table containing same number of fields as of spreadsheet.
now i'm implodin data from excel sheet to mysql table easily by startin a COM application defining the excel sheet path then defining the columns of excel sheet and then inserting data.itz a mundane task.
but now i need to save the spreadsheet on client machine as .XLS and then import it to DB in either xls or csv formats..
as we have idea about google spreadsheets, which end user has choice of loading an existing MS EXCEL sheet or saving the current spreadsheet as MS EXCEL.. iwant to do the same in my application.
please give any suggestions..
Many Regards.
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: javascript spreadsheet implode to MYSQL
hii..
can any one suggest me how to do this please...
can any one suggest me how to do this please...
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: javascript spreadsheet implode to MYSQL
Why are you trying to use MySQL?pavanpuligandla wrote:can any one suggest me how to do this please...
Why not use a decent PHP/Excel library like PHPExcel that can read and write Excel workbooks
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: javascript spreadsheet implode to MYSQL
i've to maintain a database so i need to import spreadsheet values to mysql.Why are you trying to use MySQL?
yes i've used php excel reader to read the excel sheet and rendered it graphically in a web browser too.but how can we edit that? it is not possible to edit the content.Why not use a decent PHP/Excel library like PHPExcel that can read and write Excel workbooks
i think as far as my knowledge, php spreadsheet writer is for saving data in excel which may a processed Db query or an array of predefined values..so it will not server my purpose of editing the excel sheet in a WEB BROWSER. this can only be done by using an editable spreadsheets like google docs, zoho worksheets..
no issues i've my own javascript spreadsheet which we can edit it.
now my task is to move all those spreadsheet columns to the database.
secondly loading an existing xls sheet in my java script based spreadsheet like google docs.
hope u understand my problem mate..
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: javascript spreadsheet implode to MYSQL
I still don't particularly understand why you can't simply read the spresheet file, and pass the information directly from that to your javascript editor. Once the worksheets have been edited within your javascript editor, then you simply post the data back to the server, and write it back to the filesystem as an Excel workbook again. I just can't see any need for a database.pavanpuligandla wrote:i've to maintain a database so i need to import spreadsheet values to mysql.
Data to write back on a user clicking "save" can just as easily come from a POST request carrying the spreadsheet data from a browser front end.pavanpuligandla wrote:i think as far as my knowledge, php spreadsheet writer is for saving data in excel which may a processed Db query or an array of predefined values..so it will not server my purpose of editing the excel sheet in a WEB BROWSER. this can only be done by using an editable spreadsheets like google docs, zoho worksheets..
pavanpuligandla wrote:yes i've used php excel reader to read the excel sheet and rendered it graphically in a web browser too.but how can we edit that? it is not possible to edit the content.
Surely if you've already rendered the spreadsheet data to your web browser, and have created a javascript spreadsheet editor, then you have all the browser front-end components working.pavanpuligandla wrote:no issues i've my own javascript spreadsheet which we can edit it.
now my task is to move all those spreadsheet columns to the database.
secondly loading an existing xls sheet in my java script based spreadsheet like google docs.
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: javascript spreadsheet implode to MYSQL
database is must, bcoz i'm developing an university system portal, so instead of html forms, i' ve opted excel as my frontend containing columns like studentid, name, field of study, course, semester and marks , grades obtained in each n every exam. so i must maintain a database, excel is just a customized front end form in my application. which is easy to upload as many records as possible at a time but not just like a form which will inserts 1 record.(there mite be an alternative for inserting many records, but its not related to me at all.I still don't particularly understand why you can't simply read the spresheet file, and pass the information directly from that to your javascript editor. Once the worksheets have been edited within your javascript editor, then you simply post the data back to the server, and write it back to the filesystem as an Excel workbook again. I just can't see any need for a database.
this is the purpose i'm using a DB for.
yes i rendered the excel sheet in a web browser using php excel reader, which looks like a html static table. so if the administrator find any wrong or duplicate entries while viewing this browser based excel sheet, then he has to again modify the original ms excel file, instead of doing that why cant we provide an EDITABLE spreadsheet in the browser itself?Surely if you've already rendered the spreadsheet data to your web browser, and have created a javascript spreadsheet editor, then you have all the browser front-end components working.
i can only give google spreadsheets as an example which is absolutely realted to my case.
so i have to provide these features in my javascript spreadsheet.
-> Load an existing excel file in my spreadsheet.
-> save as a copy.
-> uploading it to the DB (which i'm uploading excel sheet values directly to mysql tables now)
my doubt is whether my javascript spreadsheet allows me to save it as MICROSOFT EXCEL format or not? and whether i can upload it to DB as i'm doing now or not?
i think i'm almost clear with my topic..
please try to understand..
i've seen the same post( modifying or updating an existing excel sheet using php in a web browser on the fly) in around 100 forums..
i've googled around 500 pages..
but i recognized that this can be done easily using ASP.NET, as we know ASP.NET supports microsofts API's.
Many Regards,
Pavan
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: javascript spreadsheet implode to MYSQL
i know the post will grow older but no one can can help me out..
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: javascript spreadsheet implode to MYSQL
where r u Mr.Mark Baker??

-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: javascript spreadsheet implode to MYSQL
OK, just as a proof of concept, I knocked up the attached:pavanpuligandla wrote:where r u Mr.Mark Baker??![]()
It only took an hour or so, so it's not pretty, and very simplistic, but it works. You should be able to work through the basic logic to modify it further yourself.
You'll need PHPExcel, and I've added a new writer ("HTMLEditable.php" which needs to go in the PHPExcel Writers subdirectory). Modify file paths at the top of getWorkbookHTMLEditable.php and refreshWorkbookHTMLEditable.php as necessary.
http://localhost/Excel/getWorkbookHTMLE ... aTest.xlsx
This should render the workbook FormulaTest.xlsx in an HTML table.
Click the tabs on the bottom to switch from one sheet to another.
Click on a cell, and the cell formula or value will be displayed in the form at the top of the page. Edit the cell value, then tab off the form field and the script will call refreshWorkbookHTMLEditable.php to redraw the screen with the new values.
While you're working on your workbook, you're actually working on a copy (hardcoded name is "wrkFile.xlsx"). I've not added a save, I'll leave that to you; but it should simply need to copy wrkFile.xlsx to overwrite the original workbook.
The message with the final file looks empty, but if you click on "Quote" you should be able to see the content
Last edited by Mark Baker on Sat Nov 08, 2008 5:47 am, edited 1 time in total.
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: javascript spreadsheet implode to MYSQL
getWorkbookHTMLEditable.php
Code: Select all
<?php
error_reporting (E_ERROR);
if (file_exists('./tmpFile.phpxl')) {
unlink('./tmpFile.phpxl');
}
/** Include path **/
set_include_path(get_include_path().PATH_SEPARATOR.'./PHPExcel/Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
$path = './workbooks';
$mode='list';
if (isset($_GET['node'])) {
$node = urldecode($_GET['node']);
$path .= $node;
} else {
exit();
}
$cwd = getcwd();
if (substr(PHP_OS, 0, 3) == 'WIN') {
$cwd = str_replace('\\','/',substr($cwd,2));
}
if (!file_exists($cwd.$path)) {
echo '<font color="red"><b>ERROR: </b>';
switch ($mode) {
case 'read' : echo 'unable to read '.$node.' file';
break;
default : echo 'unknown error';
}
die;
}
$fileInfo = pathinfo($path);
$fileName = $fileInfo['basename'];
switch (strtolower($fileInfo['extension'])) {
case 'xlsx' : $fileType = 'Excel 2007';
$fileReader = 'Excel2007';
break;
case 'xls' : $fileType = 'Excel 5 (BIFF)';
$fileReader = 'Excel5';
break;
}
$objReader = PHPExcel_IOFactory::createReader($fileReader);
$objPHPExcel = $objReader->load($cwd.$path);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTMLEditable');
$objWriter->writeAllSheets();
$objWriter->save('php://output');
$wrkWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$wrkWriter->save('./wrkFile.xlsx');
?>
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: javascript spreadsheet implode to MYSQL
refreshWorkbookHTMLEditable.php
Code: Select all
<?php
error_reporting (E_ERROR);
/** Include path **/
set_include_path(get_include_path().PATH_SEPARATOR.'./PHPExcel/Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
$workSheet = substr($_POST['worksheetID'],2);
$cellID = $_POST['editingID'];
$cellValue = $_POST['editingValue'];
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('./wrkFile.xlsx');
$objPHPExcel->setActiveSheetIndex($workSheet);
$objPHPExcel->getActiveSheet()->setCellValue($cellID, $cellValue);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTMLEditable');
$objWriter->writeAllSheets();
$objWriter->save('php://output');
$wrkWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$wrkWriter->save('./wrkFile.xlsx');
?>
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: javascript spreadsheet implode to MYSQL
HTMLEditable.php
Code: Select all
<?php
/**
* PHPExcel
*
* Copyright (c) 2006 - 2008 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel_Writer
* @copyright Copyright (c) 2006 - 2008 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.6.4, 2008-10-27
*/
/** PHPExcel_IWriter */
require_once 'PHPExcel/Writer/IWriter.php';
/** PHPExcel_Cell */
require_once 'PHPExcel/Cell.php';
/** PHPExcel_RichText */
require_once 'PHPExcel/RichText.php';
/** PHPExcel_Shared_Drawing */
require_once 'PHPExcel/Shared/Drawing.php';
/** PHPExcel_Shared_String */
require_once 'PHPExcel/Shared/String.php';
/** PHPExcel_HashTable */
require_once 'PHPExcel/HashTable.php';
/**
* PHPExcel_Writer_HTML
*
* @category PHPExcel
* @package PHPExcel_Writer
* @copyright Copyright (c) 2006 - 2008 PHPExcel (http://www.codeplex.com/PHPExcel)
*/
class PHPExcel_Writer_HTMLEditable implements PHPExcel_Writer_IWriter {
/**
* PHPExcel object
*
* @var PHPExcel
*/
private $_phpExcel;
/**
* Sheet index to write
*
* @var int
*/
private $_sheetIndex;
/**
* Pre-calculate formulas
*
* @var boolean
*/
private $_preCalculateFormulas = true;
/**
* Images root
*
* @var string
*/
private $_imagesRoot = '.';
/**
* Create a new PHPExcel_Writer_HTML
*
* @param PHPExcel $phpExcel PHPExcel object
*/
public function __construct(PHPExcel $phpExcel) {
$this->_phpExcel = $phpExcel;
$this->_sheetIndex = 0;
$this->_imagesRoot = '.';
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null) {
// Open file
$fileHandle = fopen($pFilename, 'w');
if ($fileHandle === false) {
throw new Exception("Could not open file $pFilename for writing.");
}
// Write headers
fwrite($fileHandle, $this->generateHTMLHeader());
fwrite($fileHandle, $this->generateStyles(true));
fwrite($fileHandle, $this->generateEditorData());
fwrite($fileHandle, '<hr />'. PHP_EOL);
// Write data
fwrite($fileHandle, $this->generateSheetData());
// Write footer
fwrite($fileHandle, $this->generateHTMLFooter());
// Close file
fclose($fileHandle);
}
/**
* Map VAlign
*/
private function _mapVAlign($vAlign) {
switch ($vAlign) {
case PHPExcel_Style_Alignment::VERTICAL_BOTTOM: return 'bottom';
case PHPExcel_Style_Alignment::VERTICAL_TOP: return 'top';
case PHPExcel_Style_Alignment::VERTICAL_CENTER:
case PHPExcel_Style_Alignment::VERTICAL_JUSTIFY: return 'middle';
default: return ' baseline';
}
}
/**
* Map HAlign
*/
private function _mapHAlign($hAlign) {
switch ($hAlign) {
case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL:
case PHPExcel_Style_Alignment::HORIZONTAL_LEFT: return 'left';
case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT: return 'right';
case PHPExcel_Style_Alignment::HORIZONTAL_CENTER: return 'center';
case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY: return 'justify';
default: return ' baseline';
}
}
/**
* Map border style
*/
private function _mapBorderStyle($borderStyle) {
switch ($borderStyle) {
case PHPExcel_Style_Border::BORDER_NONE: return '0px';
case PHPExcel_Style_Border::BORDER_DASHED: return '1px dashed';
case PHPExcel_Style_Border::BORDER_DOTTED: return '1px dotted';
case PHPExcel_Style_Border::BORDER_THICK: return '2px solid';
default: return '1px solid'; // map others to thin
}
}
/**
* Get sheet index
*
* @return int
*/
public function getSheetIndex() {
return $this->_sheetIndex;
}
/**
* Set sheet index
*
* @param int $pValue Sheet index
*/
public function setSheetIndex($pValue = 0) {
$this->_sheetIndex = $pValue;
}
/**
* Write all sheets (resets sheetIndex to NULL)
*/
public function writeAllSheets() {
$this->_sheetIndex = null;
}
/**
* Generate HTML header
*
* @return string
* @throws Exception
*/
public function generateHTMLHeader() {
// PHPExcel object known?
if (is_null($this->_phpExcel)) {
throw new Exception('Internal PHPExcel object not set to an instance of an object.');
}
// Construct HTML
$html = '';
$html .= '<!-- Generated by PHPExcel - http://www.phpexcel.net -->'.PHP_EOL;
$html .= '<html>'.PHP_EOL;
$html .= ' <head>'.PHP_EOL;
$html .= ' <title>' . $this->_phpExcel->getProperties()->getTitle() . '</title>'.PHP_EOL;
$html .= " <script language=javascript type='text/javascript'>".PHP_EOL;
$html .= " var activeWorksheet = 'WS0';".PHP_EOL;
$html .= ''.PHP_EOL;
$html .= ' function hideWorksheet(id) {'.PHP_EOL;
$html .= ' //safe function to hide an element with a specified id'.PHP_EOL;
$html .= ' if (document.getElementById) { // DOM3 = IE5, NS6'.PHP_EOL;
$html .= " document.getElementById(id).style.display = 'none';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' else {'.PHP_EOL;
$html .= ' if (document.layers) { // Netscape 4'.PHP_EOL;
$html .= " document.id.display = 'none';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' else { // IE 4'.PHP_EOL;
$html .= " document.all.id.style.display = 'none';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ''.PHP_EOL;
$html .= ' function showWorksheet(id) {'.PHP_EOL;
$html .= ' hideWorksheet(activeWorksheet)'.PHP_EOL;
$html .= ' //safe function to show an element with a specified id'.PHP_EOL;
$html .= ' if (document.getElementById) { // DOM3 = IE5, NS6'.PHP_EOL;
$html .= " document.getElementById(id).style.display = 'block';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' else {'.PHP_EOL;
$html .= ' if (document.layers) { // Netscape 4'.PHP_EOL;
$html .= " document.id.display = 'block';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' else { // IE 4'.PHP_EOL;
$html .= " document.all.id.style.display = 'block';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' activeWorksheet = id;'.PHP_EOL;
$html .= ' var editingCellDest = document.getElementById("editingID");'.PHP_EOL;
$html .= ' var editingValueDest = document.getElementById("editingValue");'.PHP_EOL;
$html .= " editingCellDest.value = '';".PHP_EOL;
$html .= " editingValueDest.value = '';".PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ''.PHP_EOL;
$html .= ' function IsNumeric(cellValue) { // check for valid numeric strings'.PHP_EOL;
$html .= ' if (!/\D/.test(cellValue)) return true; // If an integer number'.PHP_EOL;
$html .= ' else if (/^\d+\.\d+$/.test(cellValue)) return true; // If a floating point number having an integer on either side of the dot(.)'.PHP_EOL;
$html .= ' else return false;'.PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ''.PHP_EOL;
$html .= ' function editCell(cellID) {'.PHP_EOL;
$html .= ' var editingWorksheet = document.getElementById("worksheetID");'.PHP_EOL;
$html .= ' editingWorksheet.value = activeWorksheet;'.PHP_EOL;
$html .= ' var editingCellDest = document.getElementById("editingID");'.PHP_EOL;
$html .= ' var editingValueDest = document.getElementById("editingValue");'.PHP_EOL;
$html .= ' var dataSourceName = "WS"+cellID+"value";'.PHP_EOL;
$html .= ' var dataSource = document.getElementById(dataSourceName);'.PHP_EOL;
$html .= ' editingCellDest.value = cellID.slice(cellID.indexOf("-")+1);'.PHP_EOL;
$html .= ' editingValueDest.value = dataSource.value;'.PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' function changeCell() {'.PHP_EOL;
$html .= ' var editingCell = document.getElementById("editingID");'.PHP_EOL;
$html .= ' var editingValue = document.getElementById("editingValue");'.PHP_EOL;
$html .= ' var valueDIVName = activeWorksheet+"-"+editingCell.value+"value";'.PHP_EOL;
$html .= ' var valueDIV = document.getElementById(valueDIVName);'.PHP_EOL;
$html .= ' valueDIV.value = editingValue.value;'.PHP_EOL;
$html .= ' var displayDIVName = activeWorksheet+"-"+editingCell.value+"display";'.PHP_EOL;
$html .= ' var displayDIV = document.getElementById(displayDIVName);'.PHP_EOL;
$html .= ' displayDIV.innerHTML = editingValue.value;'.PHP_EOL;
$html .= ' var cellEditor = document.getElementById("cellEditor");'.PHP_EOL;
$html .= ' cellEditor.submit();'.PHP_EOL;
$html .= ' }'.PHP_EOL;
$html .= ' </script>'.PHP_EOL;
$html .= ' </head>'.PHP_EOL;;
$html .= ''.PHP_EOL;
$html .= ' <body>'.PHP_EOL;
// Return
return $html;
}
/**
* Generate HTML header
*
* @return string
* @throws Exception
*/
public function generateEditorData() {
// PHPExcel object known?
if (is_null($this->_phpExcel)) {
throw new Exception('Internal PHPExcel object not set to an instance of an object.');
}
// Construct HTML
$html = '<form id="cellEditor" method="POST" action="refreshWorkbookHTMLEditable.php">';
$html .= ' <input id="worksheetID" name="worksheetID" type="hidden" />'.PHP_EOL;
$html .= ' <b>Cell ID:</b> <input id="editingID" name="editingID" type="text" size="6" readonly />'.PHP_EOL;
$html .= ' <b>Cell Value:</b> <input id="editingValue" name="editingValue" type="text" size="40" onChange="javascript:changeCell()"/>'.PHP_EOL;
$html .= '</form>';
// Return
return $html;
}
/**
* Generate sheet data
*
* @return string
* @throws Exception
*/
public function generateSheetData() {
// PHPExcel object known?
if (is_null($this->_phpExcel)) {
throw new Exception('Internal PHPExcel object not set to an instance of an object.');
}
// Fetch sheets
$sheets = array();
if (is_null($this->_sheetIndex)) {
$sheets = $this->_phpExcel->getAllSheets();
} else {
$sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
}
// Construct HTML
$html = '';
$WSID = 0;
$sheetNames = array();
// Loop all sheets
foreach ($sheets as $sheet) {
// Calculate hash code
$hashCode = $sheet->getHashCode();
$sheetName = $sheet->getTitle();
$sheetNames[] = $sheetName;
$html .= '<div id="WS'.$WSID.'">'.PHP_EOL;
// Get cell collection
$cellCollection = $sheet->getCellCollection();
// Write table header
$html .= $this->_generateTableHeader($hashCode);
// Get worksheet dimension
$dimension = explode(':', $sheet->calculateWorksheetDimension());
$dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
$dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
$dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
$dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
$html .= '<tr><th> </th>';
$i = 'A';
do {
$html .= '<th>'.$i.'</th>';
++$i;
} while ($i != PHPExcel_Cell::stringFromColumnIndex($dimension[1][0]+1));
// Loop trough cells
$rowData = null;
for ($row = $dimension[0][1]; $row <= $dimension[1][1]; ++$row) {
// Start a new row
$rowData = array();
// Loop trough columns
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; ++$column) {
// Cell exists?
if ($sheet->cellExistsByColumnAndRow($column, $row)) {
$rowData[$column] = $sheet->getCellByColumnAndRow($column, $row);
} else {
$rowData[$column] = '';
}
}
// Write row
$html .= $this->_generateRow($sheet, $rowData, $row - 1, $WSID);
}
// Write table footer
$html .= $this->_generateTableFooter();
$html .= '</div>'.PHP_EOL;
++$WSID;
}
$html .= '<hr />'.PHP_EOL;
$html .= '<table border="1" cellpadding="2" cellspacing="2"><tr>'.PHP_EOL;
$i = 0;
foreach($sheetNames as $sheetName) {
$html .= '<td id="WS'.$i.'selector" onClick="javascript:showWorksheet('."'WS".$i++."'".');">'.$sheetName.'</td>'.PHP_EOL;
}
$html .= '</tr></table>'.PHP_EOL;
$html .= "<script language=javascript type='text/javascript'>".PHP_EOL;
$i = 1;
while ($i < $WSID) {
$html .= " hideWorksheet('WS".$i++."')".PHP_EOL;
}
$html .= "</script>".PHP_EOL;
// Return
return $html;
}
/**
* Generate image tag in cell
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param string $coordinates Cell coordinates
* @retrun string
* @throws Exception
*/
private function _writeImageTagInCell(PHPExcel_Worksheet $pSheet, $coordinates) {
// Construct HTML
$html = '';
// Write images
foreach ($pSheet->getDrawingCollection() as $drawing) {
if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
if ($drawing->getCoordinates() == $coordinates) {
$filename = $drawing->getPath();
// Strip off eventual '.'
if (substr($filename, 0, 1) == '.') {
$filename = substr($filename, 1);
}
// Prepend images root
$filename = $this->getImagesRoot() . $filename;
// Strip off eventual '.'
if (substr($filename, 0, 1) == '.' && substr($filename, 0, 2) != './') {
$filename = substr($filename, 1);
}
$html .= PHP_EOL;
$html .= ' <img style="position: relative; left: ' . $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . $filename . '" border="0">'.PHP_EOL;
}
}
}
// Return
return $html;
}
/**
* Generate CSS styles
*
* @param boolean $generateSurroundingHTML Generate surrounding HTML tags? (<style> and </style>)
* @return string
* @throws Exception
*/
public function generateStyles($generateSurroundingHTML = true) {
// PHPExcel object known?
if (is_null($this->_phpExcel)) {
throw new Exception('Internal PHPExcel object not set to an instance of an object.');
}
// Construct HTML
$html = '';
// Start styles
if ($generateSurroundingHTML) {
$html .= ' <style>'.PHP_EOL;
$html .= ' <!--'.PHP_EOL;
$html .= ' html {'.PHP_EOL;
$html .= ' font-family: Calibri, Arial, Helvetica, Sans Serif;'.PHP_EOL;
$html .= ' font-size: 10pt;'.PHP_EOL;
$html .= ' background-color: white;'.PHP_EOL;
$html .= ' }'.PHP_EOL;
}
// Write styles per sheet
foreach ($this->_phpExcel->getAllSheets() as $sheet) {
// Calculate hash code
$hashCode = $sheet->getHashCode();
// Write styles
$html .= ' table.sheet' . $hashCode . ', table.sheet' . $hashCode . ' td {'.PHP_EOL;
if ($sheet->getShowGridlines()) {
$html .= ' border: 1px dotted black;'.PHP_EOL;
}
$html .= ' page-break-after: always;'.PHP_EOL;
$html .= ' }'.PHP_EOL;
// Default column width
$columnDimension = $sheet->getDefaultColumnDimension();
$html .= ' table.sheet' . $hashCode . ' td {'.PHP_EOL;
$html .= ' width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;'.PHP_EOL;
if ($columnDimension->getVisible() === false) {
$html .= ' display: none;'.PHP_EOL;
$html .= ' visibility: hidden;'.PHP_EOL;
}
$html .= ' }'.PHP_EOL;
// Calculate column widths
$sheet->calculateColumnWidths();
foreach ($sheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
$html .= ' table.sheet' . $hashCode . ' td.column' . $column . ' {'.PHP_EOL;
$html .= ' width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;'.PHP_EOL;
if ($columnDimension->getVisible() === false) {
$html .= ' display: none;'.PHP_EOL;
$html .= ' visibility: hidden;'.PHP_EOL;
}
$html .= ' }'.PHP_EOL;
}
// Default row height
$rowDimension = $sheet->getDefaultRowDimension();
$html .= ' table.sheet' . $hashCode . ' tr {'.PHP_EOL;
// height is disproportionately large
$px_height = round( PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12 );
$html .= ' height: ' . $px_height . 'px;'.PHP_EOL;
if ($rowDimension->getVisible() === false) {
$html .= ' display: none;'.PHP_EOL;
$html .= ' visibility: hidden;'.PHP_EOL;
}
$html .= ' }'.PHP_EOL;
// Calculate row heights
foreach ($sheet->getRowDimensions() as $rowDimension) {
$html .= ' table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1) . ' {'.PHP_EOL;
// height is disproportionately large
$px_height = round( PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12 );
$html .= ' height: ' . $px_height . 'px;'.PHP_EOL;
if ($rowDimension->getVisible() === false) {
$html .= ' display: none;'.PHP_EOL;
$html .= ' visibility: hidden;'.PHP_EOL;
}
$html .= ' }'.PHP_EOL;
}
// Calculate cell style hashes
$cellStyleHashes = new PHPExcel_HashTable();
$cellStyleHashes->addFromSource( $sheet->getStyles() );
for ($i = 0; $i < $cellStyleHashes->count(); ++$i) {
$html .= $this->_createCSSStyle( $cellStyleHashes->getByIndex($i) );
}
}
// End styles
if ($generateSurroundingHTML) {
$html .= ' -->'.PHP_EOL;
$html .= ' </style>'.PHP_EOL;
}
// Return
return $html;
}
/**
* Create CSS style
*
* @param PHPExcel_Style $pStyle PHPExcel_Style
* @return string
*/
private function _createCSSStyle(PHPExcel_Style $pStyle) {
// Construct HTML
$html = '';
// Create CSS
$html .= ' .style' . $pStyle->getHashCode() . ' {'.PHP_EOL;
$html .= $this->_createCSSStyleAlignment($pStyle->getAlignment());
$html .= $this->_createCSSStyleFont($pStyle->getFont());
$html .= $this->_createCSSStyleBorders($pStyle->getBorders());
$html .= $this->_createCSSStyleFill($pStyle->getFill());
$html .= ' }'.PHP_EOL;
// Return
return $html;
}
/**
* Create CSS style (PHPExcel_Style_Alignment)
*
* @param PHPExcel_Style_Alignment $pStyle PHPExcel_Style_Alignment
* @return string
*/
private function _createCSSStyleAlignment(PHPExcel_Style_Alignment $pStyle) {
// Construct HTML
$html = '';
// Create CSS
$html .= ' vertical-align: ' . $this->_mapVAlign($pStyle->getVertical()) . ';'.PHP_EOL;
$html .= ' text-align: ' . $this->_mapHAlign($pStyle->getHorizontal()) . ';'.PHP_EOL;
// Return
return $html;
}
/**
* Create CSS style (PHPExcel_Style_Font)
*
* @param PHPExcel_Style_Font $pStyle PHPExcel_Style_Font
* @return string
*/
private function _createCSSStyleFont(PHPExcel_Style_Font $pStyle) {
// Construct HTML
$html = '';
// Create CSS
if ($pStyle->getBold()) {
$html .= ' font-weight: bold;'.PHP_EOL;
}
if ($pStyle->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE && $pStyle->getStriketrough()) {
$html .= ' text-decoration: underline line-through;'.PHP_EOL;
} else if ($pStyle->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE) {
$html .= ' text-decoration: underline;'.PHP_EOL;
} else if ($pStyle->getStriketrough()) {
$html .= ' text-decoration: line-through;'.PHP_EOL;
}
if ($pStyle->getItalic()) {
$html .= ' font-style: italic;'.PHP_EOL;
}
$html .= ' color: ' . '#' . $pStyle->getColor()->getRGB() . ';'.PHP_EOL;
$html .= ' font-family: ' . $pStyle->getName() . ';'.PHP_EOL;
$html .= ' font-size: ' . $pStyle->getSize() . 'pt;'.PHP_EOL;
// Return
return $html;
}
/**
* Create CSS style (PHPExcel_Style_Borders)
*
* @param PHPExcel_Style_Borders $pStyle PHPExcel_Style_Borders
* @return string
*/
private function _createCSSStyleBorders(PHPExcel_Style_Borders $pStyle) {
// Construct HTML
$html = '';
// Create CSS
$html .= ' border-bottom: ' . $this->_createCSSStyleBorder($pStyle->getBottom()) . ';'.PHP_EOL;
$html .= ' border-top: ' . $this->_createCSSStyleBorder($pStyle->getTop()) . ';'.PHP_EOL;
$html .= ' border-left: ' . $this->_createCSSStyleBorder($pStyle->getLeft()) . ';'.PHP_EOL;
$html .= ' border-right: ' . $this->_createCSSStyleBorder($pStyle->getRight()) . ';'.PHP_EOL;
// Return
return $html;
}
/**
* Create CSS style (PHPExcel_Style_Border)
*
* @param PHPExcel_Style_Border $pStyle PHPExcel_Style_Border
* @return string
*/
private function _createCSSStyleBorder(PHPExcel_Style_Border $pStyle) {
// Construct HTML
$html = '';
// Create CSS
$html .= $this->_mapBorderStyle($pStyle->getBorderStyle()) . ' #' . $pStyle->getColor()->getRGB();
// Return
return $html;
}
/**
* Create CSS style (PHPExcel_Style_Fill)
*
* @param PHPExcel_Style_Fill $pStyle PHPExcel_Style_Fill
* @return string
*/
private function _createCSSStyleFill(PHPExcel_Style_Fill $pStyle) {
// Construct HTML
$html = '';
// Create CSS
$html .= ' background-color: ' . '#' . $pStyle->getStartColor()->getRGB() . ';'.PHP_EOL;
// Return
return $html;
}
/**
* Generate HTML footer
*/
public function generateHTMLFooter() {
// Construct HTML
$html = '';
$html .= ' </body>'.PHP_EOL;
$html .= '</html>'.PHP_EOL;
// Return
return $html;
}
/**
* Generate table header
*
* @param string $pIdentifier Identifier for the table
* @return string
* @throws Exception
*/
private function _generateTableHeader($pIdentifier = '') {
// Construct HTML
$html = '';
$html .= ' <table border="1" cellpadding="1" cellspacing="0" class="sheet' . $pIdentifier . '">'.PHP_EOL;
// Return
return $html;
}
/**
* Generate table footer
*
* @throws Exception
*/
private function _generateTableFooter() {
// Construct HTML
$html = '';
$html .= ' </table>'.PHP_EOL;
// Return
return $html;
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0, $WSID = 0) {
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Write row start
$html .= ' <tr class="row' . $pRow . '">'.PHP_EOL;
$cellRow = $pRow+1;
$html .= '<th>'.$cellRow.'</th>'.PHP_EOL;
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cellValue = $cellUnformattedValue = '';
$cssClass = 'column' . $colNum;
$colSpan = 1;
$rowSpan = 1;
$writeCell = true; // Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' .
str_replace(PHP_EOL, '',
$this->_createCSSStyleFont($element->getFont())
) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
// Decode UTF8 data
$cellText = $element->getText();
if (PHPExcel_Shared_String::IsUTF8($cellText)) {
$cellData .= utf8_decode($cellText);
}
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellUnformattedValue = $cell->getCalculatedValue();
$cellData = PHPExcel_Style_NumberFormat::toFormattedString(
$cellUnformattedValue,
$pSheet->getstyle( $cell->getCoordinate() )->getNumberFormat()->getFormatCode()
);
$cellValue = $cell->getValue();
} else {
$cellUnformattedValue = $cell->getCalculatedValue();
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString(
$cellUnformattedValue,
$pSheet->getstyle( $cell->getCoordinate() )->getNumberFormat()->getFormatCode()
);
$cellValue = $cell->getValue();
}
// Decode UTF8 data
if (PHPExcel_Shared_String::IsUTF8($cellData)) {
$cellData = utf8_decode($cellData);
$cellValue = utf8_decode($cellValue);
}
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode();
}
} else {
$cell = new PHPExcel_Cell(
PHPExcel_Cell::stringFromColumnIndex($colNum),
($pRow + 1),
'',
null,
null
);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
if ($cellValue == '') {
$cellValue = $cellData;
}
$align='left';
if ((is_numeric($cellValue)) || (is_numeric($cellUnformattedValue))) {
$align='right';
} elseif ((is_bool($cellValue)) || (is_bool($cellUnformattedValue))) {
$align='center';
}
$cellID = $cell->getCoordinate();
// Column start
$html .= ' <td';
$html .= ' class="' . $cssClass . '"';
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= ' class="'.$cssClass.'"';
$html .= ' style="text-align: '.$align.'" onclick=javascript:editCell("'.$WSID.'-'.$cellID.'")>';
$html .= '<span id="WS'.$WSID.'-'.$cellID.'display" type="text" size="'.strlen($cellData).'">';
// Image?
// $html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
$html .= $cellData;
$html .= '</span><input name="WS'.$WSID.'-'.$cellID.'value" id="WS'.$WSID.'-'.$cellID.'value" type="hidden" value="'.$cellValue.'" />';
// Column end
$html .= '</td>'.PHP_EOL;
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>'.PHP_EOL;
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* Get Pre-Calculate Formulas
*
* @return boolean
*/
public function getPreCalculateFormulas() {
return $this->_preCalculateFormulas;
}
/**
* Set Pre-Calculate Formulas
*
* @param boolean $pValue Pre-Calculate Formulas?
*/
public function setPreCalculateFormulas($pValue = true) {
$this->_preCalculateFormulas = $pValue;
}
/**
* Get images root
*
* @return string
*/
public function getImagesRoot() {
return $this->_imagesRoot;
}
/**
* Set images root
*
* @param string $pValue
*/
public function setImagesRoot($pValue = '.') {
$this->_imagesRoot = $pValue;
}
}
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: javascript spreadsheet implode to MYSQL
hii mark,
thanks alot for your help,
i completed the rest part left by you, saving the modified excel sheet and replacing it with the original one.
but i've a doubt, whether we can render the excel sheet in an ajax based editable GRID or not?
that will be more easy and elegant in look.
i tried phpgrid trial version, which we can only output mysql query results in that grid which are editable.
i downloaded phpmydatagrid 2007, but hadent tested it.
any suggestions from ur side?
many many thanks again,
pavan.p
thanks alot for your help,
i completed the rest part left by you, saving the modified excel sheet and replacing it with the original one.
but i've a doubt, whether we can render the excel sheet in an ajax based editable GRID or not?
that will be more easy and elegant in look.
i tried phpgrid trial version, which we can only output mysql query results in that grid which are editable.
i downloaded phpmydatagrid 2007, but hadent tested it.
any suggestions from ur side?
many many thanks again,
pavan.p