php导出excel分析（phpexcel-避免数字前面的0消失）

最新推荐文章于 2023-02-16 10:16:28 发布

weixin_34362790

最新推荐文章于 2023-02-16 10:16:28 发布

阅读量914

点赞数

文章标签： php 人工智能数据库

原文链接：https://my.oschina.net/fedde/blog/132326

版权

为什么80%的码农都做不了架构师？>>>

注意事项：1.开启zip扩展 2.打开 open_basedir='' 3.打开xmlwriter xmlreader扩展

phpexcel导出excel例子：

$content = '数据库取出的数据';

$title = '连接关系比较';

$colname = 'CMDB数据-接口数据';
$url = $export->export($content,$title,$colname);
header('location:'.$url);

导出类：

<?php
/***********************
   cmdben_日期_ipmanage(table)表的分类导出
   注：导出2007版本文件，直接导出即可；导出2003版本，内容需为gbk编码
**********************/
class IpmanExport {

  public function export ($data, $title, $colname) {
   //导入PHPExcel类
    require_once './Classes/PHPExcel/IOFactory.php';
    require_once './Classes/PHPExcel/Writer/Excel2007.php';

   $objExcel = new PHPExcel();
   // $objWriter = new PHPExcel_Writer_Excel5($objExcel);
   $objWriter = new PHPExcel_Writer_Excel2007($objExcel);

   //设置文档基本属性
   $objProps = $objExcel->getProperties();
   $objProps->setCreator("CMDBen");
   $objProps->setLastModifiedBy("CMDBen");
   $objProps->setTitle("CMDBen data export");
   $objProps->setSubject("CMDBen data export");
   $objProps->setDescription("CMDBen data export");
   $objProps->setKeywords("CMDBen data export");
   $objProps->setCategory("CMDBen data export");

   $objExcel->setActiveSheetIndex(0);//设置活动的sheet是index为0的

   $objActSheet = $objExcel->getActiveSheet();//获得活动的sheet

   $objActSheet->setTitle($title);//设置当前活动sheet的title

   //整理数据导出
   // echo "<pre>";print_r($data);

   //列名
   $colNameArray = explode('-',$colname);

   //表头部分
   $objActSheet->setCellValue('A1', $title);
   $objActSheet->mergeCells('A1:C1');
   $objActSheet->setCellValue('A2', '盘点号');
   $objActSheet->setCellValue('B2', $colNameArray[0]);
   $objActSheet->setCellValue('C2', $colNameArray[1]);
    /*设置style*/
    //设置字体
    $objStyleA1 = $objActSheet->getStyle('A1');
    $objFontA1 = $objStyleA1->getFont();
    $objFontA1->setName('宋体');
    $objFontA1->setSize(14);
    $objFontA1->setBold(true);
    // $objFontA5->getColor()->setARGB('FF999999');

    //设置对齐方式
    $objAlignA1 = $objStyleA1->getAlignment();
    $objAlignA1->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objAlignA1->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objStyleA2 = $objActSheet->getStyle('A2');
    $objAlignA2 = $objStyleA2->getAlignment();
    $objAlignA2->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objAlignA2->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objStyleB2 = $objActSheet->getStyle('B2');
    $objAlignB2 = $objStyleB2->getAlignment();
    $objAlignB2->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objAlignB2->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objStyleC2 = $objActSheet->getStyle('C2');
    $objAlignC2 = $objStyleC2->getAlignment();
    $objAlignC2->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objAlignC2->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

   //表格内容部分
   $rowNum = 3;//初始行号
   $overMark = true;//此列是否为空的标记
   $lackMark = true;//此列是否为空的标记
   foreach ($data as $value){
   //设置单元格格式为文本格式
    $objActSheet->setCellValueExplicit('A'.$rowNum,$value['asset'],PHPExcel_Cell_DataType::TYPE_STRING);
    $objActSheet->getStyle('A'.$rowNum)->getNumberFormat()->setFormatCode("@");

    $objActSheet->setCellValue('B'.$rowNum, $value['over']);
    $objActSheet->setCellValue('C'.$rowNum, $value['lack']);
    ++$rowNum;
    if($value['over']=='') $overMark = $overMark&&false;
    if($value['lack']=='') $lackMark = $lackMark&&false;
   }

   $objActSheet->getColumnDimension('A')->setWidth(15);
   $overMark ? $objActSheet->getColumnDimension('B')->setAutoSize(true) : $objActSheet->getColumnDimension('B')->setWidth(30);
   $lackMark ? $objActSheet->getColumnDimension('C')->setAutoSize(true) : $objActSheet->getColumnDimension('C')->setWidth(30);

   $date = date('ymdHis');
   if(is_dir('export')){
    $outputFileName = "export/ipmanage_report_".$date.".xlsx";
    $objWriter->save("./".$outputFileName);

    return $outputFileName;
   }
  }
}
?>