php excel 导入导出源代码

导入

require_once './includes/reader.php';
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('gbk');
//”data.xls”是指要导入到mysql中的excel文件
$data->read('date.xls');
@ $db = mysql_connect('localhost', 'root', '1234') or
die("Could not connect to database.");//连接数据库
mysql_query("set names 'gbk'");//输出中文
mysql_select_db('wenhuaedu'); //选择数据库
error_reporting(E_ALL ^ E_NOTICE);
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
//以下注释的for循环打印excel表数据
/*
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo """.$data->sheets[0]['cells'][$i][$j]."",";
}
echo "n";
*/
//以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！
$sql = "INSERT INTO test VALUES('".
$data->sheets[0]['cells'][$i][1]."','".
$data->sheets[0]['cells'][$i][2]."','".
$data->sheets[0]['cells'][$i][3]."')";
echo $sql.'<br />';
$res = mysql_query($sql);

 

 

ini_set('memory_limit', '-1');
  set_time_limit(0);

  $callStartTime = microtime(true);

  PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
     $objExcel = new PHPExcel();

     if (isset($datas) && isset($startdate) && isset($enddate)){
   $title = time();

   $objProps = $objExcel->getProperties();
   $objProps->setCreator("cbsi ");
   $objProps->setLastModifiedBy("report for finance");
   $objProps->setTitle("report for finance");
   $objProps->setSubject("report for finance");
   $objProps->setDescription("report for finance");
   $objProps->setKeywords("report for finance");
   $objProps->setCategory("report for finance");
   $objExcel->setActiveSheetIndex(0);
   $objActSheet = $objExcel->getActiveSheet();
   //设置当前活动sheet的名称
   $objActSheet->setTitle('report for finance');
             /////////////设置标题
   $already_income_amount = 0;
   $invoice_amount = 0;
   $The_invoices_amount = 0;
   foreach ($datas as $key => $val){
    $already_income_amount = $already_income_amount + $val['already_income_amount'];
    $invoice_amount = $invoice_amount + $val['invoice_amount'];
    $The_invoices_amount = $The_invoices_amount + $val['The_invoices_amount'];
   }

   foreach ($datas as $key => $val){
        $key = $key+3;

     //$objActSheet->setCellValue('A'.$key, $val['id']);
     $objActSheet->setCellValue('A'.$key, $key-2);
     $objActSheet->setCellValue('B'.$key, $val['company_name']);
     $objActSheet->setCellValue('C'.$key, $val['bu_name']);
     $objActSheet->setCellValue('D'.$key, $val['customer_name']);
     $objActSheet->setCellValue('E'.$key, $val['contract_number']);
     $objActSheet->setCellValue('F'.$key, $val['already_income_amount']);
     $objActSheet->setCellValue('G'.$key, $val['invoice_amount']);
     $objActSheet->setCellValue('H'.$key, $val['The_invoices_amount']);
     $objActSheet->setCellValue('I'.$key, $val['Differences_note']);
                    $objExcel->getActiveSheet()->setSharedStyle(Custom_ExcelStyle::line(), "A".$key.":I".$key);

   }

        $count = count($datas)+3;  //=27
        $objActSheet->setCellValue("A"."$count", '总计');
        $objActSheet->setCellValue("B"."$count", '-');
        $objActSheet->setCellValue("C"."$count", '-');
  $objActSheet->setCellValue("D"."$count", '-');
  $objActSheet->setCellValue("E"."$count", '-');
  $objActSheet->setCellValue("F"."$count", $already_income_amount);
  $objActSheet->setCellValue("G"."$count", $invoice_amount);
  $objActSheet->setCellValue("H"."$count", $The_invoices_amount);
  $objActSheet->setCellValue("I"."$count", '-');

        $objExcel->getActiveSheet()->setSharedStyle(Custom_ExcelStyle::total(), "A".$count.":I".$count);
        //表格第一行内容
        $objActSheet->setCellValue('A1', 'CCOM月度/季度/年度销售及发票开具情况统计表');
        $objActSheet->setCellValue('A2', '日期：'.$startdate."到".$enddate);
  //显式指定内容类型
  //$objActSheet->setCellValueExplicit('A5', '847475847857487584',PHPExcel_Cell_DataType::TYPE_STRING);
  //合并单元格
  $objActSheet->mergeCells('A1:I1');
  $objActSheet->mergeCells('A2:I2');
  //设置表格内容居中
     //$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  //分离单元格
  //$objActSheet->unmergeCells('B1:C22');
  //*************************************
  //设置宽度
  $objActSheet->getColumnDimension('A')->setWidth(10);
  $objActSheet->getColumnDimension('B')->setWidth(40);
  $objActSheet->getColumnDimension('C')->setWidth(30);
  $objActSheet->getColumnDimension('D')->setWidth(40);
  $objActSheet->getColumnDimension('E')->setWidth(20);
  $objActSheet->getColumnDimension('F')->setWidth(30);
  $objActSheet->getColumnDimension('G')->setWidth(30);
  $objActSheet->getColumnDimension('H')->setWidth(40);
  $objActSheet->getColumnDimension('I')->setWidth(30);

  $objExcel->createSheet();
        $objExcel->getActiveSheet()->setSharedStyle(Custom_ExcelStyle::head(), "A1:I3");

  $objWriter = new PHPExcel_Writer_Excel2007($objExcel);  
  $objWriter->setUseDiskCaching(true);
  $outputFileName = "download/excel/";
  $outputFileName .= Custom_Session::Get('id')."invoice_issue".".xlsx";
        $objWriter->save($outputFileName);
  //$objPHPExcel->disconnectWorksheets();
  unset($objPHPExcel);
  }
  $back =  array(1,$outputFileName);
        return $back;