- /**
- * 设置导出的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 = $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 = strtotime($value);
- }
- $value = 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");
来源: http://www.bubuko.com/infodetail-1976783.html