'validated_path' => $validatedPath,
),
'human_error' => 'There was a problem validating your file. Please try again.',
));
}
$header = isset($_REQUEST['has_header']) ? boolstr($_REQUEST['has_header']) : true;
$sheets = $_REQUEST['sheets'];
$sheets_arr = explode(',', $sheets);
if (sizeof($sheets_arr) > 1) {
# PHPExcel only wants array for many sheets
$sheets = $sheets_arr;
}
try {
returnAjax(array(
'status' => true,
'data' => excelToArray($validatedPath, $header, $sheets),
'path' => array(
'requested_path' => $_REQUEST['path'],
'validated_path' => $validatedPath,
),
));
} catch (Exception $e) {
returnAjax(array(
'status' => false,
'error' => $e->getMessage(),
));
}
break;
default:
returnAjax(array(
'status' => false,
include 'PHPExcel/IOFactory.php';
//读取控制
error_reporting(E_ALL);
function excelToArray($file)
{
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
//读取文件
$objWorksheet = $objPHPExcel->getActiveSheet(0);
//读取excel文件中的第一个工作表
$highestRow = $objWorksheet->getHighestRow();
//计算总行数
$highestColumn = $objWorksheet->getHighestColumn();
//取得列数中最大的字母。如(J)
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
//通过字母计算总列数
$excelData = array();
//存放读取的数据
for ($row = 2; $row <= $highestRow; ++$row) {
//从第二行开始读取数据
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
//读取每行中的各列
//把读取的数据放入数组中
$excelData[$row - 2][] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
print_r(excelToArray('csat.xls'));
//iconv('utf-8','gb2312', $val)//出现乱码解决方法
function MappingArray()
{
$org_mappings = array();
$mapping = excelToArray('sites/all/modules/custom/custom_report/events/organization_mapping.xls');
$agency_org_mappings = array();
foreach ($mapping as $key => $organization) {
$agency_org_mappings[$organization['assoc']['Agency']] = trim($organization['assoc']['Organization']);
}
$distinct_org_names = GetUniqueValues($agency_org_mappings);
foreach ($distinct_org_names as $agency => $org) {
$agencies = array_keys($agency_org_mappings, trim($org));
$org_mappings[$org] = $agencies;
}
/*$org_mappings=array(
"D.O. State Program" => array("DOS","Department of State"),
"DOC Program" => array("doc","Department of Commerce"),
"DoD Program" => array("dod","Department of Defense"),
"EEOC" => array("EEOC","Equal Employment Opportunity Commission"),
"EPA" => array("Environmental Protection Agency"),
"Export-Import Bank Program" => array("Export-Import"),
"FCC" => array("FCC","Federal Communications Commission"),
"GSA Program" => array("GSA","General Services Administration"),
"IRS Program" => array("Internal Revenue Service"),
"OPIC" => array("Overseas Private Investment Corporation"),
"SBA Program" => array("sba","Small Business Administration","Career Works","MA District Office","SCORE"),
"SBDC" => array("SBDC","Small Business Development Center","Small Business Center","Small Busniness Development Center"),
"Treasury Program" => array("Treasury"),
"USDA Program" => array("USDA","of Agriculture"),
"ED" => array("of Education"),
"DOE" => array("of Energy","Dept. of Energy"),
"HHS" => array("Health and Human Services"),
"DHS" => array("of Homeland Security"),
"HUD" => array("of Housing and Urban Development"),
"DOJ" => array("of Justice"),
"DOL" => array("of Labor"),
"DOI" => array("of the Interior"),
"U.S. Department of Transportation (DOT)" => array("DOT","of Transportation"),
"USTDA" => array("USTDA","United States Trade and Development Agency"),
"SSA" => array("SSA","Social Security Administration"),
"VA Program" => array("Veterans Affairs","VA Program"),
"WH" => array("White House"),
"ITA" => array("International Trade Administration"),
"NSF" => array("NSF","National Science Foundation"),
);*/
$org_exact_mappings = array("IRS Program" => array("IRS", "EDD/IRS"), "ED" => array("ED"), "DOE" => array("DOE"), "OPIC" => array("OPIC"), "EPA" => array("EPA"), "VA Program" => array("VA"), "ITA" => array("ITA"), "WH" => array("WH"));
return array($org_mappings, $org_exact_mappings);
}
if (strpos($target, '?') !== false) {
$target = explode('?', $target);
$target = $target[0];
@ob_end_clean();
while (@ob_end_clean()) {
}
header('Location: /export-portal?' . $target);
exit;
}
// Check if the target [url] is referenced on the export-dashboard-mappings.xls spreadsheet
if (function_exists('excelToArray') == false) {
// Ensure we have declared our helper functions for use with the PHPExcel library
include_once 'sites/all/libraries/PHPExcelHelper/phpexcel-helper-functions.php';
}
$mappingsFilePath = variable_get('exporter_dashboard_mappings_filepath', drupal_get_path('module', 'exporter_dashboard') . '/export-dashboard-mappings.xls');
$mappings = excelToArray($mappingsFilePath);
// Load all mappings for the Export Dashboard - load this information into an array. Note: excelToArray() is defined in phpexcel-helper-functions.php
foreach ($mappings as $mapping) {
// Foreach row in this spreadsheet...
$linkOnThisRow = trim($mapping['assoc']['dashboard_link_url'], '/ ');
$linkOnThisRow = str_replace('http://www.export.gov/', '', $linkOnThisRow);
$linkOnThisRow = str_replace('http://export.gov/', '', $linkOnThisRow);
$linkOnThisRow = str_replace('www.export.gov/', '', $linkOnThisRow);
$linkOnThisRow = str_replace('export.gov/', '', $linkOnThisRow);
$linkOnThisRow = str_replace('/index.asp', '', $linkOnThisRow);
$linkOnThisRow = trim($linkOnThisRow, '/ ');
if (trim($target, '/ ') === $linkOnThisRow) {
// If this line is hit, then that means the target (url) was found in the spreadhseet. We shall redirect the user
$redirectLink = '/export/' . $mapping['assoc']['dashboard_cateory_title'] . '/';
if (strval($mapping['assoc']['dashboard_link_title']) === '' && strval($mapping['assoc']['topic_post']) !== '') {
$redirectLink .= str_replace('/', '-', $mapping['assoc']['topic_post']);