Loading data from Excel file - date problem

Suite and Optimus Suite 5
#1

I followed the instructions in the documentation, and everything works fine.
( docs.dhtmlx.com/doku.php?id=dhtm … cel_file&s[]=excel&s[]=xls)
Except that for the cells with date format.
example: the date 12/27/2010 turns into 40539
Do you have any suggestion to solve this problem ?

[code]<?php
//files from libExcel package
require_once(‘dhtmlxConnector/lib2/PHPExcel.php’);
require_once(‘dhtmlxConnector/lib2/PHPExcel/IOFactory.php’);

//connectors
require(“dhtmlxConnector/codebase/db_excel.php”);
require(“dhtmlxConnector/codebase/grid_connector.php”);

$grid = new GridConnector(“test.xls”, “ExcelDBDataWrapper”);
$config=new GridConfiguration();
//array of cells, with labels for grid’s header
$config->setHeader($grid->sql->excel_data(array(“A1”,“B1”,“C1”,“D1”,“E1”,“F1”,“G1”,“H1”,“I1”,“J1”,“K1”,“L1”,“M1”,“N1”,“O1”,“P1”,“Q1”)));
$grid->set_config($config);
$grid->render_table(“A2”, “A”, “A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q”);
?>[/code]

#2

Hi,
problem is confirmed.
Please, update db_excel.php in connector package from attachment.
After that date in excel will be converted to timestamp which you may format in beforeRender event like here:

$grid = new GridConnector("test.xlsx", "ExcelDBDataWrapper");
	$config=new GridConfiguration();
	//array of cells, with labels for grid's header
	$config->setHeader($grid->sql->excel_data(array("A1","B1","C1","D1","E1","F1","G1","H1","I1","J1","K1","L1","M1","N1","O1","P1","Q1")));
	$grid->set_config($config);
	$grid->event->attach("beforeRender", "format");
	$grid->render_table("A2", "A", "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q");
	
	function format($row) {
		$row->set_value("B", date("Y-m-d", $row->get_value("B")));
	}

db_excel.zip (1.72 KB)

#3

Thanks for the quick response.
I did what you suggested, and it works fine.

The problem that remains is that I do not know which column is a date field. An authenticated user sends a file xls, xls and I insert data in a grid.

Before sending the xls file, I’ll fill out a form to find out which column is a date field…

greetings

#4

Hi,
you may customize db_excel.php. It contains the follow code:

if (PHPExcel_Shared_Date::isDateTime($cell)) {
	$r[PHPExcel_Cell::stringFromColumnIndex($col)] = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());
}  else if ($cell->getDataType() == 'f') {
	$r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue();
} else {
	$r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue();
}
if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') {
	$emptyNum++;
}

It takes value from cell.
So you may do something like here:

$r[PHPExcel_Cell::stringFromColumnIndex($col)] = date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));
#5

I always use the date format ‘d-m-Y’. This is why I preferred to change db_excel.php
the line 73 in this manner

$r[PHPExcel_Cell::stringFromColumnIndex($col)] = date("d-m-Y",PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));

Now everything works as I wanted.
Thanks again for your help
Regards

#6

Radyno, now I read your post … we had the same idea!

Thanks also to you for the help