function computeSourceCells($formula, $dbTable, $ccid, $crid, $cpid, &$aPSCs, $sciri, &$dbtUpdate)
{
function getCells($cid, $ri, $tid, &$liveDBTable, &$sCs, &$tp, &$dbtUpdate, $sciri, $dbTable, $ccid)
{
$cid = $cid == 'index' ? $ccid : $cid;
if ($cid[0] == '/') {
foreach ($liveDBTable['cells'][$ri] as $ci => $cell) {
if (preg_match($cid, $ci)) {
array_push($sCs, $ci . "," . ($ri != 'tHR' ? $liveDBTable['cells'][$ri][$liveDBTable['priCol']]['innerHTML'] : "") . ($tid != $dbTable ? "," . $tid : ''));
$tp[$ri][$ci]['f']['ee'][] = $tid == $dbTable ? $sciri : $sciri . "," . $dbTable;
$dbtUpdate['tables'][$tid]['cells'][$ri][$ci]['f']['ee'] = $tp[$ri][$ci]['f']['ee'] = true_array_unique($tp[$ri][$ci]['f']['ee']);
}
}
} elseif ($cid[0] == '*') {
array_push($sCs, "index," . ($ri != 'tHR' ? $liveDBTable['cells'][$ri][$liveDBTable['priCol']]['innerHTML'] : "") . "," . ($tid != $dbTable ? "," . $tid : ''));
} elseif (count($cids = split('~', $cid)) > 1) {
$rangeE = FALSE;
foreach ($liveDBTable['cells']['tHR'] as $ci => $cell) {
if (($ci == $cids[0] or $rangeS) and !$rangeE) {
$rangeS = true;
if ($ci == $cids[1]) {
$rangeE = true;
}
$rcells[] = $ci;
}
}
if ($rangeE) {
foreach ($rcells as $rci => $ci) {
array_push($sCs, $ci . "," . ($ri != 'tHR' ? $liveDBTable['cells'][$ri][$liveDBTable['priCol']]['innerHTML'] : "") . ($tid != $dbTable ? "," . $tid : ''));
$tp[$ri][$ci]['f']['ee'][] = $tid == $dbTable ? $sciri : $sciri . "," . $dbTable;
$dbtUpdate['tables'][$tid]['cells'][$ri][$ci]['f']['ee'] = $tp[$ri][$ci]['f']['ee'] = true_array_unique($tp[$ri][$ci]['f']['ee']);
}
}
} elseif ($cid != '') {
if (isset($liveDBTable['cells'][$ri][$cid])) {
array_push($sCs, $cid . "," . ($ri != 'tHR' ? $liveDBTable['cells'][$ri][$liveDBTable['priCol']]['innerHTML'] : "") . ($tid != $dbTable ? "," . $tid : ''));
$tp[$ri][$cid]['f']['ee'][] = $tid == $dbTable ? $sciri : $sciri . "," . $dbTable;
$dbtUpdate['tables'][$tid]['cells'][$ri][$cid]['f']['ee'] = $tp[$ri][$cid]['f']['ee'] = true_array_unique($tp[$ri][$cid]['f']['ee']);
}
}
}
$fx = $formula;
$sCs = array();
$matches = array();
preg_match_all('/[0-9a-zA-Z_~]+\\([0-9a-zA-Z_~*]*\\)\\([0-9a-zA-Z_]*\\)/i', $formula, $matches);
foreach ($matches[0] as $key => &$match) {
$ml = strlen($match);
$rstart = strpos($match, '(', 0);
$rend = strpos($match, ')', 0);
$rid = substr($match, $rstart + 1, $rend - $rstart - 1);
$tstart = strpos($match, '(', $rstart + 1);
$tid = substr($match, $tstart + 1, $ml - $tstart - 2);
if ($_SESSION['tables'][$tid]) {
$liveDBTable = getLiveTable($tid);
$tp = getTableFromFile($tid);
if ($ca = $aPSCs[$tid]) {
foreach ($ca as $i => $c) {
$c[1] = $c[1] ? rowWithPid($liveDBTable, $c[1]) : 'tHR';
foreach ($tp[$c[1]][$c[0]]['f']['ee'] as $e => $ee) {
if ($ee == $sciri or $ee == $sciri . "," . $dbTable) {
unset($tp[$c[1]][$c[0]]['f']['ee'][$e]);
$dbtUpdate['tables'][$tid]['cells'][$c[1]][$c[0]]['f']['ee'] = $tp[$c[1]][$c[0]]['f']['ee'] = true_array_unique($tp[$c[1]][$c[0]]['f']['ee']);
break;
}
}
}
unset($aPSCs[$tid]);
}
$rid = substr($match, $rstart + 1, $rend - $rstart - 1);
$cid = substr($match, 0, $rstart);
if ($rid[0] == '/') {
foreach ($liveDBTable['cells'] as $ri => $row) {
if (preg_match($rid, $row[$liveDBTable['priCol']])) {
getCells($cid, $ri, $tid, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
}
} elseif ($rid[0] == '*') {
getCells($cid, 'tHR', $tid, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
} elseif (count($rids = split('~', $rid)) > 1) {
$rangeE = FALSE;
foreach ($liveDBTable['cells'] as $ri => $row) {
if (($row[$liveDBTable['priCol']]['innerHTML'] == $rids[0] or $rangeS) and !$rangeE) {
$rangeS = true;
if ($row[$liveDBTable['priCol']]['innerHTML'] == $rids[1]) {
$rangeE = true;
}
$rrows[] = $ri;
}
}
if ($rangeE) {
foreach ($rrows as $kei => $ri) {
getCells($cid, $ri, $tid, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
}
} elseif ($rid != '') {
getCells($cid, rowWithPid($liveDBTable, $rid), $tid, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
} elseif ($rid == '') {
getCells($cid, rowWithPid($liveDBTable, $cpid), $tid, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
putTableInFile($tp, $tid);
closeLiveTable($liveDBTable);
}
$k = 0;
$formula = str_replace($match, 'var' . $k++, $formula);
}
$liveDBTable = getLiveTable($dbTable);
$tp = getTableFromFile($dbTable);
if ($ca = $aPSCs[$dbTable]) {
foreach ($ca as $i => $c) {
$c[1] = $c[1] ? rowWithPid($liveDBTable, $c[1]) : 'tHR';
foreach ($tp[$c[1]][$c[0]]['f']['ee'] as $e => $ee) {
if ($ee == $sciri or $ee == $sciri . "," . $dbTable) {
unset($tp[$c[1]][$c[0]]['f']['ee'][$e]);
$dbtUpdate['tables'][$dbTable]['cells'][$c[1]][$c[0]]['f']['ee'] = $tp[$c[1]][$c[0]]['f']['ee'] = true_array_unique($tp[$c[1]][$c[0]]['f']['ee']);
break;
}
}
}
unset($aPSCs[$dbTable]);
}
foreach ($liveDBTable['cells']['tHR'] as $colName => $column) {
$start = 0;
$start = strpos($formula, $colName, $start);
while ($start > -1) {
if (preg_match("/[^a-zA-Z0-9_]/", $formula[$start - 1]) or !$formula[$start - 1]) {
$pst = $start;
$start += strlen($colName);
if ($formula[$pst - 1] != ';' and ($formula[$start] != '=' or $formula[$start + 1] == '=') and (preg_match("/[^a-zA-Z0-9_]/", $formula[$start]) or !$formula[$start])) {
$cid = $colName;
$row = '';
unset($col2);
if ($formula[$start++] == '~') {
foreach ($liveDBTable['cells']['tHR'] as $col2 => $column2) {
if (strpos($formula, $col2, $start) == $start) {
$colName2 = $col2;
break;
}
}
}
if ($col2 and $colName2 or !($col2 or $colName2)) {
$cid = $colName2 ? $cid . '~' . $colName2 : $cid;
$j = $start;
unset($rid);
while ($formula[$start - 1] == '(' and $formula[$j] != '' and $formula[$j] != ')') {
$rid .= $formula[$j++];
}
if ($rid) {
$start = ++$j;
if (count($rids = split('~', $rid)) > 1) {
$rangeE = FALSE;
$rrows = array();
foreach ($liveDBTable['cells'] as $ri => $row) {
if (($row[$liveDBTable['priCol']]['innerHTML'] == $rids[0] or $rangeS) and !$rangeE) {
$rangeS = true;
if ($row[$liveDBTable['priCol']]['innerHTML'] == $rids[1]) {
$rangeE = true;
}
$rrows[] = $ri;
}
}
if ($rangeE) {
foreach ($rrows as $kei => $ri) {
getCells($cid, $ri, $dbTable, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
}
} elseif ($rid[0] == '/') {
foreach ($liveDBTable['cells'] as $ri => $row) {
if (preg_match($rid, $row[$liveDBTable['priCol']])) {
getCells($cid, $ri, $dbTable, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
}
} elseif ($rid == '*') {
getCells($cid, 'tHR', $dbTable, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
} elseif ($rid != '' and $rid != ' ') {
getCells($cid, rowWithPid($liveDBTable, $rid), $dbTable, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
} else {
getCells($cid, $crid, $dbTable, $liveDBTable, $sCs, $tp, $dbtUpdate, $sciri, $dbTable, $ccid);
}
}
$start = strpos($formula, $colName, $start);
}
} else {
$start = strpos($formula, $colName, $start);
}
}
}
putTableInFile($tp, $dbTable);
closeLiveTable($liveDBTable);
foreach ($aPSCs as $tid => $ca) {
$liveDBTable = getLiveTable($tid);
$tp = getTableFromFile($tid);
foreach ($ca as $i => $c) {
$c[1] = $c[1] ? $c[1] : 'tHR';
foreach ($tp[$c[1]][$c[0]]['f']['ee'] as $e => $ee) {
if ($ee == $sciri or $ee == $sciri . "," . $dbTable) {
unset($tp[$c[1]][$c[0]]['f']['ee'][$e]);
$dbtUpdate['tables'][$tid]['cells'][$c[1]][$c[0]]['f']['ee'] = $tp[$c[1]][$c[0]]['f']['ee'] = true_array_unique($tp[$c[1]][$c[0]]['f']['ee']);
break;
}
}
}
putTableInFile($tp, $tid);
closeLiveTable($liveDBTable);
unset($aPSCs[$tid]);
}
$sCs = true_array_unique($sCs);
return $sCs;
}
$classBorder=getBorderStyle("445cc1");//获取班级边框样式代码
$objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->applyFromArray($classBorder);//设置每个班级的边框
$info=$db->getDataByClassGrade($c_v['class'],$g_v['grade']);//查询每个班级的学生信息
$objSheet->setCellValue($nameIndex."3",$c_v['class']."班");//填充班级信息
$objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);//设置文字自动换行
$objSheet->setCellValue($nameIndex."4","姓名\n换行")->setCellValue($scoreIndex."4","分数");
$objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置某列单元格格式为文本格式
$j=5;
foreach($info as $key=>$val){
$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValue($scoreIndex.$j,$val['score']."21312321321321321321");//填充学生信息
//$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValueExplicit($scoreIndex.$j,$val['score']."12321321321321312",PHPExcel_Cell_DataType::TYPE_STRING);//填充学生信息
$j++;
}
$index++;
}
$endGradeIndex=getCells($index*2-1);//获得每个年级的终止单元格
$objSheet->mergeCells($gradeIndex."2:".$endGradeIndex."2");//合并每个年级的单元格
$objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('c1b644');//填充年级背景颜色
$gradeBorder=getBorderStyle("c144b1");//获取年级边框样式代码
$objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->applyFromArray($gradeBorder);//设置每个年级的边框
}
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//生成excel文件
//$objWriter->save($dir."/export_1.xls");//保存文件
browser_export('Excel5','browser_excel03.xls');//输出到浏览器
$objWriter->save("php://output");
function browser_export($type,$filename){
if($type=="Excel5"){
$info = $db->getDataByClassGrade($c_v['class'], $g_v['grade']);
//查询每个班级的学生信息
$objSheet->setCellValue($nameIndex . '3', $c_v['class'] . '班');
//填充班级信息
$objSheet->setCellValue($nameIndex . "4", "姓名")->setCellValue($scoreIndex . "4", "分数");
//在第四行，填充个人信息的表头的姓名和班级等名称
$j = 5;
//让学生的信息，从第五行开始填充
foreach ($info as $key => $val) {
$objSheet->setCellValue($nameIndex . $j, $val['username'])->setCellValue($scoreIndex . $j, $val['score']);
//填充学生的数据
$j++;
}
$index++;
}
$endGradeIndex = getCells($index * 2 - 1);
//获取每个年级终止单元格
$objSheet->mergeCells($gradeIndex . "2:" . $endGradeIndex . "2");
//合并每个年级的单元格
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//按照指定格式生成excel文件
//$objWriter->save($dir.'/file/demo3.xlsx'); //保存文件
browser_export('Excel2007', 'demo4.xlsx');
//输出到浏览器
$objWriter->save("php://output");
/**
* 根据下标获得单元格所在列位置
* @param $index
* @return mixed
*/
$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置excel文件默认水平垂直方向居中
$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");
//设置默认字体大小和格式
$title = array('id', '姓名', '手机号码', '省份', '地级市', '县级市', '具体地址', '物品', '订单号');
$data = array(array('10', '张三', '12345678901', '地球', '地球', '地球', '河边', '移动电源', 'hha1343'), array('11', '李四', '12345678902', '地球', '地球', '地球', '河边', '移动电源', 'hha1343'));
$objSheet->getStyle('C')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
for ($i = 1; $i < 10; $i++) {
$index = getCells($i - 1);
$objSheet->setCellValue($index . '1', $title[$i - 1]);
}
$j = 2;
foreach ($data as $value) {
$i = 0;
foreach ($value as $v) {
$in = getCells($i);
// echo $in."$j";
// echo $v.' ';
$objSheet->setCellValue($in . "{$j}", $v);
$i++;
}
// echo "<br/>";
$j++;
}
function getCells($value)
{
$arr = range('A', 'Z');
return $arr[$value];
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
browser_export('Excel5', '订单.xls');
