/**
* Reads the data from file in the DB and generates a wpDataTable
*/
public function readFileData($table_data)
{
$columnTypes = array();
if (!empty($table_data['file'])) {
$xls_url = urldecode($table_data['file']);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $xls_url);
} else {
$xls_url = str_replace(site_url(), ABSPATH, $xls_url);
}
} else {
return _('Empty file', 'wpdatatables');
}
for ($i = 0; $i < count($table_data['columns']); $i++) {
if ($table_data['columns'][$i]['orig_header'] == '%%NEW_COLUMN%%') {
$table_data['columns'][$i]['orig_header'] = 'column' . $i;
}
$columnTypes[$table_data['columns'][$i]['orig_header']] = $table_data['columns'][$i]['type'];
}
$this->_id = $this->generateManualTable($table_data);
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
return _('File format not supported!', 'wpdatatables');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$insertArray = array();
// Insert statement default beginning
$insert_statement_beginning = "INSERT INTO " . $this->_name . " (" . implode(', ', array_values($this->_column_headers)) . ") ";
$insert_blocks = array();
for ($row = 2; $row <= $highestRow; ++$row) {
// Set all cells in the row to their defaults
foreach ($table_data['columns'] as $column) {
$insertArray[$this->_column_headers[$column['orig_header']]] = "'" . esc_sql($column['default_value']) . "'";
}
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
if (!in_array($dataColumnHeading, array_keys($this->_column_headers))) {
continue;
}
if ($columnTypes[$dataColumnHeading] != 'date') {
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($dataRow[$row][$dataColumnIndex]) . "'";
} else {
if ($objReader instanceof PHPExcel_Reader_CSV) {
$date = strtotime(str_replace('/', '-', $dataRow[$row][$dataColumnIndex]));
} else {
$date = esc_sql(PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]));
}
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
}
}
}
$insert_blocks[] = '(' . implode(', ', $insertArray) . ')';
if ($row % 100 == 0) {
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
$insert_blocks = array();
}
}
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
}
public function excelBasedConstruct($xls_url, $wdtParameters = array())
{
ini_set("memory_limit", "2048M");
if (!$xls_url) {
throw new WDTException('Excel file not found!');
}
if (!file_exists($xls_url)) {
throw new WDTException('Provided file ' . stripcslashes($xls_url) . ' does not exist!');
}
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
throw new WDTException('File format not supported!');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if (WDT_DETECT_DATES_IN_EXCEL) {
$cellID = $dataColumnIndex . $row;
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
$namedDataArray = apply_filters('wpdatatables_filter_excel_array', $namedDataArray, $this->getWpId(), $xls_url);
return $this->arrayBasedConstruct($namedDataArray, $wdtParameters);
}
