导出excel:
转自:
http://blog.csdn.net/hwhjava/article/details/48023517
- header("Content-type: text/html; charset=utf-8");
- include_once dirname(dirname(__FILE__)).'/Classes/PHPExcel.php';
- $objPHPExcel = new PHPExcel();
- $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
- ->setLastModifiedBy("Maarten Balliauw")
- ->setTitle("Office 2007 XLSX Test Document")
- ->setSubject("Office 2007 XLSX Test Document")
- ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
- ->setKeywords("office 2007 openxml php")
- ->setCategory("Test result file");
- //设置标题
- $objPHPExcel->getActiveSheet()->setTitle($filename);
- //设置表头
- $key1 = 1;
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue('A'.$key1, 'ID')
- ->setCellValue('B'.$key1, '关键词')
- ->setCellValue('C'.$key1, '歌曲HASH')
- ->setCellValue('D'.$key1, '歌曲名')
- ->setCellValue('E'.$key1, '排序')
- ->setCellValue('F'.$key1, '开始日期')
- ->setCellValue('G'.$key1, '结束日期')
- ->setCellValue('H'.$key1, '操作人');
- //设置样式:
- $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true); //多个单元格
- // $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->getColor()->setARGB('FFFF0000'); //设置颜色
- // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); //单个单元格
- $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //列宽必须单个设置
- $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
- //写入内容
- foreach($datalist as $key =>$value){
- $key1=$key+2;
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue('A'.$key1, $value['id'])
- ->setCellValue('B'.$key1, $value['keyword'])
- ->setCellValue('C'.$key1, $value['hash'])
- ->setCellValue('D'.$key1, $value['filename'])
- ->setCellValue('E'.$key1, $value['weight'])
- ->setCellValue('F'.$key1, $value['startdate'])
- ->setCellValue('G'.$key1, $value['enddate'])
- ->setCellValue('H'.$key1, $value['editor']);
- }
- // $objPHPExcel->setActiveSheetIndex(0);
- header('Content-Type: application/vnd.ms-excel');
- header('Content-Disposition: attachment;filename="'. $filename .'.xls"');
- header('Cache-Control: max-age=0');
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save('php://output');
- exit;
从excel导入:
- header("Content-type: text/html; charset=utf-8");
- $addFile=$_FILES['excel'];
- $excelFileName =$addFile['tmp_name'];
- $location = KG_DOMAIN . '/diysort/index';
- if ($addFile['type'] != 'application/vnd.ms-excel' && $addFile['type'] != 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
- echo '<script type="text/javascript">alert("上传格式错误");window.location.href="' . $location .'";</script>';
- //header("Location: " . KG_DOMAIN . '/diysort/index');
- exit;
- }
- include_once dirname(dirname(__FILE__)).'/Classes/PHPExcel/IOFactory.php';
- $objPHPExcel = PHPExcel_IOFactory::load($excelFileName);
- $objWorksheet = $objPHPExcel->getSheet(0);
- $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
- $highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
- $highestColumnIndex = PHPExcel_Cell::columnIndexFromString ( $highestColumn );
- $excelData = array ();
- for($row = 1; $row <= $highestRow; $row++) {
- for($col = 0; $col < $highestColumnIndex; $col++) {
- $excelData[$row-1][] = $objWorksheet->getCellByColumnAndRow( $col, $row )->getValue();
- }
- }
- //数据入库
- $diysortmodule = new DiysortModule();
- $rowins = 0;
- foreach ($excelData as $key => $value) {
- if (empty($value) || trim($value[0]) == '关键词' || trim($value[0] == 'ID')) {
- continue;
- }
- $keyword = trim(addslashes($value[0]));
- $hash = trim(addslashes($value[1]));
- $filename = trim(addslashes($value[2]));
- $weight = intval($value[3]);
- $startdate = trim(addslashes($value[4]));
- $enddate = trim(addslashes($value[5]));
- $adddate = date('Y-m-d H:i:s',time());
- $input_data = array(
- 'keyword' => $keyword,
- 'startdate' => $startdate,
- 'enddate' => $enddate,
- 'hash' => $hash,
- 'filename' => $filename,
- 'weight' => $weight,
- 'adddate' => $adddate,
- 'editor' => $this->editor,
- );
- if (!empty($hash) && !empty($filename) && is_numeric($weight)) {
- $newid = $diysortmodule->addData($input_data);
- if ($newid > 0) {
- $rowins ++;
- }
- }
- }
- echo '<script type="text/javascript">alert("共上传成功'. $rowins.' 条记录");window.location.href="' . $location .'";</script>';
- exit;