导出excel:
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;