最近公司项目中使用PHPExcel进行表格的处理问题。
$phpExcel =new PHPExcel(); //创建一个空白表格
$phpSheet = $phpExcel->getActiveSheet(); //获取当前操作的表
$phpSheet->setTitle("sheet1");//给表格命名
$phpSheet->getDefaultStyle()->getAlignment()->setVertical(phpExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(phpExcel_Style_Alignment::HORIZONTAL_CENTER); //设置表格的默认样式->宽高->居中.
$phpSheet->getStyle('A1:G1')->getFont()->setName("宋体")->setSize('24')->setBold(true);//表格A1到G1这一行字体和大小加粗。其他样式设置同理
$phpSheet->getColumnDimension('A')->setWidth(16); //设置表格A列宽16;
$phpSheet->getStyle('C3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);//设置C3的数据格式为YYYY/MM/DD;
$phpSheet->getStyle('A')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置A列为文本格式
$phpSheet->mergeCells('A1:G1')->setCellValue("A1","班级1")//合并A1到G1并设置值 ,
$phpSheet->setCellValue("A4","产品条码") //设置A4的值
//边框
$styleArray = array(
'borders' => array(
'allborders' => array(
//'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
//'color' => array('argb' => 'FFFF0000'),
),
),
);
$phpSheet->getStyle('A2:G'.$j)->applyFromArray($styleArray); //设置边框
$newnames = date('YmdGis').rand(1000,9999);
ob_end_clean();防止乱码
//文件保存
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$newnames.".xls");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel,"Excel5");
$objWriter->save($dir."/shop/Excel/".$newnames.".xls");
2.打开Excal文件填写数据
$inputFileName = './Excel/xxx.xls';
if(!file_exists($inputFileName)){
exit("文件不存在");
}
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
$phpSheet = $objPHPExcel->getActiveSheet();
} catch(Exception $e) {
die(pathinfo($inputFileName,PATHINFO_BASENAME).$e->getMessage());
}
$phpSheet->setCellValue("F2",'aaaa');//写入数据
$phpSheet->setCellValue("F3",PHPExcel_Shared_Date::PHPToExcel(2019-9-12,true)); //设置时间格式
//保存
。。。