首页 > 代码库 > 设置导出的excel数据

设置导出的excel数据

/**
* 设置导出的excel数据
* @param type $objPHPExcel
* @param type $colModel
* @param type $grid
*/
public function setExcelData($objPHPExcel, $colModel, $grid)
{
$table = $this->model->_table;
for ($i = 0, $t = 0; $i < count($colModel); $i++) {
$label = $colModel[$i][‘label‘];
$name = $colModel[$i][‘name‘];
$hidden = empty($colModel[$i][‘hidden‘]) ? FALSE : $colModel[$i][‘hidden‘];
$key = empty($colModel[$i][‘key‘]) ? FALSE : $colModel[$i][‘key‘];
$sorttype = empty($colModel[$i][‘sorttype‘]) ? ‘string‘ : $colModel[$i][‘sorttype‘];
$width = empty($colModel[$i][‘width‘]) ? 20 : $colModel[$i][‘width‘] / 8;
$isEdit = empty($colModel[$i][‘editable‘]) ? FALSE : $colModel[$i][‘editable‘];
if ($isEdit && !empty($name) && strstr($name, "go_" . $table . ".") == $name) {
$isEdit = TRUE;
} else {
$isEdit = FALSE;
}
if ($hidden && !$key) {
continue;
}
if ($key) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($t, 1, $label)->getStyleByColumnAndRow($t, 1)->getFont()->setBold(true)->getColor()->setRGB(‘FF0000‘);
} else if ($isEdit) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($t, 1, $label)->getStyleByColumnAndRow($t, 1)->getFont()->setBold(true)->getColor()->setRGB(‘00FF00‘);
} else {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($t, 1, $label)->getStyleByColumnAndRow($t, 1)->getFont()->setBold(true);
}
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($t)->setWidth($width);
for ($j = 0; $j < count($grid["rows"]); $j++) {
$item = $grid["rows"][$j];
$value = http://www.mamicode.com/$item[$name];
if ($sorttype === ‘image‘ && !empty($value)) {
$url = $this->saveImage("statics/uploads/export_excel_images/", $value);
if (!$url || !isImage($url)) {
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($t, $j + 2, $value, PHPExcel_Cell_DataType::TYPE_STRING);
continue;
}
$img = new PHPExcel_Worksheet_Drawing();
$img->setName($name);
$img->setDescription($name);
$img->setPath($url);
$img->setWidth(40); //写入图片宽度
$img->setHeight(40); //写入图片高度
$img->setOffsetX(1); //写入图片在指定格中的X坐标值
$img->setOffsetY(1); //写入图片在指定格中的Y坐标值
$img->setRotation(1); //设置旋转角度
$img->getShadow()->setVisible(true);
$img->getShadow()->setDirection(50);
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($t);
$coordinate = $columnLetter . ($j + 2);
$img->setCoordinates($coordinate); //设置图片所在表格位置
$img->setWorksheet($objPHPExcel->getActiveSheet()); //把图片写到当前的表格中
} else if ($sorttype === ‘datetime‘ && !empty($value)) {
if (strtotime($value)) {
$value = http://www.mamicode.com/strtotime($value);
}
$value = http://www.mamicode.com/date("Y-m-d H:m:s", $value);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($t, $j + 2, $value, PHPExcel_Cell_DataType::TYPE_STRING);
} else if ($sorttype === ‘number‘) {
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($t, $j + 2, $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
} else {
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($t, $j + 2, $value, PHPExcel_Cell_DataType::TYPE_STRING);
}
}
$t++;
}
}


 //开始导出列表
$grid = array("page" => $page, "records" => $day_num, "rows" => $rowsV, "total" => $total);
writelog2([‘msg2‘ => $this->lang->line(‘txt_base_upload‘) . ‘2‘, ‘settlement_list_export_post_grid‘ => $grid]);
writelog([‘msg2‘ => $this->lang->line(‘txt_base_upload‘) . ‘2‘, ‘settlement_list_export_post_grid‘ => $grid]);
$colModel = json_decode($colModel, TRUE);
require_once APPPATH . ‘/third_party/PHPExcel.php‘;
require_once APPPATH . ‘/third_party/PHPExcel/Writer/Excel5.php‘;
$title = empty($title) ? "gplqdb" : $title;
$fileName = $title . date("YmdHis");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($this->lang->line(‘txt_base_web_name‘));
$objPHPExcel->getProperties()->setLastModifiedBy("1.gaopeng.com");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Document");
$objPHPExcel->getProperties()->setDescription($this->lang->line(‘txt_base_web_name‘));
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);
//填充数据
$this->setExcelData($objPHPExcel, $colModel, $grid);
$objPHPExcel->getActiveSheet()->setTitle($title);
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$pub_dir = FCPATH . "statics/uploads/export_excels/";
if (!is_dir($pub_dir)) {
@mkdir($pub_dir, 0777, TRUE);
}
$file_name = $pub_dir . $fileName . ".xls";
$objWriter->save($file_name);

$this->returnData($_SERVER[‘SERVER_NAME‘]."/statics/uploads/export_excels/".$fileName . ".xls");

设置导出的excel数据