/**
* excel的导出
*/
public function outexcel(){
include_once VENDOR_PATH."PHPExcel/PHPExcel.php";
include_once VENDOR_PATH."PHPExcel/PHPExcel/IOFactory.php";
$objPHPExcel = new \PHPExcel();
/ar_dump($objPHPExcel);exit;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','用户名')
->setCellValue('B1','用户密码')
->setCellValue('C1','用户备注');
//设置单列宽度
//print_r($objPHPExcel);exit;
$objPHPExcel->getProperties()->setCreator("majunhong")
->setLastModifiedBy("majunhong")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setCategory("Test result file");
$model = new UserModel();//实例化模型
$rs=$model->checktables();
//dump($rs);exit;
$i=2;
// print_r($rs);exit;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','用户名')//设置表格中A1单元个为用户名
->setCellValue('B1','用户密码')//设置表格中B2单元格为用户密码
->setCellValue('C1','用户备注');//设置C1单元格为用户备注
$objPHPExcel->setActiveSheetIndex(0);
foreach($rs as $k=>$v){//$k为键值,$v为键值的内容
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i, $v['uname'])//将键值对应的内容传递给相应的单元格
->setCellValue('B'.$i, $v['upwd'])//将键值对应的内容传递给相应的单元格
->setCellValue('C'.$i, $v['beizhu']);//将键值对应的内容传递给相应的单元格
$i++;
//print_r($rs);exit;
}
$day = date("Y_m_d-H_i_s");//输出日期格式
$filename = $day.'导出模板.xls';
//dump($filename);exit;
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();
header("Content-Type: applicationnd.ms-excel; charset=utf-8");
header('Content-Disposition: attachment;filename='.$filename);
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
/**实现导入excel
**/
public function importexcel(){
if (!empty($_FILES)) {
$upload = new \Think\Upload();// 实例化上传类
$filepath='./Public';
$upload->exts = array('xlsx','xls');// 设置附件上传类型
$upload->rootPath = $filepath; // 设置附件上传根目录
$upload->saveName = 'time';
$upload->autoSub = false;
if (!$info=$upload->upload()) {
$this->error($upload->getError());
}
foreach ($info as $key => $value) {
unset($info);
$info[0]=$value;
$info[0]['savepath']=$filepath;
}
vendor("PHPExcel.PHPExcel");
$file_name=$info[0]['savepath'].$info[0]['savename'];
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($file_name,$encode='utf-8');
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$j=0;
for($i=3;$i<=$highestRow;$i++)
{
$data['uname'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
$data['upwd'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data['beizhu'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
M('User')->add($data);
$j++;
}
unlink($file_name);
$this->success('导入成功!本次导入用户数量:'.$j);
}else
{
$this->error("请选择上传的文件");
}
}
* excel的导出
*/
public function outexcel(){
include_once VENDOR_PATH."PHPExcel/PHPExcel.php";
include_once VENDOR_PATH."PHPExcel/PHPExcel/IOFactory.php";
$objPHPExcel = new \PHPExcel();
/ar_dump($objPHPExcel);exit;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','用户名')
->setCellValue('B1','用户密码')
->setCellValue('C1','用户备注');
//设置单列宽度
//print_r($objPHPExcel);exit;
$objPHPExcel->getProperties()->setCreator("majunhong")
->setLastModifiedBy("majunhong")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setCategory("Test result file");
$model = new UserModel();//实例化模型
$rs=$model->checktables();
//dump($rs);exit;
$i=2;
// print_r($rs);exit;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','用户名')//设置表格中A1单元个为用户名
->setCellValue('B1','用户密码')//设置表格中B2单元格为用户密码
->setCellValue('C1','用户备注');//设置C1单元格为用户备注
$objPHPExcel->setActiveSheetIndex(0);
foreach($rs as $k=>$v){//$k为键值,$v为键值的内容
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i, $v['uname'])//将键值对应的内容传递给相应的单元格
->setCellValue('B'.$i, $v['upwd'])//将键值对应的内容传递给相应的单元格
->setCellValue('C'.$i, $v['beizhu']);//将键值对应的内容传递给相应的单元格
$i++;
//print_r($rs);exit;
}
$day = date("Y_m_d-H_i_s");//输出日期格式
$filename = $day.'导出模板.xls';
//dump($filename);exit;
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();
header("Content-Type: applicationnd.ms-excel; charset=utf-8");
header('Content-Disposition: attachment;filename='.$filename);
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
/**实现导入excel
**/
public function importexcel(){
if (!empty($_FILES)) {
$upload = new \Think\Upload();// 实例化上传类
$filepath='./Public';
$upload->exts = array('xlsx','xls');// 设置附件上传类型
$upload->rootPath = $filepath; // 设置附件上传根目录
$upload->saveName = 'time';
$upload->autoSub = false;
if (!$info=$upload->upload()) {
$this->error($upload->getError());
}
foreach ($info as $key => $value) {
unset($info);
$info[0]=$value;
$info[0]['savepath']=$filepath;
}
vendor("PHPExcel.PHPExcel");
$file_name=$info[0]['savepath'].$info[0]['savename'];
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($file_name,$encode='utf-8');
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$j=0;
for($i=3;$i<=$highestRow;$i++)
{
$data['uname'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
$data['upwd'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data['beizhu'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
M('User')->add($data);
$j++;
}
unlink($file_name);
$this->success('导入成功!本次导入用户数量:'.$j);
}else
{
$this->error("请选择上传的文件");
}
}